YIELDDISC

What it does

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

YIELDDISC(settlement,maturity,pr,redemption,[basis]) returns the annual investment yield of a discounted security. Both amounts are expressed per the same face-value unit.

Try it

=YIELDDISC(DATE(2027,1,1),DATE(2027,1,31),96,100,2) is about 0.5. The gain is four on a price of 96, annualized from thirty actual days using a 360-day year.

Quirks

Basis defaults to zero: 0 US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. Dates and selectors are truncated under their documented rules. Use DATE values rather than locale-dependent date text. Price and redemption must be positive and settlement before maturity. Invalid dates give #VALUE!; invalid amounts, date order or basis give #NUM!. This is yield on purchase price, not the discount rate on redemption.

How to implement

1. Truncate the dates and basis, validate the workbook dates, require positive price and redemption and settlement before maturity.
2. Obtain the numerator day count days for the selected basis. Bases 0 and 4 use their respective 30/360 rules; bases 1, 2 and 3 use actual serial days.
3. Obtain the year denominator B: 360 for bases 0, 2 and 4; 365 for basis 3; the source’s calendar-dependent days_in_year_for_mat for basis 1. The latter can use 365, 366 or a multi-year average. Do not silently substitute a fixed 365 or call a different YEARFRAC algorithm.
4. Using ordinary binary64 stores D, form gain=D(redemption-price), relative_gain=D(gain/price), year_scale=D(B/days) and yield=D(relative_gain*year_scale).

For the example the gain is four, relative gain is about 0.041666666666666664, and year scale is 360/30=12. Their product is one half. The year ratio is computed separately: ((red-price)/price)*B/days is another association with potentially different low bits.

The retained source records 25 of 25 readings distinguishing this grouped year ratio; the left-to-right candidate matched 17. Its calendar scaffolding and initial formula include a credited, changed translation of the Apache-licensed ExcelFinancialFunctions project, alongside project-owned changes guided by recorded answers. This scope does not establish every date seam or whole-function numerical agreement.

Rust code

7 yielddisc_fn "YIELDDISC" (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)?);
    bond_out(s, bond::yielddisc_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, Some(or(s, a, 4, 0.0)?)))
}

pub fn yielddisc_kernel(
    settlement: f64,
    maturity: f64,
    price: f64,
    red: f64,
    basis_: Option<f64>,
) -> Result<f64, BondCoreEvalError> {
    let settlement = dser(settlement)?;
    let maturity = dser(maturity)?;
    let price = pos(price)?;
    let red = pos(red)?;
    let basis_ = basis(basis_.unwrap_or(0.0))?;
    if settlement >= maturity {
        return Err(derr(WorksheetErrorCode::Num));
    }
    // Faithful port of ExcelFinancialFunctions `yieldDisc` (bonds.fs):
    // `(redemption - pr) / pr * b / dim`, with `dim` the numerator day-count and
    // `b` DaysInYear. The prior `(red/price - 1) / yearfrac` form was algebraically
    // equal but ~5 ULP off (different operation order) and used the YEARFRAC
    // act/act algorithm rather than DaysBetween/DaysInYear.
    let dim = dd(settlement, maturity, basis_)?;
    let b = days_in_year_for_mat(settlement, maturity, basis_)?;
    // Excel groups the year ratio: ((red - price)/price)*(b/dim) (dc-j9k: 25 of 25 readings in
    // the catalogue oracle's sheet dcj9k_closed_form; the left-to-right form matched 17).
    Ok(((red - price) / price) * (b / dim))
}

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

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

DISC uses a discount-on-redemption rate; PRICEDISC solves price; TBILLYIELD applies its bill-specific convention.

Named variants

No named variants are recorded yet.