Excel PMT Function: Calculate Loan Payments or Saving Plans

of 01

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

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

PMT Function Overview

The PMT function, one of Excel's financial functions, can be used to calculate:

  1. the constant periodic payment required to pay off (or partially pay off) a loan;
  2. 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.

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:

  • "0" (or omitted) - at the end of the payment period (end of the month);
  • "1" - at the beginning of the payment period  (beginning of the month).

Excel PMT Function Examples

The image above includes a number of examples of using the PMT function 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 D3) returns the monthly payment for a $15,000, 3 year loan, interest rate of 6% with a remaining balance of $1,000.
  3. The third example (cell D4) calculates the quarterly payments to a savings plan with a goal of $5,000 after 2 years at an interest rate of 2%.

Below are listed the steps used to enter the PMT function into cell D2 

Steps for Entering the PMT Function

Options for entering the function and its arguments into a worksheet cell include:

  1. Typing the complete function, such as: = PMT ( B2/12 , B3 , B4 ) into cell D2;
  2. 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 - such as brackets and the comma separators between arguments.

The steps below cover entering the PMT function example using the function's dialog box.

  1. Click on cell D2 to make it the active cell;
  2. Click on the Formulas tab of the ribbon;
  3. Choose Financial functions to open the function drop down list;
  4. Click on PMT in the list to bring up the function's dialog box;
  5. Click on the Rate line in the dialog box;
  6. Click on 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. Click on the Nper line in the dialog box;
  9. Click on cell B3 to enter this cell reference;
  10. Click on the Pv line in the dialog box;
  11. Click on cell B4 in the spreadsheet;
  12. Click OK to close the dialog box and complete the function;
  13. The answer ($943.56) appears in cell D2;
  14. When you click on cell D2 the complete function =PMT(B2/12,B3,B4) appears in the formula bar above the worksheet.

Loan Repayment Total 

Finding the total amount of money paid over the duration of a loan is easily accomplished by multiplying the PMT value (cell D2) by the value of the Nper argument (number of payments).

$943.56 x 60 = $56,613.70

Formatting Negative Numbers in Excel

In the 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.