EXCEL FUNCTIONS-ACCRINTM

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.

BasisDay count basis
0 or omittedUS (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European
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. 

EXCEL:ACCRINTM:EXAMPLE

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.