PMT function

In this lesson you can learn how to calculate loan installment using PMT function in Excel.

PMT function helps you to calculate the amount of the installment loan assuming a fixed interest rate and fixed monthly payment amount. ith this feature, you can also calculate the amount you need to deposit to save a certain amount at a certain time.

Payments calculated by PMT contain basis and exclude taxes and interest, and other fees associated with the loan.

This function has the following syntax:

=PMT(rate,nper,pv,[fv],[type])

  • Rate - the rate of interest loans.
  • Nper is the total number of installments
  • PV - current value (for example the amount of credit)
  • FV - value or future financial level you want to get after the last payment
  • Type is the number 0 or 1 indicates when the payment takes place 1-payment in advance, 0-payment of arrears.

 

Example 1 Calculating monthly installment

Calculate the monthly installment of the loan in the amount of 100 000 $. Interest is 7% per annum, and the loan must be paid within 15 months.

In any cell, enter the function: =PMT (7%/12, 15, -100000)

  • 7%/12 is interest rate per month
  • number of rates is 15
  • amount of credit is 100 000 (with minus becase that's not your money and you have to pay it back)

Result is 6 982$

 

Example 2 Calculating monthly installment paying in the beginnig of the month

Calculate the monthly payment amount for the same loan, if payments with payment in advance (in the beginning of the month)

Formula is: =PMT (7%/12, 15, -100000, 0, 1)

  • 7%/12 is interest rate per month
  • number of rates is 15
  • amount of credit is 100 000 (with minus becase that's not your money and you have to pay it back)
  • future value is 0, because you want to pay back whole loan
  • 1 means payments in beginning of the period

Result is 6 941,51$

 

Example 3 How much to save to become millionaire?

You want to save $ 1 000 000 over 10 years. Every year you deposit a fixed amount. The interest rate is 5% savings. Using the PMT function, calculate how much you should deposit per month.

Formula is: =PMT (5%/12, 120, 0, 1000000)

  • 5%/12 is interest rate per month
  • 120 is number of month (10 years * 12)
  • 0 is present value (you have any deposit now)
  • 1 000 000$ is future value

Result is -6 439,88$ (with minus because you have to pay it to the bank)