The RECEIVED function in Excel is a powerful tool designed for financial analysis, specifically for calculating the actual amount received from an investment or loan when interest has been paid. This function takes parameters such as interest rate, number of periods, and the total bond repayment to determine the amount received.

Syntax

The syntax for the RECEIVED function is as follows:

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

• Where:
  Settlement (required): The security’s settlement date, which is the date after the issue date when the security is traded to the buyer.
• Maturity (required): The security’s maturity date, which is the date when the security expires.
• Investment (required): The amount invested in the security.
• Discount (required): The security’s discount rate.
• Basis (optional): The type of day count basis to use. The possible values are:
  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360
  • 3: Actual/365
  • 4: European 30/360

Examples

Example 1: Calculating the Amount Received from a Bond
Suppose you have the following data:

• Settlement date: February 15, 2008
• Maturity date: May 15, 2008
• Investment: $1,000,000
• Discount rate: 5.75%
• Basis: Actual/360

You can use the RECEIVED function as follows:

=RECEIVED("2/15/2008", "5/15/2008", 1000000, 0.0575, 2)

This results in $1,014,585

Error Handling

Errors in the RECEIVED function may occur due to the following reasons:

• Dates should be entered using the DATE function or as results of other formulas or functions to avoid errors.
• If the settlement or maturity date is not valid, the function returns the #VALUE! error.
• If the investment or discount is less than or equal to 0, the function returns the #NUM! error.
• If the basis is less than 0 or greater than 4, the function returns the #NUM! error.
• If the settlement date is greater than or equal to the maturity date, the function returns the #NUM! error.

Conclusion

The RECEIVED function is an essential financial tool in Excel that can assist users in determining the amount of money received from investments and loans. By understanding its syntax, parameters, and potential errors, users can effectively utilize this function to enhance their financial analyses and decision-making.