The Excel Oddlprice function calculates the price, per $100 face value of a security with an odd (short or long) last period.
The syntax of the function is :
Where the arguments are as follows:
|settlement||-||The settlement date of the security (ie. the date that the coupon is purchased)|
|maturity||-||The maturity date of the security (ie. the date that the coupon expires)|
|last_interest||-||The date of the security's last coupon|
|rate||-||The security's interest rate|
|yld||-||The security's annual yield|
|redemption||-||The security's redemption value per $100 face value|
|frequency||-||The number of coupon payments per year. This must be either 1, 2 or 4, meaning:|
|[basis]||-||An optional integer argument which specifies the financial day count basis that is to be used in the calculation. Possible values are:|
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page
Note that the date arguments must satisfy the following:
Note also, that the settlement, maturity and last_interest arguments should be entered into the function as either:
In the following spreadsheet, the Excel Oddlprice function is used to calculate the price per $100 face value of a security with a last coupon date of 31-Jan-2011, a settlement date of 14-Apr-2011, and a maturity date 30-Jun-2011. The rate of interest is 5.5%, the annual yield is 3.5% and the redemption value is $100. Payments are made quarterly and the US (NASD) 30/360 day count basis is used:
|4||=ODDLPRICE( B1, B2, B3, 5.5%, 3.5%, 100, 4 )|
The function calculates the price per $100 face value to be $100.41.
Note that, in the above example:
Further examples of the Excel Oddlprice function can be found on the Microsoft Office website.
The most common Oddlprice function errors and their causes are listed in the following table:
Occurs when Analysis ToolPak add-in is not enabled in your Excel.