PMT() Function

July 28, 2019
Financial Functions

The PMT function returns a payment amount, so you can use it to:

  • Calculate the monthly payment due on a personal loan
  • Calculate the payment due for a Canadian mortgage loan, with interest compounded bi-annually

Syntax

The syntax for the PMT function in Microsoft Excel is:

=PMT( interest_rate, number_payments, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

Optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

Optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0   –   the payment is made at the end of the period;
1   –   the payment is made at the beginning of the period.

Excel Pmt Function Examples

Example 1

In the following spreadsheet, the Excel Pmt function is used to calculate the monthly payments on a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.

Formula:

Result:

Note that in this example:

  • The payments are made monthly, so the annual interest rate of 5% has been converted into the monthly rate (=5%/12), and the period of 5 years is expressed in months (=5*12).
  • As the future value is zero, and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above function.
  • The value returned from the function is negative, as this represents an outgoing payment (for the individual taking out the loan).

Example 2

In the spreadsheet below, the Excel Pmt function is used to calculate the quarterly payments required to increase an investment from $0 to $5,000 over a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the investment is to be made at the beginning of each quarter.

Formula:

Result:

Note that, in this example:

  • The payments into the investment are made quarterly, so the annual interest rate of 3.5% is converted into a quarterly rate (3.5%/4), and the number of years is converted into quarters (=2*4).
  • The [type] argument has been set to 1, to indicate that the payment into the investment is to be made at the beginning of each quarter.
  • The value returned from the function is negative, as this represents an outgoing payment.

Notes

  • The PMT function can be used to figure out the future payments for a loan, assuming constant payments and a constant interest rate. For example, if you are borrowing $10,000 on a 24 month loan with an annual interest rate of 8 percent, PMT can tell you what your monthly payments be and how much principal and interest you are paying each month.
  • The payment returned by PMT includes principal and interest but will not include any taxes, reserve payments, or fees.
  • Be sure you are consistent with the units you supply for rate and nper. If you make monthly payments on a three-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 3*12 for nper. For annual payments on the same loan, use 12 percent for rate and 3 for nper.

Common Problem

The result from the Excel Pmt function is much higher or much lower than expected.

Possible Reason

When calculating monthly or quarterly payments, users sometimes forget to convert annual interest rates or the number of periods to months or quarters.