ODDFYIELD function

The ODDFYIELD function in Excel calculates the yield of a bond with an odd first period. This function is used when a bond’s first coupon period is not a full period (i.e., it is “odd”). It takes into account the settlement date, the maturity date, the last coupon date, and other bond parameters to compute the yield, which reflects the bond’s return if held to maturity.

Syntax

ODDFYIELD(settlement, maturity, last_coupon, rate, pr, redemption, frequency, [basis])

Parameters

1. settlement: The bond’s settlement date, which is the date after issuance when the bond is traded to the buyer. The settlement date must be after the issue date.
2. maturity: The bond’s maturity date, which is the date when the face value of the bond is due to be paid.
3. last_coupon: The bond’s last coupon date. This is the date when the bond’s last coupon payment was made.
4. rate: The bond’s annual coupon rate expressed as a decimal. For example, for a 5% coupon rate, you would enter 0.05.
5. pr: The bond’s price (the current market price of the bond). It is expressed as a percentage of the bond’s face value. For example, a price of 95 means 95% of the face value.
6. redemption: The bond’s redemption value, which is typically the face value, often $1,000 or $100.
7. frequency: The number of coupon payments per year:
   • 1 = annual payments
   • 2 = semiannual payments
   • 4 = quarterly payments
8. [basis] (optional): The day count basis to use for the calculation. If omitted, it defaults to 0 (US (NASD) 30/360). The available options are:
   • 0: US (NASD) 30/360 (default)
   • 1: Actual/actual
   • 2: Actual/360
   • 3: Actual/365
   • 4: European 30/360

How It Works

The ODDFYIELD function calculates the yield of a bond by taking into account the price, coupon rate, and other bond details, adjusting for the “odd” first period. It is similar to the YIELD function but with a correction for the irregular first period between the issue date and the settlement date.

The yield is calculated using the formula:

$$\text{Yield} = \frac{\text{Coupon Payment} + \frac{\text{Redemption Value} – \text{Price}}{\text{Years to Maturity}}}{\frac{\text{Price} + \text{Redemption Value}}{2}}$$

Example

Suppose you want to calculate the yield of a bond with the following details:

• Settlement date: January 15, 2025
• Maturity date: January 15, 2035
• Last coupon date: July 15, 2024
• Coupon rate: 6% (0.06)
• Bond price: 950 (95% of the face value, $1,000)
• Redemption value: $1,000
• Frequency: 2 (semiannual payments)
• Basis: 0 (US (NASD) 30/360)

The formula would look like this:

=ODDFYIELD("2025-01-15", "2035-01-15", "2024-07-15", 0.06, 950, 1000, 2)

This will calculate the yield of the bond given the specified parameters.

Important Notes

• The ODDFYIELD function is useful for bonds with an “odd” first period, where the first coupon is not for a full period. For bonds with regular coupon periods, the YIELD function should be used instead.
• The ODDFYIELD function calculates the yield based on the bond’s price, the coupon rate, and the bond’s face value. The result is expressed as an annual yield.
• The settlement, maturity, and last_coupon dates must be valid Excel date values.
• The price (pr) parameter should be entered as a percentage of the face value. For example, if the bond is priced at 95% of face value, you would enter 950 (not 0.95).

Summary

The ODDFYIELD function in Excel calculates the yield of a bond with an odd first coupon period. This function adjusts for the non-standard first period and computes the bond’s yield based on its price, coupon rate, and other bond characteristics. It is essential for valuing bonds that are issued or traded with an irregular first coupon period.