How to Calculate Accrued Interest Using the ACCRINTM Function in Google Sheets

Using the ACCRINTM function in Google Sheets, you can easily calculate the accrued interest or the interest accumulated for a security that pays interest only at maturity. In this tutorial, you will learn how to use this powerful financial function and avoid common errors.

ACCRINTM Function in Google Sheets: Syntax and Arguments

The syntax of the ACCRINTM function in Google Sheets is as follows:

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

The arguments for the ACCRINTM function are:

• issue: The issue date of the security.
• maturity: The date the security matures.
• rate: The annualized rate of interest.
• redemption: The par value of the security. If omitted, the function will use $1,000. (Optional)
• day_count_convention: An indicator of what day count method to use. The default is 0 (zero), which is the US (NASD) 30/360 convention. (Optional)

It’s important to note that the ACCRINT function (not ACCRINTM) should be used for securities with periodic payments, and it uses the settlement date and first coupon payment date instead of the maturity date.

How to Use the ACCRINTM Function in Google Sheets

Let’s take an example to understand how the ACCRINTM function works. Assume you borrowed $15,000 from a bank to finance one of your new projects. The loan has a 7-year term from January 1, 2015, to January 1, 2022, and an interest rate of 12%.

To calculate the interest that accrues on the loan balance, which is paid back to the bank when the loan matures, you can use the ACCRINTM function:

=ACCRINTM(DATE(2015, 1, 1), DATE(2022, 1, 1), 12%, 15000, 3)

In this case, the accrued interest would be $12,609.86.

You can also enter the input values within the sheet and refer to them in the ACCRINTM formula for better readability and easier maintenance:

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

Where:

• B1 is the cell reference of the cell containing the issue date.
• B2 is the cell reference of the cell containing the maturity date.
• B3 is the cell reference of the cell containing the interest rate.
• B4 is the cell reference of the cell containing the loan amount (redemption).
• B5 is the cell reference of the cell containing the day_count_convention.

By using the ACCRINTM function, you can easily calculate the interest accrued on any loan or security.

Accrued Interest Formula Explained: How to Calculate Accrued Interest Step-by-Step

If you prefer to calculate accrued interest without using the ACCRINTM function, you can follow this formula:

Accrued interest = Principal amount * Interest rate * (Number of days from the issue date to the maturity date / n)

For example, if you have a principal amount of $15,000 and an interest rate of 12%, you can calculate the accrued interest as follows:

=15000 * 12% * (DAYS(DATE(2022, 1, 1), DATE(2015, 1, 1)) / 365)

In this example, we are using the “Actual / 365 days years” day count convention, assuming that there are 365 days in a year (n is 365).

Conclusion

In conclusion, the ACCRINTM function in Google Sheets is a powerful tool for calculating accrued interest in securities that pay interest only at maturity. By using this function or the formula explained above, you can easily determine the amount of interest that accrues on your loans or investments.

If you encounter any errors while using the ACCRINTM function, such as the VALUE! error or the NUM! error, make sure to check the validity of the arguments and the correct formatting of the dates.

For more helpful tutorials and tips on Google Sheets, visit Crawlan.com and unlock the full potential of this versatile spreadsheet tool.