How to use ACCRINTM Function in Excel

The Excel ACCRINTM function returns the accrued interest for a security that pays interest at maturity (i.e. pays interest one time only)

Syntax:= ACCRINTM (id, sd, rate, par, [basis])

The ACCRINTM function syntax has the following arguments:

  • Issue    Required. The security’s issue date.
  • Settlement    Required. The security’s maturity date.
  • Rate    Required. The security’s annual coupon rate.
  • Par    Required. The security’s par value. If you omit par, ACCRINTM uses $1,000.
  • 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

Example: Let’s look at some Excel ACCRINTM function examples and explore how to use the ACCRINTM function as a worksheet function in Microsoft Excel:

ACCRINTM Function - How to use ACCRINTM Function in Excel

Syntax:  =ACCRINTM(B1,B2,B3,B4,B5)

Result:

ACCRINTM Function in Excel - How to use ACCRINTM Function in Excel

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

Syntax: =ACCRINTM(DATE(2019,1,1),DATE(2020,2,15),0.068,20000,3)
Result: 1527.671233

Syntax: =ACCRINTM(DATE(2019,1,1),DATE(2020,2,15),6.8%,20000,3)
Result: 1527.671233

Syntax: =ACCRINTM(DATE(2019,1,1),DATE(2020,2,15),6.8%,1000,3)
Result: 76.38356164

Syntax: =ACCRINTM(DATE(2019,1,1),DATE(2020,2,15),6.8%,,3)
Result: 76.38356164

Note:

  • In Excel, dates are serial numbers.
  • All dates, and basis, are truncated to integers.
  • If dates are invalid (i.e. not actually dates) ACCRINTM returns #VALUE!
  • ACCRINTM returns #NUM when:
    • issue date >= settlement date
    • rate < 0 or par <= 0
    • Basis is out-of-range
  • ACCRINTM is calculated as follows:

    ACCRINTM = par x rate x A/D

    where:

    • A = Number of accrued days counted according to a monthly basis. For interest at maturity items, the number of days from the issue date to the maturity date is used.
    • D = Annual Year Basis.

Add a Comment

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