In this comprehensive guide, we will explore the Excel formula RECEIVED, which calculates the amount received at maturity for a fully invested security. This formula is particularly useful for investors and financial analysts who need to determine the future value of an investment based on its interest rate, investment period, and other relevant factors. We will cover the syntax of the formula, provide examples, share tips and tricks, discuss common mistakes, troubleshoot issues, and introduce related formulae.
RECEIVED Syntax
The syntax for the RECEIVED formula in Excel is as follows:
RECEIVED(settlement, maturity, investment, discount, basis)
Where:
- settlement is the security’s settlement date, which is the date after the issuance when the security is delivered to the buyer.
- maturity is the security’s maturity date, which is the date when the security expires and the principal amount is paid to the investor.
- investment is the amount invested in the security.
- discount is the security’s annual discount rate.
- basis (optional) is the type of day count basis to use. If omitted, the default value is 0, which represents the US (NASD) 30/360 day count basis. Other options include: 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360.
RECEIVED Examples
Let’s explore some examples of using the RECEIVED formula in Excel:
- Basic Example: Suppose an investor purchases a security with a settlement date of January 1, 2022, a maturity date of January 1, 2023, an investment amount of $10,000, and an annual discount rate of 5%. To calculate the amount received at maturity, the formula would be: =RECEIVED(“1/1/2022”, “1/1/2023”, 10000, 0.05). The result would be $10,512.50, which is the amount the investor will receive at maturity.
- Using Different Day Count Basis: In the previous example, if we want to use the actual/actual day count basis (basis = 1), the formula would be: =RECEIVED(“1/1/2022”, “1/1/2023”, 10000, 0.05, 1). The result would be $10,500, which is the amount received at maturity using the actual/actual day count basis.
RECEIVED Tips & Tricks
- Ensure that the settlement and maturity dates are entered in a recognized date format in Excel, such as “MM/DD/YYYY” or “DD/MM/YYYY” depending on your regional settings.
- Remember that the discount rate should be entered as a decimal. For example, a 5% discount rate should be entered as 0.05.
- When comparing different investments, use the same day count basis for consistency.
- Use Excel’s built-in date functions, such as EDATE and EOMONTH, to calculate settlement and maturity dates based on specific criteria.
Common Mistakes When Using RECEIVED
- Entering the discount rate as a percentage instead of a decimal. For example, entering 5 instead of 0.05 for a 5% discount rate.
- Using an incorrect or unsupported date format for the settlement and maturity dates.
- Not specifying the day count basis when comparing different investments, which can lead to inconsistent results.
Why Isn’t My RECEIVED Working?
If you encounter issues when using the RECEIVED formula, consider the following troubleshooting steps:
- Double-check the date format for the settlement and maturity dates. Ensure they are in a recognized format and are valid dates.
- Verify that the discount rate is entered as a decimal and not a percentage.
- Ensure that the day count basis, if specified, is a valid option (0, 1, 2, 3, or 4).
- Check for any errors in the formula syntax, such as missing or extra commas, parentheses, or quotation marks.
RECEIVED: Related Formulae
Here are some related Excel formulae that can be useful when working with investments and securities:
- DISC: Calculates the discount rate for a security.
- PRICEDISC: Calculates the price per $100 face value of a discounted security.
- YIELD: Calculates the yield of a security that pays periodic interest.
- COUPNUM: Calculates the number of coupons (interest payments) between the settlement date and maturity date of a security.
- ACCRINT: Calculates the accrued interest for a security that pays periodic interest.
By mastering the RECEIVED formula and its related functions, you can effectively analyze and compare various investment opportunities, helping you make informed financial decisions.
