Related Function:
ACCRINTMThe Excel Accrint function returns the accrued interest for a security that pays periodic interest.
The syntax of the function is:
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:
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page |
||||||||||||
| [calc_method] | - |
An optional logical value that specifies the way to calculate the total accrued interest when the settlement date is later than the first_interest date. This can have the value TRUE or FALSE, meaning:
If omitted, [calc_method] takes the default value of TRUE. |
Warning: 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 to the Accrint function as either:
The following spreadsheet shows an example of the Excel Accrint function, 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 $1,600.
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 are provided on the Microsoft Office website.
If you get an error from the Excel Accrint function, this is likely to be one of the following:
| #NUM! | - |
Occurs if either:
|
| #VALUE! | - |
Occurs if either:
|