The RECEIVED function in Google Sheets is a valuable tool for financial analysts and investors, enabling them to calculate the total amount received from fixed-income securities upon maturity. This function is essential for assessing the profitability of bond investments, offering insights into both the cash flow and return on investment.

Syntax

RECEIVED(settlement, maturity, coupon, frequency, [basis])

• settlement: The date when the investment is purchased.
• maturity: The date when the investment matures.
• coupon: The annual interest payment of the bond.
• frequency: The number of interest payments made each year (1 for annual, 2 for semiannual, etc.).
• basis: (optional) The day count basis to use (defaults to 0 if omitted).

Example #1

=RECEIVED("2023-01-01", "2025-01-01", 5, 2)

This function calculates the total received at maturity for a bond purchased on January 1, 2023, which matures on January 1, 2025, with a coupon rate of 5% and semiannual payments. Result: $105.00.

Example #2

=RECEIVED("2023-06-15", "2028-06-15", 4, 1, 1)

Here, the function evaluates a bond bought on June 15, 2023, maturing on June 15, 2028, with a 4% coupon paid annually, using the actual/actual basis. Result: $120.00.

Example #3

=RECEIVED("2022-12-01", "2025-12-01", 6, 2, 0)

In this scenario, the bond is purchased on December 1, 2022, and matures on December 1, 2025, featuring a coupon rate of 6% with semiannual payments. Result: $118.00.

Error handling

• NUM! – This error occurs if the maturity date is earlier than the settlement date, which is invalid for any investment.
• VALUE! – This indicates that one of the parameters is not of the correct type—often due to incorrect date formatting or non-numeric values for coupon and frequency.
• DIV/0! – This happens if the frequency parameter is set to zero, which is not allowable as it leads to division by zero in the calculation.

Conclusion

The RECEIVED function is an essential tool in Google Sheets for quantifying the returns from fixed-income securities. By using this function, investors can easily assess their investments’ performance at maturity, allowing for better financial planning and decision-making. Understanding the parameters and possible errors enhances the user experience, making this function crucial for effective investment analysis.