ODDFPRICE function

The ODDFPRICE function in Excel calculates the price of a bond for an odd (or non-standard) first period. It is used when the bond’s first coupon period does not match the typical period of the bond. This function is useful for determining the price of a bond when it is issued or traded on a date other than the bond’s regular coupon date.

Syntax

ODDFPRICE(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 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. yld: The bond’s annual yield (as a decimal) based on the bond’s market price and the coupon rate. For a 6% yield, you would enter 0.06.
6. redemption: The bond’s redemption value, usually the face value (often $100 or $1,000).
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 ODDFPRICE function calculates the price of a bond by considering the bond’s settlement date, maturity date, last coupon date, coupon rate, yield, redemption value, and frequency of coupon payments. The function adjusts for the “odd” first period where the bond’s first coupon is not for a full period.

The formula for the ODDFPRICE function takes into account the accrued interest for the bond from the last coupon date to the settlement date.

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:

=ODDFPRICE("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 conditions.

Important Notes

• The ODDFPRICE function is specifically for bonds with an “odd” first period. If the bond’s first coupon period is a standard period, you should use the PRICE function.
• The ODDFPRICE function returns the bond price, which includes the accrued interest as of the settlement date.
• The settlement date must always come after the issue date, and the maturity and last_coupon dates must be provided as valid Excel date values.

Summary

The ODDFPRICE function in Excel is used to calculate the price of a bond with an odd first coupon period. It adjusts for the irregular first period by considering various factors like the bond’s settlement date, last coupon date, coupon rate, yield, redemption value, and payment frequency. This function is important for valuing bonds that are issued or traded on dates that don’t coincide with the bond’s usual coupon payment schedule.