What is the ACCRINTM Function?
The ACCRINTM Function is a Financial functionFunctionsThe ACCRINTM Function is a Financial function. ACCRINTM was introduced in 2007 and hence not available for earlier versions. When we invest in an interest-paying security, we will receive interest payments that are paid either periodically or once. The function will calculate the accrued interest for a security that. ACCRINTM was introduced in MS Excel 2007 and hence not available in earlier versions.
When we invest in an interest-paying securityPublic SecuritiesPublic securities, or marketable securities, are investments that are openly or easily traded in a market. These securities are either equity or debt-based. An equity security is an investment based on the equity of a company. A debt security is an investment based on the debt of a company or entity, we will receive interest payments that are paid either at once or periodically. The function will calculate the accrued interest for a security that pays interest on maturity.
MS Excel provides two functions for calculating accrued interest: ACCRINT and ACCRINTM. ACCRINT helps calculate the interest that is paid by issuer periodically. On the other hand, ACCRINTM calculates the interest that is paid at the maturity, or the lump sum interest when a security expires.
Formula
=ACCRINTM(issue, settlement, rate, par, [basis])
The ACCRINTM function uses the following arguments:
- Issue (required argument) – It is the security’s issue date. If it is not an integer, it will be truncated.
- Settlement (required argument) – It is the security’s maturity date, which is when the security expires.
- Rate (required argument) – It is the security’s annual coupon rate.
- Par (required argument) – It is the security’s par value. If omitted, then ACCRINTM function will take the par value as $1,000.
- Basis (optional argument) – It is a kind of day count that is used for calculating the interest on a given security. If we omit the basis argument, it is set to 0. Basis can be any of the following values:
Basis | Day Count basis |
---|
0 or omitted | US(NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
How to use the ACCRINTM Function in Excel?
To understand the uses of the ACCRINTM function, let’s consider a few examples:
Example 1
Suppose we are given the following data:
The formula to use is shown below:
We get the result below:
Example 2 – Using DATE with ACCRINT
Let’s now see how we can use the DATE function along with ACCRINTM to find out the interest payable. The reason we use the DATE function is that the dates given are not in date format. We can use DATE along with ACCRINT to get the interest receivable on maturity.
Suppose we are given the following data:
The formula to use is shown below:
We get the result below:
Example 3
Let’s now see what happens when do not provide par value. Suppose we are given the following data:
There is no par value given. The formula to use is shown below:
The ACCRINTM function took the par value as $1,000, which is the default value. The result we got was from the interest calculation at a par value of $1,000:
Things to remember about the ACCRINTM Function
- #NUM! error – Occurs when:
- The given rate argument is ≤ 0 or the given [par] argument is ≤ 0.
- The provided issue argument is greater than or equal to the settlement date. For example, when we provide =ACCRINTM(“1/10/2017″,”1/7/2017”,0.05,,1).
- The given basis argument is not equal to 0, 1, 2, 3 or 4. For example, when we provide =ACCRINTM(“1/1/2017″,”1/7/2017”,0.05,10000,5).
- #VALUE! error – Occurs when:
- The given issue or settlement arguments are not valid dates.
- Any of the arguments provided is non-numeric.
- When we input the issue and settlement dates, it should be entered as either:
- References to cells that contain dates; or
- Dates that are returned from formulas; or
- If we attempt to input the date arguments as text, MS Excel may interpret them incorrectly, due to different date systems or date interpretation settings.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts