PMT Excel Function

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.