ODDLYIELD

In this comprehensive guide, we will explore the ODDLYIELD function in Excel, which is used to calculate the yield of a security that has an odd (short or long) first period or an odd last period. This function is particularly useful for financial analysts and investors who deal with bonds and other fixed-income securities. We will cover the syntax of the function, provide examples, share tips and tricks, discuss common mistakes, troubleshoot issues, and introduce related formulae.

ODDLYIELD Syntax

The syntax for the ODDLYIELD function in Excel is as follows:

=ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])

Where the arguments are:

• settlement – The security’s settlement date, which is the date after the issuance when the security is delivered to the buyer.
• maturity – The security’s maturity date, which is the date when the security expires.
• last_interest – The security’s last interest payment date before the settlement date.
• rate – The security’s annual coupon rate.
• pr – The security’s price per $100 face value.
• redemption – The security’s redemption value per $100 face value.
• frequency – The number of coupon payments per year. It can be 1 (annual), 2 (semi-annual), or 4 (quarterly).
• [basis] – (Optional) The day count basis to be used. If omitted, it defaults to 0 (US (NASD) 30/360). The available options are:
• 0 – US (NASD) 30/360
• 1 – Actual/actual
• 2 – Actual/360
• 3 – Actual/365
• 4 – European 30/360

ODDLYIELD Examples

Let’s go through some examples to better understand how the ODDLYIELD function works in Excel.

Example 1: Suppose we have a bond with the following details:

• Settlement date: 01/15/2021
• Maturity date: 06/15/2025
• Last interest payment date: 12/15/2020
• Annual coupon rate: 5%
• Price per $100 face value: $95
• Redemption value per $100 face value: $100
• Frequency: 2 (semi-annual)
• Basis: 0 (US (NASD) 30/360)

To calculate the yield of this bond, we would use the following formula:

=ODDLYIELD(“01/15/2021”, “06/15/2025”, “12/15/2020”, 5%, 95, 100, 2, 0)

The result would be approximately 5.56%, which represents the bond’s yield.

Example 2: Now, let’s consider a bond with an odd last period:

• Settlement date: 01/15/2021
• Maturity date: 06/15/2025
• Last interest payment date: 12/15/2020
• Annual coupon rate: 5%
• Price per $100 face value: $95
• Redemption value per $100 face value: $100
• Frequency: 2 (semi-annual)
• Basis: 1 (Actual/actual)

In this case, we would use the following formula:

=ODDLYIELD(“01/15/2021”, “06/15/2025”, “12/15/2020”, 5%, 95, 100, 2, 1)

The result would be approximately 5.57%, which represents the bond’s yield with an odd last period.

ODDLYIELD Tips & Tricks

Here are some tips and tricks to help you effectively use the ODDLYIELD function in Excel:

1. Ensure that the settlement, maturity, and last_interest dates are entered as valid Excel dates. You can use the DATE function to create a date value.
2. Remember that the rate, pr, and redemption values should be expressed as percentages or decimal values. For example, a 5% rate should be entered as 5% or 0.05.
3. When dealing with bonds that have an odd first or last period, it’s essential to use the ODDLYIELD function instead of the YIELD function, as the latter assumes regular periods.
4. Experiment with different day count basis options to see how they affect the bond’s yield. This can help you better understand the impact of different conventions on your calculations.

Common Mistakes When Using ODDLYIELD

Here are some common mistakes that users make when using the ODDLYIELD function in Excel:

1. Entering the settlement, maturity, and last_interest dates as text strings instead of valid Excel dates. This can cause the function to return an error.
2. Forgetting to express the rate, pr, and redemption values as percentages or decimal values. This can lead to incorrect results.
3. Using the YIELD function instead of the ODDLYIELD function for bonds with odd first or last periods. This can result in inaccurate yield calculations.
4. Not specifying the correct frequency value (1, 2, or 4) for the bond’s coupon payments. This can also lead to incorrect results.

Why Isn’t My ODDLYIELD Working?

If you’re having trouble with the ODDLYIELD function in Excel, consider the following troubleshooting steps:

1. Double-check the settlement, maturity, and last_interest dates to ensure they are valid Excel dates and not text strings.
2. Verify that the rate, pr, and redemption values are expressed as percentages or decimal values.
3. Ensure that you’re using the ODDLYIELD function and not the YIELD function for bonds with odd first or last periods.
4. Check the frequency value to make sure it’s set to the correct number of coupon payments per year (1, 2, or 4).
5. Review the day count basis option to ensure it’s set to the appropriate convention for your calculations.

ODDLYIELD: Related Formulae

Here are some related formulae that you might find useful when working with the ODDLYIELD function in Excel:

1. YIELD – Calculates the yield of a security with regular periods. Use this function when the bond does not have an odd first or last period.
2. ODDFPRICE – Calculates the price of a security with an odd first period.
3. ODDFYIELD – Calculates the yield of a security with an odd first period.
4. ODDLPRICE – Calculates the price of a security with an odd last period.
5. YIELDDISC – Calculates the annual yield of a discounted security, such as a Treasury bill.

By mastering the ODDLYIELD function and its related formulae, you can effectively analyze and evaluate bonds and other fixed-income securities with odd first or last periods in Excel.