Master the TBILLEQ Function: Calculate Treasury Bill Bond Equivalent Yield
=TBILLEQ(settlement, maturity, discount)The TBILLEQ function is a specialized financial tool in Excel that calculates the bond equivalent yield for a Treasury bill based on its discount rate. This advanced function is essential for financial analysts, investment professionals, and treasury managers who need to compare Treasury bill returns with other bond investments on a standardized basis. Treasury bills are short-term U.S. government securities sold at a discount, and their returns must be converted to a bond equivalent yield to enable meaningful comparisons with other fixed-income securities. Understanding TBILLEQ is crucial for anyone involved in debt market analysis, portfolio management, or fixed-income valuation. The function bridges the gap between the discount rate convention used for Treasury bills and the bond equivalent yield convention used for most other securities. By mastering this formula, you'll be able to make more informed investment decisions and conduct sophisticated financial analysis that accurately reflects the true yield potential of Treasury bill investments relative to other market instruments.
Syntax & Parameters
The TBILLEQ function uses three essential parameters to calculate the bond equivalent yield: =TBILLEQ(settlement, maturity, discount). The settlement parameter represents the Treasury bill's settlement date—the date when the security is purchased and ownership transfers to the buyer. This date must be entered as a valid Excel date value. The maturity parameter specifies the date when the Treasury bill reaches maturity and the face value is paid to the holder. Treasury bills typically mature within one year of issuance, commonly at 4, 13, 26, or 52 weeks. The discount parameter is the annual discount rate expressed as a decimal (for example, 0.05 for 5%). This represents the percentage discount from the face value at which the Treasury bill is sold. All three parameters are required; omitting any will result in an error. The dates must be valid and the maturity date must be after the settlement date. The discount rate should be positive and typically ranges from 0% to 10% in normal market conditions. Excel will calculate the bond equivalent yield, which annualizes the return using a 365-day year convention.
settlementmaturitydiscountPractical Examples
Standard Treasury Bill Analysis
=TBILLEQ(DATE(2024,1,15), DATE(2024,4,15), 0.0485)This formula converts the discount rate into bond equivalent yield. The settlement date is January 15, 2024, the maturity date is April 15, 2024 (approximately 91 days), and the discount rate is 4.85%. The function accounts for the difference between the discount method used for Treasury bills and the bond yield method used for other securities.
Portfolio Comparison Analysis
=TBILLEQ(DATE(2024,6,1), DATE(2025,5,31), 0.0475)This longer-duration Treasury bill calculation helps the manager compare yields on an equivalent basis with corporate bonds. The one-year maturity period and 4.75% discount rate are converted to bond equivalent yield for accurate comparison purposes.
Short-Term Cash Management
=TBILLEQ(DATE(2024,3,10), DATE(2024,4,7), 0.045)This very short-term Treasury bill calculation converts the discount rate to bond equivalent yield for comparison with money market fund yields. Despite the short duration (28 days), the function properly annualizes the return for meaningful comparison.
Key Takeaways
- TBILLEQ converts Treasury bill discount rates to bond equivalent yields for accurate comparison with other fixed-income securities
- The function requires three parameters: settlement date, maturity date, and discount rate (as decimal)
- Bond equivalent yield is always higher than the discount rate due to the mathematical conversion and 365-day year convention
- TBILLEQ is specifically designed for Treasury bills (typically one year or less); use YIELD for longer-term securities
- Proper date formatting and decimal rate entry are critical to avoid #VALUE! and #NUM! errors
Pro Tips
Always use DATE function or proper date format for settlement and maturity parameters. Excel interprets text dates inconsistently across different regional settings, leading to calculation errors.
Impact : Prevents #VALUE! errors and ensures calculations work correctly regardless of user location or Excel settings.
Create a reference table with multiple Treasury bills and their TBILLEQ calculations. Use conditional formatting to highlight the highest-yielding options for quick visual comparison.
Impact : Streamlines portfolio analysis and decision-making by providing immediate visual feedback on relative Treasury bill yields.
Validate your results by comparing TBILLEQ output with published Treasury bill data from Federal Reserve or Treasury Department sources. This cross-verification ensures your calculations align with market data.
Impact : Builds confidence in your analysis and identifies any data entry errors before making investment decisions.
Remember that TBILLEQ assumes a 365-day year. If you need 360-day calculations (some markets use this convention), adjust your discount rate by multiplying by 360/365 before using TBILLEQ.
Impact : Ensures compatibility with different market conventions and prevents yield comparison errors when dealing with international or alternative market standards.
Useful Combinations
Compare Multiple Treasury Bills
=TBILLEQ(A2, B2, C2) - TBILLEQ(A3, B3, C3)Subtract the bond equivalent yield of one Treasury bill from another to determine the yield spread. This helps identify which Treasury bill offers superior returns and assists in portfolio selection decisions.
Calculate Yield-to-Cost Ratio
=TBILLEQ(A2, B2, C2) / TBILLPRICE(A2, B2, C2)Combine TBILLEQ with TBILLPRICE to create a yield-to-cost ratio metric. This ratio helps analyze the efficiency of Treasury bill investments by comparing yield to actual price paid.
Conditional Treasury Bill Ranking
=IF(TBILLEQ(A2, B2, C2) > 0.045, "BUY", "HOLD")Use TBILLEQ within IF statements to create automated Treasury bill selection criteria. This formula recommends buying Treasury bills when bond equivalent yield exceeds a threshold (4.5%), otherwise hold existing positions.
Common Errors
Cause: The maturity date is not after the settlement date, or the discount rate is negative or exceeds 1 (100%), or the settlement date is after the maturity date by more than one year.
Solution: Verify that maturity date > settlement date. Ensure discount rate is between 0 and 1 (expressed as decimal, not percentage). Check that the time between settlement and maturity is reasonable (typically less than one year for Treasury bills).
Cause: One or more parameters are not recognized as valid numeric values or dates. This occurs when dates are entered as text strings without DATE function conversion, or when the discount rate is entered as text.
Solution: Use the DATE function to properly format dates: DATE(year, month, day). Ensure discount rate is entered as a decimal number (0.05 for 5%), not as text. Remove any extra spaces or special characters from parameter values.
Cause: The formula references cells that have been deleted or moved, or the cell references are broken due to worksheet reorganization.
Solution: Verify all cell references in the formula are correct. Use absolute references ($A$1) if copying the formula to prevent reference shifts. Reconstruct the formula if necessary by re-entering the cell references or using the function wizard.
Troubleshooting Checklist
- 1.Verify that maturity date is after settlement date and the difference is less than one year (typically 4, 13, 26, or 52 weeks)
- 2.Confirm discount rate is entered as a decimal between 0 and 1 (use 0.05 for 5%, not 5)
- 3.Check that dates are formatted as valid Excel dates using DATE function, not text strings
- 4.Ensure all three parameters (settlement, maturity, discount) are provided and none are empty or reference blank cells
- 5.Validate results against published Treasury bill data from Federal Reserve or Treasury Department websites
- 6.Test the formula with known values to confirm it produces expected bond equivalent yield conversions
Edge Cases
Settlement and maturity dates are very close (less than 7 days apart)
Behavior: TBILLEQ calculates correctly but produces yields that may appear extremely high due to annualization of very short periods. The mathematical conversion remains valid but results should be interpreted carefully.
Solution: Verify the dates are correct. Consider whether such short-term Treasury bills are realistic in your analysis. Use the calculated yield appropriately in comparisons.
Extremely short-duration Treasury bills are rare in practice; this usually indicates data entry errors.
Discount rate is exactly 0% (zero-yield Treasury bill)
Behavior: TBILLEQ returns 0 (zero bond equivalent yield). While mathematically correct, this represents a Treasury bill with no return, which is unrealistic in normal market conditions.
Solution: Verify discount rate data. Check if this represents a special situation or data entry error. Confirm with market sources before using in analysis.
Zero-yield Treasury bills do not occur in functioning markets; this typically indicates incorrect data.
Settlement date equals maturity date
Behavior: TBILLEQ returns #NUM! error because there is zero time to maturity, making yield calculations impossible and meaningless.
Solution: Correct the maturity date to be after the settlement date. Verify dates are entered correctly. Ensure you're using actual Treasury bill settlement and maturity dates.
A Treasury bill must have time to maturity; equal dates represent invalid securities.
Limitations
- •TBILLEQ only works for Treasury bills with maturity dates within one year of settlement. For longer-term Treasury securities, you must use YIELD or other bond functions instead.
- •The function assumes a 365-day year convention and cannot be adjusted to use 360-day or other day count conventions. If your analysis requires different conventions, manual calculation is necessary.
- •TBILLEQ requires the discount rate as input; it cannot calculate bond equivalent yield from Treasury bill price alone. Use TBILLYIELD if you have price data instead of discount rate.
- •The function does not account for transaction costs, taxes, or other real-world factors affecting actual investment returns. Results represent theoretical yields only and should be adjusted for practical investment analysis.
Alternatives
Calculates the yield of a Treasury bill directly from price rather than discount rate. Use when you have the price instead of the discount rate.
When: When Treasury bill price data is available from market sources instead of discount rate data, TBILLYIELD provides direct yield calculation without intermediate conversion steps.
More flexible function for any bond type with customizable day count conventions and payment frequency. Provides greater control over calculation parameters.
When: For Treasury notes, bonds, or corporate securities with various maturity dates and payment schedules beyond the scope of Treasury bill specific functions.
Compatibility
✓ Excel
Since 2007
=TBILLEQ(settlement, maturity, discount) - Identical syntax across all versions from 2007 through 365✓Google Sheets
=TBILLEQ(settlement, maturity, discount)Google Sheets supports TBILLEQ with identical syntax and parameters. Date handling uses same DATE function. Results should match Excel calculations.
✓LibreOffice
=TBILLEQ(settlement, maturity, discount)