EXCEL FUNCTIONS-ACCRINTM
ACCRINTM function is given in EXCEL for financial purpose.
ACCRUED INTEREST means the interest which is accrued with time.It calculates the interest of those securities which give returns on maturity.
PURPOSE OF ACCRINTM IN EXCEL
ACCRINTM FUNCTION CALCULATES THE ACCRUED INTEREST OF THE SECURITIES AT THE TIME OF MATURITY.
It means a bond kind of security paying out interest at the maturity.
PREREQUISITES TO LEARN ACCRINTM
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?
SYNTAX: ACCRINTM
The syntax ( the way how formula is phrased for excel) of ACCRINTM is
=ACCRINTM(ISSUING DATE, SETTLEMENT DATE, COUPON RATE , PAR VALUE , BASIS )
ISSUING DATE is the security’s issue date.
SETTLEMENT DATE is the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
ALL TWO DATES MUST BE ENTERED THROUGH THE DATE FUNCTION.
=DATE(YYYY,MM,DD) OTHERWISE ERROR WILL EMERGE.
COUPON RATE is the security’s annual coupon rate. Its just like INTEREST RATE.
PAR VALUE is the security’s par value. If we don’t use Par value,ACCRINT uses 1,000.
Basis is 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 |
BASIS VALUE CAN BE BETWEEN 0 TO 4 AS PER THE TABLE OTHERWISE ERROR WILL BE SHOWN.
EXAMPLE :ACCRINTM IN EXCEL
DATA SAMPLE
A Security is issued on date 1.1.2020 . The settlement date is 30.06.2020Coupon rate is 10%.Par value is 1000 (any currency)BASIS is 0 Let us find out the accrued interest using ACCRINTM FUNCTION.
STEPS TO USE ACCRINTM
THE DATA SAMPLE IS ALREADY GIVEN.
The data is put as per the picture from cell F7 TO F12
We put in the cell G12 the formula to get interest
=ACCRINTM(G7,G8,G10,G9,G11)
ONE thing to be noticed here is that its almost a simple interest calculation. The extra luxury here is that we need not calculate the days and directly dates can be entered.
CONFUSION CLARIFICATIONS
DATE INPUT ISSUE IN ACCRINTM
MICROSOFT EXCEL counts the number of days right from 1900, JAN 1 and converts the date entered by us to that number and then does the calculations and convert it back to our format.
In ACCRINT AND ACCRINTM both, its necessary to add DATES with DATE function only so that excel is able to do that conversion to calculate the number of days, as its important for the calculation of interest.
