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:
Syntax: =ACCRINTM(B1,B2,B3,B4,B5)
Result:
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.
