Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .