I teach Excel and computing as a volunteer at an adult education centre. I love Excel, I love playing around with it.
Today I was doing a lesson plan on some of the inbuilt functions, and it got me thinking about the using the PMT Function in Excel to calculate monthly debt repayments.
I realise that you can use online calculators to work out repayments on mortgages and loans, but if you enjoy using excel as much as I do, then you might like to do it yourself using this function. It’s a great exercise for understanding how excel and interest rates work.
You can also use your spreadsheet to play around with the variables, like increasing the interest rate and seeing what effect that it has on the repayment amount.
You can work out how much you can afford to borrow and what affect an interest rate rise will have on your overall budget.
A quick disclaimer though. The calculations are based on the assumption that all the variables remain constant, i.e. that the interest rate always stays the same over the entire course of the loan, which we know doesn’t happen in reality.
The other thing that the repayment amount doesn’t take into account is monthly bank fees, so if you expect to have to pay fees, you might like to factor these into your calculations.
how to calculate loan repayments in excel
First you need to enter your data.
Principle = the amount you want to borrow.
The Interest Rate = the per annum interest rate divided by 12. So if the interest rate is 6.5%pa then calculate it as:
=0.065 / 12
The term = how long you’ll have the loan in months. So if it’s a 30 year loan calculate it as:
= 30 * 12
This is what it will look like:
showing the formulas:
Now in the cell for the monthly repayment calculation, insert the PMT function (I’m on 2007, if you have 2003 it will be under the insert functions menu):
The solution is represented as a negative number because it’s money you’re paying out.
Once this is calculated, you can run scenarios by changing some of the variables.
What is the repayment amount if you only borrow $400,000?
What is the repayment amount if the interest rate is 7%?
See how this simple yet powerful calculation can make future budget planning easier?
Plug the numbers from this spreadsheet into your budget (you can create a link, here’s how) and when you update this spreadsheet, your budget will update automatically.
taking this one step further…
Based on the input data, you can also use the CUMIPMT function to calculate the total interest paid over the whole loan (assuming the rate stays constant – it doesn’t in real life, but that’s the only way to do the calc and it gives you a ball park figure) and also the total amount paid for the asset – principle plus interest.
In the CUMIPMT dialogue box The start period is month 1 so you type in 1 in this field and the end period is month 360.
The type refers to whether the monthly repayments and interest charges occur at the beginning or the end of the month, typing in 0 means that they occur at the end of the month, which is usual.
Watch the video for a demonstration of how it works.
To calculate the total paid over the term of the loan, you need to add the principle to the interest amount.
The only problem is that the interest is a negative so you need to change it into a positive. There are probably a number of ways to do this, but this is the way I do it and it gives you the right answer anyway.
Edited to note: Rather than using the formula to change the results from negative to positive, adjust the PMT and CUMIPMT formulas by typing in a negative (minus sign) in front of the formula, between the equals sign and the letters PMT (or CUMIPMT).
A little bit shocking, isn’t it, the amount we really pay when borrowing to buy.
When getting into debt, especially debt as big as a mortgage, then it’s important to make informed decisions as to how much you can afford.
Using excel and running various scenarios, like changes in interest rates, gives you a good idea of your capacity to repay your debt if the interest rate goes up.
If looking at that bottom number, the total payment amount, is giving you heart palpitations, then the next step is to look at ways to reduce the interest you pay on your loan. I go over this in the Plan Save Thrive eCourse, as well as how to build a better budget and manage your money. You can get a taste of what’s in the course by signing up to the FREE mini eCourse here.