Syntax. DISC(Settlement,Maturity,Price,Redemption,Basis)
Definition. This function calculates the anticipative interest rate (percent markdown, discount, disagio) of a given cash value or final value and the given duration (simple interest yield).
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The date when the payments from the security must take place.
Price (required) Specifies the price at which the ownership changes.
Redemption (required) Describes the payment at the day of maturity.
Basis (optional) Defines the method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is not used, Excel calculates with Basis = 0.
All function arguments that concern a date use the date without the time; that is, fractions are rounded. The Basis argument also requires an integer, and decimal places are truncated.
If invalid dates are used or no numbers are entered where required, the function returns the #VALUE! error. If invalid numbers are entered for nondate arguments, the function returns the #NUMBER! error.
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 note regarding the par value in the Price and Repayment arguments is irrelevant, because DISC() returns a ratio.
Background. The principle of anticipative interest yield is based on the assumption that a seed capital amount (deposit amount/loan) results from the repayment amount minus the interest on this amount. This principle is mainly used in the intra-annual scope and differs from the principal based on a regular 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).
Note
The functions are connected as follows (in this example, Basis = 4):
But that is exactly the principle of anticipative interest yield. The DISC() function resolves the equation assuming that the other specifications are available.
The DISC() function has the following relation to RECEIVED():
This relationship permits you to calculate the equivalent anticipative annual interest rate from an annual interest rate in arrears. Equivalence means that two investors who are investing the same amount of capital (one with anticipative interest yield and one with interest yield in arrears) at the end of a year receive the same payment. The YIELDDISC() function is basically the same as RECEIVED(), only the arguments are named differently.
Examples. The following examples explain the DISC() function:
Bill of Exchange Calculation. On May 10, 2010, a businessman presented his bank with a bill of exchange of $5,000.00 and a (remaining) maturity of two months. The bank has credited $4,958.33 to his account (without adding fees). How high was the discount rate?
From the background information, you can read the equation as follows:
and use
=DISC("10/5/2010","10/7/2010",4958.33,5%,4)to get to a result of 5 percent.
Usually you would use cell references in this function to refer to the concrete numbers located in cells.
Treasury Bonds. At the time of this writing, Germany offered treasury bonds (security ID 111 704) with the following terms: Duration = 1 year, redemption value (value at end of duration) = €500 (the result from the minimum purchase price), and maturity August 22, 2011. On August 30, 2010, the sale price was €497.75. How high did the German Federal Bank specify the sale interest (this is the name for the anticipative markdown)?
DISC() delivers the correct answer: 0.46 percent.
For data acquisition you should read the preceding formula as:
You can calculate the annuity of the investment of 0.45 percent specified by the German Federal Bank in two steps: Create the difference between the purchase price and revenue and then build a ratio to the purchase price, or you could use YIELDDISC() or RECEIVED().