What Is ACCRINTM Function in Google Sheets?
ACCRINTM in Google Sheets calculates the accrued interest for securities that pay interest only at maturity while ACCRINT calculates accrued interest for securities with periodic interest payments in financial calculations. The ACCRINTM Function is a financial function in Google Sheets.
When one invests in a security, one receives interest payments that are paid at a single time or periodically. This function calculates the accrued interest for a security that pays interest on maturity.
Key Takeaways
- ACCRINTM in Google Sheets calculates the accrued interest for securities that pay interest only at maturity.
- The syntax of the function is as follows:
=ACCRINTM(issue, settlement, rate, par, [basis])
- It is very useful for estimating the interest earned before maturity on non-coupon-based investments.
- Avoid mistakes such as entering the rate as a whole number or reversing the issue and settlement dates when entering the parameters of the ACCRINTM function.
- ACCRINTM can be used to track simple-interest earnings on fixed-income investments that don’t pay periodic interest.
Syntax
The ACCRINTM function calculates the accrued interest based on the number of days from the settlement date to the maturity date. Let us look at the ACCRINTM in Google Sheets formula below.
=ACCRINTM(issue, settlement, rate, par, [basis])
Arguments:
issue (mandatory) – The security’s issue date. It will be truncated if not an integer.
settlement (mandatory) – The security’s maturity date when it expires
rate (mandatory) – the security’s annual coupon rate.
par (mandatory) – The security’s par value. The default value is $1,000 if omitted.
Basis (optional) – The day count that is used to calculate the interest on a given security. The default value is 0. Basis can be any of the following values:
Basis Day Count basis
0 US(NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360
Basis (optional.) – The type of day count basis to use. If omitted, the default is 0, meaning US (NASD) 30/360.
How To Use ACCRINTM Function in Google Sheets?
The ACCRINTM function in Google Sheets is one of the most important financial functions as it calculates the interest paid at maturity, or the interest in whole when a security expires/matures. We learn how to use ACCRINTM in Google Sheets in two ways. First, it can be entered manually or selected from the Google Menu bar.
Entering the ACCRINTM Function Manually
Below are the details of a security bond. Let us look at the steps below.
Step 1: Enter the details in Google Sheets as shown below.
We have the following bond details:
- Issue date: January 1, 2022
- Settlement date: May 1, 2024
- Rate: 5% annual coupon rate
- Par value: $1,000
- Frequency: 2 (semi-annual payments)
Step 2: To find the accrued interest, enter the formula beginning with an = sign followed by ACCRINTM and open braces.
Enter the parameters one by one or enter their cell references and close the parentheses.
=ACCRINT(DATE(2022, 1, 1), DATE(2024, 5, 1), 0.05, 1000, 2)
We use the DATE function in case the date is entered as a text as it should be the proper date format.
You can also use the formula with cell references as follows:
=ACCRINT(B1, B2, B3, B4, B5, B6).
Step 3: Press Enter. The function will return the accrued interest from the bond issue date until the settlement date.
Access From the Google Menu Bar
1: Choose an empty cell to get the output.
2: Go to the Insert tab -> Function -> Financial.
3: You get all the functions under this category. Select the ACCRINTM function, enter the relevant arguments, close the braces and press Enter.
Examples
The ACCRINTM function calculates the accrued interest on a security that pays periodic interest. Let us look at some examples of how to use the ACCRINT function.
Example #1 – Calculate Accrued Interest for a Zero-Coupon Bond Maturing in 6 Months
In this example, we will calculate the accrued interest for a zero-coupon bond using the ACCRINTM function in Google Sheets, for a bond that matures in 6 months.
The issue date isApril 1, 2025 and the maturity date is October 1, 2025, which is six months later. The annual interest rate is 6% and the par value is at $1,000 and the day count basis is 0.
Step 1: Enter the following details in a Google sheet.
Step 2: Enter the following details in a Google Sheet.
=ACCRINTM(DATE(2025,4,1), DATE(2025,10,1), 0.06, 1000, 0)
0 Press Enter. This will return $30.
It is since 6% annual interest on $1,000 for semi-annual is $30
Example #2 – Calculate the Exact Interest Earned on a Treasury Bill Before Maturity
In this example, we calculate the interest earned on a Treasury Bill before maturity. For this, we must find the difference between the face value and the purchase price. Then allocate that difference based on the time elapsed until maturity.
Before we proceed further, let us understand treasury bills. These are short-term debt securities issued by the government. They are sold at a discount to their face value, with the difference representing the investor’s return. The difference between the purchase price and the face value represents the investor’s return.
Step 1: The details are entered as shown below. It calculates the accrued interest for a security that pays interest just at maturity which is similar to a T-bill.
- Issue Date: 1/1/2024
- Settlement date: 4/1/2024 (to calculate accrued interest as of this date)
- Discount Rate: 5%
- Face Value: $800
- Basis: 1 (Actual/Actual)
Step 2: Apply the ACCRINTM function as follows. We use the DATE function for a valid date input.
=ACCRINTM(DATE(2024,1,1), DATE(2024,4,1), 0.05, 800, 1)
Step 3: Now, press Enter. You get the accrued interest value from January 1 to April 1, for a 5% annualized rate.
This gives you the interest earned up to the date before maturity as if the interest is accumulating over time.
Example #3 – Estimate the Interest Accumulation on a Bank Certificate of Deposit
To estimate the interest accumulation on a Bank Certificate of Deposit, we can use the ACCRINTM function in Google Sheets . All we must do is input the issue date, settlement date, interest rate, and par value or face value.
Step 1: Enter all the details in a sheet as shown below.
- Issue Date: 1/1/2024
- Settlement Date: 4/1/2024
- Annual Interest Rate: 6%
- Par Value (Face Value): $10,000
- Day Count Basis: 1 (Actual/Actual)
Step 2: Let us apply the ACCRINTM function as follows:
=ACCRINTM(DATE(2024,1,1), DATE(2024,4,1), 0.06, 10000, 1)
This is the interest accumulated on the CD or bank certificate of deposit from January 1 to April 1.
Important Things to Note
- The first three date arguments should be entered correctly as Google Sheets uses date serial numbers that ensure accurate calculations.
- It is helpful for any investment that pays interest at the end.
- ACCRINT in Google Sheets only shows the interest earned so far based on the time between the issue and settlement
Frequently Asked Questions (FAQs)
Let us look at some mistakes which can occur when using the ACCRINT function.
One of the issues, is that we should enter the percentage not as a whole number but a decimal format. For the interest rate argument, we enter 0.05 and not 5%.
If the issue or settlement dates aren’t valid, Google Sheets will return an error.
If the issue date comes after the settlement date, we get an error.
ACCRINTM assumes a simple interest paid at maturity. So if you need a compound interest, we use the FV.
ACCRINTM is used for investments that accrue interest but pay it at maturity only.
These include the following:
1. Zero-Coupon Bonds
2. Certificates of Deposit (CDs)
3. Treasury Bills (T-Bills)
4. Investments where interest is paid at maturity
Both the ACCRINT and ACCRINTM functions calculate accrued interest on a security, such as a bond. But they have slight differences.
1. ACCRINT calculates the accrued interest for a security that pays periodic interest.
It calculates the interest from the issue date to the settlement date as the interest is paid periodically.
2. ACCRINTM is used for a bond that pays interest only at maturity with no periodic payments.
It calculates the interest from the date of issue to the maturity date as there are no periodic payments.
Download Template
This article must help understand ACCRINTM Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is ACCRINTM Function in Google Sheets. We learn its syntax & how to use it to calculate the accrued interest with examples. You can learn more from the following articles. –
Leave a Reply