Financial

CUMIPMT Function in Excel

Returns the cumulative interest paid between two periods.

Syntax

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

Arguments

  • rate (required): Interest rate per period
  • nper (required): Total number of periods
  • pv (required): Present value (loan amount)
  • start_period (required): First period
  • end_period (required): Last period
  • type (required): 0=end of period, 1=beginning

Examples

  • =CUMIPMT(0.05/12, 360, 200000, 1, 12, 0) - First year interest - Result: Total interest

Excel CUMIPMT function documentation

  • CUMIPMT returns cumulative interest paid on a loan between two specified periods.
  • Syntax: =CUMIPMT(rate, nper, pv, start_period, end_period, type).
  • Use CUMIPMT for annual interest totals, refinance timing analysis, and budget forecasting.
  • Related schedule tools: [PMT](/functions/pmt/), [IPMT](/functions/ipmt/), [PV](/functions/pv/), [Financial functions](/categories/financial/).

CUMIPMT parameters and setup

  • rate and nper must use the same periodic unit (monthly, quarterly, yearly).
  • start_period and end_period define inclusive interval boundaries.
  • type is 0 for end-of-period payments and 1 for beginning-of-period payments.
  • Loan principal pv follows standard sign conventions and impacts output sign.

Step-by-step: first-year interest on a mortgage

  • Step 1 — Enter annual rate, term years, and loan amount.
  • Step 2 — Convert to monthly inputs: rate/12 and nper*12.
  • Step 3 — Calculate =CUMIPMT(rate/12, nper*12, pv, 1, 12, 0).
  • Step 4 — Repeat for periods 13 to 24 to compare year-over-year decline.
  • Step 5 — Pair with [CUMPRINC](/functions/cumprinc/) to split total payments cleanly.

CUMIPMT vs IPMT

  • CUMIPMT aggregates interest over a period range.
  • IPMT returns interest for a single period.
  • Use CUMIPMT for annual or quarterly reporting summaries.
  • Use IPMT for detailed amortization rows and period-level visuals.

Worked examples to copy

  • Interest months 1-12: =CUMIPMT(5.5%/12,360,350000,1,12,0).
  • Interest months 61-72: =CUMIPMT(rate, nper, pv, 61, 72, 0).
  • Beginning-payment model: =CUMIPMT(rate, nper, pv, 1, 12, 1).
  • Total paid interest check: compare full-range CUMIPMT to schedule sum of [IPMT](/functions/ipmt/).

People also ask

  • Why is CUMIPMT negative? — Represents borrower cash outflow.
  • Can I use annual periods directly? — Yes, if rate and nper are annual.
  • How to get positive display values? — Multiply by -1 for presentation.
  • How to evaluate refinance benefit? — Compare remaining-interest scenarios with [NPV](/functions/npv/) discounting.

Common errors

  • #NUM! if parameters invalid

Use cases

  • Loan analysis
  • Mortgage planning
  • Interest tracking

Frequently asked questions

  • What is CUMIPMT used for? CUMIPMT calculates total interest paid over a range of loan periods. It's essential for understanding the true cost of borrowing, comparing loan options, and tax planning since mortgage interest is often tax-deductible.
  • How do I calculate first year's interest on a mortgage? Use =CUMIPMT(rate/12, total_months, loan_amount, 1, 12, 0). Example: $300,000 mortgage at 6% for 30 years: =CUMIPMT(0.06/12, 360, 300000, 1, 12, 0) returns about -$17,900 (negative indicates payment out).
  • How do I find total interest over the life of a loan? Use =CUMIPMT(rate/12, nper, pv, 1, nper, 0). For a $300,000 mortgage at 6% for 30 years: =CUMIPMT(0.06/12, 360, 300000, 1, 360, 0) returns about -$347,500. That's total interest paid over 30 years!

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use CUMIPMT

  • Loan analysis — common Financial scenario for CUMIPMT.
  • Mortgage planning — common Financial scenario for CUMIPMT.
  • Interest tracking — common Financial scenario for CUMIPMT.

CUMIPMT in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • CUMIPMT syntax: =CUMIPMT(rate, nper, pv, start_period, end_period, type)
  • rate (required): Interest rate per period
  • nper (required): Total number of periods
  • pv (required): Present value (loan amount)
  • start_period (required): First period
  • Confirm CUMIPMT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm CUMIPMT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • CUMPRINC (/functions/cumprinc/): Returns the cumulative principal paid between two periods.
  • IPMT (/functions/ipmt/): Returns the interest payment for a given period of an investment.
  • PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate.

Errors to watch for

  • #NUM! if parameters invalid — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter CUMIPMT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

CUMIPMT worked examples to copy

  • =CUMIPMT(0.05/12, 360, 200000, 1, 12, 0) — First year interest. Expected result: Total interest.

CUMIPMT reference summary for crawlers and offline review

  • CUMIPMT belongs to the Financial category in Excel. Returns the cumulative interest paid between two periods.
  • Full syntax: =CUMIPMT(rate, nper, pv, start_period, end_period, type). Open /functions/cumipmt/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if parameters invalid
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.