The `ACCRINTM` function in Excel is used to calculate the accrued interest for a security that pays interest at maturity. This can be useful when you want to determine how much interest has been earned on a bond or similar financial instrument up to its maturity date. Here’s how you can use the `ACCRINTM` function:
Syntax
ACCRINTM(issue, settlement, rate, par, [basis])
Parameters
- `issue`: The security’s issue date (the date when the security was issued), which should be entered in date format. Use the `DATE(year, month, day)` function for reliability.
- `settlement`: The maturity date of the security (the date when the security is due to be paid), also in date format.
- `rate`: The annual coupon rate of the security. It is important to input this as a percentage. For example, for a 5% interest rate, you should enter 0.05.
- `par`: The par value, or face value, of the security. This is typically $1,000 for bonds, but can vary.
- `[basis]` (optional): The day count basis to be used for the calculation. If omitted, the default is 0 (US 30/360). The options for basis are:
- 0 or omitted: US 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you want to calculate the accrued interest of a bond that was issued on January 1, 2023, and matures on January 1, 2024, with an annual interest rate of 6% and a par value of $1,000 using the US 30/360 day count convention.
Here’s how you would set up the function:
=ACCRINTM(DATE(2023, 1, 1), DATE(2024, 1, 1), 0.06, 1000)
If you want to use an Actual/365 basis, you’d set it up like this:
=ACCRINTM(DATE(2023, 1, 1), DATE(2024, 1, 1), 0.06, 1000, 3)
Notes
- Ensure that the `issue` and `settlement` dates are entered correctly. Using functions like `DATE()` helps avoid potential errors due to regional date settings.
- The `ACCRINTM` function will return an error if the settlement date is before or the same as the issue date.
- The function assumes that interest is paid at maturity, different from the `ACCRINT` function, which deals with securities that pay periodic interest.
By understanding these details, you should be well-equipped to use the `ACCRINTM` function effectively in your financial calculations.
Unlock Your Potential
Excel
Access
Power BI
