When investing in short-term money market instruments—specifically sovereign Treasury bills (T-bills)—securities are traditionally sold at a discount to their par face value rather than distributing periodic semi-annual coupon payments. Because discount yields (such as those quoted by central banks) are computed using a 360-day year convention based on par value rather than actual invested capital, comparing a 90-day T-bill yield directly against a conventional 2-year or 5-year coupon bond introduces severe pricing skew.
To standardize comparisons across fixed-income asset classes, financial markets calculate the Bond-Equivalent Yield (BEY). In Google Sheets, this financial conversion is automated through the dedicated TBILLEQ function. This guide explains how the function translates money market discount rates into accurate, annualized bond-equivalent yields.
Understanding the Financial Mathematics of TBILLEQ
The TBILLEQ function calculates the annualized percentage yield an investor realizes on actual purchase capital, adjusting for an actual 365-day (or 366-day leap) calendar year:
BEY = (365 * Discount Rate) / [360 - (Discount Rate * Days Between Settlement and Maturity)]When the term to maturity is six months or less, this linear money market formula accurately reconciles cash yields. When maturity exceeds six months, TBILLEQ seamlessly switches to a compounding semi-annual coupon equivalent equation, ensuring strict regulatory compliance with sovereign bond market pricing standards.
Syntax and Parameter Breakdown
=TBILLEQ(settlement, maturity, discount)Parameter Reference
- settlement: The transaction settlement date when funds are exchanged (e.g.
DATE(2026, 3, 1)). Must be entered as a valid date serial. - maturity: The contractual redemption date of the Treasury bill. Under financial regulations, a T-bill maturity must be strictly one year or less from settlement (maximum 365 calendar days).
- discount: The annualized discount rate at which the Treasury bill is purchased, expressed as a decimal or percentage (e.g.
0.0485or4.85%).
Step-by-Step Practical Implementation
Suppose an institutional treasury team purchases a 6-month US Treasury bill under the following terms:
- Settlement Date: 1 March 2026
- Maturity Date: 1 September 2026
- Quoted Discount Rate: 5.15%
Configure your spreadsheet model:
A1: Settlement Date | B1: =DATE(2026, 3, 1)
A2: Maturity Date | B2: =DATE(2026, 9, 1)
A3: Quoted Discount | B3: 5.15%
A4: Bond-Equivalent Yield | B4: =TBILLEQ(B1, B2, B3)Google Sheets evaluates the elapsed day count (184 days) and returns a Bond-Equivalent Yield of 5.361%. This reveals that the investment actually generates a 5.361% annual yield on invested cash—significantly higher than the nominal 5.15% discount rate quoted on trading desks.
Comparison: Treasury Bill Formulas in Google Sheets
| Function | Primary Output | Financial Purpose |
|---|---|---|
| TBILLEQ | Bond-Equivalent Yield (Percentage) | Compares T-bill return against conventional coupon bonds |
| TBILLPRICE | Price per $100 face value | Computes exact secondary market cash purchase price |
| TBILLYIELD | Annualized Yield (Percentage) | Solves yield given a specific cash purchase price |
Troubleshooting Common Errors
- #NUM! Error: Occurs if
settlement >= maturity, or if the period between settlement and maturity exceeds 365 days (Treasury bills cannot exceed a 1-year lifespan). - #VALUE! Error: Occurs when dates are supplied as raw text strings without the
DATE()function.