How to Calculate Each Component of a Loan

The idea behind a loan is simple.

You are borrowing funds from someone else that you promise to pay back at a later time.

The simplicity ends there for the average person.

Loans quickly get complicated when you factor in what type of loan is being taken out, the interest rate being charged, the length of time you have to pay back the loan, and several other things as well.

In this post, we’ll walk through the main components to a loan and how to calculate each component.

In real life, you’ll find yourself given numbers and figures on a loan offer and you’ll need to know what to do with them.

After this post, you’ll know how to calculate any variable in a loan and this knowledge will be useful in making sense of the numbers and making smarter financial decisions.

WHAT ARE THE MAIN COMPONENTS OF A LOAN?

• Loan Balance
• Interest Rate
• Loan Term
• Monthly Loan Payment
• Number of Payments
• Total Amount Paid
• Interest Paid

Loan Balance

The loan balance (or principal)  is the amount left on the loan that you are required to pay back.

When you first take out the loan, the loan balance is the amount you decided to borrow.

Between that time and the end of the term on the loan, the loan balance is the remaining amount left to be paid.

For example, you take out a loan for $10,000. At the start of the loan, that is the loan balance. After several years of payments, your loan balance may be $6,000, meaning $6,000 still needs to be paid back.

Interest Rate

An interest rate is a percentage that is charged on top of the principal loan balance that must be paid back by the borrower.

It is the cost of borrowing money. When a lender gives you a loan, they charge an interest rate for giving you that money. This is the primary way lenders make money.

Interest rates vary based on several factors:

• Type of loan
• Term of loan
• Amount of down payment
• Creditworthiness
• Geographical location

One of the common factors in interest rates that people have the most control over is creditworthiness.

If you are deemed responsible with your credit and have a history of paying your bills on time, you likely have a higher credit score and will be seen as a “low-risk” borrower to the lender. Low-risk borrowers are typically rewarded with the lowest interest rates.

For higher-risk borrowers, lenders need to receive a higher return on the funds they lend out for the heightened risk so they charge higher interest rates to those with low creditworthiness.

Loan Term

The loan term is the length of time that you are required to make payments until you fully pay off the loan.

Common loan terms for a mortgage include a 15-year and 30-year terms.

For auto loans, a 6-year term is typical and means that you will have a maximum of 6 years to pay off your loan.

The term of the loan can be whatever length of time you and the lender are able to negotiate and the balance of the loan can be paid off anytime before the term ends unless stated otherwise.

Monthly Loan Payment

The monthly loan payment is just that. It is the amount you will pay each month towards your loan balance.

The monthly payment amount can include payment towards just your principal balance, or it can include payment towards your principal balance and the interest accumulated.

For example, if your monthly payment is $200 and it is all applied to the principal balance, your principal balance would decrease by $200 and you are $200 closer to paying off your debt.

On the other hand, if your monthly payment is $200, and interest is included in that, you can find that only $180 of that $200 goes towards paying down the principal loan balance and $20 goes to the lender as interest.

In this case, you paid $200, but you are only $180 closer to paying off your debt. That is the cost of doing business with a lender and borrowing their funds.

Number of Payments

The number of payments is how many times you will make a payment towards your loan over the term of your loan.

If your loan term is 10 years and you are expected to pay monthly, your number of payments will be 120.

Total Amount Paid

Total amount paid is how much you will have paid to the lender when you finally pay off your debt.

It will be the original loan balance plus the total amount of interest you paid on the loan.

This is the real value that needs to be considered when taking out a loan. One should ask themselves, “What is the total amount I am going to pay back when this is all said and done?”

Interest Paid

Interest paid is the amount of interest you have paid on your loan.

You can calculate how much interest you paid on one monthly payment and how much interest you have paid over a period of time within the initial borrowing of the loan and the final payment of the debt. 

The latter would be total interest paid and shows you the total cost of borrowing the loan.

For example, if you pay off your 6-year car loan and see that you paid $10,000 in interest in total, your total cost of borrowing for that loan was $10,000.

HOW TO CALCULATE EACH PART OF A LOAN

When dealing with loans, you can find yourself in a scenario where you have values for most of the components of a loan but need to solve for one.

You might be presented with information and ask yourself:

• What will my loan balance be in 5 years?
• What interest rate do I need if I only can afford to pay $400 a month?
• How many years would it take me to pay off this loan?
• What is the total amount of interest I’ll pay at the end of this 10-year term?

Using simple functions in Microsoft Excel, I’ll walk you through how to calculate each component of a loan so you can answer these questions.

Monthly Payment

We’ll first begin with the calculation for monthly payment. To calculate monthly payment we will need to have the variables…

• Loan balance
• Interest rate
• Loan term

You’ll see in the screenshot, that I divided the annual interest rate by 12 to show the monthly interest rate and I also multiplied the loan term by 12 to get the total amount of months requiring payment.

We are calculating monthly payment, so both of those variables will need to be in the monthly format.

Once the formatting has been set, we will use the PMT function in Excel.

The syntax of the PMT function is:

=PMT(rate,nper,pv,[fv],[type])

For our example, the arguments to the PMT function are as follows:

• Rate = 0.42% (note: this is monthly)
• Nper = 120 (also monthly)
• Pv = $20,000
• Fv = $0 (our future value is 0 because once the loan is paid off in the future, the balance will be $0)
• Type = 0 (For type, you have the option of 1 or 0. 1 is if you pay your monthly payment at the beginning of each period. 0 is for paying your month at the end of the period. Typically, loans are paid at the end of a period. For example, if you take out a loan, your first payment is typically at the end of month 1. So for type, we will enter 0.

Fill in the arguments of the PMT function, press enter, and the function will output -$212.13.

This is the monthly payment amount. The amount is negative because a payment is an outflow of cash from you.

Loan Balance

Sometimes you wonder what your loan balance will be after a certain amount of time if you make your monthly payments each month.

Using the FV function in Excel, we can calculate the future value of your loan amount.

In this example, we’ll see what balance will be remaining after you have made your payments for 6 years.

The syntax for the FV function is

=FV(rate,nper,pmt,[pv],[type])

Our values in our example for this argument will be:

• Rate = 0.42%
• Nper = 72 (months you have made a monthly payment)
• Pmt = -$212.13
• Pv = $20,000 (initial loan amount)
• Type = 0

When you press enter, the FV function will return the value -$9,211. This is how much is left on your loan balance to pay after making 6 years of monthly payments.

If you type 0 in for nper, you’ll get back a loan balance of -$20,000. 0 means you have not made a payment yet, so it makes sense the loan balance value would be -$20,000.

If you type in 120 for nper, you will get back a loan balance of $0. Typing in 120 means you have made all 120 payments over the 10 year term so the function returning the value $0 makes sense.

Interest Rate

To calculate the interest rate of the loan, we will use the RATE function in Excel.

The syntax of the rate function is:

=RATE(nper,pmt,pv,[fv],[type][guess])

Our values for the arguments of the RATE function will be:

• Nper = 120
• Pmt = -$212.13
• Pv = $20,000
• [fv] = 0
• [type] = 0
• [guess] is not needed for this calculation so we won’t enter anything for this.

When you press enter, the RATE function will return 0.42%. Note that this is the monthly rate since our arguments for nper and pmt were in the monthly format.

To calculate the yearly rate, simply multiply the output of the RATE function by 12 to receive the annual interest rate of 5%.

Number of Payments and Loan Term

To calculate our loan term, we will use the NPER Excel function to first calculate our number of payments.

The syntax of the NPER function is:

=NPER(rate,pmt,pv,[fv],[type])

Our values for the arguments of the NPER function will be:

• Rate = 0.42%
• Pmt = -$212.13
• Pv = $20,000
• Fv = $0
• Type = 0

Hit enter and the NPER function will return 120. This value is in months and represents the total number of payments. 

To find the loan term in years, simply divide 120 by 12 to arrive at the loan term value of 10 years.

Interest Paid for a Specific Month

What if you want to know how much interest you were charged for any given month during the loan?

Well, we can use the IPMT function in Excel to find that out.

In this example, we will calculate how much interest we will pay in month 1.

The syntax for IPMT is:

=IPMT(rate,per,nper,pv,[fv],[type])

Our values for the arguments of the IPMT function will be:

• Rate = 0.42%
• Per = 1 (This is the period or month you want to know how much interest you will be paying. We are looking at month 1, so this will be 1. If we wanted to look at month 60, we would enter 60 for per)
• Nper = 120
• Pv = $20,000
• Fv = $0
• Type = 0

When you hit enter, the IPMT function will return -$83.33. This is how much you will pay in interest at the end of month 1.

So if your monthly payment is $212.13, this means $83.33 of that will be used to pay interest and $128.80 of that will be used to pay down your principal in month 1.

If you change the per argument from 1 to 60 (meaning you want to know how much interest you will pay in month 60) the IPMT function will return -$47.52.

This value is lower than the interest paid in month 1.

This makes sense because over time, you will pay down your loan balance and the amount of interest charged to that declining loan balance will also decline.

Total Payments and Total Interest Paid

We’ll group these two into one screenshot.

Before you sign the papers to take out a loan, you’ll want to run these calculations here to make sure you know what you are getting into regarding total interest paid and the total amount paid.

First, calculate the cumulative amount of interest paid over the term of the loan: 10 years

We will do this with the CUMIPMT function in Excel.

The syntax of the CUMIPMT function in Excel is:

=CUMIPMT(rate,nper,pv,start_period,end_period,type)

The values for these arguments in our example will be:

• Rate = 0.42%
• Nper = 120
• Pv = $20,000
• Start_period = 1 (1 represents month 1, which is your first payment on your loan
• End_period = 120 (120 represents month 120, which is your final payment on your loan
• Type = 0

When you hit enter the CUMIPMT function will return -$5,455.72. This is the cumulative or total amount of interest you will pay over the course of the 10-year loan term.

Think of this value as the cost of borrowing. To borrow $20,000 for 10 years, you had to pay $5,455.72 for it.

To find your cumulative amount paid, simply add the -$5,455.72 to the -$20,000 principal loan balance.

The result is -$25,455.72. This means when things are all said and done and you have made all 120 payments to pay off your loan balance, you will have paid a total of $25,455.72.

SUMMARY

A loan is made up of several important variables.

You may know all the variables or may lack one that you need to calculate.

Using the information provided in the post, you can use simple Excel functions to answer any questions you have related to loan balances, interest rates, loan term, payments, etc.

About Post Author