Excel's PMT Function: Calculate Loan Payments or Saving Plans

The PMT function provides a way to calculate loan payments and savings plans in Excel. For example, you can use it to determine the annual or monthly amount required to pay off (or partially pay off) a loan or how much to set aside each month or quarter to reach a savings goal.

PMT Function Syntax and Arguments

A function's syntax is its layout 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) is the annual interest rate for the loan. If you make payments monthly, rather than yearly, divide this number by 12.

Nper (required) is the number of payments for the loan. You can input either the total number of months or the number of years multiplied by 12. In the first example above, you could enter 60 or 5*12.

Pv (required) is the size of the loan or the principal.

Fv (optional) is the future value. If omitted, Excel assumes the balance will be $0.00 at the end of the period. For loans, you can usually omit this argument.

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

Excel PMT Function Examples

The image below includes several examples of the PMT function calculating loan payments and savings plans.

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

Steps for Entering the PMT Function

The instructions below outline how to use the PMT function in the first example. Options for entering the function and its arguments into a worksheet cell include:

• Typing the complete function into cell D2
• Selecting the function and its arguments using the Function Dialog Box

Although you can type the 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.

Before you enter the PMT function, input the data, as shown above in columns A and B.

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.

4. Select PMT from the list.

5. Select the Rate line.

6. Select cell B2 to enter this cell reference.

7. Type a forward slash ​(/) followed by the number 12 in the ​Rate line.

8. Select the Nper line.

9. Select cell B3 to enter this cell reference.

10. Select the Pv line.

11. Select cell B4 in the spreadsheet.

12. Select Done to complete the function.

=PMT(B2/12,B3,B4)

The answer (in this case $943.56) should appear in cell D2. When you select cell D2, the complete function appears in the formula bar above the worksheet.

Additional Formula Modifications

You can find the total amount of money you'll pay over the lifetime of loan by multiplying the PMT value in cell D2 by the value of the Nper argument in cell B3. So for this example, the formula would be:

=D2*B3 

Input the formula in one of the worksheet's cells, and the result will be: $56,613.70.

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