ODDFPRICE

In this comprehensive guide, we will explore everything you need to know about the ODDFPRICE formula in Excel. The ODDFPRICE function is a financial function that calculates the price per $100 face value of a security with an odd first period. This function is particularly useful for bond pricing and analysis, especially when dealing with bonds that have irregular first interest periods.

ODDFPRICE Syntax

The syntax for the ODDFPRICE function in Excel is as follows:

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

Here is a description of each argument in the ODDFPRICE function:

• settlement: The settlement date of the security, which is the date after the issue date when the security is traded to the buyer.
• maturity: The maturity date of the security, which is the date when the security expires.
• issue: The issue date of the security.
• first_coupon: The end date of the first coupon period.
• rate: The security’s annual coupon rate.
• yield: The security’s annual yield.
• redemption: The security’s redemption value per $100 face value.
• frequency: The number of coupon payments per year. For annual payments, use 1; for semiannual payments, use 2; for quarterly payments, use 4.
• [basis] (optional): The day count basis to be used. If omitted, the default is 0, which represents the US (NASD) 30/360 day count basis. Other options include 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360.

ODDFPRICE Examples

Let’s look at some examples of how to use the ODDFPRICE function in Excel:

Example 1: A bond has a settlement date of January 15, 2022, a maturity date of January 15, 2027, an issue date of November 15, 2021, and a first coupon date of April 15, 2022. The bond has an annual coupon rate of 5%, an annual yield of 6%, a redemption value of $100, and semiannual coupon payments. To calculate the price per $100 face value of this bond, you would use the following formula:

=ODDFPRICE(“2022-01-15”, “2027-01-15”, “2021-11-15”, “2022-04-15”, 5%, 6%, 100, 2)

Example 2: A bond has a settlement date of March 1, 2022, a maturity date of March 1, 2028, an issue date of December 1, 2021, and a first coupon date of June 1, 2022. The bond has an annual coupon rate of 4%, an annual yield of 5%, a redemption value of $100, and quarterly coupon payments. To calculate the price per $100 face value of this bond, you would use the following formula:

=ODDFPRICE(“2022-03-01”, “2028-03-01”, “2021-12-01”, “2022-06-01”, 4%, 5%, 100, 4)

ODDFPRICE Tips & Tricks

Here are some tips and tricks to help you get the most out of the ODDFPRICE function in Excel:

• Make sure to enter dates in the correct format, such as “YYYY-MM-DD” or by using the DATE function, e.g., DATE(2022, 1, 15).
• Remember that the ODDFPRICE function returns the price per $100 face value. To calculate the actual price of the bond, multiply the result by the face value of the bond and divide by 100.
• When comparing bonds with different coupon frequencies, it’s important to annualize the yield to make a fair comparison. You can do this by using the EFFECT function in Excel.

Common Mistakes When Using ODDFPRICE

Here are some common mistakes to avoid when using the ODDFPRICE function:

• Using incorrect date formats or entering dates as text. Always use the correct date format or the DATE function to avoid errors.
• Forgetting to adjust the coupon rate and yield for the frequency of payments. If the bond pays semiannual coupons, divide the annual coupon rate and yield by 2; if it pays quarterly coupons, divide by 4.
• Not specifying the optional [basis] argument when using a day count basis other than the default US (NASD) 30/360.

Why Isn’t My ODDFPRICE Working?

If you’re having trouble with the ODDFPRICE function, consider the following troubleshooting tips:

• Check your date formats and ensure they are entered correctly.
• Make sure you have entered all required arguments and that they are in the correct order.
• Ensure that the coupon rate, yield, and redemption values are entered as percentages or decimal values, not as whole numbers.
• Verify that the frequency argument is entered as a whole number (1, 2, or 4) and not as text.

ODDFPRICE: Related Formulae

Here are some related financial functions in Excel that you may find useful when working with bonds and securities:

• ODDLPRICE: Calculates the price per $100 face value of a security with an odd last period.
• PRICE: Calculates the price per $100 face value of a security that pays periodic interest.
• YIELD: Calculates the yield of a security that pays periodic interest.
• ACCRINT: Calculates the accrued interest for a security that pays periodic interest.
• COUPNCD: Calculates the next coupon date after the settlement date for a security that pays periodic interest.

By mastering the ODDFPRICE function and related financial functions in Excel, you can effectively analyze and compare bonds and other securities with irregular first interest periods, making more informed investment decisions.