CUMPRINC Function

Adds up the principal repaid between two loan periods.

Category

Financial

Available in

All versions, Microsoft 365

Returns

Total principal over a range of periods

Want to practice CUMPRINC 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

CUMPRINC totals the principal paid from one period to another — how much of the balance you cleared in year 2, or by month 30.

Every argument is required, pv must be positive and type must be 0 or 1. Over the whole loan it returns exactly the loan amount (as a negative).

Syntax

=CUMPRINC(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.
end_periodYesThe last period to include.
typeYes0 = payments at period end, 1 = at period start.

Examples

Example 1 — Balance paid down each year

Principal repaid on the $45,000 van loan.

D2fx=CUMPRINC(6.5%/12,60,45000,B2,C2,0)
ABCD
1SpanFromToPrincipal
2Year 1112-$7,872.51
3Year 21324-$8,399.75
4Whole loan160-$45,000.00

Example 2 — Balance left after year 1

Add the (negative) principal paid to the loan amount.

=45000+CUMPRINC(6.5%/12,60,45000,1,12,0) Explain

Result: $37,127.49 still owed.

Common mistakes

  • #NUM! error. pv must be positive and type must be 0 or 1 — it can't be left out.
  • 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.