IPMT Function

Returns how much of a given loan payment is interest.

Category

Financial

Available in

All versions, Microsoft 365

Returns

The interest part of one payment

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

Every loan payment is part interest, part principal. IPMT returns the interest part for one specific period. Early payments are mostly interest; by the end almost none is.

Add IPMT and PPMT for the same period and you always get the full PMT payment.

Syntax

=IPMT(rate, per, nper, pv, [fv], [type])

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
perYesWhich period you want, from 1 to nper.
nperYesThe total number of payment periods.
pvYesThe present value — the loan amount, or what a series of payments is worth today.
fvNoThe balance you want left after the last payment. Omitted = 0 (a loan paid off in full).
typeNoWhen payments fall: 0 or omitted = end of each period, 1 = start of each period.

Examples

Example 1 — Interest in the first months of the van loan

The $45,000 van loan at 6.5% over 60 months. Fill the formula down the period column.

B6fx=IPMT($B$1/12,A6,$B$2,$B$3)
AB
1Annual rate6.5%
2Months60
3Loan amount$45,000
4
5MonthInterest
61-$243.75
72-$240.30
83-$236.83
960-$4.74

Month 1's interest is simply $45,000 × 6.5% ÷ 12 = $243.75. By month 60 there's only $4.74 of interest left.

Common mistakes

  • Rate and periods in different units. Keep them matched: for monthly payments divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods gives wildly wrong answers.
  • #NUM! error. per must be between 1 and nper. Period 0 or 61 on a 60-month loan fails.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.