ACCRINT Function Examples in Excel, VBA, & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to use the Excel ACCRINT Function in Excel to calculate the accrued interest for a security.
ACCRINT Function Overview
The ACCRINT Function Calculates the accrued interest.
To use the ACCRINT Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
ACCRINT Function Syntax and Inputs:
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])issue – It’s the issue date of the security or a bond.
first_interest – It’s the date at which the first interest is paid.
settlement – It’s the settlement date of the security or the date at which the security is purchased. It’s the date that comes after the issuing date of the security.
rate – It’s the annual interest rate of the bond or security at which the coupon payments are processed.
par – It’s the annual interest rate of the bond or security at which the coupon payments are processed.
frequency – It refers to the number of periodic coupon payments per year. The value of frequency for annual, semi-annual, and quarterly payments are 1, 2, and 4, respectively.
basis – OPTIONAL: It specifies the type of day counting to be used by the security or bond. Possible values can be:
| Basis | Day Count |
| 0 | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Acutal/360 |
| 3 | Acutal/365 |
| 4 | European 30/360 |
If the basis argument is omitted, it assumes its default value i.e. US (NASD) 30/360.
calc_method – OPTIONAL: The [calc_method] argument’s value specifies a way to calculate the accrued interest when the settlement date is greater than the date of first_interest. Its value is either TRUE/1 (calculates the total accrued interest from issue to settlement) or FALSE /0 (calculates the accrued interest from first_interest to settlement). Its default value is 1(TRUE).
What is the Excel ACCRINT Function?
The Excel ACCRINT function calculates the interest accrued for a security that pays interest periodically.
The Excel ACCRINT function uses the following formula for the calculation of accrued interest.
Where:
NC = number of quasi-coupon periods that fit in the odd period (Fractions are raid to the next whole number).
Ai = number of the accrued days for the ith quasi-coupon period within the odd period.
NLi = normal length in days of the quasi-coupon period within the odd period.
Calculate Accrued Interest on US Treasury Bond
Let’s calculate how much interest has accrued on a US Treasury bond between June 1st, 2015, and March 3rd, 2016. The bond has a par value of $1,000. Other details of the bond are mentioned in the above figure.
The Formula to use is:
=ACCRINT(C4,C5,C6,C7,C8,C9,C10,C11)
The ACCRINT function returns the interest on the US Treasury bond:
ACCRINT = $49.11
Accrued Interest on a simple bond
Now let’s calculate the accrued interest on a bond with a coupon rate of 8%. The bond has a par value of $10,000. Other details of the bond are mentioned above in the table.
The formula to use is:
=ACCRINT(C4,C5,C6,C7,C8,C9,C10,C11)
The above function gives the result:
ACCRINT = $133.33
Additional Notes
#NUM! Error occurs if rate ≤ 0, par ≤ 0, or issue ≥ settlement.
#NUM! Error also occurs when the [basis] argument value is not 1, 2, 3, or 4, or the value of frequency argument is other than 1, 2, or 4.
#VALUE! Error occurs if either the issue, first_interest, or settlement dates are not valid Excel dates, or any of the argument value is non-numeric.
It is recommended that the settlement and maturity dates should be entered in the YIELD function as references to cells containing the dates or dates returned from formulas.
Return to the List of all Functions in Excel
ACCRINT in Google Sheets
All of the above examples work exactly the same in Google Sheets as in Excel.
ACCRINT Examples in VBA
You can also use the ACCRINT function in VBA. Type:
application.worksheetfunction.accrint(Issue,FirstIneterst,Settlement,Rate,Par,Frequency,Basis,CalcMethod)For the function arguments (Rate, etc.), you can either enter them directly into the function or define variables to use instead.
