Depending on the buyer’s preferences, there are periodic investments as well as fixed investments to choose from. Suppose that we purchased a fixed-income security today. We would certainly be interested to know how much we receive when the investment matures. The RECEIVED formula in Google Sheets appropriate for this context.
RECEIVED(settlement, maturity, investment, discount, [day_count_convention])
- settlement – is the date after issuing the security, when it is actually delivered to the buyer.
- maturity – is the end or maturity date of the security, when the buyer can redeem it at face or par value.
- investment – is the amount the buyer invests regardless of the face value.
- discount – it is the discount rate of the security the buyer has invested in.
- day_count_convention – [OPTIONAL parameter – 0 by default ] – it is an indicator of the day count method that Google Sheets should consider. There are five different possible values for this parameter.
- 0 – assumes that there are 30 day months and 360 day years (US (NASD) 30/360). Using this value ensures there are specific adjustments to the entered dates that usually are at the end of months.
- 1 – calculates based on the actual number of days between the specified dates, and the actual number of days in the intervening years.
- 2 – calculates based on the actual number of days between the specified dates, but assumes a 360 day year.
- 3 – evaluates the RECEIVED formula in Google Sheets based on the actual number of days between the specified dates, but assumes a 365 day year.
- 4 – very similar to the first option 0, except, it adjusts end-of-month dates according to European financial conventions.
Usage: RECEIVED formula in Google Sheets
We get our concepts much clearer when we try and apply them in practice. So, let’s go straight ahead and dig into a few examples. Please take a look at the snapshot below.
Like in other Google Sheets’ financial formulas, the settlement and maturity parameters expect valid dates. Therefore, we need to either use outputs from functions such as DATE, TO_DATE. Or, they can also be references to other date type cells. Google Sheets may return parsing errors if we enter direct text values for dates.
While all the formulas in the above image are essentially the same, the output values seem to vary. This is because of the different day_count_convention methods we are using.