Syntax. PRICEDISC(Settlement,Maturity,Discount,Redemption,Basis)
Definition. This function calculates the disbursement amount of a simply discounted security with anticipative interest yield.
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.
Discount (required) The interest rate of the discount (disagio).
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; fractions are rounded. The argument Basis 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 error #VALUE!. 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 reference to the par value is wrong.
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 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).
Note
The functions are connected as follows (in this case, Basis = 4):
But that is exactly the principle of anticipative interest yield.
The Repayment argument used by the function should be considered with respect to the task of the INTRATE() function.
Examples. The following examples show how to use PRICEDIS().
Bill of Exchange Calculation. On May 10, 2010, a businessman presented his bank with a bill of exchange in the amount of $5,000 with a (residual) maturity of two months. The bank adds a discount rate of 5 percent p.a. and does not charge fees. How high is the amount that is credited to the account?
From the background information, you can read the equation as follows:
and use
=PRICEDISC("10/5/2010","10/7/2010",5%,5000,4))to get to a result of $4,958.33. This formula would usually use cell references to refer to concrete numbers.
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), mature on August 22, 2011; sale interest 0.46 percent (this is the name for the percent anticipative discount). How high is the purchase price?
PRICEDISC() delivers the correct answer here as well: €497.75.
At which interest rate in arrears would a comparable investment (same duration, same usage, same result) have to take place? To answer this question, you just need to create a ratio between the gain and the invested capital and make sure that the duration is 365 of 365 days. The function INTRATE() is easier to use. It works according to the pattern INTRATE(Settlement,Maturity,Investment,Redemption,Basis) and returns 500. You can also get the same number for the yield from the German Federal Bank. The function YIELDDISC() works the same as INTRATE() and returns the same result.
The described process will also work in the future. The number of days you need to use will depend on the specifications of the Federal Bank and the actual purchase date.
Because of the low yield, these results are not very interesting when it comes to more decimal places and the differences to the nominal interest.