The Excel RECEIVED Function

Function Description

The Excel RECEIVED function calculates the amount received at maturity for a fully invested security.

The syntax of the function is:

RECEIVED( settlement, maturity, investment, discount, [basis] )

where the arguments are:

settlement

The security's settlement date (i.e. the date that the coupon is purchased).

maturity

The security's maturity date (i.e. the date that the coupon expires).

investment

The initial amount invested into the security.

discount

The security's discount rate.

[basis]

An optional argument which defines the day count basis to be used in the calculation.

Possible values of the [basis] argument, and their meanings are:

[basis]	Day Count Basis
0 (or omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	European 30/360

The financial day count basis rules are explained further on the Wikipedia Day Count Convention page

Note also, that Microsoft recommends that the settlement and maturity arguments should be entered into the Received function as either:

References to cells containing dates

Dates returned from formulas.

Warning: If you attempt to input the date arguments as text, the interpretion of these can differ, depending to the date system and date interpretation settings on your computer.

Received Function Example

Cell A3 of the following spreadsheet shows the Excel Received function, used to calculate amount received at maturity, on an investment of $1,000, which was used to purchase a security on 01-Apr-2011. The security matured on 31-Mar-2016, with a discount rate of 4.5%. The US (NASD) 30/360 day count basis is used:

	A	B
1	Settlement Date:	01-Apr-2011
2	Maturity Date:	31-Mar-2016
3	=RECEIVED( B1, B2, 1000, 4.5% )

The formula in the above spreadsheet returns the value $1,290.32.

Note that, in the above example:

As recommended, the settlement and maturity dates are supplied to the function as references to dates stored in cells B1 and B2;
The discount argument is supplied to the function as a percentage value, 4.5%. This could also have been provided as the decimal value 0.045;
The [basis] argument is omitted and so takes on the default value 0 (and therefore uses the US (NASD) 30/360 basis).

For further examples of the Excel Received function, see the Microsoft Office website.

Received Function Errors

If you get an error from the Excel Received Function, this is likely to be one of the following:

Common Errors

#NUM!

Occurs if either:

The supplied investment or the supplied discount argument is ≤ 0;
The [basis] argument is supplied and is not equal to 0, 1, 2, 3 or 4;
The supplied maturity date is ≤ the supplied settlement date;

#VALUE!

Occurs if either:

The supplied settlement or maturity arguments are not a valid Excel dates;
Any of the supplied arguments are non-numeric.

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions