How to Use ACCRINT Function in Excel: Calculate Accrued Interest
Need to calculate accrued interest on a bond or security in Excel? The ACCRINT function lets you compute the interest that has accumulated between coupon payment dates without manually calculating daily rates. This guide shows you how, step-by-step.
1. Understand ACCRINT Function Logic
By default, ACCRINT calculates accrued interest from the issue date to the settlement date based on the coupon rate and par value. So before you use ACCRINT, you must:
- Have the bond’s issue date, first coupon date, settlement date, and maturity date
- Know the annual coupon rate and par value of the bond
2. Step-by-Step: Use ACCRINT to Calculate Accrued Interest
✅ Step 1: Gather Your Bond Information
- Open your Excel spreadsheet
- In cell A1, enter the issue date (for example: 1/1/2025)
- In cell A2, enter the first coupon date (for example: 6/30/2025)
- In cell A3, enter the settlement date (for example: 3/15/2025)
- In cell A4, enter the coupon rate as a decimal (for example: 0.05 for 5%)
- In cell A5, enter the par value (for example: 1000)
✅ Step 2: Write the ACCRINT Formula
- Click on cell B1 where you want the result
- Type the formula: =ACCRINT(A1,A2,A3,A4,A5)
- Press Enter
- Excel calculates the accrued interest and displays the result
Now the formula is complete. ACCRINT calculated how much interest has accumulated from the issue date to your settlement date.
3. Tips for Better ACCRINT 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 coupon rate is entered as a decimal (0.05 for 5%, not 5)
4. Bonus: Advanced ACCRINT with Different Day Count Conventions
Want even more control? ACCRINT supports different day count methods used in bond markets. Here’s an example:
Example 1: Basic accrued interest calculation
Issue date: 1/1/2025
First coupon: 6/30/2025
Settlement date: 3/15/2025
Coupon rate: 5%
Par value: 1000
Formula: =ACCRINT(A1, A2, A3, 0.05, 1000)
Result: 20.83 (interest accrued for about 73 days)
Example 2: Add day count basis (6th 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: =ACCRINT(A1, A2, A3, 0.05, 1000, 1)
(Uses actual days / actual days convention)
Example 3: Multiple coupon periods with ACCRINT
Settlement: 4/15/2025
Previous coupon: 3/31/2025
Next coupon: 9/30/2025
Rate: 4%
Par: 1000
Formula: =ACCRINT(PrevCoupon, NextCoupon, Settlement, 0.04, 1000)
Result: Accrued interest from previous coupon date
Example 4: Combine with other bond functions
Total bond price = Clean price + Accrued interest
Formula: =PRICE(...) + ACCRINT(...)
This approach lets you calculate accrued interest using different market conventions or combine it with other bond pricing functions.
Troubleshooting ACCRINT Functions
Sometimes users say that ACCRINT 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 settlement date is between issue date and maturity date.
- Verify that the first coupon date is after the issue date and before or on maturity.
Another common issue is that ACCRINT returns #NUM! error or seems to calculate wrong values.
- Make sure your coupon rate is entered as a decimal (0.05) not a percentage (5).
- Verify that your settlement date is not before the issue date or after maturity.
- Check that all date values are valid dates—typos or impossible dates will cause errors.
Learning how to use ACCRINT in Excel is a great way to calculate bond interest accurately without manual calculations. You don’t have to figure out daily interest rates—let ACCRINT handle the accrual and focus on your bond analysis.
Try it out and master bond interest calculations in Excel today!
