Syntax
PPMT(rate, per, nper, pv, [fv], [type])
| Parameter | Description |
|---|---|
| rate | Parameter of the PPMT function. |
| per | Parameter of the PPMT function. |
| nper | Parameter of the PPMT function. |
| pv | Parameter of the PPMT function. |
| [fv] | (Optional.) Parameter of the PPMT function. |
| [type] | (Optional.) Parameter of the PPMT function. |
Examples
Principal in first mortgage payment
Formula
=PPMT(0.065/12, 1, 360, -350000)
$318.54. In the very first payment of a $350K mortgage at 6.5%, only about $319 goes toward principal — the rest is interest.
Principal in payment #120 (year 10)
Formula
=PPMT(0.065/12, 120, 360, -350000)
$621.09. By the 120th payment, the principal portion has roughly doubled as the balance has decreased.
Last year of a car loan
Formula
=PPMT(0.049/12, 55, 60, -28000)
$516.58. Near the end of the loan, almost the entire payment goes toward principal.
Common Errors
#VALUE!
A non-numeric argument was supplied, such as text in the period or rate field.
#NUM!
The per argument is less than 1 or greater than nper (you asked for a period that doesn't exist in the loan term).
Tips
PPMT + IPMT = PMT
For any given period, PPMT and IPMT always add up to the total payment from PMT. This is a great way to double-check your amortization schedule.
Build an amortization table
Put period numbers 1 through nper in a column, then use PPMT and IPMT with that column as the per argument to build a full amortization schedule instantly.
Watch the sign
Like PMT, if pv is positive (loan received), PPMT returns negative (cash outflow). Negate pv to get positive results.
Try PPMT in Viztab
Import your data and use PPMT with 370+ other formulas. No signup required.
Open Viztab