ODDLPRICE Excel Formula
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
Categories
Recent Posts
- How to Use the MS Excel Keyboard Shortcuts
- How to Locate Duplicate Records Quickly By Using Conditional Formatting in MS Excel
- How to Use PasteSpecial – Procedures for Microsoft Excel 2003 and Microsoft Excel 2007
- How to Sort using an AutoFilter – Understanding and Learning the Feature
- How to Base a Chart Off of Auto Filtered Records
- How to Use IF IsError Together?
- How to Use Custom Views
- How to Create Pivt Table
- How to Manipulate PivotTable after It has Been Created
- How to Update the Data in a Pivot Table
- How to Create Dynamic Named Ranges
- How to Use an Array
- How to Embed 8 If Statements into 1
- How to Edit a Recorded Macro?
- How to Record a Macro