This detailed guide will cover everything you need to know about the Microsoft Excel ACCRINT function. A financial function called ACCRINT determines the accumulated interest on a security that has periodic interest payments.
We will go over the syntax, examples, helpful hints, typical errors, troubleshooting, and related formulae for the ACCRINT function in this tutorial.
This Tutorial Covers:
- What is the ACCRINT Function
- Syntax
- Arguments
- Usage notes
- How to use the ACCRINT Function in Excel
- Example 1 – Basic ACCRINT Function
- Example 2 – Combining DATE and ACCRINT
- Things to remember about the ACCRINT Function
- ACCRINT Tips & Tricks
- What Excel formulas are comparable to ACCRINT
- What is the ACCRINT Function?
The ACCRINT Function is a financial function in Microsoft Excel that enables users to compute the accrued interest for a security that pays interest periodically, such as a bond. This function is particularly helpful when a security is sold or transferred to a new owner on a date that is not the issue date or an interest payment date.
ACCRINT was first introduced in Excel 2007 and is not accessible in previous versions of the software. By using the ACCRINT function, analysts and investors can calculate the accrued interest on securities accurately, allowing for informed investment decisions.
- Syntax:
The ACCRINT function is a financial function used to calculate the accrued interest for a security that pays periodic interest.
The syntax for the ACCRINT function are as follows:
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
- Arguments:
The function requires eight arguments, out of which only the first six are mandatory:
- issue: The issue date of the security.
- first_interest: The first interest payment date of the security.
- settlement: The settlement date of the security.
- rate: The annual interest rate of the security.
- par: The face value of the security at maturity.
- frequency: The number of interest payments per year. This can be 1, 2, 4, or any other number.
- [basis]: [optional] The day-count basis to use for the calculation. The default is 0 or “actual/actual”.
8. [calc_method]: [optional] The method to use for calculating the accrued interest. The default is 0 or “regular”.
Note that the issue, first_interest, settlement, rate, and par arguments must be entered as numeric values or as cell references that contain numeric values. The [basis] and [calc_method] arguments are optional and may be omitted. The [basis] argument must be a numeric value, while the [calc_method] argument can be one of the following strings: “regular”, “european”, or “actual/360”. The calculated accrued interest is returned as a numeric value.
- Usage notes:
In the financial sector, bond prices are frequently given as “clean prices,” which do not include any interest that has accumulated since the bond’s issue date or the most recent coupon payment. On the other hand, accumulated interest is included in the “dirty price” of a bond. To get the accumulated interest on a security that pays periodic interest, use Excel’s ACCRINT function. To guarantee accurate results, the date configuration should be appropriately specified, it is vital to remember.
- How to use the ACCRINT Function in Excel?
Using ACCRINT Function in Excel, let’s take a look at some examples to better understand its applications:
- Example 1 – Basic ACCRINT Function:
Suppose an investor purchased a bond with a par value of $100,000 on January 1, 2022, with an annual interest rate of 8%, and semi-annual coupon payments. The bond has a maturity date of December 31, 2024. The investor sells the bond on July 1, 2023, and the settlement date is July 10, 2023. The actual/actual basis is determined by the day-count.
To calculate the accrued interest on the bond, the Excel ACCRINT function can be used with the following arguments:
issue: 01/01/22
first_interest: 01/01/22 (since the first interest payment was made on the issue date)
settlement: 7/10/23
rate: 8%
par: $200,000
frequency: 2 (since the bond pays semi-annual coupon payments)
basis: 1 (since the actual/actual day-count basis is being used)
The formula for the accrued interest calculation using the ACCRINT function is:
=ACCRINT(B2,B3,B4,B5,B6,B7,B8)
The result is $24,391.30, which represents the accrued interest on the bond as of July 10, 2023.
- Example 2 – Combining DATE and ACCRINT:
The DATE function and ACCRINT can be used to calculate the interest due upon maturity even if the dates provided are not in date format.
Suppose an investor purchased a bond with a par value of $50,000 on January 15, 2022, with an annual interest rate of 6%, and quarterly coupon payments. The bond has a maturity date of December 31, 2024. The investor sells the bond on June 30, 2023, and the settlement date is July 10, 2023. The day-count basis is the actual/365 basis.
To calculate the accrued interest on the bond, the ACCRINT function can be used with the following arguments:
issue: 15-Jan-22
first_interest: 31-Mar-22 (since the first interest payment was made on that date)
settlement: 10-Jul-23
rate: 6%
par: $200,000
frequency: 4 (since the bond pays quarterly coupon payments)
basis: 1 (since the actual/365 day-count basis is being used)
We can use the DATE function to construct the first_interest argument. The formula for the accrued interest calculated using the ACCRINT function is:
=ACCRINT(DATE(2022,1,15),DATE(2022,3,31),DATE(2023,7,10),B5,B6,B7,B8)
The result is $17,833.33, which represents the accrued interest on the bond as of July 10, 2023.
3. Things to remember about the ACCRINT Function:
Here are the things to remember about the Excel ACCRINT formula:
#NUM! error – Occurs when:
- The provided rate argument is ≤ 0 or the provided par argument is ≤ 0.
- The provided frequency argument is not equal to 1, 2, or 4.
- The settlement date is more than or equal to the issue date specified.
- The provided basis argument is not equal to 0, 1, 2, 3, or 4.
#VALUE! error – Occurs when:
- Issue, first_interest, and settlement reasons given are invalid for the given dates.
- Any of the arguments provided is non-numeric.
When entering the issue and settlement dates, they should be entered as either:
- References to cells that contain dates; or
- Dates that are returned from formulas; or
- If attempting to input these date arguments as text, Excel may incorrectly interpret them, due to different date systems or date interpretation settings.
4. ACCRINT Tips & Tricks:
Here are some tips and tricks to keep in mind when using the ACCRINT function in Excel:
- Ensure that the dates provided in the function are in the correct format, such as “yyyy-mm-dd”. Incorrectly formatted dates may result in errors.
- Remember that the function calculates the accrued interest up to, but not including, the settlement date.
- If you need to calculate the accrued interest for a bond with a different day count basis, make sure to specify the correct basis value in the [basis] argument.
- Note that the ACCRINT function assumes that interest payments are made on a regular schedule. If the interest payments are irregular, the function may not provide accurate results.
5. What Excel formulas are comparable to ACCRINT?
You may find the following related formulas helpful when using the ACCRINT function:
ACCRINTM: Calculates the interest that has accrued on a security that will mature with interest.
COUPDAYBS: Counts the days between the start of the coupon period and the settlement date.
COUPDAYS: Determines how many days are in the coupon period that are on the settlement date.
COUPDAYSNC: Determines how many days there are between the settlement date and the subsequent coupon date.
COUPNCD: Determines the coupon date that comes after the settlement date.
You can accurately compute the accumulated interest for assets that pay periodic interest by knowing the ACCRINT function and its associated equations, which will help you make wise investment choices and manage your financial portfolio.
This is how you make an Excel amortization schedule for a loan or mortgage.
For ready-to-use Dashboard Templates:
