ODDFYIELD is a financial function in Excel that calculates the yield of a security with an odd (non-annual) first period. This function is useful for calculating the yield of bonds that have an irregular first coupon period. In this article, we will discuss the syntax of ODDFYIELD, provide examples of how to use it, share tips and tricks for using the function effectively, discuss common mistakes to avoid, and explore related formulae.
ODDFYIELD Syntax
The syntax for ODDFYIELD is as follows:
=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])
The function has the following arguments:
- settlement: The settlement date of the security.
- maturity: The maturity date of the security.
- issue: The issue date of the security.
- first_coupon: The date of the first coupon payment.
- rate: The annual coupon rate of the security.
- pr: The price per $100 face value of the security.
- redemption: The redemption value per $100 face value of the security.
- frequency: The number of coupon payments per year.
- basis (optional): The day count basis to use for the calculation. If omitted, Excel uses the US (NASD) 30/360 day count basis.
ODDFYIELD Examples
Let’s look at some examples of how to use ODDFYIELD.
Example 1: Calculate the yield of a bond with an odd first coupon period.
Suppose we have a bond with the following details:
- Settlement date: 1/1/2022
- Maturity date: 1/1/2027
- Issue date: 1/1/2021
- First coupon date: 7/1/2022
- Annual coupon rate: 5%
- Price: $95
- Redemption value: $100
- Coupon frequency: 2
To calculate the yield of this bond using ODDFYIELD, we would use the following formula:
=ODDFYIELD(“1/1/2022”, “1/1/2027”, “1/1/2021”, “7/1/2022”, 0.05, 95, 100, 2)
This formula returns a yield of 5.79%.
Example 2: Calculate the yield of a bond with a semi-annual coupon payment.
Suppose we have a bond with the following details:
- Settlement date: 1/1/2022
- Maturity date: 1/1/2027
- Issue date: 1/1/2021
- First coupon date: 1/1/2022
- Annual coupon rate: 6%
- Price: $98
- Redemption value: $100
- Coupon frequency: 2
To calculate the yield of this bond using ODDFYIELD, we would use the following formula:
=ODDFYIELD(“1/1/2022”, “1/1/2027”, “1/1/2021”, “1/1/2022”, 0.06, 98, 100, 2)
This formula returns a yield of 6.23%.
ODDFYIELD Tips & Tricks
Here are some tips and tricks for using ODDFYIELD effectively:
- Make sure to use the correct day count basis for your calculation. The default basis is the US (NASD) 30/360 day count basis, but you can specify a different basis if needed.
- Double-check your inputs to make sure they are in the correct format. Dates should be entered as text strings in the format “mm/dd/yyyy”.
- Remember that the price argument should be entered as a percentage of the face value of the security. For example, if the price is $95 per $100 face value, you would enter 95 as the price argument.
Common Mistakes When Using ODDFYIELD
Here are some common mistakes to avoid when using ODDFYIELD:
- Entering dates in the wrong format. Dates should be entered as text strings in the format “mm/dd/yyyy”.
- Forgetting to convert the price to a percentage of face value. The price argument should be entered as a percentage, not a dollar amount.
- Using the wrong day count basis. Make sure to use the correct basis for your calculation.
Why Isn’t My ODDFYIELD Working?
If your ODDFYIELD formula isn’t working, here are some things to check:
- Make sure all of your inputs are in the correct format. Dates should be entered as text strings in the format “mm/dd/yyyy”.
- Double-check your inputs to make sure they are correct. Check the settlement date, maturity date, issue date, first coupon date, coupon rate, price, redemption value, and coupon frequency.
- Make sure you are using the correct day count basis for your calculation.
ODDFYIELD: Related Formulae
Here are some related formulae that you might find useful:
- YIELD: Calculates the yield of a security that pays periodic interest.
- YIELDDISC: Calculates the yield of a discounted security.
- PRICE: Calculates the price of a security that pays periodic interest.
- PRICEDISC: Calculates the price of a discounted security.
- DURATION: Calculates the Macaulay duration of a security.
