The Excel PMT Function

Related Functions:
RATE Function
NPER Function

Basic Description

The Excel PMT function calculates the constant periodic payment required to pay off (or partially pay off) a loan or investment, with a constant interest rate, over a specified period.

The syntax of the function is:

PMT( rate, nper, pv, [fv], [type] )

Where the arguments are as follows:


rate-The interest rate, per period
nper-The number of periods over which the loan or investment is to be paid
pv-The present value of the loan / investment
[fv]-

An optional argument that specifies the future value of the loan / investment, at the end of nper payments.

If omitted, [fv] has the default value of 0.
[type]-

An optional argument that defines whether the payment is made at the start or the end of the period.

The [type] argument can have the value 0 or 1, meaning:

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

If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).

Cash Flow Convention:

Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the examples below.


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:
 A
1Monthly payments on a loan of
$50,000 that is to be paid off in
full over 5 years, with an interest
rate of 5% per year (payment
made at end of each mth):
2=PMT( 5%/12, 60, 50000 )
 Result:
 A
1Monthly payments on a loan of
$50,000 that is to be paid off in
full over 5 years, with an interest
rate of 5% per year (payment
made at end of each mth):
2-943.56

Note that in this example:


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:
 A
1Quarterly payments into an investment
with current value $0, which is
required to reach $5,000 over 2 yrs.
The interest rate is 3.5% per year
(payment made at start of each qtr):
2=PMT( 3.5%/4, 8, 0, 5000, 1 )
 Result:
 A
1Quarterly payments into an investment
with current value $0, which is
required to reach $5,000 over 2 yrs.
The interest rate is 3.5% per year
(payment made at start of each qtr):
2-600.85

Note that, in this example:


Further examples of the Excel Pmt function can be viewed on the Microsoft Office website.


Pmt Function Errors

If you get an error from the Excel Pmt function, this is likely to be one of the following:

Common Errors
#NUM!-

Occurs if either:

  • the supplied value of rate is less than or equal to -1;
  • the supplied value of nper is equal to 0
#VALUE!-Occurs if any of the supplied arguments are not recognised as numeric values

Also, the following problem is encountered by some users:

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, many users forget to convert annual interest rates or the number of periods to months or quarters.

Solve this problem by ensuring that the rate and the nper arguments are expressed in the correct units. i.e.:

months = 12 * years
quarters = 4 * years
monthly rate = annual rate / 12
quarterly rate = annual rate / 4