How to Calculate Cumulative Interest: Formulas, CUMIPMT, and Edge Cases

To calculate cumulative interest, you need to know which of two situations you’re in and use the matching method. For a lump sum sitting untouched in a savings account, CD, or investment, use the compound interest formula A = P × (1 + r/n)^(n×t) and subtract the original principal. For a loan you’re paying down each month, like a mortgage, auto loan, or student loan, use Excel’s CUMIPMT function or build an amortization schedule. Applying the lump-sum formula to a loan you’re actively paying off will overstate your interest by tens of thousands of dollars, because the formula assumes the full balance stays put for the entire term.

Which Situation Are You In

The first decision is whether your balance is static or shrinking. A lump-sum investment grows on top of itself: this month’s interest gets added to the balance, and next month’s interest is calculated on the new, larger total. Nothing leaves the account.

An amortizing loan works in reverse. Each monthly payment covers that month’s interest first, then uses whatever’s left over to chip away at the principal. Next month’s interest is calculated on the smaller remaining balance, so the interest portion of each payment shrinks over time while the principal portion grows. Total interest over the life of the loan is the sum of every single month’s interest charge.

Mixing these up is the most common mistake. Running a $200,000 mortgage at 6.5% for 30 years through the lump-sum formula produces a balance over $1.3 million, which is obviously wrong for a loan you’ve been paying down for three decades.

What You Need Before You Start

Every calculation uses four inputs: the principal, the annual interest rate, the term in years, and the compounding frequency. Your closing disclosure, promissory note, or account agreement should list all four. Federal regulation requires lenders to label the “amount financed” and “finance charge” with those exact terms on consumer loan disclosures.1Consumer Financial Protection Bureau. Regulation Z Section 1026.18 – Content of Disclosures

Watch the difference between APR and APY. An APR is the base annual rate and does not include the effect of compounding. An APY already bakes compounding in, so it’s a slightly higher number whenever interest compounds more than once a year.2eCFR. Part 1030 – Truth in Savings (Regulation DD) Compound interest formulas and Excel functions expect the APR; feeding them an APY double-counts compounding and inflates the result.

Compounding frequency tells you how often interest is applied to the balance. Monthly (12 times per year) is standard for most consumer loans and savings accounts. Some accounts compound daily, and certain bonds and CDs compound quarterly or semiannually.3U.S. Securities and Exchange Commission. Compound Interest Calculator More frequent compounding grows the balance faster, though the gap between monthly and daily is usually small.

Lump-Sum Compound Interest by Hand

When money sits and grows without any payments or withdrawals, use A = P × (1 + r/n)^(n×t), where A is the future value, P is the starting principal, r is the annual rate as a decimal, n is the number of compounding periods per year, and t is the number of years. Subtract P from A to isolate the cumulative interest.

Try it with $10,000 deposited at 5% annually, compounded monthly, for 10 years:

  • Convert the rate to a decimal: 5% ÷ 100 = 0.05
  • Find the periodic rate: 0.05 ÷ 12 = 0.004167
  • Add 1: 1 + 0.004167 = 1.004167
  • Count total compounding periods: 12 × 10 = 120
  • Raise the base to that power: 1.004167^120 = 1.6470
  • Multiply by the principal: $10,000 × 1.6470 = $16,470.09
  • Subtract the original deposit: $16,470.09 − $10,000 = $6,470.09

That $6,470.09 is cumulative interest over 10 years. Simple interest at the same rate for the same period would have produced only $5,000. The extra $1,470 came from compounding alone: interest earned on previous interest.

Cumulative Interest on an Amortizing Loan

For a mortgage, car loan, or student loan with regular payments, the lump-sum formula won’t work. Interest recalculates each period on whatever principal remains, so early payments are mostly interest (large balance) and later payments are mostly principal (small balance). Getting the total by hand means building a full amortization schedule, calculating each month’s interest and principal split one row at a time. For a 30-year mortgage, that’s 360 rows.

CUMIPMT in Excel

Excel’s CUMIPMT function is built for this. It returns the total interest paid between any two payment periods, so you can get the figure for the whole loan or any slice of it. The syntax is =CUMIPMT(rate, nper, pv, start_period, end_period, type).4Microsoft Support. CUMIPMT Function

  • rate: interest rate per period (annual rate ÷ 12 for monthly payments)
  • nper: total number of payments (years × 12)
  • pv: the loan amount
  • start_period: first payment in your range; use 1 for the full loan
  • end_period: last payment in your range; use the same number as nper for the full loan
  • type: 0 for payments at the end of each period, which is standard

For a $125,000 mortgage at 9% for 30 years, the formula is =CUMIPMT(0.09/12, 360, 125000, 1, 360, 0). Excel returns roughly -$237,081.4Microsoft Support. CUMIPMT Function The negative sign reflects a cash outflow; drop it and you have about $237,000 in cumulative interest over 30 years, nearly double what was borrowed. To see one year’s interest instead, change the start and end periods (13 through 24 for year two, for instance).

FV for Lump Sums in Excel

If you’d rather let Excel handle the lump-sum compound interest calculation too, the FV function does it: =FV(rate, nper, 0, -pv). Set the payment argument to 0 and enter the deposit as a negative number.5Microsoft Support. FV Function For the $10,000 example above, =FV(0.05/12, 120, 0, -10000) returns the same future value. Subtract your deposit to get cumulative interest.

A Note on Auto Loans

Most auto loans use simple interest calculated daily on the outstanding balance rather than monthly compound interest.6Consumer Financial Protection Bureau. Whats the Difference Between a Simple Interest Rate and Precomputed Interest on an Auto Loan The practical result looks a lot like monthly amortization, so CUMIPMT gets you a close estimate. The actual total will shift slightly depending on exactly when each payment hits: pay a few days early and you owe a little less interest, pay late and a little more.

How Extra Principal Payments Change the Total

Because interest recalculates on the remaining balance each period, even small extra payments toward principal cut cumulative interest sharply. A lower balance means less interest next month, which means more of your regular payment attacks principal, which drops the balance further. The savings feed on themselves.

On a $200,000 mortgage at 4% for 30 years, an extra $100 per month toward principal can trim more than $26,000 off total interest and shorten the term by several years. Raise that to $200 extra per month and the savings exceed $44,000. Timing matters: an extra payment in year two eliminates interest that would have accrued across the remaining 28 years, while the same payment in year 25 only cancels a few years of interest.

You can model the effect by running CUMIPMT twice, once with the original term and once with a shorter term that reflects the faster payoff, and comparing results. Or build a simple amortization table where each row adds the extra amount to that month’s principal and recalculates the balance.

Two Situations That Break the Standard Calculation

Rule of 78s Loans

Some older or subprime loans use the Rule of 78s, which front-loads interest. Instead of charging interest on the actual remaining balance, the method assigns a fixed share of total interest to each month using a declining fraction: month one of a 12-month loan gets 12/78 of total interest, month two gets 11/78, down to 1/78 in the final month. Pay the loan off early and you’ll have already paid a disproportionate share of the interest.

Federal law prohibits the Rule of 78s on any consumer loan with a term longer than 61 months.7Office of the Law Revision Counsel. 15 USC 1615 – Prohibition on Use of Rule of 78s in Connection With Mortgage Refinancings and Other Consumer Loans It can still appear on shorter loans, especially at buy-here-pay-here auto lots and on some personal installment loans. If your paperwork mentions “precomputed interest,” ask the lender directly, because a standard cumulative interest calculation will understate how much interest you’d forfeit by paying off early.

Negative Amortization

Negative amortization happens when your monthly payment doesn’t cover the interest due. The shortfall is added to your principal, so the balance grows even while you’re paying.8Consumer Financial Protection Bureau. What Is Negative Amortization It shows up on certain adjustable-rate mortgages with minimum payment options and on payment-option loans when rates rise faster than the minimum payment.

If your balance has been climbing instead of falling, CUMIPMT and the standard formula won’t capture the true interest. You’d need to track the actual balance month by month, folding each shortfall back into principal before calculating the next month’s charge. Comparing your current statement balance to your original loan amount is the quickest way to catch it.