Syntax. RECEIVED(Settlement,Maturity,Investment,Discount,Basis)
Definition. This function determines the repayment amount of a deposit resulting from a simple anticipative deduction of accrued interest.
Arguments
Settlement (required) The day on which the deposit (investment on the buyer side, borrowing on the seller side) is made
Maturity (required) The date when the repayment of the deposit takes place
Investment (required) The amount of the deposit (the loan)
Discount (required) Specifies the percentage of the discount rate
Basis (optional) Defines the method you want to use for determining the days in the year according to Table 15-2, shown earlier
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. However, problems might occur if you enter dates as text.”
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 that 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 (noted here in the case of Basis = 4):
But that is exactly the principle of anticipative interest yield. The function RECEIVED() resolves the preceding equation assuming the other specifications are available.
Examples. The following examples explain how to use the RECEIVED() function.
Bill of Exchange Calculation. On May 10, 2010, a businessman presented his bank with a bill of exchange with a (remaining) maturity of two months. The bank set a discount rate of 5 percent p.a. and credited $4,958.33 to his account. How high was the bill amount?
Of course, this exercise is not a typical situation. Normally you would know the bill amount (the repayment amount, which is called INTRATE() in Excel) and would ask for the amount of the disbursement (called PRICEDISC()).
From the background information, you can read the equation as follows:
and use
=RECEIVED("10/5/2010","10/7/2010",4958.33,5%,4)to get to the result of $5,000. Usually this formula would use numbers located in cells and would use cell references.
Treasury Bonds. Treasury bonds represent a further example where anticipative interest yield is used. Take a look at the examples for the functions PRICEDISC() and YIELDDISC().