RECEIVED()

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().

See Also

DISC(), INTRATE(), PRICEDISC(), YIELDDISC()

Sample Files

Use the Bill of Exchange worksheet in the Simple Interest Calculation.xls or Simple Interest Calculation.xlsx sample file. The sample files are found in the Chapter15 folder. For more information about the sample files, see the section titled Using the Sample Files.