TBILLEQ

What it does

TBILLEQ(settlement, maturity, discount)

TBILLEQ(settlement, maturity, discount) returns the bond-equivalent yield of a Treasury bill: the yield that lets a bill quoted at a bank-discount rate be compared with a coupon bond.

A bill's price per 100 follows from its discount rate d and the actual day count DSM:

price = 100 × (1 − d × DSM/360)

Write p = price/100. What happens next depends on how long the bill runs, and Microsoft's page describes only one of the two cases.

For the bill of 182 days or fewer, the two forms below are the same number:

TBILLEQ = (365 × d) / (360 − d × DSM)  =  (1/p − 1) × 365/DSM

That is a simple yield on the price, over a 365-day year.

For a bill of more than 182 days, Excel returns

TBILLEQ = (−t + √(t² − (2t − 1)(1 − 1/p))) / (t − 1/2)

with t = DSM/365, as recorded in the notes below. Microsoft's page does not mention 182 days or this expression. Both accounts are shown here because they differ, and on the note's own row the difference is large: 203 days at a discount of about 21.2%, where the documented formula gives 0.244038 and Excel gives 0.241103.

Where the quadratic comes from

A coupon bond pays every half year, so a bill longer than about half a year has to be compared with a bond that has already paid once. The standard construction lets the first half year earn y/2, with the interest reinvested, and the rest of the span earn simple interest at y:

p × (1 + y/2) × (1 + y × (t − 1/2)) = 1

Expanding gives a quadratic in y, and its positive root is the expression above. That is algebra from the construction; Microsoft does not state it.

The table shows how the two compare at a 5% discount. These are formula values, not Excel readings.

The 366-day bill

The note writes t = DSM/365, and that holds with one exception. All 33 numeric rows with DSM = 366 (for example 13 July 2047 to 13 July 2048, a span that crosses a 29 February) fit t = 1, that is 366/366, and do not fit 366/365. The 95 rows of shorter long bills across a 29 February keep 365. The one reading at this length, from serial 0 to serial 366, agrees.

The documented DSM wording

Microsoft says DSM is computed on a 360-day-year basis. Its own example does not bear that out: 31 March 2008 to 1 June 2008 at 9.14% gives 0.0941515 with 62 actual days, matching the 9.42% Microsoft shows, whereas a 30/360 count of 61 gives 0.0941272, or 9.41%. The note's row is 203 actual days where 30/360 would give 200. The 360 in the formula is the year length of the discount quotation; the day count is actual.

Arguments

There is no basis. The day count is always actual, and the conventions in the formulas are fixed. Dates are documented as truncated to integers, and 205 of the 2,400 rows behind the notes carry a fractional date that fits this.

discount is the bank-discount quotation, not a price and not a yield. The function that takes a price is TBILLYIELD.

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: TBILLEQ(B1,5,0.1) is 36.5/359.5. 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 (Excel 16.0 build 20430, Windows 64-bit, AMD Ryzen 7 4800H) 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.

• The branch point is exact in the rows. The 992 numeric rows with DSM ≤ 182 fit the documented formula, including 48 at 182. The 712 numeric rows above it fit the quadratic, including 91 at 183. No row above 182 fits the documented formula.
• A non-positive price is refused. Where d × DSM/360 ≥ 1 the bill would have no positive price, and all 25 such rows inside the one-year limit return #NUM!.
• 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 28 February of the year before a leap year, maturity on 29 February a year later is #NUM! (2 rows), although that is the last day of the month. From 29 February, the 28 February a year later is allowed, and 2 March is #NUM!.
• 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: TBILLEQ(#REF!,TRUE,0.1) is #REF! and TBILLEQ(TRUE,#REF!,0.1) is #VALUE!.

Errors

Three of these depart from Microsoft's page, and both accounts are shown:

• Microsoft's TBILLEQ page says settlement > maturity, where its TBILLYIELD page says settlement ≥ maturity. The formula has a value at DSM = 0, so the difference is not vacuous. A reading of TBILLEQ(0,0,0.1) returns #NUM!. That is the only equal-dates reading.
• Microsoft says an invalid date is #VALUE!. Readings of settlement −1, −0.5 and 2958466 (the first serial past 31 December 9999) all return #NUM!, so an out-of-range number is not treated that way. The #VALUE! measured for a date argument is the logical one.
• Microsoft does not mention a price that reaches zero.

How to implement

Numerical notes

The first branch is plain. One multiplication, one subtraction and one division. It has a pole at d × DSM = 360, which is where the price reaches zero, and Excel refuses from there on.

The second branch has a cancellation site. The numerator is −t plus a square root that is close to t, and it is small when the yield is small or t is close to 1/2. The denominator t − 1/2 is smallest at DSM = 183, where it is 1/730. The expression cannot be evaluated at t = 1/2, and the integer day count keeps it away, because DSM = 182.5 does not exist.

The exact arithmetic. The note records how the square root is staged, and why Excel's answer on its row sits 22 units below the correctly rounded value of the quadratic. Reproducing Excel means following that staging, not computing the quadratic more accurately. The note's count applies to the same 2,400 rows, and one row separates the staged result from the plain one, so this is a small effect measured on few rows.

Discriminant. The value under the square root was positive on all 712 long-bill rows that reached it, so no row shows what a negative value would do. For 0 < p < 1 and t > 1/2 the term −(2t − 1)(1 − 1/p) is positive, so it does not arise on admissible inputs.

Rust code

20 tbilleq_fn "TBILLEQ" (3, 3) [V "settlement", V "maturity", V "discount"] |s, a, sv| {
    let [settlement, maturity, rate] = bill_arguments(s, a, sv)?;
    bill_out(s, bill::tbilleq_kernel(settlement, maturity, rate))
}

pub fn tbilleq_kernel(
    settlement: f64,
    maturity: f64,
    discount: f64,
) -> Result<f64, DiscountBillYearfracEvalError> {
    if !discount.is_finite() {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Value,
        ));
    }
    if discount <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    let dsm = tbill_days(settlement, maturity)?;
    if dsm > 182.0 {
        return tbilleq_long_bill(dsm, discount);
    }
    let denom = 360.0 - discount * dsm;
    if denom <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    Ok(365.0 * discount / denom)
}

/// TBILLEQ for bills of more than 182 days: Excel switches to the bond-equivalent yield of a
/// bill longer than one coupon period, the root of a quadratic in the price. With
/// t = dsm / year and p = price / 100:
///
///   TBILLEQ = (-t + sqrt(t*t - (2t - 1) * (1 - 1/p))) / (t - 0.5)
///
/// `year` is 365, except that a 366-day bill (possible only across a Feb 29) uses 366, so t = 1.
/// The shared staged SQRT resolves the recorded cancellation discrepancy in
/// the long-bill numerator; all 2,400 TBILLEQ bank outcomes agree at R23.
fn tbilleq_long_bill(dsm: f64, discount: f64) -> Result<f64, DiscountBillYearfracEvalError> {
    let num = || DiscountBillYearfracEvalError::Domain(WorksheetErrorCode::Num);
    let price = 100.0 - discount * 100.0 * dsm / 360.0;
    if price <= 0.0 {
        return Err(num());
    }
    let year = if dsm == 366.0 { 366.0 } else { 365.0 };
    let t = dsm / year;
    let term = 1.0 - 1.0 / (price / 100.0);
    let disc = t * t - (2.0 * t - 1.0) * term;
    if disc < 0.0 {
        return Err(num());
    }
    Ok((-t + crate::ported::excel_numeric::excel_sqrt_staged(disc)) / (t - 0.5))
}

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

• TBILLYIELD — the yield of the same bill from its price, on a 360-day year and with no long-bill branch. Up to 182 days, on the same bill, TBILLEQ = TBILLYIELD × 365/360. Above that the relation fails.
• TBILLPRICE — the price from the discount, which is the first step of this function.
• DISC — the general discount quotation with a basis. The T-bill functions fix the convention and add the one-year limit.
• INTRATE — the simple rate on the investment. For bills up to 182 days, TBILLEQ is INTRATE on an actual/365 footing.
• YIELD — the coupon-bond yield that a bond-equivalent yield is built to be compared with.

Named variants

No named variants are recorded yet.