CUMPRINC

What it does

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

CUMPRINC(rate, nper, pv, start_period, end_period, type) returns the total principal repaid on a loan between two period numbers, inclusive — the amount by which the outstanding balance falls over that span.

It is built on the same level-payment annuity as its sibling. The model is one equation:

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

Solving for the payment gives the constant pmt; each period then splits it:

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,

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

In Excel that sum holds on one loan and not in general. CUMPRINC(0.05/12,360,200000,1,12,0) is the left-to-right binary64 sum of Excel's own twelve PPMT answers, to the last bit, but over recorded type 0 calls for a single period CUMPRINC equals PPMT on 56 of 135 rows, with PPMT computed by the route in FN-IPMT-001 (FN-CUMPRINC-001).

Because fv is fixed at zero, summing over the whole schedule gives back the loan: CUMPRINC(r, n, pv, 1, n, type) is −pv in exact arithmetic. That identity is the single best sanity check available for this function, and it is exact in the mathematics and only approximate in binary64 — which makes it a measurement rather than an assertion.

Arguments

CUMPRINC(rate, nper, pv, start_period, end_period, type) — six arguments, all required, and identical in meaning to CUMIPMT.

Periods are numbered from 1. rate and nper must use the same unit — Microsoft: "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."

Try it

Quirks

Sign conventions

Money you receive is positive; money you pay is negative. For a loan pv is positive — cash in — and every payment is negative, so CUMPRINC returns a negative number. Repaying principal is money leaving.

The full-schedule identity above is the clearest statement of the convention: borrow pv, repay −pv. A model that treats CUMPRINC as a positive "principal repaid" figure without negating it will report the loan growing.

Result and edge cases

• start_period = 1, end_period = nper returns −pv in exact arithmetic. Any deviation is accumulated floating-point error, and measuring it is the most informative single experiment on this page.
• type = 1 shifts the schedule: the first payment happens before interest accrues, so the first period's principal component is the whole payment. This is the same first-period boundary an attached defect stream names for the interest side.
• Early periods repay little principal, late periods almost all of it; the function is strongly non-linear in start_period even though the payment is constant.
• Long ranges cost time proportional to the number of periods summed.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

Microsoft's Learn page for this function documents three conditions, and states the first one with the inequalities the right way round:

"If rate ≤ 0, nper ≤ 0, or pv ≤ 0, CumPrinc generates an error."

The sibling page disagrees with this one. Microsoft's CumIPmt page states the same condition as "If rate > 0, nper > 0, or pv > 0 … generates an error" — the inequalities inverted. Two pages documenting the same validation for two functions that share an implementation cannot both be right; this page's form is the one the rest of the documentation and every implementation support. Recorded on CUMIPMT as a documentation defect.

How to implement

Numerical notes

1. The subtraction is where the digits go. Each summand is pmt − r × balance; early in a loan those two quantities are close, so the principal component is a small difference of two larger numbers. That is ordinary cancellation, and it means CUMPRINC over an early range is intrinsically harder than CUMIPMT over the same range.
2. Summation order is observable, and so is the choice between summing PPMT terms and forming pmt × count − CUMIPMT. Those are different functions in binary64.
3. The full-schedule identity is a calibration instrument. CUMPRINC(r, n, pv, 1, n, type) should be −pv; the deviation is a direct, dimensioned measure of the accumulated error, and it needs no oracle to observe.
4. Boundary periods are where implementations fork: the first period under type = 1, and the last period, where the remaining balance should land exactly on zero rather than on a small residual.

Rust code

17 cumprinc_fn "CUMPRINC" (6, 6) [V "rate", V "nper", V "pv", V "start_period", V "end_period", V "type"] |s, a, sv| {
    cumulative_out(s, cumulative::cumprinc_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 cumprinc_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)?;

    let total = principal_sum(rate, periods, pv, start_period, end_period, timing)?;
    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

• CUMIPMT — the interest half. CUMIPMT + CUMPRINC equals pmt × (end_period − start_period + 1) in exact arithmetic; in floating point the residual is a measurement of both functions at once.
• PPMT — the single-period principal component and this function's summand in the mathematics. On a one-period range the two differ on 79 of 135 recorded rows (FN-CUMPRINC-001).
• IPMT — the interest component, and CUMIPMT's summand in the mathematics.
• PMT — the payment every evaluation starts from. Any PMT error is inherited by both cumulative functions with the same sign in every term.
• 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 kernel across them: PMT uses a cancellation-safe discount form where FV and PV use the naive power form.
• ISPMT — a different loan model (level principal rather than level payment); not a substitute.
• Confused with: PPMT (single period versus range), and with a hand-built amortization table, which will disagree in the last digits precisely because it accumulates in a different order.

Named variants

No named variants are recorded yet.