PMT Function

Microsoft Excel 2010

The PMT function is useful to calculate loan payments and the total cost of the loan (if you really want to know). The easiest way to enter the PMT formula is by using the Insert Function wizard.

  1. Select the cell where you wish to place the result.
  2. Display the Formulas Ribbon and click on the Insert Function button then choose the PMT function. Refer to the Formula Functions article for more information on creating formulas.
  3. Note: The above window displays the values for calculating payments for a $15,000 loan at 6% rate with 12 payments/year over a period of 5 years with payments at the beginning of each month.

  4. Rate: Enter your interest rate, e.g. 6% per 12 months.
  5. Nper: Enter the number of payments, e.g. 12 payments times 5 years.
  6. Pv: Enter the value of the loan.
  7. Fv: Optional field. Fv is the future value, or in the case of 'saving up,' the cash balance you want to attain after making the last payment. If fv is omitted, it is assumed to be 0 (zero).
  8. Type: Optional field. The number 0 (zero) or 1 and indicates when payments are due.  0 (or blank) indicates payments are due at the end of each period.  1 indicates payments are due at the beginning of each period.
  9. When all required fields are entered, notice the formula result at the bottom of this window. Choose OK to insert the answer into your worksheet.

Additional References