Tutorial 2: Working with the PMT Function

The PMT function is a financial function that you can use to calculate periodic payments for loans based on constant payments and a consistent interest rate. For example, you can use this function to calculate car, mortgage, or any other amortized loan payments.

The syntax for a PMT function requires three arguments, each of which must be preceded by =PMT and enclosed within parentheses:

  • Interest Rate (Rate): the rate at which the money is being borrowed.
  • Pay Period (Nper): the total number of payment periods in an annuity.
  • Amount Borrowed (Pv): the present value or the total amount of the loan.

Optional information includes:

  • Future Value (Fv): your goal for the loan, usually set at 0 (default).
  • Type: whether your loan payments occur at the beginning (1) or the end (0 or default) of each payment period.

It is critical that all argument information be expressed in comparable units. For example, if you are computing monthly payments on a four-year loan based on a 12 percent annual interest rate, use 12%/12 for Rate and 4*12 for Nper. If you make annual payments on the same loan, use 12% for Rate and 4 for Nper. Although you can do the division and multiplication yourself, it’s easier to let Excel take care of it. If you use a cell reference for any of these arguments, they also must be of comparable unit. For example, if you intend to use the formula above, the % symbol must be in the cell containing the interest rate.

The payment returned by PMT includes principal and interest only. These calculations do not include any taxes, reserve payments, or fees associated with loans.

When you open the Formula Palette, you will see a Collapse Dialog button at the end of each argument box. Use these buttons to collapse the dialog box temporarily, to help you select cells and ranges in the worksheet as arguments. When done, click the toggled Collapse Dialog box button in the collapsed dialog box.

 

PMT-Funtion-Window

 

To work with arguments using the PMT function:

First select the cell where you want to enter the function.

  1. On the Formulas tab, click the Insert Function button.
  2. In the Function category list box, select Financial.
  3. In the Function name list box, select PMT.
  4. Choose OK.
  5. In the Formula Palette, enter the following arguments:
    1. For Rate, the interest rate.
    2. For Nper, the number of payment periods.
    3. For Pv, the amount of the loan.
    4. For Fv, the goal amount, or omit it. (optional)
    5. For Type, 1 or 0, or omit it. (optional)
  6. Choose OK

Note: Arguments may be numbers, formulas, or cell references. You can enter arguments by typing them or by selecting them with the mouse in the worksheet.