Excel ODDFPRICE Function: Calculate Price of Odd First Period Bonds

Excel Financial Functions

Learn how to use Excel’s ODDFPRICE function to calculate bond prices with odd first periods. Ideal for bond investors needing accurate price calculations.

1. Overview of the Function’s Purpose

The ODDFPRICE function in Excel calculates the price per $100 face value of a security (like a bond) with an odd (irregular) first period. This is particularly useful when dealing with bonds that do not have a standard coupon payment structure in their first period. The irregular first period may occur because the bond was issued mid-cycle or for any other financial reasons.

Essentially, ODDFPRICE helps investors determine how much they should pay for a bond with an unusual first coupon period based on key bond details, such as settlement date, maturity date, coupon rate, and yield.

2. Syntax and Explanation of Each Argument

The syntax for the ODDFPRICE function is:

=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

Arguments:

  1. settlement: The settlement date of the security, i.e., the date when the bond is traded to the buyer.
  2. maturity: The maturity date, when the bond expires, and the face value is paid to the bondholder.
  3. issue: The bond’s issue date, the date it was initially offered to the public.
  4. first_coupon: The date of the bond’s first coupon payment (this is where the odd first period is reflected).
  5. rate: The bond’s annual coupon rate, representing the interest the bondholder will receive.
  6. yld: The annual yield of the bond, i.e., the return an investor will receive for holding the bond.
  7. redemption: The bond’s redemption value per $100 face value, typically 100.
  8. frequency: The number of coupon payments per year. Typically:
    • 1 for annual payments,
    • 2 for semiannual payments,
    • 4 for quarterly payments.
  9. [basis](optional): The day count basis for calculating interest. Options include:
    • 0 or omitted: 30/360 (U.S. default)
    • 1: Actual/Actual
    • 2: Actual/360
    • 3: Actual/365
    • 4: European 30/360

Syntax Example:

=ODDFPRICE("01/01/2024", "01/01/2030", "01/01/2023", "06/30/2023", 0.05, 0.04, 100, 2, 0)

This calculates the price of a bond with a semi-annual coupon rate of 5%, an annual yield of 4%, a first odd coupon period ending on June 30, 2023, and a maturity date of January 1, 2030. The bond’s redemption value is $100 per face value.

3. Practical Business Examples

1. Pricing a Bond with an Odd First Coupon Period

A company issues a bond on January 1, 2024, with a maturity date of January 1, 2030. The first coupon period is irregular, ending on June 30, 2024. The bond has a 6% coupon rate, pays semi-annually, and the yield is 5%. To calculate the bond price:

Example:

=ODDFPRICE("01/01/2024", "01/01/2030", "01/01/2023", "06/30/2024", 0.06, 0.05, 100, 2, 0)

This formula calculates the price per $100 face value for a bond with an odd first coupon period. The investor can use this value to assess if the bond is worth purchasing.

2. Government Bond Valuation

A government issues a bond with an odd first coupon period due to unforeseen delays in issuance. The bond was issued on February 1, 2022, and the first coupon is due on July 15, 2022. The bond matures on February 1, 2032, with a 4% coupon rate, and yields are currently at 3.5%. The bond pays interest annually.

Example:

=ODDFPRICE("02/01/2022", "02/01/2032", "02/01/2021", "07/15/2022", 0.04, 0.035, 100, 1, 0)

This formula helps bond traders or investors determine the fair price to pay for this government bond, given its odd first coupon period and yield.

3. Corporate Bond Evaluation with Quarterly Payments

A corporate bond is issued on March 1, 2023, and has a maturity date of March 1, 2028. The first coupon payment is on May 1, 2023, which creates an odd first period. The bond pays quarterly coupons at a 3% annual rate, and the current yield is 2.5%.

Example:

=ODDFPRICE("03/01/2023", "03/01/2028", "03/01/2022", "05/01/2023", 0.03, 0.025, 100, 4, 0)

This example helps calculate the price of a corporate bond with quarterly payments and an odd first coupon period, providing clarity on whether it is a good investment.

4. Pricing a Bond with Irregular Payments Due to Economic Changes

An economic event forced a bond issue delay, leading to an odd first coupon period. The bond was issued on October 1, 2021, with a maturity of October 1, 2026. The first coupon is due on January 15, 2022. The bond has a 5% coupon rate, pays semi-annually, and the market yield is 4%.

Example:

=ODDFPRICE("10/01/2021", "10/01/2026", "10/01/2020", "01/15/2022", 0.05, 0.04, 100, 2, 0)

The function calculates the bond’s price, considering the irregular first payment period, providing valuable insight into whether the bond’s current price is fair.

5. Valuing a Municipal Bond with a Short First Period

A municipal bond is issued on April 1, 2023, with a maturity date of April 1, 2033. The first coupon is due on May 15, 2023. The bond has a 2.5% annual coupon rate, and the market yield is 3%. The bond pays interest annually.

Example:

=ODDFPRICE("04/01/2023", "04/01/2033", "04/01/2022", "05/15/2023", 0.025, 0.03, 100, 1, 0)

This example calculates the price for a municipal bond that has a short odd first coupon period, helping investors determine the fair price to pay based on current yields.

4. Best Practices

  • Use Accurate Dates: Ensure all dates (settlement, issue, first coupon, and maturity) are accurate to avoid mispricing the bond.
  • Match Frequency with Coupon Payments: Use the correct frequency argument (annual, semiannual, quarterly) to reflect how often coupon payments are made.
  • Understand Day Count Basis: Know the basis for calculating interest (e.g., 30/360, actual/actual) since it can impact the price calculation.
  • Consider Market Conditions: Ensure the yield (yld) reflects the current market rate for accurate pricing.

5. Common Mistakes or Limitations

  • Incorrect Date Format: Excel may misinterpret date entries if not entered correctly. Always ensure dates are in valid formats (e.g., MM/DD/YYYY or use Excel date functions).
  • Mismatched Frequency and Coupon Payment: Ensure that the frequency argument matches the actual bond’s payment schedule. For instance, don’t use a semi-annual frequency for a bond that pays quarterly.
  • Forgetting to Account for Odd First Period: The ODDFPRICE function specifically accounts for bonds with odd first periods. Using a standard bond pricing formula may result in inaccurate pricing if an irregular first period exists.

6. Combining with Other Related Functions

  • ODDFYIELD: Use the ODDFYIELD function to calculate the yield of a bond with an odd first coupon period, complementing the price determined using ODDFPRICE.Example: =ODDFYIELD("01/01/2024", "01/01/2030", "01/01/2023", "06/30/2023", 0.05, 100, 100, 2, 0)
  • PRICE: For bonds with regular payment periods (no odd first or last period), you can use the PRICE function to calculate the bond price.Example: =PRICE("01/01/2024", "01/01/2030", 0.05, 0.04, 100, 2, 0)
  • YIELD: Combine ODDFPRICE with YIELD to determine the bond’s yield after calculating the price.

7. Summary and Key Points

  • ODDFPRICE helps calculate bond prices for securities with odd (irregular) first coupon periods.
  • It requires detailed inputs: settlement date, issue date, first coupon, maturity, coupon rate, yield, and frequency.
  • Use the correct day count basis and frequency for accurate pricing.
  • Related functions like ODDFYIELD and PRICE help complement bond pricing tasks.

Key Points:

  • Use for bonds with odd first coupon periods.
  • Accurate date and yield inputs are crucial.
  • Matches frequency of coupon payments for correct results.

8. Frequently Asked Questions (FAQs)

  1. What is an odd first period?
    • An odd first period is an irregular time frame between a bond’s issue date and its first coupon payment date.
  2. Can I use ODDFPRICE for bonds with a standard first period?
    • No, use the PRICE function instead for bonds with regular coupon periods.
  3. What happens if I enter an incorrect date?
    • The function may return an error or inaccurate bond price if dates are incorrect.
  4. What does the redemption argument mean?
    • Redemption refers to the bond’s face value at maturity, typically $100 per $100 face value.
  5. Can I calculate the yield after using ODDFPRICE?
    • Yes, use ODDFYIELD to calculate the bond’s yield.

Related Posts

Scroll to Top