ISPMT

What it does

ISPMT(rate, per, nper, pv)

ISPMT(rate, per, nper, pv) returns the interest paid during period per of a loan in which the principal is repaid in equal instalments — a straight-line amortization, not the constant-total- payment annuity that IPMT models.

The closed form is one line:

ISPMT = pv × rate × (per/nper − 1)

Read it as interest on the remaining balance. Under straight-line repayment the outstanding principal after per instalments is pv × (1 − per/nper), and the interest charged is rate times that:

remaining balance = pv × (nper − per)/nper
interest          = rate × remaining balance = pv × rate × (per/nper − 1) × (−1)

The sign is the family's: with a positive pv (money you received) the result is negative, money leaving. That is why the formula is written with (per/nper − 1), which is negative on the admissible range, rather than the positive (1 − per/nper).

The period index starts at 0, not at 1. This falls straight out of the formula and it is the single most important fact on this page:

So a loan with nper = 10 has its ten interest charges at per = 0 … 9, and ISPMT(rate, 10, 10, pv) is zero rather than the last instalment. IPMT, its near-namesake, indexes 1 … nper. Two functions whose names differ by one letter, whose signatures look alike, and whose first period is a different number.

The result is linear in per: the interest declines by a constant pv·rate/nper each period. That is the visible signature of straight-line amortization, and it is how you tell at a glance which loan model a column of numbers came from — annuity interest decays geometrically, straight-line interest decays in a straight line.

ISPMT exists for Lotus 1-2-3 compatibility. It is not the function to reach for in new work unless the straight-line model is genuinely what you mean.

Arguments

All four are required; there is no fv and no type. The absence of type is not an oversight: under straight-line repayment with the instalment at period end there is nothing for a timing switch to select, and the model has no annuity-due variant.

rate and nper must use the same period, exactly as in the annuity functions. A 10% annual rate on a monthly schedule is 0.1/12.

Try it

Quirks

Result and edge cases

Returns Number — an interest amount carrying the family sign convention.

• per = 0 gives −pv·rate, the largest charge. per = nper gives exactly 0.
• Non-integer per is accepted and interpolates linearly; nothing truncates it.
• rate = 0 returns zero for every period, with no special branch needed.
• Negative pv flips the sign throughout, as the convention requires.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

Microsoft's ISPMT page does not publish an error table in the way the securities functions do. What the Handbook can state:

How to implement

Numerical notes

ISPMT is the numerically easiest function in this batch and it is worth saying why, because the contrast is instructive.

The expression pv × rate × (per/nper − 1) has one division, two multiplications and one subtraction, and no cancellation of consequence. The subtraction per/nper − 1 loses precision only as per → nper, where the true answer is approaching zero anyway, so the relative error of a vanishing quantity degrades while the absolute error stays bounded by rounding. Nothing compounds, nothing iterates, no transcendental function is involved. There is no small-rate regime, no expm1/log1p question, no evaluation-order decision of any consequence.

That is a direct consequence of the model: straight-line amortization is linear, and linear functions are numerically boring. Every difficulty catalogued on the IPMT page — the near-payoff cancellation, the dependence on an accurate PMT, the expm1 substrate, the epsilon-band trap around zero rate — exists because the annuity balance is exponential. ISPMT buys its simplicity by modelling a different loan.

Rust code

9 ispmt_fn "ISPMT" (4, 4) [V "rate", V "per", V "nper", V "pv"] |s, a, sv| {
    tv_out(s, tv::ispmt(num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?, num(s, a, 3)?))
}

pub fn ispmt(
    periodic_rate: f64,
    period_index: f64,
    periods: f64,
    present_value: f64,
) -> Result<f64, FinancialError> {
    validate_finite(&[periodic_rate, period_index, periods, present_value])?;
    if periods <= 0.0 {
        return Err(FinancialError::Num);
    }
    let result = present_value * periodic_rate * (period_index / periods - 1.0);
    if result.is_finite() {
        Ok(result)
    } else {
        Err(FinancialError::Num)
    }
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• IPMT — the same question under a different loan model. IPMT assumes a constant total payment; ISPMT assumes constant principal repayment. They agree on nothing except at nper = 1, and their period indices are offset by one. Substituting one for the other is a modelling error, not a refactor.
• PPMT — the principal component under the annuity model. ISPMT's principal component needs no function: it is pv/nper, constant.
• PMT — the constant total payment. Under ISPMT's model the total payment is not constant: it is pv/nper + ISPMT(per), which declines each period.
• CUMIPMT — cumulative interest under the annuity model. There is no ISPMT cumulative sibling; the sum is closed-form, pv·rate·(nper − 1)/2 over per = 0 … nper − 1.
• SLN — straight-line depreciation, the same "equal instalments" idea applied to an asset rather than a debt. The two functions are conceptual cousins and are often used together.
• Confused with: IPMT, constantly. If a model's interest column is a straight line, it came from ISPMT; if it curves, it came from IPMT.

Named variants

No named variants are recorded yet.