CUMIPMT

What it does

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

CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the total interest paid on a loan between two period numbers, inclusive.

It sits on top of the standard level-payment annuity. That model is one equation:

pv·(1+r)^n  +  pmt·(1 + r·type)·((1+r)^n − 1)/r  +  fv  =  0

with r the per-period rate, n the number of periods, and type 0 for payments at the end of each period or 1 for payments at the beginning. Solving it for the payment gives the constant pmt that PMT returns; every period then splits that payment into interest and principal:

interest in period k  =  r × (balance outstanding at the start of period k)
principal in period k =  pmt − interest in period k

and, in exact arithmetic,

CUMIPMT(r, n, pv, s, e, type)  =  Σ_{k = s}^{e}  IPMT(r, k, n, pv, 0, type)

Note that fv is fixed at zero: CUMIPMT models a loan amortized to nothing. There is no argument for a balloon payment, which is the usual reason a real amortization schedule and a CUMIPMT total fail to agree.

Arguments

CUMIPMT(rate, nper, pv, start_period, end_period, type) — six arguments, all required.

Periods are numbered from 1. start_period = 1 is the first payment, not the second and not the zeroth. This differs from AMORLINC and AMORDEGRC, which count depreciation periods from 0.

rate and nper must use the same unit. Microsoft states it directly: "If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper." Feeding an annual rate with a monthly period count is the second most common error in this family, after the sign convention.

Try it

Quirks

Sign conventions

This is where wrong answers in this family come from, and it is worth stating flatly.

Money you receive is positive; money you pay is negative. For a loan:

• pv is the amount borrowed — money received — and so is positive.
• The payment is money paid — and so is negative.
• CUMIPMT sums interest components of those negative payments, so the result is negative.

A CUMIPMT that returns a positive number has been given inputs with the opposite convention somewhere, and a model that adds CUMIPMT to a cost total rather than subtracting it will be wrong by twice the interest. Wrapping the call in ABS is the conventional fix for presentation and a common source of sign errors in the arithmetic underneath.

Result and edge cases

• start_period = end_period gives the single period's interest — the same quantity IPMT returns for that period.
• start_period = 1 with type = 1 is the case an attached defect stream names: under a beginning-of-period annuity the first payment happens before any interest has accrued, so the first period's interest component is zero, and every implementation has to special-case it. See "What has not been checked".
• Very small rate is where the arithmetic gets interesting rather than the semantics; see the numerical notes.
• Long ranges cost time proportional to end_period − start_period, because the sum is evaluated term by term.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

Microsoft's Learn page documents three conditions. The first of them is printed with the inequalities the wrong way round:

"If rate > 0, nper > 0, or pv > 0, CumIPmt generates an error."

Read literally, that sentence says a positive rate on a positive loan is an error, which would make the function unusable. The sibling page for CUMPRINC states the same condition as "If rate ≤ 0, nper ≤ 0, or pv ≤ 0" — the reading everything else supports. The Handbook records the CumIPmt page's inequality as a documentation defect, evidenced by its own sibling page, and does not adopt it.

The three documented conditions, with that correction noted:

How to implement

Numerical notes

3. The algebraic shortcut is a different function numerically. pmt × count − CUMPRINC and Σ IPMT are equal in exact arithmetic and not in binary64. Choosing between them is a compatibility decision, not an optimization.
4. The balance recurrence amplifies. Each term is rate × balance, and the balance is itself an annuity value; an error in pmt enters every term with the same sign, so residuals accumulate rather than cancel across a long range. This is why cumulative functions are harder than their single-period siblings.
5. type = 1 shifts the whole schedule by one payment and makes the first period's interest exactly zero. A single mis-handled first period changes every subsequent balance.

Rust code

16 cumipmt_fn "CUMIPMT" (6, 6) [V "rate", V "nper", V "pv", V "start_period", V "end_period", V "type"] |s, a, sv| {
    cumulative_out(s, cumulative::cumipmt_kernel(required_num(s, a, 0)?, required_num(s, a, 1)?, required_num(s, a, 2)?, required_num(s, a, 3)?, required_num(s, a, 4)?, required_num(s, a, 5)?))
}

pub fn cumipmt_kernel(
    rate: f64,
    periods: f64,
    pv: f64,
    start_period: f64,
    end_period: f64,
    type_number: f64,
) -> Result<f64, CumulativeFinanceEvalError> {
    let periods = trunc_positive_period(periods)?;
    let start_period = trunc_positive_period(start_period)?;
    let end_period = trunc_positive_period(end_period)?;
    let timing = payment_timing_arg(type_number)?;
    validate_cumulative_inputs(rate, periods, pv, start_period, end_period, timing)?;

    // dc-j9k: Excel's CUMIPMT is the payments less the principal, count * PMT - sum(PPMT), not
    // the sum of IPMT. Over 400 Excel readings (rates 0.001 to 0.25, up to 400 periods, both
    // types) it matches 131 of 195 CUMIPMT bit for bit against 53 for the sum of IPMT, and the
    // others are within a few ulp.
    let payment = time_value::pmt(rate, periods as f64, pv, 0.0, timing).map_err(kernel_error)?;
    let principal = principal_sum(rate, periods, pv, start_period, end_period, timing)?;
    let total = (end_period - start_period + 1) as f64 * payment - principal;
    if total.is_finite() {
        Ok(total)
    } else {
        Err(CumulativeFinanceEvalError::Domain(WorksheetErrorCode::Num))
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• CUMPRINC — the principal half. Same six arguments, same validation, same period range. The identity CUMIPMT + CUMPRINC = pmt × (end_period − start_period + 1) holds exactly in the mathematics. In floating point FN-CUMPRINC-001 measures it to the last bit on one loan and infers the rest, which makes it a useful consistency probe rather than a substitute for either function.
• IPMT — the single-period interest component. CUMIPMT is its sum in the mathematics. On a one-period range FN-CUMPRINC-001 measures CUMPRINC and PPMT differing, so do not assume this pair agrees in the last bits.
• PPMT — the single-period principal component. CUMPRINC is its sum in the mathematics, and not in general in Excel (FN-CUMPRINC-001).
• PMT — the payment the whole family is built on. Every CUMIPMT evaluation computes it first, so any PMT error is inherited.
• FV, PV, NPER, RATE — the other faces of the same annuity equation. The Handbook's research record documents that Excel does not share one annuity helper across them: PMT uses a cancellation-safe discount form where FV and PV use the naive power form. A page that assumes one shared kernel will mispredict all of them.
• ISPMT — a different interest function entirely, for loans where principal is repaid in equal instalments rather than the payment being level. Not a substitute.
• Confused with: IPMT (single period versus range) and ISPMT (level principal versus level payment).

Named variants

No named variants are recorded yet.