PPMT FunctionWayne Becker20150903T07:26:22+00:00
The PPMT function calculates the payment on the principal, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.
Syntax
=PPMT(rate,per,nper,pv,[fv],[type])
Arguments
Argument 
Description 
rate 
The interest rate, per period 
per 
Specifies the period and must be in the range 1 to nper 
nper 
The total number of payment periods in an annuity 
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

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

A 
B 
C 
D 
1 
Data 



2 
10% 
Annual interest rate 


3 
5 
Loan term – years 


4 
$20,000 
Loan Amount 


5 




6 
Formula 
Result 
Notes 
7 
=PPMT(A2/12,1,A3*12,A4) 
$258.27 
Payment on principle for the first month of loan 
8 
=PPMT(A2/12,24,A3*12,A4) 
$312.59 
Payment on principal for the 24th month of loan 
Note: In the above example, the payments are made monthly, so
 the annual interest rate of 10% had to be converted into a monthly rate (=10%/12), and
 the number of years had to be converted into months (=5*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 the supplied per argument is < 0 or is > the supplied value of nper 
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 
