Excel ACCRINT Function

ACCRINT is the Excel function that calculates the interest accrued on a bond between two coupon dates. ACCRINT calculates accrued interest by multiplying the coupon rate with the face value of the bond and the number of days between the issue date or the last coupon date and the settlement date and dividing the resulting figure by the total days in a coupon payment.

The periodic coupon payment we get on a bond equals the face value of the bond multiplied by the coupon rate divided by the number of coupon payments per year (i.e. frequency). This is expressed by the following equation:

$$ \text{Full Coupon Payment} = \\ \text{Face Value}\ \times\ \text{Coupon Rate}\ \times\ \frac{\text{1}}{\text{Coupon Frequency}} $$

Since bonds have a secondary market where bond transactions occur regardless of the coupon date, we have to determine the accrued interest at a date that falls between two coupon dates because this is the amount that the buyer of a bond shall pay to the bond-holder in addition to the price of the bond because it has accrued to the seller.

$$ \text{Partial Coupon Payment} = \\ \text{Face Value}\ \times\ \text{Coupon Rate}\ \times\ \frac{\text{1}}{\text{Coupon Frequency}}\times\frac{\text{A}}{\text{B}} $$

A refers to the number of days between the last coupon date and the date till which we want to calculate accrued interest while B stands for total number of days in a year.

The price of a bond inclusive of the accrued interest is called the dirty price and the price of the bond exclusive of the accrued interest is called the clean price.

There is an Excel function called ACCRINT (stands for accrued interest) that calculates accrued interest on a bond since its issue date.


ACCRINT has the following syntax:

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

Issue stands for the date of issue of the bond, first_interest is the date of the first interest payment, settlement is the date on which the bond is sold or purchased, rate is the annual coupon rate, par is the face value of the bond; [basis] specifies the day-counting method. [calc_method] is an optional argument that specifies whether we want to calculate the accrued interest from the issue date (TRUE, which is also the default value) or we want to calculate accrued interest from the last coupon payment date (in which case it is FALSE).

The function is such that it always calculates the accumulated accrued interest i.e. interest since the issue date. However, to calculate the accrued interest since the last coupon payment, we need to specify issue, first_interest and settlement arguments to the last coupon date.


The following screenshot shows how we can use the ACCRINT function:

Excel ACCRINT Function Screenshot

You can see even though we entered FALSE in the [calc_method] argument, the formula calculated accrued interest since issue date. We had to set the issue and first_interest arguments to last coupon date to calculate the accrued interest since last coupon date.

by Obaidullah Jan, ACA, CFA and last modified on
Studying for CFA® Program? Access notes and question bank for CFA® Level 1 authored by me at AlphaBetaPrep.com

XPLAIND.com is a free educational website; of students, by students, and for students. You are welcome to learn a range of topics from accounting, economics, finance and more. We hope you like the work that has been done, and if you have any suggestions, your feedback is highly valuable. Let's connect!

Copyright © 2010-2020 XPLAIND.com