Thursday, 8 November 2018

ACCRINTM Finance Function in Google Sheet

What is ACCRINTM Function Google Sheet?

ACCRINTM Finance Function


ACCRINTM is a Financial Function in Google Sheet. This function is used to calculate the accrued interest of a  security which pays the interest at the time of maturity of the security.


Securities pay interest periodically or at the time of maturity. ACCRINTM Finance function calculates accrued interest of a security, paying interest on maturity.

Business Analytics from the Wharton School

We have two type of methods with regards to the calculation of accrued interest. 
   
    2. ACCRINTM Finance Function

ACCRINT Finance function calculates the accrued interest of a security that paying interest on a periodic basis.

ACCRINTM Finance Function calculate the accrued interest of a  security which pays the interest at the time of maturity of the security.

Syntax

ACCRINTM(issue, maturity, rate, [redemption], [day_count_convention])

Compulsory Parameters

Issue: It is the date of issue of the security
Maturity: It is indicating the maturity date of a security.
Rate: The annual rate of return

Optional Parameters

Redemption: Indicates the redemption value of the security
DayCount 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.

How to Use ACCRINTM Finance Function in Google sheet


We can consider an example to have a clear understanding of the use of the ACCRINTM Function.

Suppose the issue date of the security is 01-01-2018 and the maturity or settlement date is 31-12-2018. We can find the ACCRINTM value if the rate of interest is 5 %, the redemption of $ 1000 and day count convention as European 30/360.

Let us present the data in a table and apply the equation


ACCRINTM Finance Function



Once we apply the equation as 

=ACCRINTM(C2,C3,C4,C5,C6)

We will get the result as $ 49.86.

Important Points


Issue Date and Maturity Date in the formula need to be entered as Date function or other Date parsing function . Id you try to enter it as text, it may result in error.

Errors

Two type of errors may return when we use the parameters in the formula inappropriately. They are #VALUE !error and #NUM! error.

1. #VALUE! error

This formula accepts only numeric values for its parameters and the Date need to be inputted by using a DATE Function or any other Date parsing Function. When we violate these rules, it may return a value as #VALUE! error

2. #NUM! error

ACCRINTM Finance Function may return this error if

  • we provide a value other than 0,1,2,3 or 4 to Day Count Convention,
  • The Issue Date and Maturity Date are illogical. It means the Issue Date come first then the Maturity Date.
  • Inputting the Rate and Redemption values ≤ 0.




Recommended for Further Reading 

ACCRINT Finance Function in Google Sheet


Qatar Airways