Syntax. ODDFYIELD(Settlement,Maturity,Issue,First_Coupon,Rate,Price,Redemption, Frequency,Basis)
Definition. This function calculates the yield of a fixed-interest security for the duration from the settlement date to the maturity date. It takes into consideration that the first interest period might be shorter or longer than the other regular interest periods.
Arguments
Settlement (required) The date when the bond 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.
Price (required) The price of the security on the day of settlement as a percentage; 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 ODDFYIELD() function returns the error
#VALUE!.Price requires nonnegative numbers. Otherwise ODDFYIELD() 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, ODDFYIELD() returns the
#NUMBER!error.The same result as explained 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. For information about this function, see the background information for the ODDFPRICE() function.
The ODDFYIELD() 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.
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 shown later in Figure 15-7. The calculation follows the pattern of the price calculation (as shown in the example for ODDFPRICE()). 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). ODDFYIELD() returns the same result.