ODDLYIELD function

The ODDLYIELD function in Excel calculates the yield of a bond with an odd first period. This function is used when the first coupon period is not a full period, meaning the bond’s first interest payment is for a shorter or longer duration than usual (an “odd” first period). The ODDLYIELD function returns the bond’s yield based on its settlement, maturity, coupon rate, yield, and other factors, considering the irregularity of the first coupon period.

Syntax

ODDLYIELD(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 occur after the bond’s 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 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, enter 0.05.
5. pr: The bond’s price expressed as a percentage of the face value. For example, a price of $950 for a $1,000 bond would be entered as 95.
6. redemption: The bond’s redemption value (usually the face value), which is typically $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 ODDLYIELD function calculates the yield of a bond considering the irregular first coupon period. The formula accounts for the bond’s:

• Coupon rate
• Price
• Redemption value
• Frequency of coupon payments
• Basis for day count
• Time value of money

It then calculates the yield to maturity, which is the annual return expected from the bond if held until maturity, factoring in the “odd” first period.

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 $1,000 face value)
• Redemption value: $1,000
• Frequency: 2 (semiannual payments)
• Basis: 0 (US (NASD) 30/360)

The formula would look like this:

=ODDLYIELD("2025-01-15", "2035-01-15", "2024-07-15", 0.06, 95, 1000, 2)

This formula will calculate the bond’s yield given the specified parameters, accounting for the odd first coupon period.

Important Notes

• The ODDLYIELD function is specifically designed for bonds with an odd first period, where the first coupon is not for a full period. For regular bonds with standard first periods, the YIELD function should be used instead.
• The settlement, maturity, and last_coupon dates must be valid Excel date values.
• The rate and pr (price) parameters should be expressed as decimals or percentages, respectively. For example, a 6% coupon rate should be entered as 0.06, and a bond price of $950 should be entered as 95 (representing 95% of face value).

Summary

The ODDLYIELD function calculates the yield of a bond with an odd first period. This is useful for bonds where the first coupon period is shorter or longer than a standard period. By considering the bond’s coupon rate, price, redemption value, and other factors, it provides the yield to maturity, adjusting for the irregular first period.