What Is ACCRINTM Excel Function?
The ACCRINTM Excel function is a powerful tool designed for professionals in finance and accounting to calculate the accrued interest for a security that pays periodic interest. It can be used to determine the amount of interest earned on an investment between two coupon payment dates, considering factors such as the terms of the security, issue date, settlement date, redemption value, and frequency of coupon payments.
In the following example, let us look at the concept of the ACCRINTM Excel function. The table below contains some details. To calculate the interest receivable on maturity, let us apply the formula below:
= ACCRINTM(B2,B3,B4,B5)
You get the result as shown. Let’s get started and explore this function!
Table of Contents
Key Takeaways
- The ACCRINTM Excel function helps finance and accounting professionals calculate accrued interest for securities that pay periodic interest. The formula used is =ACCRINTM(issue, settlement, rate, par, basis).
- The ACCRINTM Excel function allows users to handle complex interest calculations accurately and efficiently, saving time and eliminating errors.
- The ACCRINTM function is useful in scenarios where bonds or other fixed-income securities are involved.
- The calculation of this function can provide valuable insights on interest accruals while complying with industry standards.
Syntax
Issue – (Mandatory) It represents the issue date of the security. If it is not an integer, it will be truncated.
Settlement – (Mandatory) The maturity date of the security refers to the date on which it expires.
Rate – (Mandatory) This represents the annual coupon rate for the security.
Par – (Mandatory) The par value of the security should be specified. If it is not provided, the ACCRINTM function will assume a par value of $1,000.
Basis – (Optional) This type of day count is utilized to calculate the interest on a specific security. In the absence of the basis argument, it defaults to 0. The basis can assume 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 ACCRINTM Function in Excel?
To effectively utilize the ACCRINTM function in Excel, follow these steps.
#1 – Access From the Excel Ribbon
Step 1: To designate the cell where the result will be displayed, select an empty cell. Next, navigate to the “Formulas” tab in the Excel ribbon and click on it.
Step 2: Navigate to the menu and select the “Financial” option.
Step 3: Next, navigate to the drop-down menu and select ACCRINTM.
Step 4: A window named “Function Arguments” will appear. Input the values for the number of arguments in the “issue,” “settlement,” “rate,” “par,” and “basis” fields. Once you have finished this step, click on the OK button to proceed.
#2 – Enter the Worksheet Manually
To ensure a vacant cell is assigned for the output, input the formula “=ACCRINTM()” into the desired cell. Alternatively, you may type “=A” and promptly double-click on the ACCRINTM function from the comprehensive list of suggestions offered by Excel. Press OK.
Examples
Example #1 – DATE Function & Referencing
To understand the functionality of the ACCRINTM Excel function, we will explore an illustrative example using the DATE Function & referencing.
Step 1: Select cell B6 and enter the ACCRINTM formula in the designated cell. In this example, we have applied the DATE excel function for calculating the issue date and maturity date and then used the cell reference in the ACCRINTM formula.
= ACCRINTM (B2, B3, B4, B5,0)
We can also use the = ACCRINTM (DATE (2022,2,1), DATE (2023,12,31), B4, B5,0) formula directly if the issue date and the maturity date are not given in the data table in the date format.
Step 2: The result will be displayed in cell B6 and formulas in column C.
Example #2 – Absent Value of Basis & Referencing
In this example, let us calculate the accrued interest for security in the absence of basis and referencing.
To apply the ACCRINTM Excel function calculation, look at the table and follow the steps;
Step 1: Input the complete ACCRINTM formula in the B7 cell:
The basis parameter is used to count accrued days, and it can use different numeric values. If we omit the basis value, it will take 0 as the value by default.
= ACCRINTM(B2, B3, B4, B5)
Step 2: The outcome in the absence of the basis value is showcased in cell B7.
Example #3 – Number as dates & Referencing
In the example shown, we want to calculate accrued interest for a bond with a 5% coupon rate. The issue date is 1-Jan-2019, and the maturity date is 31-Dec-2023. We want accrued interest from the issue date to the maturity date using the ACCRINTM Excel function.
As seen in the table below, the dates are in the form of numbers, as the cells have been formatted that way. We all know that Excel stores data in the form of serial numbers. Now, let us see what happens when we use these serial numbers in our formula.
Step 1: Start the calculation process by selecting cell B6. Enter the ACCRINTM formula in the designated cell:
= ACCRINTM(B2, B3, B4, B5)
Step 3: The calculated result will be displayed in cell B6. As observed, the cells, though they display serial numbers, are formatted as dates, and hence, they did not affect the final calculation of accrued interest for the bond.
Important Things To Note
- The #NUM! error occurs when the rate argument provided is less than or equal to 0, or the par argument is less than or equal to 0, or if the issued argument provided is greater than or equal to the settlement date.
- The #VALUE! error occurs when the provided issue or settlement arguments are not valid dates or when any of the arguments provided are non-numeric.
- When entering the issue and settlement dates, use references to cells that contain dates, utilize dates that are returned from formulas, and avoid inputting the date arguments as text, as Excel may interpret them incorrectly due to variations in date systems or date interpretation settings.
- It plays an essential role in streamlining financial operations and supporting decision-making processes within professional environments.
- The ACCRINTM function was introduced in MS Excel 2007 and is therefore not available in earlier versions.
Frequently Asked Questions (FAQs)
The ACCRINTM Excel function can ascertain the interest earned on bonds, treasury bills, and other fixed-income instruments. By considering various parameters such as issue date, settlement date, rate of interest, frequency of interest payments, and security nominal value, this function delivers precise results
Let’s dive into an example to understand the errors that can pop up when using the ACCRINTM Excel function.
To check the errors enter the formula in cells B6 & C6, like this:
=ACCRINTM(B2,B3,B4,B5) in cell B6, which might cause a #NUM! error because rate < 0 value.
=ACCRINTM(B2,B3,B4,B5) in cell C6, which might cause a #VALUE error because wrong date format of issue date.
The result right away in cells B6 to C6.
The ACCRINTM function, a built-in feature in many financial software programs such as Excel, calculates the accrued interest on security between two coupon payment dates. It is commonly used for interest-bearing securities where periodic coupon payments are made. Non-interest-bearing securities include zero-coupon bonds or Treasury bills that do not pay periodic interest but are sold at a discount to their face value. Since there is no regular interest payment involved, using the ACCRINTM function would yield inaccurate results.
The limitations or caveats to consider while using the ACCRINTM function are;
• This function is specifically designed for calculating the accrued interest on bonds with fixed coupon rates that pay interest at maturity.
• The function assumes that interest is compounded on an annual basis, irrespective of the actual compounding frequency of the bond.
• This function does not consider any day count conventions that can affect accurate accrual calculations.
• The function requires both issue and settlement dates for calculations to get accurate results.
Download Template
This article must help us understand the ACCRINTM Excel Function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to ACCRINTM Excel. Here we learn how to use ACCRINTM Function in Excel with step by step examples and a downloadable template. You can learn more from the following articles –
Leave a Reply