PPMT function
PPMT returns the part of a loan payment that reduces the balance for a given period, as a negative number. Combine it with IPMT to build an amortization schedule.
Example
Read it as a sentence: find the principal part of the payment for period 1 on a 2000 loan over 24 monthly periods at 10% a year, which gives -75.6231860083663.
A
B
C
D
1
-75.6231860083663
2
3
Syntax
PPMT(rate, per, nper, pv, [fv], [type])
Arguments in [square brackets] are optional.
-
rate(required) - Interest rate per period.
-
per(required) - Period to analyse (1 to nper).
-
nper(required) - Total number of periods.
-
pv(required) - Loan amount today.
-
fv(optional) - Balance wanted at the end; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
Returns: Principal portion of one loan payment, as a negative number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=PPMT(0.05/12,1,60,20000) | ($294.09) | Period 1 |
=PPMT(0.05/12,1,60,20000,0,1) | ($375.86) | Type 1 |
=PPMT(0,1,10,1000) | ($100.00) | Rate 0 |
=PPMT(0.05,{1,2},10,1000) | ($79.50) | Spill |
=PPMT(0.05,0,10,1000) | #NUM! | Per 0 |
=PPMT(0.05,11,10,1000) | #NUM! | Per > nper |
=PPMT("a",1,10,1000) | #VALUE! | Text |
Watch out for
- PPMT plus IPMT for the same period equals PMT.
Where it works
- Excel
- PPMT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .