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

YIELD() is a function provided in Microsoft Excel, Apache OpenOffice and other spreadsheet tools. It allows you to calculate the yield of a debt security that pays periodic interest.

=YIELD(settlement, maturity, rate, price, redemption, frequency, basis)

where:
  settlement = the settlement date after purchasing the security
  maturity = the maturity date of the security
  rate = the coupon rate of the security
  price = the purchase price 
  redemption = the redemption value 
  frequency = number of coupon payments per year
  basis = the day count basis:
    0: US (NASD) 30/360
    1: Actual/actual
    2: Actual/360
    3: Actual/365
    4: European 30/360

We can try to apply the YIELD() to a US Treasury Bill (T-Bill) by setting:

rate = 0.00% 
redemption = $100.00 
basis = 1: Actual/actual

1. Start Excel or other spreadsheet tool.

2. Enter the following formula in a cell:

=YIELD("2024-12-26", "2025-06-26", 0.0, 97.891833, 100.0, 1, 1)

3. Take the output:

0.043189684 (4.3190% as the BEY)

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 YIELD() function matches well with the BEY in this example.

You can also use our online calculator to validate the result. Click this link "T-Bill 912797NW3 settled on 2024-12-26 with a price of $97.891833, you will see the BEY of 4.3190% displayed.

Let's double check with 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:

=YIELD("2025-04-17", "2026-04-16", 0.0, 96.137556, 100.0, 1, 1)

output:
  0.040286598 (4.0287% as the BEY)

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

So YIELD() function can only be applied to short T-Bills that are less than or equal to one half-year for calculating the BEY.

⇒ Introduction of US Treasury Note

⇐ DISC() Spreadsheet Function for T-Bill

⇑ Spreadsheet Functions for T-Bill

⇑⇑ US Treasury Securities