ACCRINT Formula is a function of Excel used for the purpose of totalling the returns of accrued interest over a security in periodic interest.
Basically, this formula is used at times, when a person purchases a bond or makes an investment in something which gives him or her interest in the near future. So, to total the returns of the same, ACCRINT Function of Excel is used.
To understand how the following function works, lets study an example for the same.
In the above image, you can notice that we have taken the Issue date, first interest date, settlement date, annual rate, per value and we are expecting the total result i.e. the returns of the accrued interest from it.
- The issue date can be defined as the date when you issued the bond, or when you purchased the share.
- First interest date is the date when you will get the interest on the same bond or share for the first time.
- Settlement Date is the date when the amount is settled.
- Annual Rate is the interest rate provided by the company on your share or bond.
- Per Value is the value on which the rate is given.
Step1: Input the Formula
The formula for the following is:
All the things required in the formula are explained above except for:
- Frequency: Frequency can be explained by numbers, i.e. 1, 2, 4, etc.
Here 1 means the whole year,
2 is used for the purpose of half-yearly or in other words 6 months + 6months.
And 4 is used as a quarterly.
- Basis is basically the difference between date, you will understand the same when we will use the formula later.
- Calculation method has two options, i.e. true and false.
Step2: Input the values in the formula
The first requirement of the formula is the issue date, so we will select the cell which has our issue date, i.e. A2.
*do not forget to put coma after every value in the formula, to separate the values and bring meaning to the same.
Then the formula requires the first interest date, i.e. the cell C2 in the present example.
After the first interest date comes the settlement date, i.e. when the amount was settled for your bond/ share. In the following sheet, our settlement date is 01-05-2019, i.e. cell E2.
Then comes the annual rate, the rate at which we are getting our interest on.
After the annual rate, you need to jot down the “par value” i.e. cell I2 in the example.
After putting all these entries and putting a comma in the end, the following appears on the screen related to frequency:
Now let’s say, I want it to be quarterly and so I write 4 in the same.
After putting a coma, we come to the point of basic.
As you can observe in the above image, it has a drop-down box with 5 options.
People often go for 1- Actual/actual, this option means that the following dates that you have jotted down are moving as per the exact dates in India.
You can opt for the option as per your requirement. Every option can explain itself. Like the option 0 is on the basis of US, option 2 and 3 are on the basis for if you are calculating your interest by taking the following number of days in a year, and option 4 which is based on the European format.
So, after going for the option 1, i.e. Actual/actual, we come to the point of calculation method.
All you have to write is TRUE (in full caps) or you can choose the option if a drop-down box appears on your screen.
Then press “Enter”.
TADAAAA! Your answer is 1500.
It’s easy, isn’t it?