What is RECEIVED Function in Google Sheets?
The RECEIVED function in Google Sheets is used to calculate the amount received at maturity for a security, such as a bond, that pays interest at maturity. It is particularly useful in financial analysis when you wish to determine the final return on an investment based on the issue price, maturity value, interest rate, and the investment’s duration. It assumes that the security is held to maturity and uses a fixed annual interest rate. The function requires the following five arguments: settlement date, maturity date, investment, discount, and day count basis (optional).
As an example, consider an investment of $9,500 in a bond that matures on December 31, 2025. It was issued on January 1, 2025, with a discount rate of 6%. Let us calculate the amount we will receive at maturity using the following formula:
=RECEIVED(DATE(2025,1,1), DATE(2025,12,31), 9500, 0.06)
This formula finds the final amount received from the bond investment using the specified dates, investment amount, and discount rate. It is the amount the investor receives on the maturity date, which includes both the investment and interest earned.
Key Takeaways
- RECEIVED in Google Sheets calculates the amount one receives from an investment after adding up the interest at maturity. It’s particularly useful when dealing with bonds or other fixed-income securities.
- The syntax of the function is as follows: =RECEIVED(settlement, maturity, investment, discount, basis)
- The output is the amount received after accounting for the discount rate over the investment period.
- Understanding how much a person will receive from their investments is very useful for investors. The RECEIVED function allows one to forecast these returns easily.
Syntax
The following is the RECEIVED Google Sheets formula and is its syntax.
=RECEIVED(settlement, maturity, investment, discount, [basis])
Arguments:
- settlement – The date on which the security is purchased.
- maturity – The date when the security matures or expires. Its value must be grwater than the settlement date.
- investment – The amount of money invested in the security.
- discount – The annual discount rate supplied as a decimal.
- basis (optional) – The type of day count basis to use which affects the interest calculation over time. The options are
- 0 or omitted – US (NASD) 30/360
- 1 – Actual/actual
- 2 – Actual/360
- 3 – Actual/365
- 4 – European 30/360
How to Use RECEIVED Function in Google Sheets?
The RECEIVED function helps calculate the amount received at the maturity of a discounted security. The type of securities includes a bond or treasury bill. We use this function in financial modeling to determine the returns on short-term investments. This helps investors and analysts forecast the maturity value of fixed-income instruments.
There are two ways to use RECEIVED in Google Sheets:
- Enter RECEIVED manually in a cell
- Use the Google Sheets menu bar
Enter RECEIVED in Google Sheets Manually
Let’s understand how to use the RECEIVED function manually with a simple example. A person invests $12,000 in a discounted bond on January 1, 2024, and the bond matures on December 31, 2025. The bond offers a 5% annual discount rate. Let us calculate the amount the person will receive when the bond reaches maturity at the end of the year.
Follow these steps:
Step 1: In a spreadsheet, input the required data. Ensure that the dates are in the correct date format for Google Sheets to perform accurate calculations.
Step 2: Go to the cell where you wish to display the final maturity amount. Enter the function and its arguments within the parenthesis based on the syntax.
Here, we enter:
=RECEIVED(DATE(B1), DATE(B2), B3, B4)
This formula uses the dates, investment amount, and discount rate to calculate the amount the person will receive at the end of the term. The discount rate should be entered as a decimal value for accurate results.
Step 3: Press Enter. The result shown in the selected cell will be the total amount you’ll receive at maturity, including the interest earned from the discount.
Using the Menu Bar
- Click on the cell where you want to display the result.
- Navigate to the following path.
Insert → Function → Financial → RECEIVED.
3. Afterwards, enter each argument: settlement date, maturity date, investment, discount rate, and optionally, the day count basis.
4. Press Enter. The cell will show the final maturity value based on your inputs.
Examples
We look at some examples to see how the RECEIVED function in Google Sheets is used in financial scenarios in real-time. We get an idea of its practical application in calculating the maturity amount of discounted investments.
Example #1
In this example, an investor purchases a short-term discounted bond for $10,700 on January 31, 2020, with a maturity date of December 31, 2022. The bond has an annual discount rate of 7%. The investor must find how much they will receive at maturity using the RECEIVED function.
Step 1: Enter the data in a sheet, as shown below.
Step 2: Click on a cell where you want to see the result and enter the following formula:
=RECEIVED(B1,B2, B3, B4)
Step 3: Press Enter. We get the amount to be received at maturity, which in this case is $13,445.03.
The result shows the full amount the investor will receive when the bond matures, allowing them to assess potential returns and compare investment options in an efficient manner.
Example #2 – Calculate Maturity Value of a 6-Month Treasury Bill
In this example, we perform the calculation for a 6-month Treasury Bill. An investor buys a 6-month Treasury bill for $5,500 on March 1, 2025. It matures on September 1, 2025. The bill carries a 5% annual discount rate. The investor wants to find out the amount they will receive at maturity.
Step 1: The calculation is as straightforward as in the previous example. Enter the details in a sheet.
Step 2: Click on an empty cell and enter the following formula:
=RECEIVED(A1, A2, A3, A4)
Step 3: Press Enter. The function will return the total amount receivable at maturity, which helps the investor understand the short-term gain from the Treasury bill.
Example #3 – Find Return on a Corporate Bond Purchased at 5% Discount
A businessman purchases a corporate bond at a 5% discount for $19,000 on July 1, 2025, with a maturity date of July 1, 2026. He wishes to calculate the total amount he will receive when the bond matures. Let us use the RECEIVED function in Google Sheets.
First, we must understand that a corporate bond purchased at a discount will have a higher return than its coupon rate due to the appreciation in value as it approaches maturity.
Step 1: We enter the details in Google Sheets as shown below. Now, type in the following formula in an empty cell:
=RECEIVED(B1,B2,B3,B4)
Step 2: Press Enter. Google Sheets will return the total maturity value, which is $20,000 in this example.
Thus, the RECEIVED function is a powerful tool for investors and financial planners to calculate the amount earned from discounted securities, such as Treasury bills, corporate bonds, and other short-term investments. It requires very direct inputs like the settlement date, maturity date, investment amount, and discount rate to find the maturity value. Thus, one can make informed decisions and compare investment options for both personal and business finance.
Important Things to Note
- We use RECEIVED in Google Sheets for estimating the maturity value of short-term investments, particularly when dealing with bonds or other such securities that pay a lump sum at maturity.
- In the syntax, the day_count_convention argument allows for flexibility in how the number of days between settlement and maturity is calculated.
Frequently Asked Questions (FAQs)
The following scenarios may lead to errors.
• Always ensure that the discount rate is entered as a decimal and not a percentage.
• Also, check the value you enter for the day count basis; different settings can lead to different calculations.
The basis argument determines how the number of days is calculated in the year when computing interest. It affects the result slightly depending on the financial context.
The following are the values we use:
0: US (NASD) 30/360
1: Actual/actual
2: Actual/360
3: Actual/365
4: European 30/360
Dates must be recognized as valid date values. For this, we can enter the date directly in a cell in the correct date format. You can also use the function DATE(year, month, day) function inside the formula. For instance: DATE(2025,1,1) ensures that the RECEIVED function reads the date correctly. Text-formatted dates may not work and can lead to errors.
If the settlement date is later than maturity, the function returns a #NUM! error. It is because logically the investment should begin before it matures. Always ensure that the input dates follow a valid time order, first the settlement first, followed by a later maturity date.
Download Template
This article must help understand RECEIVED Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is RECEIVED Function in Google Sheets. We learn how to use it to calculate the amount received at maturity for a security. You can learn more from the following articles. –
FORECAST.LINEAR in Google Sheets
Leave a Reply