The RECEIVED function in Google Sheets is a financial (category) function for calculating the amount received at maturity for investment in fixed-income securities like bonds, ABS, etc.
You need not be a financial analyst to calculate the return of a bond you purchased! You can use Google Sheets for this by entering the URL https://sheets.new/ on your browser’s address bar.
Entering the input values such as settlement date, maturity date, and the discount rates, you can calculate the maturity amount of the bond you hold using the RECEIVED function in Google Sheets.
To make you ease in using the RECEIVED function, I know I must explain the syntax and arguments of this function in detail.
Google Sheets RECEIVED Function – Syntax and Arguments
Syntax
RECEIVED(settlement, maturity, investment, discount, [day_count_convention])Arguments
There are three arguments and one optional argument in the function RECEIVED. The details are as follows.
- settlement – The settlement date of the security. It’s the date after the issue date (date after issuance) when the security is delivered (traded) to the buyer.
- Assume a 6-year bond is issued on January 1, 2014. If a buyer purchased it after 3 months, the settlement date would be April 1, 2014.
- maturity – The maturity or expiry date of the security (when it can be redeemed at the face or par value).
- For your info, the face value (the amount to be paid at maturity) for bonds is often referred to as par value.
- If you consider the above 6-year bond, the maturity date would be January 1, 2020.
- discount – The discount rate (rate of return) of the security invested in.
- day_count_convention – Day count basis. This is an optional argument and 0 is by default.
- Please refer to the below day count table for more info.
Day Count Table
| day_count_convention | Description |
| 0 or omitted | US (NASD) 30/360 – 30 day months and 360-day years. |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
RECEIVED Function Example in Google Sheets
In the following example, let’s see how to calculate the maturity for a bond with an initial investment of $10,000 and a discount rate of 4.5%.
The settlement date of the bond is 1-Apr-2014 and the maturity date is 01-Jan-2020.
As a side note, in simple terms, the bond (an instrument of indebtedness) can be defined as loans made by a bondholder to an issuer.
Formula:
=received(C4,C5,C2,C6)
In this example to the RECEIVED function in Google Sheets, I have omitted the day_count_convention. So it’s considered as 0; it means US (NASD) 30/360 (see the table above).
When you directly feed the arguments take care of the two arguments they are settlement and maturity dates.
You must specify the dates using the DATE function to avoid unintentional errors. The date should be provided in the DATE(year, month, day) format.
=received(date(2014,4,1),date(2020,1,1),10000,4.5%)Possible Errors in the RECEIVED Function Use
The #VALUE! and #NUM! are the two possible errors that you may happen to see in the use of the RECEIVED function in Google Sheets. If you are familiar with spreadsheets, you won’t make these errors happen.
Reasons for the #VALUE! Error:
- Invalid settlement/maturity date formats.
- When investment amount and discount percentage are formatted as texts.
Reasons for #NUM! Error:
- When you have forgotten to enter the investment amount or discount % you will see the #NUM! error in the RECEIVED function use.
- If the investment or discount is <=0, the formula returns this error.
- Invalid day_count_convetion base. It should be omitted, or one of the numbers from 0, 1, 2, 3, and 4.
- If the settlement date is >= maturity date, the RECEIVED formula returns the #NUM! error.
That’s all. Enjoy!
