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

Syntax

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

Arguments

Argument Description
settlement Settlement date of the security, i.e. the date that the coupon is purchased
maturity Maturity date of the security, i.e. the date that the coupon expires
investment Initial amount invested in the security
discount Security’s discount rate
[basis] Optional. Defines the day count basis to be used in the calculation

  [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: The settlement and maturity dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C  
1 Data      
2 01-Apr-2010 Purchase date    
3 31-Mar-2015 Maturity date    
4 $100,000.00 Investment    
5 4.5% Discount Rate    
6 2 Actual/360 basis    
7        
8 Formula Result Notes
9 =RECEIVED(A2,A3,A4,A5,A6) $129,554.66 The total amount to be received at maturity, for the bond with the terms in A2:A6
10 =RECEIVED(DATE(2010,4,1),DATE(2015,3,31),A4,A5) $129,032.26 The total amount to be received at maturity, for the bond with the terms in A2:A5, [basis] is omitted – defaults to 0, DATE function used to enter dates

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either of the supplied settlement or maturity arguments are not valid dates or any of the other arguments are not recognized as numeric values
#NUM! Occurs if either:

  the supplied maturity date is ≤ the supplied settlement date
  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
#NAME? Occurs when Analysis ToolPak add-in is not enabled

RECEIVED is calculated as:  

    \[ \text{RECEIVED} = \frac{investment}{1-(discount\ \text{x}\ \frac{DIM}{B}) }  \]

where:

  • B = number of days in year, depending on [basis]
  • DIM = number of days from issue to maturity