PRICEDISC

What it does

PRICEDISC(settlement, maturity, discount, redemption, [basis])

PRICEDISC(settlement, maturity, discount, redemption, [basis]) returns the price per 100 of face value of a security sold at a discount: one that pays nothing until maturity, when it pays redemption.

The model is simple bank discount. With T the year fraction from settlement to maturity under the chosen day-count basis, and d the discount rate:

price = redemption × (1 − d × T)

Written with B the number of days in the year implied by the basis and DSM the number of days from settlement to maturity, so that T = DSM/B:

PRICEDISC = redemption − discount × redemption × DSM / B

Microsoft's page prints its equation as an image that could not be read, and defines B and DSM in words. The expression above is the one the notes below record from Excel's own answers.

The discount is charged on the redemption value, not on the amount paid, and it is charged once. Nothing compounds. That makes the price a straight line in time, falling from redemption at T = 0 and reaching zero at T = 1/d. A 10% discount rate reaches zero at ten years.

Microsoft's example: settlement 16 February 2008, maturity 1 March 2008, discount 5.25%, redemption 100, basis 2 (Actual/360). That is 14 days, and 2008 is a leap year, so

100 × (1 − 0.0525 × 14/360) = 99.795833…

which Microsoft shows as $99.80.

Arguments

The documented basis values are the standard five, as 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, and Microsoft advises entering dates with DATE rather than as text.

redemption is documented as a per-100 quantity. The result is proportional to it, so passing an amount returns the price of that amount; the function has no way to tell the two apart.

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

Try it

Quirks

Result and edge cases

Returns Number, a price per 100 of face value, or an amount in the units of redemption when redemption is an amount.

• A negative price is an answer, not an error. There is no check on the result. In the 2,400 rows behind the notes below (Excel 16.0 build 20430, Windows 64-bit, AMD Ryzen 7 4800H), 284 return a negative price and none returns an error. The documented conditions are on the inputs; a long span at a high discount passes all of them. The sibling RECEIVED does refuse the equivalent case, and the two functions differ here.
• settlement ≥ maturity is documented as #NUM!, not a reversed reading.
• The fractional part of a date is dropped. 195 of those rows carry a fractional date, and each fits the integer day count.
• The 1900 calendar. The rows include a settlement of serial 60, the fictitious 29 February 1900, and on basis 4 it counts as day 29 of February, not as 1 March. The basis 1 treatment of the same date is the subject of the second note below.
• Empty, missing and error arguments are expected to follow the shared call model; see The value universe. No reading records this for PRICEDISC.

Errors

As documented on Microsoft's PRICEDISC page:

None of these rows is measured. All 2,400 recorded rows return a number, so the table is what Microsoft states and nothing more.

How to implement

Numerical notes

The arithmetic is one subtraction, two multiplications and one division. Two things matter: how T is obtained, and the order of the operations.

Near-zero prices. Where d × T is close to 1, the subtraction cancels: the result is small and its relative error is large, though its absolute error stays of the order of rounding at the size of redemption.

The year length at basis 1. Microsoft says only that B is the number of days in the year. Reading B back from the 815 basis 1 rows gives one rule, and all 815 fit it to at least ten significant figures:

• for a span of at most one year, if both dates are in one calendar year, B is that year's length; otherwise B is 366 if a 29 February lies in the span and 365 if not;
• for a span of more than one year, B is the average length of the calendar years from the settlement year to the maturity year, both included.

So 31 May 2012 to 19 February 2013 is 264 days with B = 365, although 2012 is a leap year, because 29 February 2012 lies before the span. And 30 April 1923 to 12 June 1924 is 409 days with B = 365.5, the average of 365 and 366.

The 30/360 bases. All rows at bases 0 and 4 fit the standard US and European 30/360 counts, with the one 1900 date noted above. PRICEMAT is the family member that departs from the standard rule at basis 0; see PRICEMAT.

Rust code

19 pricedisc_fn "PRICEDISC" (4, 5) [V "settlement", V "maturity", V "discount", 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::pricedisc_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, basis(s, a, 4)?))
}

pub fn pricedisc_kernel(
    settlement: f64,
    maturity: f64,
    discount: f64,
    redemption: f64,
    basis: Option<f64>,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !discount.is_finite() || !redemption.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if discount <= 0.0 || redemption <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    // Excel: redemption - discount*redemption*D/B, from left to right (dc-j9k: 40 of 40
    // readings; redemption*(1 - discount*fraction) matched 21).
    let (days, year) = security_days_and_year(settlement, maturity, basis)?;
    let amount = excel_x87_mul(discount, redemption);
    let accrual = excel_x87_div(excel_x87_mul(amount, days), year);
    Ok(excel_x87_sub(redemption, accrual))
}

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 inverse: given the price, recover the rate. DISC(s, m, PRICEDISC(s, m, d, r, b), r, b) returns d up to floating-point rounding, for any positive price. A negative price from PRICEDISC has no inverse, because DISC documents pr ≤ 0 as #NUM!.
• RECEIVED — the same relation solved for the other end of the trade: price known, redemption wanted. PRICEDISC multiplies by (1 − d × T) and RECEIVED divides by it.
• INTRATE — the same trade quoted on the investment. If the rate you hold is an INTRATE-style rate, it is not a valid discount here. Convert it first with d = r / (1 + r × T).
• TBILLPRICE — this function fixed to redemption 100 and Actual/360, with a one-year limit.
• PRICEMAT — the price of a security that does pay interest, all at maturity. PRICEDISC models no interest at all.
• YEARFRAC — the year fraction on its own, and the quickest way to separate a calendar question from a price question.

Named variants

No named variants are recorded yet.