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.
=PMT: Calculate annual payment for amortizing loan
--- rate = Annual Interest rate
--- nper = Number of periods (years)
--- PV = Loan principal
To calculate monthly repayments, divide rate by 12 and multiply nper by 12
Year-by-year loan calculations
=IPMT: Calculates interest payment for a given year of the loan
=PPMT: Calculates principal payment for a given year of the loan
--- rate = Interest rate
--- per = Current period number
--- nper = Number of periods
--- PV = Loan principal
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
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 principle.
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 principle is zero.
Excel calls this amount PMT.
To calculate PMT, I write =PMT, and open the bracket, and the first value we must put in is the interest rate.
So I'll add the interest rate and write a comma.
The next value is the number of periods, which in this case is the loan term.
Next is the present value of the loan, which is the principle.
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 $29,433 if we wanted to pay it off in 20 years.
You might have noticed that the sign of the repayment is negative, while the principle is positive. This will always be the case, because I receive money with the principle, but I pay back the repayments, so clearly they showed up different signs.
Now what happens if you want to pay back your loan monthly? Well, all we do is change the number of periods and the interest rate in the formula.
Lets write =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 principle as before, and close the bracket.
And when I press Enter, this tells me that my monthly repayments on the loan are $2,424.
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 principle, as you make your payments over time.
To do this, we 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 principle 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 =IPMT, open the bracket and write interest rate.
I'll then write the period, which is number 1.
Next I'll write the number of periods, which is the loan term.
And then finally, the present value, which is the principle.
Then close the bracket and press Tab.
And 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.
And now I need to calculate the principle, and this is also completed with an inbuilt function, PPMT I'll just include the same inputs again.
Again, the period is number 1, the number of periods is the loan term, and the present value is the principle.
Close the bracket and press Tab.
And now the ending balance is equal to the beginning balance, plus the principle repayment.
We can then complete this formula for each of the years.
So I'll copy, select the remaining cells, and Alt + E S F.
This table now shows us how the principle is paid down over time.
As you can see at the start of the loan, the repayments are split almost evenly between interest and the principle.
But over time, as the principle is paid down, and the interest payments decrease, more of the principle 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 principle early, increasing the size of your repayments, or even paying interest only for the first few years.