RECEIVED

What it does

RECEIVED(settlement, maturity, investment, discount, [basis])

RECEIVED(settlement, maturity, investment, discount, [basis]) returns the amount received at maturity for a fully invested security: one bought for investment, paying nothing in between, and quoted at the bank-discount rate discount.

Let T be the year fraction from settlement to maturity under the chosen day-count basis, and d the discount rate. Bank discount says the price is the redemption less a charge on the redemption:

investment = received × (1 − d × T)

RECEIVED solves that for the redemption:

received = investment / (1 − d × T)

With B the number of days in the year implied by the basis and D the day count from settlement to maturity, T = D/B and

RECEIVED = investment / (1 − discount × D / B)

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

The word "discount" is the point of this function. The second input is a discount rate, charged on the redemption. It is not an interest rate on the investment, and using one here does not give the right answer. With an interest rate r, the amount received is investment × (1 + r × T), which is an ordinary multiplication and needs no function. Over short spans the two are close enough to hide the mistake, and over long spans they are not.

Microsoft's example: settlement 15 February 2008, maturity 15 May 2008, investment 1,000,000, discount 5.75%, basis 2 (Actual/360). That is 90 days, so

1,000,000 / (1 − 0.0575 × 90/360) = 1,014,584.654…

which matches the $1,014,584.65 Microsoft shows.

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.

investment is an amount, not a per-100 price. The result is proportional to it and is in the same units, so a per-100 price returns a per-100 redemption. There is no redemption argument to keep consistent, unlike DISC and INTRATE.

There is no issue date, whatever the page says. Microsoft's page defines its day count DIM as the days from issue to maturity, and its example labels the first date a settlement (issue) date. The function has no issue argument. Every numeric row behind the notes below fits a count from settlement.

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

Try it

Quirks

Result and edge cases

Returns Number, in the units of investment.

• A pole at d × T = 1. As the denominator falls towards zero the result grows without limit, and past that point the formula would give a negative redemption. Excel refuses instead. In the 2,400 rows behind the notes below (Excel 16.0 build 20430, Windows 64-bit, AMD Ryzen 7 4800H), all 282 rows with 1 − d × D/B ≤ 0 return #NUM!, and all 2,118 rows with a positive denominator return a number. Microsoft's page does not list this condition.
• A 10% discount rate reaches the pole at ten years. The function is usable with long spans only while d × T stays below 1, and well before that the answer stops being commercially meaningful.
• The result is always greater than investment for any admissible input, because discount must be positive.
• settlement ≥ maturity is documented as #NUM!, not a reversed reading.
• The fractional part of a date is dropped. 195 of the rows carry a fractional date, and each fits the integer day count.
• Empty, missing and error arguments are expected to follow the shared call model; see The value universe. No reading records this for RECEIVED.

Errors

As documented on Microsoft's RECEIVED page, with what the recorded rows add:

Where documentation and behaviour differ, both are shown: the last row is behaviour Microsoft does not mention. The rows contain no other error, so the first four are Microsoft's statements only.

How to implement

Numerical notes

Near the pole. When d × T is close to 1 the subtraction in the denominator cancels, and the result inherits a large relative error. This is intrinsic to the quantity: a small change in discount moves the answer by a large amount, in any arrangement of the arithmetic. The #NUM! boundary also depends on rounding at that point, so an input sitting exactly on it can fall either way between implementations that order the operations differently.

The year length at basis 1. Microsoft says only that B is the number of days in the year. Reading B back from the 696 numeric basis 1 rows gives one rule, and all 696 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.

The same rule fits PRICEDISC, whose page gives two worked spans. The reference engine at commit 473efa3 accumulates a fraction of each calendar year against that year's own length instead, which does not agree with these rows on spans that cross a year boundary. The notes below add the 1900 case: the fictitious 29 February does not make 1900 a leap year.

The 30/360 bases. All numeric rows at bases 0 and 4 fit the standard US and European 30/360 counts.

Rust code

18 received_fn "RECEIVED" (4, 5) [V "settlement", V "maturity", V "investment", V "discount", V "basis"] |s, a, sv| {
    let (settlement, maturity) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?);
    bill_out(s, bill::received_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, basis(s, a, 4)?))
}

pub fn received_kernel(
    settlement: f64,
    maturity: f64,
    investment: f64,
    discount: f64,
    basis: Option<f64>,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !investment.is_finite() || !discount.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if investment <= 0.0 || discount <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    // Excel: investment/(1 - discount*D/B), the product before the division (dc-j9k: 38 of 38
    // readings; discount times the fraction D/B matched 33).
    let (days, year) = security_days_and_year(settlement, maturity, basis)?;
    let accrual = excel_x87_div(excel_x87_mul(discount, days), year);
    let denom = excel_x87_sub(1.0, accrual);
    if denom <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    Ok(excel_x87_div(investment, denom))
}

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 in the rate: DISC(s, m, investment, RECEIVED(s, m, investment, d, b), b) returns d up to floating-point rounding. DISC documents its third argument as a per-100 price, but it uses the two money arguments only as a ratio.
• PRICEDISC — the same relation solved for the price. PRICEDISC multiplies by (1 − d × T) where RECEIVED divides by it. The two also differ at the far end: PRICEDISC returns a negative price where RECEIVED returns #NUM!.
• INTRATE — the rate on the investment rather than on the redemption. It is not RECEIVED's rate, so RECEIVED(s, m, investment, INTRATE(...), b) does not reproduce the redemption. Convert first with d = r / (1 + r × T), or compute investment × (1 + r × T).
• YEARFRAC — the year fraction on its own, and the quickest way to separate a calendar question from a rate question.
• TBILLPRICE — the nearest T-bill relative, with price rather than redemption as the unknown.

Named variants

No named variants are recorded yet.