PMT function

This article describes the formula syntax and usage of the PMTfunction in Microsoft Excel.

Description

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

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

Note   For a more complete description of the arguments in PMT, see the PV function.

The PMT function syntax has the following arguments:

  • Rate    Required. The interest rate for the loan.

  • Nper    Required. The total number of payments for the loan.

  • Pv    Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.

  • Fv    Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

  • Type    Optional. The number 0 (zero) or 1 and indicates when payments are due.

Set type equal to

If payments are due

0 or omitted

At the end of the period

1

At the beginning of the period

Remarks

  • The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.

  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Tip    To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.

Example

Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.

This example uses the PMT function to determine the monthly payment for a loan.

EWA1

To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from OneDrive and open it on your computer.

Applies To: Excel 2010, Excel Starter, Excel Online, SharePoint Online



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language