The PMT Excel function or payment function allows you to calculate what repayments are required to fully amortise a loan.
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.