YIELDDISC function

The YIELDDISC function in Excel calculates the annual yield of a discounted security, such as a Treasury bill (T-bill), which doesn’t pay periodic interest but is sold at a discount and redeemed at face value.

Syntax:

YIELDDISC(settlement, maturity, pr, redemption, [basis])

Arguments:

1. settlement: The settlement date of the security (the date the investor buys the security).
2. maturity: The maturity date of the security (when it is redeemed).
3. pr: The price per $100 face value of the security.
4. redemption: The redemption value per $100 face value of the security (usually 100).
5. basis (optional): The day-count convention to use. Defaults to 0.
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

Formula:

The formula for discounted yield is:

$$\text{Yield} = \frac{\text{Redemption} – \text{Price}}{\text{Price}} \times \frac{\text{Basis Year Days}}{\text{Days to Maturity}}$$

Where:

• Days to Maturity = Difference between the settlement and maturity dates.
• Basis Year Days depends on the selected day-count basis.

Example:

You purchase a Treasury bill with the following details:

• Settlement Date: January 1, 2023
• Maturity Date: July 1, 2023
• Price: 98 (98% of face value)
• Redemption: 100 (100% of face value)
• Basis: Actual/360 (basis = 2)

Formula:

=YIELDDISC(DATE(2023,1,1), DATE(2023,7,1), 98, 100, 2)

Result:

The function returns the annualized yield of the security as a percentage.

Key Points:

1. Settlement Date must be earlier than the Maturity Date.
2. Price and Redemption are expressed as a percentage of the face value.
3. Basis affects the calculation of days in the year and should be chosen according to market conventions.
4. If inputs are invalid (e.g., settlement after maturity), the function returns a #NUM! error.