What Is ACCRINT Function in Google Sheets?
The ACCRINT function in Google Sheets determines the accrued interest for a security that pays periodic interest. In doing so, it considers details such as issue date, settlement date, interest rate, first payment date, frequency of payments, redemption value, and day count convention. The accrued interest on a security is important for accurate accounting as it is the interest that has accumulated on the bond since the last payment date but has not been paid yet. This is where we use the ACCRINT function to make such calculations simple.
ACCRINT in Google Sheets is often used in financial analysis to determine the interest amount on a security that has been earned but not yet paid. The result of the function is the accrued interest at the settlement date. We see a simple example below where the accrued interest is calculated for a security. Here, the issue date is January 1, 2024. The first interest date is July 1, 2024, and the settlement date is March 1, 2025. Annual coupon rate is 5% and par value is $1,000 with semi-annual payments.
=ACCRINT(DATE(2024, 1, 1), DATE(2024, 7, 1), DATE(2025, 3, 1), 0.05, 1000, 2)
Key Takeaways
- ACCRINT in Google Sheets calculates the accrued interest for a security that pays periodic interest.
- The syntax of the function is as follows:
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]).
- It is very helpful as it helps investors gage the value of their bond investments.
- The function requires various inputs including like the issue date, coupon rate, payment frequency, and day count basis for accurate computation.
- When using ACCRINT, we must use the DATE function to convert a date in a text format into acceptable input for calculations.
Syntax
The ACCRINT in Google Sheets formula requires several parameters. Let us look at its syntax below. It has six mandatory and one optional parameter:
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis])
Note that all the dates should be entered using the DATE function, to be in the correct date format.
Let us look at each of the arguments.
- Issue (mandatory) – The security’s issue date.
- First_interest (mandatory) – The security’s first interest date.
- Settlement (mandatory) – The security’s settlement date.
- Rate (mandatory) – The security’s annual coupon rate.
- Par (mandatory) – The security’s par value.
- Frequency (mandatory) – The number of coupon payments per year.
As seen with other financial functions,
| Annual payments, | Frequency = 1 |
| Semi-annual | Frequency = 2 |
| Quarterly | Frequency = 4 |
Basis (optional.) – The type of day count basis to use. If omitted, the default is 0, meaning US (NASD) 30/360.
How To Use ACCRINT Function in Google Sheets?
The ACCRINT function in Google Sheets is one of the most important financial functions. We learn how to use ACCRINT in Google Sheets in two ways. First, it can be entered manually or selected from the Google Menu bar.
Entering the ACCRINT Function Manually
Below are the details
Step 1: Enter the details in Google Sheets as shown below.
We have the following bond details:
- Issue date: January 1, 2023
- First interest date: July 1, 2023
- Settlement date: May 1, 2025
- Rate: 6% annual coupon rate
- Par value: $2,000
- Frequency: 2 (semi-annual payments)
Step 2: To find the accrued interest, enter the formula as follows beginning with an = sign followed by ACCRINT and open braces.
Enter the parameters one by one or enter their cell references.
Close the parentheses.
=ACCRINT(DATE(2023, 1, 1), DATE(2023, 7, 1), DATE(2025, 5, 1), 0.06, 2000, 2)
We use the DATE function in case the date is entered as a text as it should be the proper date format.
Otherwise, you can enter 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
Step 1: Choose an empty cell to get the output.
Step 2: Go to the Insert tab -> Function -> Financial.
Step 3: You get all the functions under this category. Select the ACCRINT function, enter the relevant arguments, close the braces and press Enter.
Examples
The ACCRINT 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 on a Bond
In this example, we will show you how using a DATE function in ACCRINT is very helpful to ensure the date is in the right format. Here, we want to calculate the accrued interest for a bond with the following details.
Issue Date: March 1, 2017
First Interest Date: August 31, 2018
Settlement Date: May 1, 2018
Annual Coupon Rate: 8%
Par Value: $1000
Frequency of Payments: 2 (which means semi-annual)
Step 1: Enter all the details in a Google Sheets as shown below.
Step 2: Enter the following formula in cell B7.
Formula: =ACCRINT(B1, B2, B3, B4, B5, B6).
Press Enter.
The formula will return the accrued interest from the issue date to the settlement date. Here, the use of the DATE function ensured that the dates were in the proper format for calculations.
Example #2 – Calculations with Optional Arguments
This example is similar to the previous example, but we wish to calculate the accrued interest from the first interest date to the settlement date.
Step 1 : We enter the following formula in a cell for the same details as in Example 1.
=ACCRINT(DATE(2017,3,1), DATE(2018,8,31), DATE(2018,5,1), 0.08, 1000, 2, 0, FALSE)
Here, the 0 represents the US (NASD) 30/360 day count basis and FALSE calculates the accrued interest from the first interest date to the settlement date.
Press Enter.
The formula will return the accrued interest from the first interest date to the settlement date.
Example #3 – Using ACCRINT with SUM Function
One of the best uses of Google Sheets is the ability to combine functions to create complex calculations. In this example, we can combine two functions to calculate the total interest over three bonds. For this, we combine SUM with ACCRINT to sum up the accrued interest for multiple securities.
Step 1: Enter all the details in a sheet as shown below.
Step 2: Let us apply the ACCRINT function combined with the SUM as follows.
=SUM(ACCRINT(B2, C2, D2, E2, F2, G2), ACCRINT(B3, C3, D3, E3, F3, G3), ACCRINT(B4, C4, D4, E4, F4, G4))
Press Enter.
We get the sum of the accrued interest for multiple securities.
Important Things to Note
- The first three date arguments should be entered correctly as Google Sheets uses date serial numbers that ensure accurate calculations.
- The ACCRINT function calculates the interest that has accrued since the last interest payment date up until the settlement date.
Frequently Asked Questions (FAQs)
Let us look at some mistakes which can occur when using the ACCRINT function.
One of the issues, when we enter dates, is that they could be in a format that is not recognized in Google Sheets. We should ensure that all dates are in a consistent format either by using the DATE function or formatting the cells as Date.
Another issue is in giving the incorrect frequency of interest payments. Double-check the frequency parameter to ensure that we get the right value of accrued interest.
The basis parameter is optional but omitting it without can lead to unexpected results as it has a default setting of 0.
Commonly used frequencies include:
1. 1 – Annual payments where interest is paid once a year.
2. 2 – Semi-annual payments where interest is paid twice a year.
3. 4 – Quarterly payments where Interest is paid four times a year.
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.
Download Template
This article must help understand ACCRINT 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 ACCRINT Function in Google Sheets. We learn its syntax & how to use it to find the accrued interest with examples. You can learn more from the following articles. –
Leave a Reply