Syntax
IPMT(rate, per, nper, pv, [fv], [type])
| Parameter | Description |
|---|---|
| rate | Parameter of the IPMT function. |
| per | Parameter of the IPMT function. |
| nper | Parameter of the IPMT function. |
| pv | Parameter of the IPMT function. |
| [fv] | (Optional.) Parameter of the IPMT function. |
| [type] | (Optional.) Parameter of the IPMT function. |
Examples
Interest in first mortgage payment
Formula
=IPMT(0.065/12, 1, 360, -350000)
$1,895.83. In the first payment on a $350K mortgage at 6.5%, roughly $1,896 of the ~$2,213 total payment is pure interest.
Interest in month 180 (halfway)
Formula
=IPMT(0.065/12, 180, 360, -350000)
$1,392.07. Halfway through the mortgage, interest is still the majority of the payment but has dropped considerably.
Auto loan interest in first payment
Formula
=IPMT(0.049/12, 1, 60, -28000)
$114.33. The first payment on a $28K car loan at 4.9% includes about $114 in interest.
Common Errors
#VALUE!
One of the arguments is non-numeric.
#NUM!
The period (per) is outside the valid range of 1 to nper.
Tips
Tax deduction tracking
Sum IPMT across all 12 months of a year to calculate total deductible mortgage interest for tax purposes. This is faster than pulling it from a bank statement.
Compare loan structures
Use IPMT for the first period across different loan terms (15-year vs 30-year) to see how much more interest you pay upfront with longer terms.
Interest decreases over time
IPMT output shrinks as the period number increases because the outstanding balance gets smaller. Plotting IPMT for all periods creates a declining curve.
Try IPMT in Viztab
Import your data and use IPMT with 370+ other formulas. No signup required.
Open Viztab