RECEIVED Excel Formula

Author Zaheer    Category Formulas     Tags ,

Excel spreadsheet program is widely used for its user friendly interface and useful features. This spreadsheet program is powered with various built-in functions. Among many of them RECEIVED is a function which is used to return the amount that will be received at the maturity for the security which is invested fully. In some versions of the Excel program, this function may not be available and may return an error. In such cases like many other functions, you will have to install and load an add-in Analysis ToolPak. To know more how to install the add-in you can visit the Microsoft site.

The syntax of the RECEIVED function is as follows “RECEIVED (settlement,maturity,investment,discount,basis)”. Here, settlement denotes the settlement date of the security. It is the date when a buyer buys the security; it is after the issue date. Maturity denotes the maturity date of the security. It is the date when the security will expire. Investment denotes the amount which is invested in that security. Discount denotes the discount rate of the security. Basis denotes the day count basis type to use. Its value can be 0 to 4. If it is 0, then the day count basis will be US (NASD) 30 / 360. If it is 1, then the day count basis will be Actual / actual. If it is 2, then the day count basis will be Actual / 360. If it is 3, then the day count basis will be Actual / 365. If it is 4, then the day count basis will be European 30 / 360.

The following things should be remembered when this function will be applied in a database. The dates are stored as serial number in Microsoft Excel. As for example, the date 1st January 1900 is the serial number 1; the date 1st January 2008 is the serial number 39448. In Macintosh different system is used. An example can be given here to make the reader understand the dates.  Assume that a bond was issued on the date of 1 January 2008; it is a 30 years bond. The buyer purchased the bond after six months of the issue date. Here, the issue date is the 1st January 2008; the settlement date is the 1st July 2008; and the maturity date is the date after 30 years of the issue date 1st January 2008 – that will be 1st January 2038. Basis, maturity, and settlement date are rounded to integers.

If the maturity date and the settlement date is invalid date, then the function will return an error value. If discount is less than or equal to 0, or investment is less than or equal to 0, the RECEIVED function will return an error value. If basis is less than 0 or greater than 4, it will also return an error value. If maturity is less than or equal to settlement, then the function will also return an error value. To learn more about the function you can check the help files of the Excel program.

Post comment