Using the ACCRINT Function in Excel

ACCRINT Function in Excel

 

 
Now we are going to learn all about formulas and functions. This is the best way to learn them:
 

 

ACCRINT

Returns the accrued interest for a security that pays periodic interest.

 

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.

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • Issue; first_interest; settlement; frequency; and basis are truncated to integers.
  • If issue; first_interest; or settlement is not a valid date; ACCRINT returns the #VALUE! error value.
  • If rate = 0 or if par = 0; ACCRINT returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; ACCRINT returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ACCRINT returns the #NUM! error value.
  • If issue = settlement; ACCRINT returns the #NUM! error value.
  • ACCRINT is calculated as follows:

    • 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 ith quasi-coupon period within odd period.

 

Interactive Example

Change the data in the non-colored cells and see how the result changes in the yellow cells.