I teach as a volunteer at an adult education centre, and at the moment I’m teaching Cert II in Excel. 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 PMT Function.
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. Then you can play around with the variables, like increasing the interest rate and seeing the affect that it has on the repayment amount.
A quick disclaimer though. The calculations are based on the assumption that all the variables remain constant, ie 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.
First enter your data. The principle is the amount you want to borrow. The Interest rate given on the loan is per annum so we need to calculate it at as a monthly amount by dividing it by 12. So if the interest rate is 6.5%pa then calculate it as:
=0.065 / 12
The term will be calculated in months as well, so if the loan is taken out over 30 years, then:
= 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):
There is a reason the result comes out as a negative (possibly because it reduces the principle?) but I couldn’t remember why.
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%?
And for the very game…
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.
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’s probably a better way to do this than the way I do it, but this gives you the right answer anyway.
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 ‘service’ or repay your debt if the interest rate goes up.
If you have a cash flow budget, then using this function and inserting the monthly repayment amount into your cash flow budget will give you a good idea as to loan affordability and how it will affect your monthly and overall cash flow. It means that you are in the drivers seat when it comes to your finances.
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).