DISC

What it does

DISC(settlement, maturity, pr, redemption, [basis])

DISC(settlement, maturity, pr, redemption, [basis]) returns the discount rate of a security bought at price pr per 100 of face value and redeemed at redemption per 100 at maturity.

The underlying model is simple bank discount, not compound interest. Let

• T = the year fraction from settlement to maturity under the chosen day-count basis,
• d = the discount rate the function returns.

The model is the linear price relation

pr = redemption × (1 − d × T)

and DISC inverts it:

d = (redemption − pr) / redemption × 1/T
  = (1 − pr/redemption) / T

Written the way Microsoft's page writes it, with B the number of days in the year implied by the basis and DSM the number of days from settlement to maturity, T = DSM/B and

DISC = (redemption − pr) / redemption × B / DSM

The defining feature — and the reason DISC and INTRATE are not the same function with the arguments renamed — is the denominator of the yield fraction. DISC divides the gain by the redemption value, the amount you get back. INTRATE divides it by the investment, the amount you put in. Money-market instruments are quoted both ways, and swapping them is a real pricing error, not a rounding one.

The two are related exactly: with r the INTRATE figure on the same dates and amounts,

r = d / (1 − d·T)        and        d = r / (1 + r·T)

Domain and range: d is unbounded above as T → 0 (a security maturing tomorrow at any discount implies a huge annualized rate), and d < 0 whenever pr > redemption — a premium purchase gives a negative discount rate. The function has a pole at T = 0, which the settlement-before-maturity requirement removes from the admissible domain.

Nothing here is compounded. DISC is a first-order quotation convention; over long horizons it is not a rate of return in any compounding sense.

Arguments

Microsoft's page documents the basis values as:

Two documented rules that matter more than they look:

1. Dates are Excel serial numbers, and settlement, maturity and basis are truncated to integers. A date-with-time argument therefore loses its time part silently.
2. pr and redemption are per 100 of face value, not amounts. A bill with 1,000,000 face bought for 987,500 is pr = 98.75, not 987500. Because both appear only in the ratio pr/redemption, passing both as raw amounts happens to give the same answer — but mixing one raw amount with one per-100 quote does not, and the function cannot detect it.

The argument slots are numeric and subject to ordinary to-number coercion; see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number — an annualized rate expressed as a decimal fraction, so 5.34% comes back as 0.0534.

• settlement ≥ maturity is a domain error, not a negated result. There is no "reversed-dates" reading; see Errors below.
• pr > redemption is admissible and returns a negative rate.
• Basis choice changes the answer materially, not marginally: a 91-day bill priced on Actual/360 and on Actual/365 differ in the fourth significant figure of the rate. The basis is part of the quotation, not a rounding preference.
• Non-integer basis is truncated toward zero before the range check, so basis = 4.9 reads as 4.
• Empty, missing and error arguments follow the shared call model; see The value universe.

Errors

As documented on Microsoft's DISC page:

Non-numeric text in any slot and error values in any argument surface under the shared coercion rules.

How to implement

Numerical notes

DISC is arithmetically trivial and definitionally hard. Almost all of the difficulty is in T.

Day counts are integer arithmetic on a calendar, and the calendar rules are the algorithm. The 30/360 conventions are not approximations of the actual calendar; they are separate, exactly specified counting rules, and the US (NASD) and European variants differ precisely in how they treat the 31st and the end of February. An implementation that gets the price ratio right to the last bit and the end-of-month adjustment wrong is wrong by cents, not by ULPs.

Rust code

16 disc_fn "DISC" (4, 5) [V "settlement", V "maturity", V "pr", 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::disc_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, basis(s, a, 4)?))
}

pub fn disc_kernel(
    settlement: f64,
    maturity: f64,
    pr: f64,
    redemption: f64,
    basis: Option<f64>,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !pr.is_finite() || !redemption.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if pr <= 0.0 || redemption <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    // Excel: (1 - pr/redemption)*(B/D) (dc-j9k: 60 of 60 readings in the catalogue oracle's
    // sheet dcj9k_closed_form; dividing by the fraction D/B matched 41).
    let (days, year) = security_days_and_year(settlement, maturity, basis)?;
    // Build20430's 12,000 original security readings distinguish stored PC64
    // operations from binary64 arithmetic. Keep the published association.
    let ratio = excel_x87_div(pr, redemption);
    let spread = excel_x87_sub(1.0, ratio);
    Ok(excel_x87_mul(spread, 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

• INTRATE — the same trade quoted on the investment instead of the redemption, with the exact conversion given above. The pair is the single most useful thing to keep straight in this family.
• PRICEDISC — the inverse: given DISC's rate, recover pr. PRICEDISC(DISC(...)) should round-trip up to floating-point rounding.
• YIELDDISC — the yield of a discounted security, which is the INTRATE-side quotation of the same instrument; do not read it as an inverse of DISC.
• TBILLYIELD / TBILLPRICE / TBILLEQ — the same discount arithmetic hard-wired to the Treasury-bill Actual/360 convention, with no basis argument and their own 182-day rules.
• YEARFRAC — the year-fraction machinery DISC depends on, exposed directly. If a DISC answer looks wrong, evaluating YEARFRAC(settlement, maturity, basis) is the fastest way to find out whether the day count or the price ratio is the culprit.

Named variants

No named variants are recorded yet.