The `YIELDDISC` function in Excel is used to calculate the annual yield of a discounted security, such as a Treasury bill. The function is particularly useful for understanding the return on investment for securities that are sold at a discount to their face (par) value. Here’s how you can use the `YIELDDISC` function:
Syntax
=YIELDDISC(settlement, maturity, pr, redemption, [basis])
Arguments
- `settlement`: (required) The settlement date of the security, which is the date after the issue date when the security is traded to the buyer. The settlement date must be entered as a valid date.
- `maturity`: (required) The maturity date of the security, which is the date when the security expires. This date must be later than the settlement date and also be entered as a valid date.
- `pr`: (required) The price per $100 face value of the security.
- `redemption`: (required) The redemption value per $100 face value of the security. This is typically 100, indicating the security will be redeemed at its full face value.
- `basis`: (optional) The day count basis to be used. It determines how days are counted in calculating the fractional years. The default value is 0, which corresponds to the U.S. (NASD) 30/360 basis. Possible values are:
- 0: U.S. (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you have a discounted security with the following attributes:
- Settlement date: January 1, 2022
- Maturity date: July 1, 2022
- Price: $97
- Redemption value: $100
To calculate the yield using a U.S. 30/360 day count basis, you can use the following formula:
=YIELDDISC(DATE(2022,1,1), DATE(2022,7,1), 97, 100, 0)
This formula will output the annual yield of the security.
Important Tips
- Ensure that the settlement and maturity dates are correctly formatted as date values in Excel.
- If the formula returns an error, check to make sure that you have entered the dates in a correct and supported format and that the settlement date is prior to the maturity date.
- The basis argument is optional and defaults to 0 if omitted. However, using the correct basis is important for accurate yield calculations as it affects the day count fraction used.
By following these guidelines, you can effectively use the `YIELDDISC` function in Excel to determine the yield on discounted securities.
Unlock Your Potential
Excel
Access
Power BI
