logo-v-light

Our Service

ODDLPRICE

In this comprehensive guide, we will explore the ODDLPRICE formula in Microsoft Excel. The ODDLPRICE 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 the first interest period is shorter or longer than the subsequent periods. By the end of this article, you will have a thorough understanding of the ODDLPRICE formula, its syntax, examples, tips and tricks, common mistakes, and related formulae.

ODDLPRICE Syntax

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

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

Where the function arguments are:

  • settlement – The settlement date of the security, i.e., the date when the security is purchased.
  • maturity – The maturity date of the security, i.e., the date when the security expires.
  • issue – The issue date of the security.
  • first_coupon – The end date of the first interest period.
  • rate – The security’s annual interest rate.
  • yld – The security’s annual yield.
  • redemption – The redemption value per $100 face value of the security.
  • frequency – The number of coupon payments per year. It can be 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • [basis] – (Optional) The day count basis to be used. If omitted, it defaults to 0 (US (NASD) 30/360). The available options are:
  • 0 – US (NASD) 30/360
  • 1 – Actual/actual
  • 2 – Actual/360
  • 3 – Actual/365
  • 4 – European 30/360

ODDLPRICE Examples

Let’s look at some examples to better understand the ODDLPRICE function in Excel.

Example 1: Suppose we have a bond with the following details:

  • Settlement date: 01-Jan-2022
  • Maturity date: 01-Jan-2027
  • Issue date: 01-Jul-2021
  • First coupon date: 01-Apr-2022
  • Annual interest rate: 5%
  • Annual yield: 6%
  • Redemption value: $100
  • Frequency: 2 (semi-annual)
  • Basis: 0 (US (NASD) 30/360)

To calculate the price per $100 face value of this bond, we can use the ODDLPRICE function as follows:

=ODDLPRICE(“01-Jan-2022”, “01-Jan-2027”, “01-Jul-2021”, “01-Apr-2022”, 5%, 6%, 100, 2, 0)

This formula will return the price per $100 face value of the bond, considering the odd first period.

Example 2: Now, let’s change the day count basis to Actual/365 (basis 3) and recalculate the bond price:

=ODDLPRICE(“01-Jan-2022”, “01-Jan-2027”, “01-Jul-2021”, “01-Apr-2022”, 5%, 6%, 100, 2, 3)

This formula will return the price per $100 face value of the bond using the Actual/365 day count basis, which may result in a different bond price compared to the previous example.

ODDLPRICE Tips & Tricks

  • Ensure that the settlement, maturity, issue, and first_coupon dates are entered as valid Excel date values. You can use the DATE function to create a valid date value, e.g., DATE(2022, 1, 1) for 01-Jan-2022.
  • When comparing bond prices with different day count bases, make sure to use the same basis for all bonds to ensure a fair comparison.
  • Remember that the ODDLPRICE function returns the price per $100 face value. To calculate the total price of the bond, multiply the result by the face value of the bond and divide by 100.

Common Mistakes When Using ODDLPRICE

  • Using incorrect date formats for the function arguments. Make sure to use valid Excel date values for settlement, maturity, issue, and first_coupon dates.
  • Forgetting to specify the optional [basis] argument when a different day count basis is required. By default, Excel uses the US (NASD) 30/360 basis (0).
  • Not adjusting the frequency argument according to the number of coupon payments per year. Ensure that the frequency is set to 1, 2, or 4 for annual, semi-annual, or quarterly payments, respectively.

Why Isn’t My ODDLPRICE Working?

If you encounter issues with the ODDLPRICE function, consider the following troubleshooting steps:

  • Check for any error messages returned by the function, such as #NUM! or #VALUE!. These errors may indicate issues with the input values or the function arguments.
  • Ensure that all date values are entered as valid Excel date values. Invalid date values can cause the function to return an error.
  • Verify that the frequency argument is set to a valid value (1, 2, or 4) and matches the number of coupon payments per year for the security.
  • Double-check the [basis] argument, if specified, to ensure it is set to a valid value (0, 1, 2, 3, or 4).

ODDLPRICE: Related Formulae

Here are some related Excel financial functions that you may find useful when working with bond pricing and analysis:

  • ODDFPRICE: Calculates the price per $100 face value of a security with an odd final period.
  • PRICE: Calculates the price per $100 face value of a security with regular interest periods.
  • YIELD: Calculates the annual yield of a security with regular interest periods.
  • ACCRINT: Calculates the accrued interest for a security with regular interest periods.
  • COUPNCD: Calculates the next coupon date after the settlement date for a security with regular interest periods.

By mastering the ODDLPRICE function and its related formulae, you can effectively analyze and compare various bonds and securities, even those with odd first periods, to make informed investment decisions.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More