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:

ACCRINT Function - How to use ACCRINT Function in Excel

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

Result:

ACCRINT Function in Excel 1 - How to use ACCRINT Function in Excel

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

Syntax: =ACCRINT(DATE(2008,3,5),B2,B3,B4,B5,B6,B7,FALSE)
Result: 311.1111111

Syntax: =ACCRINT(DATE(2008, 4, 5), B2, B3, B4, B5, B6, B7, TRUE)
Result: 144.4444444

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.
  1. #VALUE! error – Occurs when:
    • The given issue, first_interest, or settlement arguments are not valid dates.
    • Any of the arguments provided is non-numeric.
  1. 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.
  2. ACCRINT is calculated as follows: ACCRINT Function in Excel - How to use ACCRINT Function in Excelwhere:
    • 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.

Add a Comment

Your email address will not be published. Required fields are marked *