TBILLYIELD Function
Calculate Treasury bill yields in Excel and Sheets. Learn the TBILLYIELD function syntax, parameters, examples, and common errors with this guide.
Excel
Google SheetsExcelGoogle Sheets=TBILLYIELD(settlement, maturity, pr)Quick Answer
TBILLYIELD function TBILLYIELD function calculates the yield for a Treasury bill. The syntax is `=TBILLYIELD(settlement, maturity, pr)` where settlement is the purchase date, maturity is the expiration date (within one year), and pr is the price per $100 face value.
=TBILLYIELD(settlement, maturity, pr)- Example: `=TBILLYIELD(DATE(2025,3,31), DATE(2025,6,1), 98.45)` returns 0.0905 or 9.05%.
Practical Examples
Basic Treasury Bill Yield Calculation
Calculate the discount rate for a T-bill purchased at $98.45
Comparing Multiple T-Bill Investment Options
Analyze yields for T-bills at different prices to find the best return
Real-Time T-Bill Yield Monitoring
Create a dynamic dashboard to track T-bill yields as market prices change
Treasury Bill Arbitrage Analysis
Identify arbitrage opportunities between T-bill market prices and calculated fair values
Error Handling for T-Bill Yield Calculations
Prevent and handle common TBILLYIELD errors with validation
Converting Discount Yield to Bond-Equivalent Yield
Calculate both discount yield and bond-equivalent yield for comparison
Common Errors and Solutions
TBILLYIELD returns #NUM! error
Settlement date is on or after maturity date, or maturity is more than one year after settlement, or price is less than or equal to 0
Ensure settlement date is before maturity date, maturity is within one year (365 days) of settlement, and price is a positive number greater than 0. For T-bills, prices are typically between $95 and $100 per $100 face value.
Validate that: (1) settlement < maturity, (2) maturity - settlement <= 365 days, (3) price > 0. Add data validation rules to prevent invalid inputs.
Example:
TBILLYIELD returns #VALUE! error
One or more arguments is not a valid data type - settlement or maturity is not a valid date, or price is not a number
Use the DATE(year, month, day) function to construct dates rather than text strings. Ensure the price cell is formatted as a number, not text. Convert text dates using DATEVALUE() if necessary.
Always use DATE() function for date inputs: =TBILLYIELD(DATE(2025,3,31), DATE(2025,6,1), 98.45). Format cells properly before calculations.
Example:
Excel displays #NAME? error
Function name is misspelled (e.g., TBILLYELD instead of TBILLYIELD) or the function is not available in the current Excel version
Check the spelling of TBILLYIELD carefully. Ensure you are using Excel 2000 or later (Excel 2007+ has it built-in). In older versions, enable the Analysis ToolPak add-in.
Use Excel's autocomplete feature when typing function names. Verify your Excel version supports financial functions. For Excel 2003 and earlier, go to Tools > Add-Ins > Analysis ToolPak.
Example:
TBILLYIELD returns negative or extremely high yields
Price entered as a percentage (e.g., 98.45%) instead of dollar amount, or price is greater than 100 for a discount instrument
Enter price as dollars per $100 face value (e.g., 98.45, not 0.9845 or 98.45%). T-bill prices should be less than or equal to 100 since they are discount instruments. If price > 100, verify your data source.
Document that price should be entered as: $98.45 per $100 face value (not as 0.9845). Add conditional formatting to highlight prices > 100 as potential errors.
Example:
Best Practices and Advanced Tips
Understanding T-Bill Price and Yield Relationship
TBILLYIELD and TBILLPRICE are inverse functions. When price goes down, yield goes up, and vice versa. This inverse relationship is fundamental to all fixed income securities. Use TBILLYIELD when you know the market price and need to calculate the return.
360-Day Year Convention
TBILLYIELD returns yields based on a 360-day year (money market basis), not 365 days. This is the standard convention for T-bills and money market instruments. To convert to bond-equivalent yield (365-day basis), use TBILLEQ or multiply by 365/360.
Always Use DATE Function for Dates
Rather than typing dates as text strings, use the DATE(year, month, day) function to avoid date interpretation issues across different regional settings and Excel versions. This ensures consistency and reduces errors.
Price Entry Format
Enter price as dollar amount per $100 face value, not as a decimal. For example, use 98.45 (representing $98.45), not 0.9845. This is the standard market convention and prevents calculation errors.
One-Year Maturity Limit
Treasury bills by definition have maturities of one year or less. TBILLYIELD will return #NUM! error if the maturity date is more than 365 days after the settlement date. For longer-term Treasury securities, use YIELD or PRICE functions instead.
Settlement vs Trade Date
Remember that the settlement date is typically 1-2 business days after the trade date (T+1 or T+2). When analyzing market transactions, ensure you're using the settlement date, not the trade date, for accurate yield calculations.
Real-World Yield Formula
TBILLYIELD calculates: Yield = ((100 - Price) / Price) * (360 / Days to Maturity). Understanding this formula helps you validate results and troubleshoot errors. The 360-day year is key to the calculation.
Combining with Other Treasury Functions
Use TBILLYIELD alongside TBILLPRICE and TBILLEQ for comprehensive T-bill analysis. TBILLYIELD gives discount rate, TBILLEQ gives bond-equivalent yield, and TBILLPRICE lets you work backward from yield to price.
Need Help with TBILLYIELD Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Calculate the bond-equivalent yield for Treasury bills. Learn TBILLEQ syntax, examples, and how to convert T-bill discount rates to comparable bond yields.
ExcelSheetsCalculate the price per $100 face value for Treasury bills. Learn TBILLPRICE syntax, examples, and T-bill pricing with settlement and maturity dates.
ExcelSheetsThe IRR function calculates internal rate of return for cash flows. Learn to evaluate investment profitability with practical examples.
ExcelSheetsThe RATE function calculates interest rate per period for loans. Master RATE with examples, error solutions, and tips. Get examples, tips, and solutions.
ExcelSheets