Syntax. ODDLPRICE(Settlement,Maturity,Last_Interest,Rate,Yld,Redemption, Frequency,Basis)
Definition. This function calculates the price of a fixed-interest security in a final interest period whose length is different from the regular interest periods in the past. Compound interest is not taken into consideration.
Arguments
Settlement (required) The date when the bond becomes the property of the purchaser.
Maturity (required) The day when the repayment of the certified amount takes place.
Last_Interest (required) The date of the final (regular) interest payment.
Rate (required) The nominal yield (annual interest rate) of the bond.
Yld (required) The market interest rate of bonds of the given duration.
Redemption (required) The percentage of the repayment based on the par value of a security (as if it was 100 monetary units).
Frequency (required) The number of interest payments within a year (annually, biannually, quarterly).
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
Take note of the following:
Date specifications must not have a time; decimal places are truncated. The arguments Frequency and Basis are also truncated to integers.
If date arguments cannot be resolved to a valid date, the ODDLPRICE() function returns the
#NUMBER!error.Rate and Yield require nonnegative numbers. Otherwise ODDLPRICE() returns the
#NUMBER!error.If Frequency cannot be resolved to 1, 2, or 4, or Basis cannot be resolved to a number from 0 through 4, ODDLPRICE() returns the error
#NUMBER!.The same result as explained in the preceding bullet happens when the correct sort order (Maturity is greater than Settlement, which is greater than Last_Interest_Date) is not kept.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
Background. To implement the finance mathematical benefit principle
Payment of the creditor = Payment of the debtor
for the start of the transaction, the price of a fixed-interest security (loan) plus potential accrued interest equals the cash value of the debtor’s future payments certified in this security. The price is the percentage of the security’s par value, as if the par value was 100 monetary units.
The cash value calculation is no problem, if the purchase date (change of ownership) of a security with an annual interest payment is the same as the day of the interest payment. In this case, only the whole year needs to be taken into consideration. A situation in which the change of ownership takes place between interest due dates or in which interest payments take place several times a year is not so easy. In finance mathematics there are several ways to deal with a year that is broken up into parts. The best known methods are Moosmüller and Braess/Fangmeyer as well as ISMA.
You can find details about the correlation between the ISMA method and the Excel function calculations in the background information about PRICE() and YIELD().
In the case being discussed, the principle used by Excel can be formulated as follows as a result of the simple yield (no compound interest) at a given market interest (annual yield):
The accrued interest from the partial interest since the last interest date and the partial yield are calculated from the days until maturity (based on the total number of days in the year).
The function is not used outside the final time period until maturity.
Example. In the sample files for this function there is an example that shows a calculation for a fictitious bond with the required terms. It is similar to the calculation shown earlier in Figure 15-3. The result is the same as what the function ODDLPRICE() would deliver.