In today’s tutorial we are going to look at how to use the PMT function in Excel to calculate mortgage repayments. In another post, I’m going to show you how to use the PMT function with another Excel feature (called Data Tables) to very quickly generate a table of values.
Essentially there are 3 main parts to it:
- Rate – Interest Rate (if the annual rate divide it by 12 if you want to show monthly payments)
- NPer – payment periods for the loan (don’t forget that if you want to show monthly payments you need to multiply the years by 12)
- PV – Present Value (the value of what you are borrowing now)
You have two other optional parameters: FV – if you want there to be an amount left when you have finished repaying the loan and Type – the default is 0 and assumes the payments are made at the end of the year/month or you can enter 1 – which assumes the payment are made at the beginning of the period. It is worth your while to experiment with these two and note how they impact on the repayment amounts.
So here’s the video
[flowplayer src=’https://s3-eu-west-1.amazonaws.com/the-excel-expert/PMT_function.mp4′]
And here (as always) is the file to practise on.