Excel PMT Function: Calculate Loan Payments or Saving Plans

This trick can help you manage your finances

Calculating Loan Payments and Saving Plans with Excel's PMT Function
Ted French

The PMT function provides an easy way to calculate loan payments and savings plans in Excel. More specifically, it can be used to determine:

  • the constant periodic payment required to pay off (or partially pay off) a loan
  • a savings plan that will result in saving a set amount in a specific length of time

For both situations, a fixed interest rate and a uniform payment schedule are assumed. Let's learn how to use the PMT function in all modern versions of Excel.

Note: This article applies to Excel 2016, 2013, 2010, Excel for Mac, and Excel for Android.

PMT Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the PMT function is:

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

Rate (required): The annual interest rate for the loan. If payments are made monthly, divide this number by 12.

Nper (required): The total number of payments for the loan. Again, for monthly payments, multiply this by 12.

Pv (required): The present or current value or the amount borrowed.

Fv (optional): Future value. If omitted, Excel assumes the balance will be $0.00 at the end of the time period. For loans, this argument can generally be omitted.

Type (optional): Indicates when payments are due. The number 0 indicates the end of the payment period, and 1 indicates the beginning of the payment period.

Excel PMT Function Examples

The PMT Function in Use in Excel

The image above includes several examples of the PMT function being used to calculate loan payments and savings plans.

  1. The first example (cell D2) returns the monthly payment for a $50,000 loan with an interest rate of 5% to be repaid over 5 years.
  2. The second example (cell D6) returns the monthly payment for a $15,000, 3-year loan, an interest rate of 6% with a remaining balance of $1,000.
  3. The third example (cell D11) calculates the quarterly payments to a savings plan with a goal of $5,000 after 2 years at an interest rate of 2%.

Steps for Entering the PMT Function

Let's look at how the PMT function is used in the first example. Options for entering the function and its arguments into a worksheet cell include:

  • Typing the complete function, such as =PMT(B2/12, B3, B4), into cell D2
  • Selecting the function and its arguments using the PMT function dialog box

Although it is possible to just type the complete function manually, many people find it easier to use the dialog box as it takes care of entering the function's syntax including brackets and commas between arguments. The steps below cover entering the PMT function example using the function's dialog box.

  1. Select cell D2 to make it the active cell
  2. Select the Formulas tab of the ribbon
  3. Choose Financial functions to open the function drop-down list
  4. Select PMT in the list to bring up the function's dialog box
  5. Select the Rate line in the dialog box
  6. Select cell B2 to enter this cell reference
  7. Type a forward slash ​(/) followed by the number 12 in the ​Rate line of the dialog box to get the interest rate per month
  8. Select the Nper line in the dialog box
  9. Select cell B3 to enter this cell reference
  10. Select the Pv line in the dialog box
  11. Select cell B4 in the spreadsheet
  12. Select OK to close the dialog box and complete the function

The answer (in this case $943.56) should appear in cell D2. When you select cell D2, the complete function, =PMT(B2/12,B3,B4), appears in the formula bar above the worksheet.

Calculating Loan Repayment Total

Find the total amount of money paid over the duration of a loan by multiplying the PMT value by the value of the Nper argument. In first example, that would mean:

$943.56 x 60 = $56,613.70

Formatting Negative Numbers in Excel

In the example image, the answer $943.56 in cell D2 is surrounded by parenthesis and has a red font color to indicate that it is a negative amount because it is a payment. The appearance of negative numbers in a worksheet can be altered using the Format Cells dialog box.