How to use the PMT function in Excel

  • Home
  • / How to use the PMT function in Excel

PMT is short for “payment”, and is a function used to calculate the payment for a loan based on constant payments and interest rates.

The PMT function consists of:

  • Rate – interest rate
  • Nper – number of payments for the loan
  • Pv – present value

While the first three are required for the formula, the next two are optional:

  • Fv – future value (desired cash balance after the last payment is made)
  • Type – indicates when the payments are due; 0 indicates the end of the period, while 1 indicates the beginning

So, the formula for a loan payment will look like this:

=PMT(Rate,Nper,Pv, Fv, Type)

Note that the last two are optional.

Note: For calculating monthly payments, divide the interest rate by 12. For loans that will take a number of years to pay, multiply the Nper by the number of years (e.g. for a 3-year payment, multiply Nper by 3).


Enter the following data in Excel:


  • Rate = 8%
  • Nper is 10
  • Pv = $10,000

To find the monthly payment for a loan with the given values, type the following in cell A5 to get your result:


Or, if you want to find the same as above, but given that payments are due at the beginning of the period, then use this formula instead:


Write your comment Here