How to Use the ACCRINTM Function in Google Sheets

ACCRINTM Function in Google Sheets

The ACCRINTM function in Google Sheets is designed specifically to calculate the interest that is paid at maturity or the lump sum interest when a security expires/matures.

Notice that the function’s name (ACCRINTM) is driven from “Accrued Interest Maturity”.

The key difference between the ACCRINTM and the ACCRINT function is that the ACCRINTM function calculates the accrued interest over the whole period of the deal, while the ACCRINT function calculates the accrued interest over the last period of the deal after the final periodic payment.

So, what’s accrued interest?

In simple form, accrued interest is the accumulated interest on a principal value over a time period. Let’s see an example to understand the concept more.

Say you decided to take a student loan to get through college, you took $10,000. The interest rate was 10%. What would be the accrued interest after half a year?

Well, here’s how it’s calculated.

Accrued Interest = Principal value x  x time period (in months)

So, Accrued Interest = $10,000 x   x 12 x 0.5 = $500

Which means that after six months you owe the bank $10,000 + $500 = $10,500

This is the idea of accrued interest maturity.

 

 

The Anatomy of the ACCRINTM Function in Google Sheets

The ACCRINTM function shall be written as follows:

=ACCRINTM(issue, settlement, rate, redemption, day count convention)

Let’s dissect this thing and understand what each of these terms means:

  • = (the equal sign) is just how we start any function in Google Sheets.
  • ACCRINTM is the name of the function we are using.
  • () These parentheses are used to host the two values we put in our 
  • function, and a comma “,” must separate these values.

Note that the values hosted in any google sheets function are called arguments.

  • (issue) 1st argument. The date the security was initially issued.
  • (maturity) 2nd argument. The maturity date of the security, the date after issuance when the security expires, ends or matures.
  • (rate) 3rd argument. The annualized rate of interest.
  • (redemption) 4th argument. The loaned or invested amount (the original value to be redeemed).
  • (day count convention) 5th and final argument. An indicator of what day count method to use.  

Know that the final argument is an optional argument, that takes from 0 to 4, and its default value is 0; 0 for 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, or 4 for 30/360 European.      

 

Notice that if any of the first 4 arguments weren’t specified correctly, the function would result in an error.

So, before we go deeper in to the function, there are a few things to be aware of in the ACCRINTM function, otherwise you will get an error:

  • The issue date must be less than or equal to the settlement date (issue date ≤ settlement date).
  • The day count conversion (7th argument) must be from 0 to 4 inclusive.

Now for further explanation, let’s go through the ACCRINTM function together, and you will understand it once you start practicing its application.

 

 

A Real Example of Using ACCRINTM Function

Take a look at the example below to see how the ACCRINTM function is used in Google Sheets.

  • Issue date = 1-Jan-2018
  • Maturity date = 1-Jul-2021
  • Interest rate = 0.12
  • Redemption = $9000
  • Day count convention = 1

And that’s how we write these data in google sheets.ACCRINTM Function in Google Sheets

As you can see in the table, the ACCRINTM Formula row has the formula of the ACCRINTM function, and the ACCRINTM Result column has the formula’s result.

Now let’s look closely at the formula we have.

 

=ACCRINTM(B2, B3, B4, B5, B6)

Or

 

=ACCRINTM(1/1/2018, 7/1/2021, 0.12, 9000, 1)

 

You may make a copy of the spreadsheet using the link attached below.

 

Make a copy of example spreadsheet

 

 

How to Use the ACCRINTM Function in Google Sheets

  • Simply click on any cell to make it the active cell. For this guide, I will be selecting B7, where I want to show my formula. Then type ‘=’.
  • Now, type ‘ACC‘ and click on the ACCRINTM function or press the Down Arrow then Tab to select it.Selecting ACCRINTM

Notice that there are two options to select from, we are only concerned with the ACCRINTM function now, so don’t confuse it with the other one that doesn’t end with an “M”.

 

  • Now, fill in the first argument of the function; issue, which would be the value in cell B2. So, you can select it or type ‘B2‘.ACCRINTM Function in Google Sheets
  • Then, type comma ‘,‘.
  • Now go ahead and select the second argument; maturity, which is the value in B3.Select Second Argument
  • Then, type comma ‘,‘ and select the third argument; rate, which would be the date in cell B4.Select Third Argument
  • Type comma ‘,‘ and select the forth argument; redemption, which would be the date in cell B5.Select 4th Argument
  • Now after you type another comma, put in the fifth argument; [day_count_convention], which would be in B6.ACCRINTM Function in Google Sheets
  • Finally, close the parenthesis, press Enter and your result will take over.

 

Now before we end this session, let’s take a look at some of the key differences between the two sibling functions ACCRINT and ACCRINTM.

  1. The ACCRINTM function in google sheets takes five arguments instead of the seven arguments that the ACCRINT function takes.
  2. One of the key arguments that differentiates between the two functions, is the frequency function, which is the 6th argument in the ACCRINT function. This argument breaks down the whole period of the agreement into smaller periods, where each of these periods represents a time of payment.
  3. The absence of the frequency argument in the ACCRINTM function, means that the payment is accumulated over the whole period. So, there’s no periodical payments in between the issue date and the maturity date.

 

That’s pretty much it. Congrats! You now learned the ACCRINTM function in Google Sheets.

So, are you eager to learn some more amazing Google Sheets functions? Well, you are in luck, as you are a single click away from your destination.

 

Get emails from us about Google Sheets.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like