ODDLPRICE Excel Formula

Author Zaheer    Category Formulas     Tags ,

When it comes to computation, one of the tools that give accurate results is Microsoft Excel. Since it is a formula-based program, you should input the right syntax to ensure that you can obtain the correct result. To accomplish computation easily, the program is equipped with different functions. These functions serve as the shortcut method to some conversions and alterations on the process of computation. If you are using the program for computation work, you need to be familiar with these functions and the codes incorporated in them. Through reading this article, you will be able to know one of the functions of the program which is known as the ODDLPRICE function. This function computes for the cost on every $100 face value of security with odd end period.

In addition to this function, the rate, expected yield and redemption value should be specified. To use this function, the applicable syntax is:

=ODDLPRICE(settlement, maturity, last_interest, yield, redemption, frequency, [basis])

For a better understanding of the formula, below are the arguments:

  • settlement – this is the date of settlement of the security. This is the exact date when the coupon was bought.
  • maturity – this is date of maturity of the security, the date when it will expire
  • last_interest – the last coupon security date
  • rate – the rate of interest of the security
  • yield – the annual yield of the security
  • redemption – this is the redemption value of the security on every $100 face value
  • frequency – the number of payments for the coupon each year. The values needed for this can be 1, 2 or 4. The value of 1 corresponds for annually, 2 for semi annually and 4 for quarterly.
  • [basis] – this is an optional value. This indicates the day count basis to be used on the process of computation. The possible value for this is 0 for US method of day count which is 30/60, 1 for actual/actual, 2 for actual/360, 3 for actual/365 and 4 for European day count method.

To use the function correctly, the date arguments must satisfy the condition that the last_coupon is less than settlement and settlement is less than maturity. User must also remember that the last_interest, maturity and settlement arguments can be supplied in the function as reference to cells that contain the dates or dates that are returned from other formulas or functions.

Study and learn this example below so you can understand the usage of the function.

Assume that a certain bond matures on September 5, 2011 having a rate of interest of 5% and yield of 4%. The final date of interest is before October 22, 2010 and was bought on May 27, 2011. It is a semi-annually payment, $100 cost of redemption and 30/360 day count basis.

Through this example you can use ODDPRICE by following the procedure:

Enter all the given data on the Microsoft Excel Worksheet.

A1 -  May 27, 2011 – settlement

A2 – September 5, 2011 – maturity

A3 – October 22, 2010 – last_interest

A4 – 0.05 – rate

A5 -  0.04 – yield

A6 – 100 – redemption

A7 -  2 – frequency

A8 – 0 -  30/360

After inputting the data, click a cell where you want the result to appear. In the cell, enter the ODDLPRICE syntax and the result will be $100.237.

User must understand that errors normally appear, with this just check the input of data because in most situations this is the primary cause for error values to appear.

Further readings

Post comment