Browsing all articles tagged with Financial Functions

ACCRINT Excel Formula

Microsoft Excel has lots of built-in functions that are very useful in all businesses and in almost all fields. This spreadsheet program features functions that can be used in businesses especially when it comes to financial and accounting matters. The financial functions in Excel are the so-called ACCRINT function. Let’s begin by learning about this function by knowing its description and working with some examples that can enable you to learn about the usage of the ACCRINT function.

Description of Excel’s ACCRINT Function

The ACCRINT function in Microsoft Excel is useful in calculating the added interest for a security that pays periodic interest. The formula of the ACCRINT function is:

ACCRINT(issue, first_interest, settlement, rate, [par], frequency, [basis])

The Arguments in this Formula are as follows:

  • Issue – The date of issuance of the security.
  • First_interest – The first interest date of the security
  • Settlement – The date of settlement of the security
  • Rate – The yearly coupon rate of the security
  • [par] – The par value of the security. If this argument is omitted, par uses the default value which is $1000.
  • [basis] – This is an optional argument. It specifies the basis of day count that will be used in the computation. The following are the possible values of this argument and their meanings:
  • (or omitted)  -  US (NASD) 30/360
  •   actual/actual
  •  actual/360
  •  actual/365
  •   European 30/360

Note: the settlement, first interest and issue dates can be supplied as either of the following

  • Dates that are returned from other functions or formulas
  • Cell reference which contains the dates

If these dates are supplied as text, there is a possibility that Excel may not understand this because of various settings of date interpretation and date systems.

Even though these dates can also be supplied as serial numbers, this is not advisable due to the fact that serial numbers may differ across various computer systems.

For a better understanding about the usage of the ACCRINT function, here’s an example which you can try in your own Excel spreadsheet.

For instance, the date of settlement is 01-Apr-10, the date of first interest is 30-Jun-10 and the date of issuance of the security is 01-Jan-10. The yearly coupon rte is 8%. The par value of the security is $10,000, payments are made quarterly, and the day count basis that is used is a US (NASD) 30/360.

If you will use the ACCRINT function formula and input all the values that were given above, the function will return the result which is $202.222. You can try this example in your own blank spreadsheet for practical applications.

Easy Steps on How to Use the ACCRINT Function

  • If Excel returns the #NAME? value, you should enable first the Analysis Toolpak. From the Tools menu, select add-ins. Click on the checkbox adjacent to Analysis Toolpak and then click the OK button.
  • Learn the formula of the ACCRINT function that was given above.
  • Supply the arguments in the formula. It is better to use the dates that are returned from other functions or formulas to avoid the problems that may take place when supplying a date as text.
  • Indicate the basis argument if required. If the basis argument is omitted, it takes the default value of 0 which is equivalent to US (NASD) 30/360.
  • Finally, you can try the example that was given earlier so that you can learn more about the usage of the ACCRINT function.