Thursday, 1 November 2018

ACCRINT Finance Function in Google Sheet

ACCRINT Finance Function in Google Sheet calculates the accrued interest of a security that paying interest on the periodic basis.

ACCRINT Finance Function in Google sheet and Excel are the same in concept and application.



ACCRINT Finance Function


What is Accrued Interest?

Suppose Mr A purchased a bond of $ 1000 from Mr B. on 1st April 2018. Thus a bond is paying 10% interest or coupon which is another name of interest payment of a bond. These coupon or interest may be payable in set intervals like annually, semi-annually etc.

In this example, the coupon pays on semi-annually and Mr A will receive the coupon or interest in June 2018, but Mr.B held the bond for 3 months from January till the purchase of Mr A in April 2018. 

As Mr B holds the bond for the 3 months, the accrued interest on the bond for the 3 months belongs to Mr.B.

So at the time of purchase of bond on 1st April 2018, Mr A has to pay the accrued interest to Mr B.

So Accrued Interest is the interest on bond or security and those interest is accumulated and accrued since the purchase or previous coupon payment.


Syntax



ACCRINT(issue, first_payment, settlement, rate, redemption, frequency, [day_count_convention])


Compulsory Arguments

Issue: It is the issue date of the security.

First Payment: It represents the first interest date of the bond or security.

Settlement: It is the settlement date of the security.  It is the date after the issue date when the security is delivered to the buyer.

Rate: It is the annual rate of return.

Redemption: It denotes the par value of the security. The reduction amount per $100 face value or par.

Frequency: It denotes the number of coupons or interest payments per year. It may be 1 if the coupon payment is annual, 2 for semi-annual payments and 4 for quarterly payments.

Optional Argument

Day Count Convention: This integer value indicated the method of the financial day counting 

0 - indicates that 30 days in the month and 360 days in a year. There are some specific adjustments to the dates entered at the end of the months. This may be the default value - US (NASD) 30/360

1- This is another day count convention. This calculates based on the actual number of days between the specified dates, and the number of days in the intervening years. This indicates Actual/Actual

2- indicates that the calculation is based on the actual number of days between specified dates, assuming the 360 days a year. This indicates Actual/360

3- indicates that the calculation is based on the actual number of days between specified dates, assuming the 365 days a year. This indicates Actual/365

4- indicates European 30/360, similar to the first option (0). This calculates as per 30-day month and a 360-day year, but it adjusts month-end dates as per European financial conventions.

Application of ACCRINT Finance Function in Google sheet

To familiar with the application of the ACCRINT finance function in google sheet, we need to consider an example.

We can prepare a table with the above example.

ACCRINT Finance Function


We applied the equation,

=ACCRINT(C2,C3,C4,C5,C6,C7,C12,)

and we received the value for ACCRINT as 25. It means that  $25 is the accrued interest for the bond held for 3 months.

If we are using the Settlement Date as Maturity Date, in that case, you can calculate the total interest on maturity as shown below.

ACCRINT Finance Function

Here, we got the ACCRINT as $500 as we considered the Settlement Date as Maturity Date and the holder of the bond will receive this amount as maturity interest.

Important Points

When entering the Date in the cells, we need to use the DATE or TO_DATE or any other Date parsing function. If you are entering text as dates, the formula may return errors.

Click here to download the Google sheet for ACCRINT Finance Function


Hope you understand the formula syntax of ACCRINT Finance Function in Google sheet and its practical application with the example. Please share it with your friends, if you find it useful.

No comments:

Post a Comment