Related Functions:

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

Syntax

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

Arguments

Argument Description
rate The interest rate for the loan
nper The total number of payments for the loan
pv The present value, or the total amount that a series of future payments is worth now; also known as the principal
[fv] Optional. The future value, or a cash balance you want to attain after the last payment is made

  If omitted, [fv] defaults to 0 (zero), i.e. the future value of a loan is 0
[type] Optional. 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; default if argument is omitted
  1 the payment is made at the beginning of the period

Examples

Example 1
  A B C D
1 Data      
2 8% Annual interest rate    
3 10 Loan term (years)    
4 $50,000 Amount of loan    
5        
6 Formula Result Notes
7 =PMT(A2/12,A3*12,A4) -$606.64 Monthly payment for a loan with terms specified as arguments in A2:A4
8 =PMT(A2/12,A3*12,A4,0,1) -$602.62 Monthly payment for a loan with with terms specified as arguments in A2:A4, except payments are due at the beginning of the period

Note: In the above example, the payments are made monthly, so

  • the annual interest rate of 8% had to be converted into a monthly rate (=8%/12), and
  • the number of years had to be converted into months (=10*12)
Example 2
  A B C D
1 Data      
2 6% Annual interest rate    
3 18 Loan term (years)    
4 $50,000 Amount of loan    
5        
6 Formula Result Notes
7 =PMT(A2/12,A3*12,0,A4) -$129.08 Amount to save each month to have $50,000 at the end of 18 years

Note: In the above example, the payments are made monthly, so

  • the annual interest rate of 6% had to be converted into a monthly rate (=6%/12), and
  • the number of years had to be converted into months (=18*12)

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#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
Result is much higher or much lower than expected Usually occurs due to failure to convert the rate or the nper to months or quarters, i.e.:

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