Syntax. ODDFPRICE(Settlement,Maturity,Issue,First_Coupon,Rate,Yld,Redemption, Frequency,Basis)
Definition. Calculates the price of a fixed-interest security while taking into consideration the first interest period, which is shorter or longer than the remaining regular interest periods (quarter, half year, entire year).
Arguments
Settlement (required) The date when the loan becomes the property of the purchaser.
Maturity (required) The date when the repayment of the certified amount takes place.
Issue (required) The date of the emission of the security.
First_Coupon (required) The date of the first 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 ODDFPRICE() function returns the
#VALUE!error.Price and Yield require nonnegative numbers. Otherwise ODDFPRICE() 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, ODDFPRICE() returns the error
#NUMBER!.The same result described in the preceding bullet happens when the correct sort order (Maturity is greater than First_Coupon, which is greater than Settlement, which is greater than Issue) 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().
The formula in Excel Help is reduced to the following, if interest is paid regularly once a year (360 days) and the first interest period is shortened:
N is the total number of interest dates, A is the time between emission date and settlement date, DSC is the time between settlement date and first interest date, and DFC is the time between the first interest day and the emission day.)
As you can see, the first period is handled differently, if you compare it to the price formula from the background information for the function PRICE(). It is not part of the summation and cash value creation of the following periods, because the interest payment does not take place with a full coupon.
If Frequency is greater than 1 (that is, if it is 2 or 4), Rate and Yield must be replaced with the regular distributions over the intra-annual periods.
To understand the formula in Excel Help for a first interest period that is longer than the “regular” ones, simply keep in mind that the first period will contain several fictitious interest payments that will become a reality at the end of the period.
Accrued interest in the first period is handled similarly.
As soon as the first interest date is passed, the function is no longer used.
Example. In the sample files for this function, there is an example that shows a calculation for a fictitious bond with a shortened first interest period. It is similar to the calculation in Figure 15-3, shown earlier. The result is the same that the function ODDFPRICE() would deliver.