Master TBILLPRICE: Complete Guide to Excel Treasury Bill Pricing
=TBILLPRICE(settlement, maturity, discount)The TBILLPRICE function is a specialized financial formula in Excel designed to calculate the price of a Treasury bill based on its discount rate. Treasury bills are short-term debt instruments issued by governments, typically maturing within one year, and are fundamental instruments in fixed-income portfolios and cash management strategies. Understanding how to calculate T-bill prices accurately is essential for financial analysts, investment professionals, and treasury managers who need to evaluate investment returns and make informed decisions about short-term debt instruments. TBILLPRICE returns the price per $100 face value of a Treasury bill, enabling professionals to determine the actual cost of acquiring these securities. This formula becomes indispensable when comparing multiple T-bill offerings, building investment portfolios, or performing yield analysis. The calculation accounts for the time between settlement and maturity dates, the discount rate provided by the market, and automatically adjusts for the actual number of days in the period, following standard financial market conventions for Treasury instruments.
Syntax & Parameters
The TBILLPRICE formula follows the syntax: =TBILLPRICE(settlement, maturity, discount). The settlement parameter represents the date when the Treasury bill transaction occurs and the buyer takes ownership—this is typically the trade date or the date funds are exchanged. The maturity parameter specifies when the Treasury bill reaches its final value and the investor receives full face value repayment; for T-bills, this is usually 90, 180, or 360 days after settlement. The discount parameter represents the annual discount rate expressed as a decimal (for example, 0.05 for 5%), which reflects the market's required return on the investment. Each parameter must be formatted correctly: dates should be valid Excel date values (either as DATE functions or recognized date formats), and the discount rate should be a positive decimal between 0 and 1. The formula calculates the discounted price by considering the exact number of days between settlement and maturity, dividing by 360 (the standard convention for Treasury bill calculations), and applying the discount rate to determine the purchase price. Understanding these components is crucial because even small errors in date entry or discount formatting can significantly affect pricing calculations and investment decisions.
settlementmaturitydiscountPractical Examples
Standard 91-Day Treasury Bill Pricing
=TBILLPRICE(DATE(2024,1,15), DATE(2024,4,15), 0.045)This formula calculates the price per $100 face value for a standard 91-day Treasury bill. The discount rate of 4.5% (entered as 0.045) is applied to the 91-day period, resulting in a discounted purchase price below par value.
180-Day Treasury Bill with Higher Discount Rate
=TBILLPRICE(DATE(2024,3,1), DATE(2024,8,28), 0.052)This example demonstrates pricing for a longer-duration T-bill with a higher discount rate. The 180-day period combined with the 5.2% rate results in a more significant discount from par value, reflecting the longer investment horizon and higher required return.
Dynamic T-Bill Pricing with Cell References
=TBILLPRICE(A2, B2, C2)Using cell references instead of hard-coded values enables flexible financial modeling. Analysts can easily change settlement dates, maturity dates, or discount rates to perform sensitivity analysis and compare different investment scenarios without modifying the formula structure.
Key Takeaways
- TBILLPRICE calculates the purchase price per $100 face value of a Treasury bill using settlement date, maturity date, and annual discount rate as inputs.
- Results always fall below 100 because Treasury bills are discount instruments sold at prices below par value; the discount amount represents investor profit.
- The formula uses a 360-day year convention standard for Treasury markets, calculating exact calendar days between settlement and maturity without excluding weekends or holidays.
- Always enter discount rates as decimals (0.05 for 5%) and dates as valid Excel date values; percentage entry or text dates cause #VALUE! errors.
- TBILLPRICE pairs with TBILLYIELD for round-trip verification and with PRICEDISC for alternative discount security pricing, enabling comprehensive fixed-income analysis.
Pro Tips
Use named ranges for settlement, maturity, and discount parameters to create self-documenting formulas that are easier to audit and maintain. Create names like 'TBill_Settlement', 'TBill_Maturity', and 'TBill_Discount' to reference throughout your financial model.
Impact : Improves formula readability, reduces errors from cell reference mistakes, and makes financial models more maintainable for team collaboration and future updates.
Build a sensitivity table by varying discount rates (rows) against different maturity dates (columns), with TBILLPRICE calculating prices at each intersection. This visual matrix helps quickly identify pricing trends and optimal investment opportunities.
Impact : Enables rapid scenario analysis and helps identify market opportunities; saves time compared to manual calculations and provides clear visualization of price sensitivity to rate changes.
Always validate TBILLPRICE results by comparing against Bloomberg, Treasury Department data, or other authoritative sources for the same T-bill parameters. Create a verification column that flags discrepancies exceeding acceptable tolerance levels (typically 0.01%).
Impact : Ensures data accuracy in financial reporting, prevents costly trading errors, and maintains audit compliance for institutional investors and treasury departments.
Combine TBILLPRICE with data validation to create dropdown menus for common T-bill maturities (91-day, 182-day, 365-day) and discount rates, enabling non-technical users to generate accurate prices without formula knowledge.
Impact : Democratizes access to complex financial calculations, reduces user error, and enables faster decision-making across broader teams within financial organizations.
Useful Combinations
Calculate Investment Return Using TBILLPRICE and TBILLYIELD
=TBILLYIELD(DATE(2024,1,15), DATE(2024,4,15), TBILLPRICE(DATE(2024,1,15), DATE(2024,4,15), 0.045))This nested formula chain calculates the yield by using TBILLPRICE as input to TBILLYIELD, creating a round-trip verification. This validates your pricing calculation and confirms the discount-to-yield conversion, useful for reconciliation and audit purposes in financial reporting.
Portfolio Analysis: Sum of Multiple T-Bill Positions
=SUMPRODUCT((TBILLPRICE(A2:A10, B2:B10, C2:C10)) * D2:D10 / 100)This formula calculates the total cost of a portfolio containing multiple T-bill positions. TBILLPRICE generates individual prices, multiplied by face values (in column D) to determine total investment required. This enables quick portfolio valuation and cash requirement analysis across multiple Treasury bill holdings.
Discount Rate Comparison with IF Statement
=IF(TBILLPRICE(A2, B2, 0.04) > TBILLPRICE(A2, B2, 0.05), "Lower rate better", "Higher rate better")This conditional formula compares T-bill prices at different discount rates to determine which rate offers better value. When the 4% rate produces a higher price than the 5% rate, it indicates the lower discount rate is more favorable for investors seeking better pricing on purchases.
Common Errors
Cause: The discount parameter is entered as a percentage (like 5) instead of a decimal (0.05), or dates are formatted as text strings rather than valid Excel date values.
Solution: Always convert percentages to decimals by dividing by 100 or using the format 0.05 for 5%. Ensure dates are recognized as Excel date values using DATE() function or proper date formatting. Verify with =ISNUMBER() and =ISDATE() checks if uncertain.
Cause: The settlement date is after the maturity date, creating a negative time period, or the discount rate is negative or exceeds 1 (100%), violating Treasury bill market conventions.
Solution: Verify chronological order: settlement must always precede maturity. Check that discount rates are between 0 and 1. Use data validation to prevent invalid date sequences and ensure discount rates fall within acceptable market ranges.
Cause: Cell references in the formula point to deleted columns or rows, breaking the formula link to required parameters.
Solution: Audit formula references by clicking the formula bar and checking that all cell references are valid and visible. Use Find & Replace (Ctrl+H) to update references if columns were inserted or deleted. Consider using absolute references ($A$2) for fixed parameters in financial models.
Troubleshooting Checklist
- 1.Verify that settlement date precedes maturity date; if reversed, TBILLPRICE returns #NUM! error. Check date order using conditional formatting or validation rules.
- 2.Confirm discount rate is entered as decimal (0.05) not percentage (5); percentage entry causes #VALUE! error. Use formula =C2/100 if rates are stored as percentages.
- 3.Ensure both date parameters are recognized as Excel date values, not text strings. Test with =ISNUMBER() function; dates should return TRUE when converted to numbers.
- 4.Check that discount rate falls between 0 and 1 (0% to 100%). Values outside this range produce #NUM! error or mathematically invalid results.
- 5.Verify cell references haven't been deleted or moved; #REF! errors indicate broken formula links. Use Find & Replace to update references if columns/rows were inserted.
- 6.Confirm the time period between settlement and maturity is reasonable (typically 91-365 days for Treasury bills). Periods exceeding one year may indicate data entry errors or inappropriate function choice.
Edge Cases
Settlement and maturity dates are identical (zero days between dates)
Behavior: TBILLPRICE returns 100 because no time passes for discounting; the purchase price equals face value. Mathematically valid but unrealistic in practice.
Solution: Add validation to prevent zero-day periods. Use data validation or conditional formatting to flag entries where maturity equals settlement.
This edge case indicates data entry error; real T-bills require minimum time between settlement and maturity.
Discount rate equals zero (0%)
Behavior: TBILLPRICE returns exactly 100, indicating no discount applied; purchase price equals face value. Mathematically correct but represents unusual market conditions.
Solution: No correction needed; formula handles correctly. However, 0% discount rates rarely occur in real markets and may indicate data error.
Verify 0% discount rate against market data; this typically signals market anomaly or data quality issue.
Maturity date is more than one year after settlement (e.g., 500 days)
Behavior: TBILLPRICE calculates mathematically but produces inappropriate results because Treasury bills by definition mature within one year. The formula doesn't validate instrument type.
Solution: Add validation to restrict maturity periods to 365 days maximum. Use IF statement to flag entries exceeding one-year periods.
TBILLPRICE is specifically designed for short-term Treasury bills; use PRICE function for longer-term Treasury instruments instead.
Limitations
- •TBILLPRICE only calculates prices; it doesn't validate whether the discount rate is market-realistic or whether the T-bill actually exists. Users must independently verify rates against Treasury Department data or market sources.
- •The formula assumes a 360-day year convention standard for Treasury bills and cannot be customized for alternative day-count conventions (actual/365, actual/actual, etc.) that apply to other securities.
- •TBILLPRICE doesn't account for transaction costs, bid-ask spreads, or other market factors affecting actual purchase prices; it returns only the theoretical discount price based on the discount rate provided.
- •The function is limited to Treasury bills maturing within one year; using it for longer-term Treasury instruments produces mathematically valid but contextually inappropriate results. Use PRICE function for Treasury notes and bonds instead.
Alternatives
More flexible for various discount securities beyond Treasury bills; allows custom year basis (360, 365, or actual day counts) for different market conventions.
When: Use when pricing corporate discount securities, municipal bonds, or when you need to specify a non-standard day-count convention different from Treasury bill standards.
Handles coupon-bearing securities with periodic interest payments; more versatile for general fixed-income pricing including Treasury notes and bonds.
When: Use for Treasury securities with coupon payments, corporate bonds, or any security where periodic interest distributions occur between settlement and maturity.
Provides complete transparency and control over calculation methodology; useful for auditing or when specific conventions differ from standard Treasury calculations.
When: Use in educational contexts, when validating TBILLPRICE results, or when implementing proprietary pricing models with non-standard conventions.
Compatibility
✓ Excel
Since 2007
=TBILLPRICE(settlement, maturity, discount) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and 365 with identical syntax and behavior.✓Google Sheets
=TBILLPRICE(settlement, maturity, discount) - Fully compatible with Google Sheets using identical syntax; date handling uses Google Sheets' date system.Google Sheets automatically converts date formats; ensure dates are recognized as date values, not text. Results match Excel calculations precisely.
✓LibreOffice
=TBILLPRICE(settlement, maturity, discount) - Supported in LibreOffice Calc with identical syntax and calculation methodology as Excel.