Sign in or start a free trial to avail of this feature.
4. Loan Repayments
Excel has some great functions for tricky loan repayment calculations. Here, I show how to calculate the principal and interest payments over the life of a mortgage loan.
The Amortizing Loan (00:03)
An amortizing loan is one where the principal is paid back over the life of the loan, typically in a series of equal payments. Here are the important terms associated with an amortizing loan:
- The amount taken out on the loan is called the principal
- The interest rate is a percentage
- The loan term is measured in years
Calculating Loan Payments (00:14)
We want to calculate the annual payment that will leave us with a principal of zero at the end of the term. We can find this payment using the PMT function. PMT takes three arguments. First is the interest rate. Second is the number of periods, or the loan term. Third is the present value of the loan, or the principal.
If you want to pay back the loan monthly instead of annually, PMT can accommodate this. We simply divide the interest rate by 12 to get the monthly interest rate, and multiply the number of periods by 12 to get the number of months.
Creating a Payment Schedule (02:24)
The PMT function doesn’t tell you how much of a loan repayment is interest and how much is principal. To find this out, we can create a year-by-year payment schedule for the mortgage, in which we calculate the interest and principal payments for each year.
We do this using the functions IPMT and PPMT. IPMT calculates the interest payment for a given year, and PPMT calculates the principal payment. Both functions take the same four arguments. First is the interest rate of the loan. Second is the period whose payment we are calculating. Third is the total number of periods. Fourth is the loan principal.
For each period, the ending balance of the loan is the opening balance minus the principal repayment. When we complete the schedule for each year, we can see that as the loan goes on, we pay back more principal and less interest each year.
In business, there are many different types of loans, from the short-term bank overdraft to corporate and government bullet bonds. However, the loan you're probably most familiar with, is the amortizing loan. Where the principal of the loan is paid back over the life of the loan. Typically through a set of equal payments. In this lesson, I'm going to show you how to calculate the repayments on an amortizing loan using the example of a home mortgage. Let's start off with some assumptions. I'm going to take out a loan of $400,000 to buy my house, and this cell will be called the principal. The interest rate from the bank will be 4%, which I'll call the interest rate and the term for the loan will be 20 years. I now need to calculate the amount I pay back every year so that at the end of the term, the principal is zero. Excel calls this amount PMT. To calculate PMT, I'll write equals PMT, and open the bracket. And the first value we must put in is the interest rate.
That's why I'll add the interest rate and write a comma. The next value is the number of periods, which in this case is the long-term.
Next, is the present value of the loan, which is the principal.
And the final two values are optional and we don't need to put them in here. So, I'll close the bracket and press enter.
This calculation now tells us that the yearly repayment on our loan would be $29433, if we wanted to pay it off in 20 years.
You might have noticed that the sign of the repayment is negative, while the principal is positive. This will always be the case, because I receive money with the principal but I pay back the repayments. So clearly, they should have different signs. Now, what happens if you wanna pay back your loan monthly? Well, all we do is change the number of periods, and the interest rate and the formula. Let's write equals PMT again.
And this time, I take the interest rate and divide it by the number of months in the year.
I now take the number of periods and multiply it by the number of months.
I then add the principal as before and close the bracket.
And when I press enter, this tells me that my monthly repayments on the loan are $2424. As you can see, PMT is a really useful inbuilt function for calculating loan repayments. But when you're paying off a loan, you often want greater visibility on how much is remaining on the principal. as you make your payments over time. To do this, you must create a year-by-year payment schedule for the mortgage. Let's start off with the beginning balance. So the beginning balance in year one, is simply the end balance in year zero, which is the principal amount.
We now need to calculate the interest payment on this beginning balance. Thankfully, Excel has an inbuilt function for doing this, called IPMT. So I'll write equals IPMT, open the bracket and write interest rate.
I'll then write the period, which is number one.
Next, I'll write the number of periods, which is the loan term.
And then finally, the present value which is the principal.
Then close the bracket and press tab.
This gives me an interest repayment of $16,000 on the $400,000 in year one. Which makes sense because 16,000 is 4% of 400,000.
Now I need to calculate the principal. And this is also completed with an inbuilt function, PPMT.
I'll just include the same inputs again.
Again, the period is number one. The number of periods is the loan term and the present value is the principal.
Close the bracket and press tab.
And now the ending balance is equal to the beginning balance plus the principal repayment.
We can then complete this formula for each of the years. So I'll copy, select remaining Cells and Alt + ESF.
This table now shows us how the principal is paid down over time. As you can see at the start of the loan, the repayments is split almost evenly between interest and the principal. But over time, as the principal is paid down and the interest payments decrease, more of the principal is paid back every year. All amortizing loans tend to show this characteristic. Year-by-year schedules such as this, also allow you to test scenarios, such as paying down some of the principal early, increasing the size of your repayments, or even paying interest only for the first few years.