How to use the function TBILLYIELD() in Excel and other spreadsheet tools for US Treasury Bills?

TBILLYIELD() is a function provided in Microsoft Excel, Apache OpenOffice and other spreadsheet tools. It allows you to calculate a special kind of "yield" for a US Treasury Bill (T-Bill) with a given purchase price.

TBILLYIELD(settlement, maturity, price)

where:
  settlement = the settlement date after purchasing the T-Bill 
  maturity = the maturity date of the T-Bill 
  price = the purchase price 

This "yield" represents an annualized interest rate of the T-Bill using the 360-day year method. We can refer this "yield" as the 360-Day Yield.

$i={\displaystyle \frac{\mathrm{Year}}{\mathrm{Days}}}\times {\displaystyle \frac{(F-P)}{P}}$
$P=\text{Purchase price}$
$F=\text{Face value}$
$i=\text{360-Day Yield}$
$\mathrm{Days}=\text{Days to maturity}$
$\mathrm{Year}=360=\text{Constant number of days in a year}$

Note that this "yield" has the save presentation structure as the BEY (Bond Equivalent Yield) except for the day count basis.

$i={\displaystyle \frac{\mathrm{Year}}{\mathrm{Days}}}\times {\displaystyle \frac{(F-P)}{P}}$
$P=\text{Purchase price}$
$F=\text{Face value}$
$i=\text{BEY (Bond Equivalent Yield)}$
$\mathrm{Days}=\text{Days to maturity}$
$\mathrm{Year}=\text{365 or 366}=\text{Actual number of days in the year}$

You can follow these steps to use the TBILLYIELD() function:

1. Start Excel or other spreadsheet tool.

2. Enter the following formula in a cell:

=TBILLYIELD("2024-12-26", "2025-06-26", 97.891833)

3. Take the output:

0.04259804451042 (4.2598% as the 360-Day Yield)

4. Compare it with the following T-Bill transaction example:

CUSIP: 912797NW3
Type: 26-Week Bill 
Par Amount: $10,000.00 (Face value) 
Purchase Price: $9,789.18

Investment/Interest Rate: 4.319% (BEY - Bond Equivalent Yield)
Yield: 4.170% (Discount rate)
Price per $100: 97.891833

Discount: $210.82

Issue Date: 2024-12-26 (Settlement date)
Maturity Date: 2025-06-26

As you can see, the output of the TBILLYIELD() function does not match any performance measures in this example.

You can also try the following 52-week T-Bill:

Term and Type of Security: 364-Day Bill 
CUSIP: 912797QD

High Rate: 3.820% (Discount rate)
Price: 96.137556
Investment Rate: 3.989% (BEY - Bond Equivalent Yield)

Issue Date: 2025-04-17
Maturity Date: 2026-04-16

Enter the following formula in a cell:

=TBILLYIELD("2025-04-17", "2026-04-16", 96.137556)

output:
  0.039734727190689 (3.9735% as the 360-Day Yield)

As you can see, the output of the TBILLYIELD() function does not match any performance measures in this example.

⇒ DISC() Spreadsheet Function for T-Bill

⇐ TBILLEQ() Spreadsheet Function for T-Bill

⇑ Spreadsheet Functions for T-Bill

⇑⇑ US Treasury Securities