PMT Excel Function

The PMT Excel function or payment function allows you to calculate what repayments are required to fully amortise a loan.

• 1:57
• Skill Level: 4
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

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.