How to use ACCRINT Function in Excel

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:

Syntax:  =ACCRINT(B1,B2,B3,B4,B5,B6,B7)

Result:

Based on the Excel spreadsheet above, the following ACCRINT examples would return:

Note:

1. #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.

2. #VALUE! error – Occurs when:
   • The given issue, first_interest, or settlement arguments are not valid dates.
   • Any of the arguments provided is non-numeric.

3. 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.
4. 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.