Syntax. ODDLYIELD(Settlement,Maturity,Last_Interest,Rate,Price,Redemption, Frequency,Basis)
Definition. This function calculates the yield 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 loan becomes the property of the purchaser.
Maturity (required) The date when the repayment of the certified amount takes place.
Last_Interest (required) The date of the final (regular) interest payment before the purchase date.
Rate (required) The nominal yield (annual interest rate) of the bond.
Price (required) The price of the security on the day of settlement as a percentage; that is, it must be interpreted as if the par value of the paper was 100 monetary units.
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 ODDLYIELD() function returns the
#NUMBER!error.Price and Rate require nonnegative numbers. Otherwise ODDLYIELD() returns the error
#NUMBER!.If Frequency cannot be resolved to 1, 2, or 4, or Basis cannot be resolved to a number from 0 through 4, ODDLYIELD() 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. For more information about this function, see the background information for the ODDLPRICE() function.
The ODDLYIELD() function determines the value of the yield in such a way that a desired price (or price payable on the market) is reached, and informs the purchaser about the expected effective yield of the investment. Because of the principle of simple yield used (no compound interest), the price formula can be resolved to the yield. The formula in Excel Help can be read like this:
The partial period based on the year makes the yield an annual interest rate. The interest at maturity results from the interest since the last interest date. The accrued interest is prorated based on the time of the last interest payment up until the purchase.
Example. In the sample files for this function there is an example that shows a calculation for a fictitious bond with a changed first interest period. It is similar to the calculation in Figure 15-7, shown later. The calculation follows the pattern of the price calculation (as shown in the example for ODDLPRICE()). The desired yield is then determined with the target value search (an assumed yield changes in such a way that the desired price is calculated). ODDLYIELD() returns the same result.