Syntax. YIELDDISC(Settlement,Maturity,Pr,Redemption,Basis)
Definition. This function calculates the annual yield (as an interest rate in arrears) for a security that has received a markdown (discount, disagio) for an intra-annual duration.
The only difference between this function and the INTRATE() function is the naming of the arguments.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The day when the payments from the security must take place.
Pr (optional) The purchase value of the security at the day of settlement.
Redemption (required) The payment at the day of maturity.
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.
All function arguments that take a date use the date without time; 0 fractions are rounded. The argument Basis also requires an integer, and decimal places are truncated.
Take note of the following:
If invalid dates are used or no numbers are entered where required, the function returns the error
#VALUE!.Pr and Redemption require positive numbers. Otherwise, YIELDDISC() 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, YIELDDISC() returns the
#NUMBER!error. The same happens when the settlement date is later than the maturity.
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.”
The notes concerning the par value of 100 are wrong.
Background. In principle, this function belongs to the group of anticipative yield. The principle is based on the assumption that a seed capital amount (deposit amount, loan) results from the redemption amount minus the interest on this amount. This principle is mainly used in the intra-annual scope and differs from the principle based on a savings account or mortgage loan. In this case, interest is calculated at the end of a period based on the seed capital (interest yield in arrears).
So that you can compare securities with interest payment in arrears, the difference between redemption and price must be put into relation to the price. The result corresponds to the relative increase until maturity—an interest rate in arrears—which, as is done frequently in similar situations, receives the name Yield. The calculation is done without compound interest. The interest rate must be extrapolated to one year (with corresponding day counting).
Examples. The following examples explain the YIELDDISC() function.
Bill of Exchange Calculation. Bills of exchange have a long tradition in money lending and are a type of supplier loan. Anticipative interest payment is used. If a business owner wants to compare taking up a supplier loan with a bill of exchange against the effective yield of an advance on a current account, he must perform an equivalent conversion of the anticipative interest rate. To do so, he can use YIELDDISC (or INTRATE()).
On May 10, 2010, a business owner presented his bank with a bill of exchange in the amount of $5,000 with a (residual) maturity of two months. The bank specifies a discount rate of 5 percent p.a. and does not charge fees. He receives a credit of $4,958.33 ($5,000.00 minus 5 percent of the total). How high is the price as interest rate in arrears, with which the bank has him pay the immediate credit?
With
=YIELDDISC("10/5/2010","10/7/2010",4958.33,5000,4)you can calculate 5.04 percent as annual interest rate.
Of course, usually the concrete numbers would be located in cells and the formula would use cell references.
Treasury Bonds. On August 30, 2010, Germany offered treasury bonds (security ID 111 704) with the following conditions: Duration 1 year, disbursement €500 (results from the minimum purchase price); maturity on August 22, 2011; sale interest 0.46 percent (this is the name for the percent anticipative discount). What is the effective yield with a purchase price of €497.75?
To answer this question, you just need to create a ratio between the “profit” and the invested capital and make sure that the duration is 360 of 360 days. The formula makes this easier (you can use cell references instead of concrete numbers). The described process will also work in the future. The number of days you need to use then depends on the specifications of the Federal Bank and the actual purchase date:
=YIELDDISC("30/8/2010","22/8/2011",497.75,500,4)which return 0.462 percent. You can also get the same number for the yield from the German Federal Bank.