Excel PMT Function: Calculate Loan Payments or Saving Plans

This trick can help you manage your finances

Bank calculates the home loan rate
Witthaya Prasongsin / Getty Images

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 or a savings plan that will result in saving a set amount in a specific length of time.

This tutorial is compatible with most versions of Excel including 2007, 2010, 2013, 2016, and Office 365.

PMT Function Syntax and Arguments

Screenshot of Excel showing PMT formula

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

Screenshot of Excel showing PMT formula examples

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

Screenshot of Excel showing how to insert a formula

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 into cell D2.
  • Selecting the function and its arguments using the Formula Builder.

Although it is possible to type the complete function manually, many people find it easier to use the Formula Builder as it takes care of entering the function's syntax including brackets and commas between arguments.

  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 in 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.
Screenshot of Excel showing the Formula Builder

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

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 the first example, that would mean modifying the formula to follow such a mathematical form:

$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.