TBILLPRICE

What it does

TBILLPRICE(settlement, maturity, discount)

TBILLPRICE(settlement,maturity,discount) returns a Treasury bill price per 100 face value using an annual discount rate and an actual-day, 360-day denominator.

Try it

=TBILLPRICE(DATE(2027,1,1),DATE(2027,1,31),0.06) returns about 99.5. Thirty days at a six-percent annual discount remove 100*0.06*30/360=0.5 from par.

Quirks

Date values are truncated. Invalid dates give #VALUE!; a nonpositive discount or an invalid/too-long settlement-to-maturity interval gives #NUM!. There is no optional basis argument.

The one-year limit follows calendar rollover, rather than a fixed 365-day cap or an end-of-month clamp. The cited build-20430 controls admit settlement 29 February 2044 with maturity 1 March 2045, but reject settlement 28 February 1991 with maturity 29 February 1992. The first limit is the result of DATE(2045,2,29), which rolls into March.

The current helper rejects equal settlement and maturity, and also rejects a calculated price at or below zero. The opened Microsoft remarks explicitly mention settlement later than maturity and nonpositive discount; they do not spell out those two additional guards. A positive discount alone does not ensure a positive price.

How to implement

1. Coerce the date arguments through the workbook’s date system and truncate their serial values. Validate the discount as finite and positive. Keep the date coercion layer separate: the notes below record a blank reference reading as serial zero, while a logical reference gives #VALUE!.
2. Require settlement before maturity. Convert settlement to year, month and day; construct the next-year date with Excel’s day rollover. Reject maturity beyond that serial date. In the current helper this uses the 1900 date-system conversion functions, including their treatment of Excel’s fictitious leap day.
3. Let days=maturity-settlement. These are actual serial days, without a 30/360 adjustment.
4. With D meaning one nearest/even binary64 rounding, calculate product=D(discount*days), fraction=D(product/360), remaining=D(1-fraction), then price=D(100*remaining). Keep these separate stores and disable contraction into fused operations.
5. Return #NUM! for a nonpositive calculated price; otherwise pass the result through the worksheet’s numeric publisher.

For the thirty-day example, the mathematical discount fraction is 0.06*30/360=0.005, so the price is 100*(1-0.005)=99.5. A discount of twelve over thirty days instead consumes the full face value and reaches the nonpositive-price guard.

This is the current code’s ordinary binary64 graph. The security functions beside it use other stored arithmetic sites, and PRICEDISC subtracts a redemption discount using a different association. An algebraically identical formula does not settle their last bits.

Rust code

21 tbillprice_fn "TBILLPRICE" (3, 3) [V "settlement", V "maturity", V "discount"] |s, a, sv| {
    let [settlement, maturity, rate] = bill_arguments(s, a, sv)?;
    bill_out(s, bill::tbillprice_kernel(settlement, maturity, rate))
}

pub fn tbillprice_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)?;
    let price = 100.0 * (1.0 - discount * dsm / 360.0);
    // Excel publishes #NUM! rather than a non-positive price (live Excel 20430, W111-5 G8-03).
    if price <= 0.0 {
        return Err(DiscountBillYearfracEvalError::Domain(
            WorksheetErrorCode::Num,
        ));
    }
    Ok(price)
}

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

TBILLYIELD computes a bill investment yield; TBILLEQ computes its specified equivalent yield; PRICEDISC accepts a general basis.

Named variants

No named variants are recorded yet.