The PMT Excel function or payment function allows you to calculate what repayments are required to fully amortise a loan.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
In this segment you will learn how to activate and use the PMT Excel function.
In this example we have a loan of 100 000, at an interest rate of 11% over a period of five years, and we’d like to know what repayment will fully amortise this loan.
We can use the PMT function in Excel.
- so you click on the cell where you want the calculation to occur,
- activate the Function Wizard
- and find the PMT function
- and you click ok
And what you’ll see is it asks for a bit of information about the underlying loan,
- so it asks for the Rate and you can refer to the interest rate,
- it asks for the Number of Periods which is the total number of payments for the loan, which is 5
- the Present Value is the total amount outstanding at the beginning of the period, you click there
- and then you can have Future Value and what this would be is if the loan does not fully amortise
- and Type where basically this is saying is the payment at the beginning or the end of a period, if its at the beginning you put a 1
- if its at the end you but a 0, so you put a 0
- and you say Ok,
And there we have the repayment that will be required to fully amortise this loan. As a proof we have a little amortisation table here,
- so if you start on a 100 000
- and charged interest at the 11%
- and used the repayment calculated
- you’ll see that over the 5 years, the eventual closing balance is 0
Therefore this correctly amortises the loan.