MS Excel: TBILLYIELD function for yield of a treasury bill

U.S. Treasury Bills (T-bills) are among the safest and most liquid short-term investments. Since they’re sold at a discount and mature at face value, investors don’t receive interest payments—they profit from the difference between the purchase price and redemption value.

To understand the return on investment, Excel provides the TBILLYIELD function, which helps calculate the annualized yield of a T-bill based on its price.

The TBILLYIELD function calculates the yield for a Treasury bill (T-bill), given its settlement date, maturity date, and price per $100 face value.

This function is especially useful for investors and analysts seeking to determine the actual return of a T-bill they’ve purchased at a discount.

Syntax

=TBILLYIELD(settlement, maturity, pr)

Argument Description:

Argument	Description
settlement	The date the T-bill is purchased (trade or settlement date)
maturity	The date the T-bill matures
pr	The purchase price per $100 face value

Output: The annualized yield (on a 360-day basis) as a decimal (e.g., 0.024 for 2.4%).

Example: Calculating T-Bill Yield

Scenario: You purchase a T-bill for $98.50 on April 1, 2025, and it matures on September 30, 2025. What is the yield?

=TBILLYIELD(DATE(2025,4,1), DATE(2025,9,30), 98.5)

Result: 0.03051 or 3.051%

This means your annualized return is 3.051%, based on a 360-day year.

When to Use TBILLYIELD

Use Case	Why It’s Useful
Compare T-bill yields	Standardizes returns for comparison
Evaluate short-term investments	Understand the ROI for discount-based instruments
Fixed-income portfolio analysis	Model cash flow and return projections
Trading desk operations	Assess value across T-bill maturity dates

How It Works Behind the Scenes

Formula used by TBILLYIELD:

Yield = ((100 - pr) / pr) × (360 / DSM)

Where:

• pr = price per $100 face value

• DSM = days between settlement and maturity

In our example:

• Price = 98.5

• DSM = 182 days

• Yield = ((100 - 98.5) / 98.5) × (360 / 182) ≈ 3.051%

Related Functions

Function	Description
TBILLPRICE	Calculates price from yield
TBILLEQ	Returns bond-equivalent yield for T-bills
YIELD	Returns yield for coupon-paying bonds
DISC	Returns discount rate for discounted securities
PRICE	Returns bond price from yield

Use TBILLYIELD when you know the purchase price and want to calculate the effective annual yield.

Tips and Common Pitfalls

Tip	Reason
Dates must be valid Excel dates	Use DATE(year, month, day) to avoid #VALUE! errors
Ensure maturity is ≤ 1 year from settlement	T-bills must mature within 1 year
Price must be < 100	Since T-bills are discounted, the price should be below par
Result is a decimal	Multiply by 100 if you need a percentage format

Best Practice: Wrap your function in TEXT() for presentation:

=TEXT(TBILLYIELD(DATE(2025,4,1), DATE(2025,9,30), 98.5), "0.00%")

Summary Table

Feature	Value
Function Name	TBILLYIELD
Purpose	Calculate yield on a Treasury bill from its price
Based On	360-day year convention
Inputs Required	Settlement date, maturity date, price
Output	Annualized yield (as decimal)

Final Thoughts

The TBILLYIELD function is essential for investors, traders, and analysts who deal with short-term U.S. Treasury securities. It provides a quick, standardized way to assess the profitability of a T-bill investment.

Whether you’re comparing short-term yields, planning cash investments, or pricing T-bills in real time, TBILLYIELD delivers precision with ease.