INTRATE

What it does

INTRATE(settlement, maturity, investment, redemption, [basis])

INTRATE(settlement, maturity, investment, redemption, [basis]) returns the simple annualized interest rate for a fully invested security — one that pays nothing until maturity, when it pays redemption against an outlay of investment.

With T the year fraction from settlement to maturity under the chosen day-count basis:

INTRATE = (redemption − investment) / investment × 1/T

In Microsoft's notation, with B the basis year length in days and DIM the number of days from settlement to maturity, T = DIM/B and

INTRATE = (redemption − investment) / investment × B / DIM

"Fully invested" is the definitional constraint, and it means exactly one thing: there are no intermediate cash flows. Every coupon-bearing instrument is outside this function's model, and INTRATE will still return a number if you feed it one — a wrong number, computed correctly.

The rate is simple, not compounded. Over a span longer than a year, INTRATE returns the total gain divided by the number of years, with no reinvestment of interest at all. It is not comparable to an EFFECT-style annual equivalent and it is not RRI.

The pair with DISC

INTRATE and DISC describe the same trade and disagree about the denominator:

Since redemption > investment for any security bought at a discount, INTRATE > DISC always, and the gap widens with T. The exact conversions:

INTRATE = DISC / (1 − DISC·T)
DISC    = INTRATE / (1 + INTRATE·T)

Confusing the two is the classic money-market quotation error. It is not a small error: on a one-year instrument at a 10% discount rate the investment-side rate is about 11.1%.

Arguments

Note the argument-shape asymmetry with DISC, and it is easy to miss: DISC takes a price per $100 face value, while INTRATE takes an amount invested. Because both functions use their two money arguments only as a ratio, either scaling gives the same answer provided both money arguments use the same one — but the documented meanings differ, and a formula that feeds a per-100 price to INTRATE alongside a raw redemption amount is silently wrong by four orders of magnitude.

The documented day-count basis values are the standard five, identical to the table on the DISC page: 0 or omitted = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360. Dates and basis are documented as truncated to integers.

Numeric coercion of the argument slots follows the shared call model; see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number — an annualized rate as a decimal fraction.

• redemption < investment is admissible and yields a negative rate. There is no guard against a loss-making trade.
• settlement ≥ maturity is a domain error rather than a sign flip.
• Short spans amplify. As T → 0 the rate diverges; a one-day instrument at a hundredth of a percent gain annualizes to a large number, and that is the arithmetic working, not failing.
• Non-integer basis is truncated toward zero before the range check.
• Empty, missing and error arguments follow the shared call model; see The value universe.

Errors

As documented on Microsoft's INTRATE page:

How to implement

Numerical notes

The arithmetic is one subtraction, two divisions and a multiplication. All of the interesting behaviour is in T and in one cancellation site.

Cancellation in redemption − investment. For a short-dated instrument the two amounts agree to many significant figures, so the difference loses precision exactly where 1/T is largest. The relative error of the result is the relative error of the difference multiplied by nothing at all — the 1/T factor scales both the value and its error — but the absolute error in the rate grows with 1/T, which is what a reader comparing two nearly identical short-dated quotes will notice. There is no rearrangement that avoids this; the information is genuinely in the last few digits of the two inputs.

Rust code

17 intrate_fn "INTRATE" (4, 5) [V "settlement", V "maturity", V "investment", V "redemption", V "basis"] |s, a, sv| {
    let (settlement, maturity) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?);
    bill_out(s, bill::intrate_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, basis(s, a, 4)?))
}

pub fn intrate_kernel(
    settlement: f64,
    maturity: f64,
    investment: f64,
    redemption: f64,
    basis: Option<f64>,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !investment.is_finite() || !redemption.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if investment <= 0.0 || redemption <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    // Excel: ((redemption - investment)/investment)*(B/D) (dc-j9k: 40 of 40 readings; dividing
    // by the fraction D/B matched 28).
    let (days, year) = security_days_and_year(settlement, maturity, basis)?;
    let spread = excel_x87_sub(redemption, investment);
    let ratio = excel_x87_div(spread, investment);
    Ok(excel_x87_mul(ratio, excel_x87_div(year, days)))
}

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

• DISC — the redemption-side quotation of the same instrument, with the exact conversion above. Read the two pages together.
• RECEIVED — what comes back at maturity for a given investment, but it takes a discount rate, not INTRATE's rate, so RECEIVED(…, INTRATE(…)) does not reproduce the redemption. Convert first with d = r / (1 + r × T), or compute investment × (1 + r × T).
• YIELDMAT — for a security that pays interest at maturity but accrues from an issue date, which INTRATE does not model. If your instrument has an issue date distinct from settlement, INTRATE is the wrong function.
• TBILLEQ — the bond-equivalent yield of a Treasury bill: the same idea of restating a discount quotation on the investment side, wired to the T-bill conventions.
• RRI — the compound rate that takes a present value to a future value over n periods. INTRATE is the simple-interest answer to a superficially similar question, and the two diverge as soon as the horizon exceeds a year.
• YEARFRAC — the day-count machinery, exposed directly; the fastest way to isolate whether a surprising INTRATE result comes from the calendar or from the amounts.

Named variants

No named variants are recorded yet.