TBILLYIELD

What it does

TBILLYIELD(settlement, maturity, pr)

TBILLYIELD(settlement, maturity, pr) returns the yield of a Treasury bill bought at price pr per 100 of face value and redeemed at 100.

With DSM the actual number of days from settlement to maturity:

TBILLYIELD = (100 − pr) / pr × 360 / DSM

The first factor is the gain as a fraction of the price paid. The second scales it to a year of 360 days. Microsoft's page prints its equation as an image that could not be read and defines DSM in words. The expression above is the one the first note below records from Excel's own answers, including the grouping ((100 − pr)/pr) × (360/DSM).

This is a yield on the investment, simple and uncompounded, on a 360-day year. It is not a discount rate, which would divide by 100 and not by pr, and it is not the bond-equivalent yield that TBILLEQ returns, which uses a 365-day year.

The relation to the discount rate d of the same bill is exact:

TBILLYIELD = 360 × d / (360 − d × DSM)

Microsoft's example: settlement 31 March 2008, maturity 1 June 2008, price 98.45. That is 62 actual days, so

(100 − 98.45) / 98.45 × 360 / 62 = 0.0914169…

which Microsoft shows as 9.14%. A 30/360 count would be 61 and would give 0.0929, so the example confirms actual days.

One formula at every span. Unlike TBILLEQ, this function has no long-bill branch. In the rows behind the notes below (Excel 16.0 build 20430, Windows 64-bit, AMD Ryzen 7 4800H), all 1,586 numeric rows fit the formula above, including the 738 with DSM above 182.

Arguments

There is no basis, and there is no redemption argument: a bill redeems at 100. An amount passed as pr returns a nonsense answer, not an error, because the function subtracts it from 100.

Dates are documented as truncated to integers, and 196 of the 2,400 rows behind the notes carry a fractional date that fits this.

Date arguments do not follow the ordinary to-number rules. Logicals are refused with #VALUE! in all three slots, including FALSE. An empty cell as settlement reads as serial 0 and gives a normal answer, as the notes below record for this family: TBILLYIELD(B1,5,90) is 8. A direct 0 gives the same answer. See Coercion and lifting for the shared model these depart from.

Try it

Quirks

Result and edge cases

Returns Number, an annualized yield as a decimal fraction.

Figures in this section and the next are from the 2,400 rows behind the notes below unless marked as a reading. Readings are from Excel 16.0.20527.20088 on Windows, with bitness and CPU not recorded in the reading file.

• pr > 100 is admissible and yields a negative rate. There is no check against a price above redemption. All 27 such rows inside the date limits return a negative number.
• A low price and a short span amplify. The first note's example is a one-day bill at a price of about 2.28, with a yield of about 15,431. The formula is working, and the number means nothing commercially.
• The one-year limit is a calendar rule. The bound is the same month and day a year on, not a count of days. A 366-day bill across a 29 February is allowed. From 29 February, maturity on 1 March a year later is allowed (3 rows), and 2 March is #NUM!. From 28 February of the year before a leap year, maturity on 29 February a year later is #NUM! (2 rows), although it is the last day of the month.
• Serial 0 works as a settlement date. Readings give numbers for maturities of serial 1, 5, 365 and 366, and #NUM! for serial 367.
• The order of arguments decides which error appears. Readings show the first failing argument wins: TBILLYIELD(#REF!,TRUE,90) is #REF! and TBILLYIELD(TRUE,#REF!,90) is #VALUE!.

Errors

Microsoft says an invalid date is #VALUE!. The one reading with a negative settlement, TBILLYIELD(−1,TRUE,90), returns #NUM!, ahead of the logical in the next slot, so an out-of-range number is not treated that way. The #VALUE! measured for a date argument is the logical one.

How to implement

Numerical notes

Cancellation. When pr is close to 100, the subtraction 100 − pr cancels: the difference keeps the absolute accuracy of pr and loses relative accuracy. DSM is small for short bills, so 360/DSM then scales up that error. This is intrinsic to a yield quoted from a price, and no rearrangement recovers digits the input does not have.

No calendar arithmetic. There is no basis and no year length, so none of the family's 30/360 and Actual/actual questions arise. The only calendar logic is the one-year limit.

Rust code

22 tbillyield_fn "TBILLYIELD" (3, 3) [V "settlement", V "maturity", V "pr"] |s, a, sv| {
    let [settlement, maturity, rate] = bill_arguments(s, a, sv)?;
    bill_out(s, bill::tbillyield_kernel(settlement, maturity, rate))
}

pub fn tbillyield_kernel(
    settlement: f64,
    maturity: f64,
    pr: f64,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !pr.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if pr <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    let dsm = tbill_days(settlement, maturity)?;
    // Excel groups the day-scale factor before the final multiplication:
    // ((100-pr)/pr) * (360/dsm).  The mathematically equivalent left-associative
    // `*360/dsm` form differs by 1 ULP on hundreds of settlement/price rows.
    let spread = excel_x87_sub(100.0, pr);
    let ratio = excel_x87_div(spread, pr);
    Ok(excel_x87_mul(ratio, excel_x87_div(360.0, dsm)))
}

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

• TBILLEQ — the bond-equivalent yield of the same bill. Up to 182 days, TBILLEQ = TBILLYIELD × 365/360 for a bill at the same price. Above 182 days TBILLEQ changes formula and this relation fails.
• TBILLPRICE — the price from the discount rate. It is not the inverse of this function, which takes a yield on the investment, so feeding a TBILLYIELD result to TBILLPRICE as discount does not recover pr.
• DISC — the discount rate from the price, with a basis. It divides by the redemption where TBILLYIELD divides by the price.
• INTRATE — the same investment-side rate. INTRATE(s, m, pr, 100, 2) is the same quantity on Actual/360, apart from the one-year limit; FN-DISC-001 gives both the same order of arithmetic.
• YEARFRAC — not used here. The day count is a plain difference of serials, and there is no year-length question at all.

Named variants

No named variants are recorded yet.