PPMT
Calculate how much of a given loan payment reduces the balance, for any period of the loan.
Syntax
PPMT(rate, per, nper, pv, [fv], [type])| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. |
per | Required | Which payment, from 1 to nper. |
nper | Required | The total number of payments. |
pv | Required | The amount borrowed. |
fv | Optional | The balance to leave at the end. Defaults to 0. |
type | Optional | 0 (the default) for payments at the end of each period, 1 for the start. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=PPMT(0.05/12,1,360,200000) | -240.31 | Only 240.31 of the first 1,073.64 payment reduces what is owed. |
=PPMT(0.05/12,360,360,200000) | -1069.19 | The final payment clears nearly its whole amount off the balance. |
=PMT(0.05/12,360,200000)-IPMT(0.05/12,1,360,200000) | -240.31 | The same number, as the payment less its interest. |
Principal grows as interest shrinks
The payment on a fixed-rate loan never changes, so as the interest part falls
the principal part rises by exactly the same amount. PPMT is the part that
actually pays the loan down — the equity built with each payment.
Balance after any payment
The principal paid so far is the reduction in the balance, which
CUMPRINC totals in one call. Subtract it from the
original loan for the balance still owed:
=200000+CUMPRINC(0.05/12,360,200000,1,60,0)— the balance after five years. CUMPRINC returns a negative number, so it is
added.
Excel compatibility
Matches Excel, including #NUM! for a period outside 1 to nper.