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.
Syntax
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.
Example
The following screenshot shows how we can use the ACCRINT function:
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