TBILLYIELD function

The TBILLYIELD function in Excel is used to calculate the yield of a Treasury bill (T-bill) based on its price, face value, and the number of days to maturity. This yield is expressed as an annualized percentage and helps investors understand the return on investment for T-bills, which are sold at a discount to their face value.

Syntax

TBILLYIELD(settlement, maturity, price, redemption)

Parameters

1. settlement: The settlement date of the T-bill. This is the date after the issue date when the T-bill is traded to the buyer. It must be a valid Excel date.
2. maturity: The maturity date of the T-bill. This is the date when the face value of the T-bill is paid back to the investor. It must be a valid Excel date.
3. price: The price of the T-bill per $100 face value. This is the amount you pay to purchase the T-bill.
4. redemption: The redemption value of the T-bill, which is typically $100 (face value).

How It Works

The TBILLYIELD function calculates the yield on a T-bill based on its price, the number of days to maturity, and the redemption value. The formula for calculating the yield is:

$$\text{Yield} = \frac{\text{Redemption} – \text{Price}}{\text{Price}} \times \frac{360}{\text{Days to maturity}}$$

Where:

• Redemption is the T-bill’s face value (usually $100),
• Price is the purchase price of the T-bill,
• Days to maturity is the number of days from the settlement date to the maturity date.

The result is the bond-equivalent yield, which is annualized and expressed as a percentage.

Example

Suppose you have the following details for a T-bill:

• Settlement date: January 1, 2025
• Maturity date: July 1, 2025
• Price: $98.50 per $100 face value
• Redemption value: $100

To calculate the yield of the T-bill, you would use the following formula in Excel:

=TBILLYIELD(DATE(2025,1,1), DATE(2025,7,1), 98.5, 100)

This would return the bond-equivalent yield for the T-bill based on its price, redemption value, and the number of days to maturity.

Important Notes

• The TBILLYIELD function assumes a 360-day year for the calculation.
• The price parameter should be the price paid for the T-bill (per $100 face value), and it is typically less than $100 because T-bills are sold at a discount.
• The yield is expressed as an annualized percentage based on the bond-equivalent yield formula.

Summary

The TBILLYIELD function in Excel calculates the yield of a Treasury bill based on its price, settlement date, maturity date, and redemption value. It is useful for understanding the return on investment for T-bills and is expressed as an annualized percentage yield.