ExcelFunctions.net

Excel ACCRINT Function

Related Function:

Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Accrint-Function

Basic Description

The Excel Accrint function returns the accrued interest for a security that pays periodic interest.

The syntax of the function is :

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

Where the arguments are as follows:

issue - The issue date of the security
first_interest - The security's first interest date
settlement - The security's settlement date
rate - The security's annual coupon rate
[par] -

The security's par value

If omitted, [par] takes the default value of $1,000
frequency - The number of coupon payments per year (must be equal to 1, 2 or 4)
[basis] -

An optional argument, that specifies the day count basis to be used in the calculation.

Possible values of basis and their meanings are:
[basis] Day Count Basis
0 (or omitted) US (NASD) 30/360
1 actual/actual
2 actual/360
3 actual/365
4 European 30/360
The financial day count basis rules are explained in more detail on the Wikipedia Day Count Convention page

Note that Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the issue, first_interest and settlement dates should be input as either:

  • References to cells containing dates
    or
  • Dates returned from other functions or formulas

Accrint Function Examples

The following spreadsheet shows an example of the Excel Accrint function being used to calculate the accrued interest of a security that pays periodic interest. The security's issue date is 01-Jan-2012, the first interest date is 01-Apr-2012, the settlement date is 31-Dec-2013 and the annual coupon rate is 8%. The security's par value is $10,000, payments are made quarterly, and a US (NASD) 30/360 day count basis is used.

  A B C D
1 issue date: 01-Jan-2012   Accrued interest on a security with an issue date
of 01-Jan-2012, a first interest date of 01-Apr-2012
and a settlement date of 31-Dec-2013.
The annual coupon rate is 8%, the par value of
the security is $10,000 and payments are made
quarterly, on a US (NASD) 30 / 360 basis:
2 first interest date: 01-Apr-2012   =ACCRINT( B1, B2, B3, 8%, 10000, 4 )
3 settlement date: 31-Dec-2013    

The above function gives the result $1600

Note that, in the example above, the [basis] argument is omitted, and therefore takes the default value of 0 (US (NASD) 30/360 basis).


Further examples of the Excel Accrint function can be found on the Microsoft Office website.


Accrint Function Errors

If you get an error from the Excel Accrint function, this is likely to be one of the following:

Common Errors
#NUM! - Occurs if either:
-   the supplied rate argument is ≤ 0 or the supplied [par] argument is ≤ 0
or
-   the supplied frequency argument is not equal to 1, 2, 3 or 4
or
-   the supplied [basis] argument is not equal to 1, 2, 3 or 4
or
-   the supplied issue ≥ settlement
#VALUE! - Occurs if the supplied issue, first_interest, or settlement arguments are not valid dates
#NAME? - Occurs when Analysis ToolPak add-in is not enabled in your Excel

To enable this add-in in Excel 2003 :

  • From the Tools drop-down menu, select the option Add-Ins ...
  • An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK

To enable this add-in in Excel 2007 :

  • Click the Microsoft button on the top left of your spreadsheet and select the Excel Options button
  • From the menu on the left hand side, select Add-Ins
  • In the 'Manage:' box, select Excel Add-ins and click Go...
  • An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK