Master the TBILLYIELD Function: Calculate Treasury Bill Yields in Excel
=TBILLYIELD(settlement, maturity, pr)The TBILLYIELD function is a specialized financial formula designed to calculate the yield of a Treasury bill based on its settlement date, maturity date, and purchase price. Treasury bills are short-term debt instruments issued by governments, typically maturing within one year, and are considered among the safest investments available. Understanding how to calculate T-bill yields is essential for financial analysts, investment managers, and portfolio strategists who need to evaluate the performance and attractiveness of these instruments. The TBILLYIELD function automates the complex mathematical calculations required to determine the annual percentage return on a Treasury bill investment. Rather than manually computing yields using intricate formulas, this Excel function provides accurate results instantly, enabling professionals to compare multiple T-bill opportunities and make informed investment decisions. By mastering this formula, you'll enhance your financial analysis capabilities and streamline your Treasury bill valuation processes, whether you're working in corporate finance, investment banking, or asset management.
Syntax & Parameters
The TBILLYIELD function uses three essential parameters to calculate Treasury bill yield. The syntax is =TBILLYIELD(settlement, maturity, pr), where each parameter plays a critical role in the calculation. The 'settlement' parameter represents the Treasury bill's settlement date—the date when the investment is purchased or the transaction is completed. This date must be entered as a valid Excel date value (serial number) or text string in a recognized date format. The settlement date marks the beginning of your investment period. The 'maturity' parameter specifies when the Treasury bill reaches its maturity date, meaning when the government repays the face value to the investor. This date must occur after the settlement date and typically falls within 365 days. Excel calculates the holding period by determining the number of days between these two dates. The 'pr' parameter represents the price per 100 face value units. For example, if you purchase a T-bill with a face value of $10,000 for $9,950, the price per 100 would be 99.50. This parameter must be a positive number greater than zero. The TBILLYIELD function then calculates the annualized yield by comparing the discount (difference between face value and purchase price) to the purchase price and adjusting for the actual holding period. All dates must be valid, and the price must reflect realistic market conditions for accurate results.
settlementmaturityprPractical Examples
Standard Corporate Treasury Bill Investment
=TBILLYIELD(DATE(2024,1,15), DATE(2024,7,14), 98.50)This formula calculates the annualized yield for a six-month Treasury bill purchased at a discount. The settlement date is January 15, 2024, maturity is July 14, 2024 (approximately 181 days), and the purchase price is 98.50 per 100 face value. The function returns the annual percentage yield, accounting for the holding period.
Short-Term Government Securities Analysis
=TBILLYIELD(DATE(2024,3,1), DATE(2024,6,1), 99.25)This example demonstrates yield calculation for a shorter-duration T-bill (approximately 92 days). The formula helps the investment manager compare this yield against other short-term investment opportunities and determine if the return meets portfolio objectives.
Year-End Treasury Bill Valuation
=TBILLYIELD(DATE(2024,11,1), DATE(2024,12,15), 99.75)This formula calculates the yield for a very short-term T-bill (approximately 44 days). Despite the brief holding period, the function annualizes the yield to provide a comparable rate metric. This is useful for balance sheet valuation and yield curve analysis.
Key Takeaways
- TBILLYIELD calculates the annualized yield of a Treasury bill given its settlement date, maturity date, and purchase price per 100 face value
- The function assumes a 360-day year (money market convention) and is designed specifically for Treasury bills maturing within one year
- Treasury bill prices below 100 indicate discounts; TBILLYIELD converts this discount into an annualized percentage return for comparison purposes
- Always use the DATE() function for date parameters to ensure cross-platform compatibility and avoid regional date format ambiguities
- Combine TBILLYIELD with TBILLEQ to convert discount yields to bond-equivalent yields for fair comparison with coupon-bearing securities
Pro Tips
Always use the DATE() function instead of text strings for settlement and maturity dates to ensure Excel interprets them correctly across different regional settings and date formats. This eliminates errors caused by ambiguous date formats.
Impact : Prevents #VALUE! errors and ensures consistent formula behavior regardless of system locale settings. Improves formula portability when sharing workbooks across different regions or Excel versions.
Remember that TBILLYIELD assumes a 360-day year (money market convention), not a 365-day calendar year. When comparing with other yield calculations that use 365-day years, adjust your comparisons accordingly to maintain consistency.
Impact : Ensures accurate yield comparisons with industry standards and prevents misleading analysis when benchmarking against market data or other financial systems that may use different day-count conventions.
Create a reference table with multiple TBILLYIELD calculations at different price points to build a yield curve. This helps you quickly identify attractive entry prices and understand the relationship between price and yield for Treasury bills.
Impact : Accelerates investment decision-making by providing visual reference points. Enables rapid price-to-yield conversions and helps identify arbitrage opportunities or mispriced securities in the market.
Combine TBILLYIELD with data validation to ensure prices are entered as reasonable values (typically between 95 and 100 per 100 face value). This prevents accidental entry of unrealistic prices that would produce misleading yield calculations.
Impact : Improves data quality and prevents analytical errors. Ensures all yield calculations are based on realistic market prices, reducing the risk of flawed investment decisions based on erroneous inputs.
Useful Combinations
Treasury Bill Comparison Matrix with TBILLYIELD and TBILLEQ
=TBILLEQ(DATE(2024,1,15), DATE(2024,7,14), TBILLYIELD(DATE(2024,1,15), DATE(2024,7,14), 98.50))This nested formula first calculates the discount yield using TBILLYIELD, then converts it to bond-equivalent yield using TBILLEQ. This allows direct comparison between Treasury bills and coupon-bearing bonds, enabling portfolio managers to compare different fixed-income securities on a consistent basis.
Multi-Bill Portfolio Yield Analysis with AVERAGE
=AVERAGE(TBILLYIELD(DATE(2024,1,15), DATE(2024,7,14), 98.50), TBILLYIELD(DATE(2024,3,1), DATE(2024,6,1), 99.25), TBILLYIELD(DATE(2024,11,1), DATE(2024,12,15), 99.75))This formula calculates yields for multiple Treasury bills and computes the average yield across the portfolio. Useful for portfolio managers who hold several T-bills with different purchase prices and maturities, providing a quick overview of overall portfolio yield performance.
Yield Ladder Analysis with IF and TBILLYIELD
=IF(TBILLYIELD(DATE(2024,1,15), DATE(2024,7,14), 98.50)>0.03, "Above Target", IF(TBILLYIELD(DATE(2024,1,15), DATE(2024,7,14), 98.50)>0.025, "Within Range", "Below Target"))This formula calculates TBILLYIELD and then uses IF statements to categorize the yield relative to target thresholds. Treasury managers can quickly identify which bills meet minimum yield requirements and make buy/sell decisions accordingly, automating yield-based portfolio rules.
Common Errors
Cause: The settlement date is equal to or later than the maturity date, or the price parameter is zero or negative. Excel cannot calculate a yield when the investment period is invalid or the price is unrealistic.
Solution: Verify that the settlement date is earlier than the maturity date. Ensure the price is a positive number greater than zero. Check date entries for typos or incorrect date formatting using DATE() function to ensure proper serial numbers.
Cause: One or more parameters are entered as text strings that cannot be converted to numbers, or date parameters are not recognized as valid dates. This occurs when dates are improperly formatted or price values contain text characters.
Solution: Use the DATE() function to create proper date values instead of text strings. Verify that the price parameter contains only numeric values without currency symbols or text. Ensure regional date settings match your data format, or use the DATE() function consistently.
Cause: The formula references cells that have been deleted, moved, or contain invalid references. This typically occurs when copying formulas between worksheets or after deleting rows/columns containing referenced data.
Solution: Check that all referenced cell ranges still exist and contain valid data. Use absolute references ($A$1) for fixed values if copying formulas. Rebuild the formula using the correct cell addresses or use the DATE() function with hardcoded values.
Troubleshooting Checklist
- 1.Verify that the settlement date is earlier than the maturity date—if they're equal or reversed, TBILLYIELD returns #NUM! error
- 2.Confirm the price parameter is a positive number between approximately 95 and 100 (per 100 face value)—zero, negative numbers, or unrealistic values cause #NUM! errors
- 3.Check that dates are entered using DATE() function or recognized date format—text strings that don't parse as dates produce #VALUE! errors
- 4.Ensure all cell references exist and haven't been deleted—#REF! errors indicate broken references to moved or deleted data
- 5.Verify the maturity date doesn't exceed 365 days from settlement—TBILLYIELD is designed for short-term bills, and very long periods may produce unexpected results
- 6.Confirm that formula cells are formatted as numbers or percentage—cells formatted as text won't display calculated results properly
Edge Cases
Treasury bill with very short duration (less than 7 days)
Behavior: TBILLYIELD produces valid results but annualizes the yield, which can appear disproportionately high compared to the actual holding period return. For example, a 2% return over 3 days annualizes to approximately 243% annual yield.
Solution: Recognize that annualized yields for very short periods may not represent realistic annual returns. Calculate the actual holding period return separately if needed: =(100-pr)/pr
This is mathematically correct but can be misleading for short-duration securities. Always contextualize annualized yields with actual holding periods.
Settlement and maturity dates spanning a weekend or holiday
Behavior: TBILLYIELD calculates based on calendar days, not business days. A Friday-to-Monday settlement counts as 3 calendar days, even though only 1 business day passes.
Solution: Understand that TBILLYIELD uses actual calendar days, not business days. This matches money market conventions but differs from some bond market calculations that use business days.
This is standard practice in Treasury bill markets, so results align with market conventions and Bloomberg terminal calculations.
Price parameter exactly equals 100
Behavior: TBILLYIELD returns 0% (zero yield) because there is no discount. The investor receives exactly the face value with no gain.
Solution: This is correct behavior—par value purchases generate zero discount yield. In practice, Treasury bills are rarely issued at exactly par; they typically trade at discounts.
This edge case represents the break-even point where purchase price equals face value, resulting in zero return on the discount basis.
Limitations
- •TBILLYIELD is designed exclusively for Treasury bills with maturities up to one year; it cannot be used for Treasury notes (2-10 years) or Treasury bonds (20+ years). For longer-term securities, use the YIELD function instead.
- •The function assumes a 360-day year (money market convention), which differs from calendar-year calculations using 365 days. This can produce slight discrepancies when comparing with other financial systems or manual calculations using actual/365 day counts.
- •TBILLYIELD cannot accommodate Treasury bills with embedded options, floating rates, or other complex features. It assumes a straightforward discount instrument with fixed settlement and maturity dates.
- •The formula requires exact settlement and maturity dates; it cannot handle partial day calculations or time-of-day precision. All calculations use full calendar days only, which may not precisely match intraday trading scenarios.
Alternatives
Handles longer-term securities including Treasury notes and bonds with periodic coupon payments. Provides more flexibility for various debt instruments beyond Treasury bills.
When: Use YIELD when analyzing Treasury notes (2-10 years), Treasury bonds (20+ years), or corporate bonds that pay semi-annual coupons. YIELD requires additional parameters for coupon rate and frequency but accommodates longer maturities.
Provides complete transparency and understanding of the underlying mathematics. Allows custom adjustments for specific investment scenarios or day-count conventions.
When: Use manual formulas when you need to customize day-count methods (Actual/360, 30/360) or when TBILLYIELD's assumptions don't match your specific requirements. Formula: =((100-pr)/pr)*(365/(maturity-settlement))
Converts Treasury bill discount yield to bond-equivalent yield for direct comparison with coupon-bearing securities. Useful for comparing T-bills with other fixed-income investments.
When: Use TBILLEQ after calculating TBILLYIELD when you need to compare Treasury bill returns with bond yields. TBILLEQ takes the TBILLYIELD result and adjusts it to bond-equivalent basis for apples-to-apples comparison.
Compatibility
✓ Excel
Since 2007
=TBILLYIELD(settlement, maturity, pr) — Available in all versions from Excel 2007 through Excel 365 with identical syntax and behavior✓Google Sheets
=TBILLYIELD(settlement, maturity, pr) — Google Sheets supports TBILLYIELD with identical syntax and parameters as ExcelGoogle Sheets date handling may differ slightly; use DATE() function for consistency. Results should match Excel calculations for equivalent inputs
✓LibreOffice
=TBILLYIELD(settlement, maturity, pr) — LibreOffice Calc includes TBILLYIELD with the same three-parameter structure as Excel