The ACCRINT Function is an Excel financial function. The function will calculate the accrued interest for a security that pays interest on a periodic basis. ACCRINT helps users calculate the accrued interest on a security, such as a bond, when that security is sold or is transferred to a new owner on a date other than the issue date or on a date that is an interest payment date.
Syntax:= ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
The ACCRINT function syntax has the following arguments:
- Issue Required. The security’s issue date.
- First_interest Required. The security’s first interest date.
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Rate Required. The security’s annual coupon rate.
- Par Required. The security’s par value. If you omit par, ACCRINT uses $1,000.
- Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis Optional. The type of day count basis to use.
Basis Day count basis 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
- Calc_method Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement. If you do not enter the argument, it defaults to TRUE.
Example: Let’s look at some Excel ACCRINT function examples and explore how to use the ACCRINT function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following ACCRINT examples would return:
- #NUM! error – Occurs when:
- The given rate argument is ≤ 0 or the provided [par] argument is ≤ 0.
- The given frequency argument is not equal to 1, 2, or 4.
- We provided issue ≥ settlement.
- The given basis argument is not equal to 0, 1, 2, 3, or 4.
- #VALUE! error – Occurs when:
- The given issue, first_interest, or settlement arguments are not valid dates.
- Any of the arguments provided is non-numeric.
- When we input the issue and settlement dates, they should be entered as either:
- References to cells that contain dates; or
- Dates that are returned from formulas; or
- If we attempt to input these date arguments as text, Excel may incorrectly interpret them, due to different date systems or date interpretation settings.
- ACCRINT is calculated as follows: where:
- Ai = number of accrued days for the ith quasi-coupon period within odd period.
- NC = number of quasi-coupon periods that fit in odd period. If this number contains a fraction, raise it to the next whole number.
NLi = normal length in days of the quasi-coupon period within odd period.