Need to calculate accrued interest on a security that pays interest at maturity in Excel? The ACCRINTM function lets you compute the interest that has accumulated from the issue date to the maturity date without manually calculating daily rates. This guide shows you how, step-by-step.
1. Understand ACCRINTM Function Logic
By default, ACCRINTM calculates accrued interest from the issue date to the maturity date for securities that pay all interest at maturity (like Treasury bills). So before you use ACCRINTM, you must:
- Have the security’s issue date and maturity date
- Know the annual interest rate and par value of the security
2. Step-by-Step: Use ACCRINTM to Calculate Accrued Interest at Maturity
✅ Step 1: Gather Your Security Information
- Open your Excel spreadsheet
- In cell A1, enter the issue date (for example: 1/1/2025)
- In cell A2, enter the maturity date (for example: 12/31/2025)
- In cell A3, enter the annual interest rate as a decimal (for example: 0.04 for 4%)
- In cell A4, enter the par value (for example: 10000)
✅ Step 2: Write the ACCRINTM Formula
- Click on cell B1 where you want the result
- Type the formula: =ACCRINTM(A1,A2,A3,A4)
- Press Enter
- Excel calculates the accrued interest and displays the result
Now the formula is complete. ACCRINTM calculated how much interest will be paid at maturity on your security.
3. Tips for Better ACCRINTM Calculations
- Use cell references instead of hard-coded values so you can change dates and rates without editing the formula
- Make sure all dates are properly formatted as dates, not text, or the calculation will fail
- Verify the interest rate is entered as a decimal (0.04 for 4%, not 4)
4. Bonus: Advanced ACCRINTM with Different Day Count Conventions
Want even more control? ACCRINTM supports different day count methods used in financial markets. Here’s an example:
Example 1: Basic accrued interest at maturity
Issue date: 1/1/2025
Maturity date: 12/31/2025
Annual rate: 4%
Par value: 10000
Formula: =ACCRINTM(A1, A2, 0.04, 10000)
Result: 400 (10000 × 4% × 365 days / 365)
Example 2: Add day count basis (5th parameter)
Basis 0 = US 30/360 (default)
Basis 1 = Actual/actual
Basis 2 = Actual/360
Basis 3 = Actual/365
Basis 4 = European 30/360
Formula: =ACCRINTM(A1, A2, 0.04, 10000, 1)
(Uses actual days / actual days convention)
Example 3: Treasury bill with 6-month maturity
Issue: 1/15/2025
Maturity: 7/15/2025
Discount rate: 3.5%
Face value: 100000
Formula: =ACCRINTM("1/15/2025", "7/15/2025", 0.035, 100000)
Result: 1750 (interest payment at maturity)
Example 4: Compare interest using different bases
Calculate same security with multiple bases to see impact
Basis 0: =ACCRINTM(Issue, Maturity, Rate, Par, 0)
Basis 1: =ACCRINTM(Issue, Maturity, Rate, Par, 1)
Basis 3: =ACCRINTM(Issue, Maturity, Rate, Par, 3)
(Results may vary by 100s depending on exact day counts)
Example 5: Calculate total maturity value
Par value + Accrued interest
Formula: =A4 + ACCRINTM(A1, A2, A3, A4)
This approach lets you calculate accrued interest at maturity using different market conventions or find total security value at maturity.
Troubleshooting ACCRINTM Functions
Sometimes users say that ACCRINTM returns unexpected results or errors.
- Check if your dates are really formatted as dates, not text—dates as text will cause errors.
- Make sure the maturity date is after the issue date.
- Verify that you’re not confusing ACCRINTM with ACCRINT—ACCRINTM is for interest paid at maturity only.
Another common issue is that ACCRINTM returns #NUM! error or seems to calculate wrong values.
- Make sure your interest rate is entered as a decimal (0.04) not a percentage (4).
- Verify that your maturity date is not before the issue date.
- Check that all date values are valid dates—typos or impossible dates will cause errors.
Learning how to use ACCRINTM in Excel is a great way to calculate security interest at maturity accurately without manual calculations. You don’t have to figure out daily interest rates—let ACCRINTM handle the accrual and focus on your investment analysis.
Try it out and master accrued interest calculations in Excel today!
