PMT FunctionWayne Becker20150907T09:29:28+00:00
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 
