CUMIPMT Function

Adds up the interest paid between two loan periods.

Category

Financial

Available in

All versions, Microsoft 365

Returns

Total interest over a range of periods

Want to practice CUMIPMT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

CUMIPMT totals the interest paid from one period to another — the interest for year 2 of a loan, say, or over the loan's whole life. It saves summing a column of IPMT results.

Unlike most loan functions, every argument is required, pv must be positive, and type must be 0 or 1.

Syntax

=CUMIPMT(rate, nper, pv, start_period, end_period, type) Explain

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
nperYesThe total number of payment periods.
pvYesThe loan amount, as a positive number.
start_periodYesThe first period to include (1 = first payment).
end_periodYesThe last period to include.
typeYes0 = payments at period end, 1 = at period start. Required here.

Examples

Example 1 — Interest per year on the van loan

How much interest does Northstar pay in year 1, in year 2, and in total?

D2fx=CUMIPMT(6.5%/12,60,45000,B2,C2,0)
ABCD
1SpanFromToInterest
2Year 1112-$2,693.21
3Year 21324-$2,165.97
4Whole loan160-$7,828.60

Handy for budgeting: the interest expense falls each year as the balance shrinks.

Common mistakes

  • #NUM! error. pv must be positive, type must be 0 or 1, and start_period can't be after end_period or below 1.
  • 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.

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.