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.
- - 1:57
- - Skill Level: 4
- - What's my skill level
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.