ODDLPRICE function

The ODDLPRICE function in Excel calculates the price of a bond with an odd first period. This function is useful when the bond’s first coupon period is not a full period, also known as an “odd” first period. The function computes the bond price considering the irregularity of the first coupon period and then adjusts it based on the bond’s face value, coupon rate, and other factors.

Syntax

ODDLPRICE(settlement, maturity, last_coupon, rate, yld, 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. yld: The bond’s annual yield expressed as a decimal. For example, for a 6% yield, enter 0.06.
6. redemption: The bond’s redemption value, which is typically the face value of the bond, 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 ODDLPRICE function calculates the price of a bond by considering the price at which the bond would trade in the market, factoring in the “odd” first coupon period. It takes into account the following:

• The bond’s coupon rate
• The bond’s yield (market rate)
• The bond’s redemption value
• The number of periods until maturity

The price is calculated using the formula for the present value of future cash flows, which takes into account the time value of money.

Example

Suppose you want to calculate the price 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)
• Yield: 5% (0.05)
• Redemption value: $1,000
• Frequency: 2 (semiannual payments)
• Basis: 0 (US (NASD) 30/360)

The formula would look like this:

=ODDLPRICE("2025-01-15", "2035-01-15", "2024-07-15", 0.06, 0.05, 1000, 2)

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

Important Notes

• The ODDLPRICE 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 PRICE function should be used instead.
• The settlement, maturity, and last_coupon dates must be valid Excel date values.
• The rate and yld parameters should be expressed as decimals. For example, a 6% coupon rate should be entered as 0.06.
• The redemption value is typically the face value of the bond, usually $1,000 or $100, depending on the bond’s denomination.

Summary

The ODDLPRICE function in Excel calculates the price of a bond with an odd first period, adjusting for the irregularity of the first coupon period. This function helps determine the present value of the bond by factoring in its coupon rate, yield, and redemption value, making it essential for pricing bonds with non-standard first coupon periods.