The calculates accrued interest for:
- Debt instruments due in full intra-annually
- Fixed-interest securities with periodic payments
Syntax
ACCRINT(Issue; First_Interest_Date; Settlement; Nominal_Interest; Par_Value; Frequency; Basis; Calculation_Method)
Arguments
| Argument | Required? | Description | Notes |
| Issue | Yes | Date of issuance | Must be valid date |
| First_Interest_Date | Yes | First coupon date | For intra-annual debt = due date |
| Settlement | Yes | Ownership transfer date | Must be ≥ Issue date |
| Nominal_Interest | Yes | Annual coupon rate | Decimal format (4% = 0.04) |
| Par_Value | No | Face value | Default = 1000 |
| Frequency | Yes | Payments per year | 1, 2, or 4 |
| Basis | No | Day-count method | 0-4 (Default=0) |
| Calculation_Method | No | Interest calculation | TRUE (full duration) or FALSE (since last payment) |
Day-Count Methods (Basis)
| Basis | Method | Description |
| 0 | 30/360 (NASD) | Standard 30-day months |
| 1 | Actual/Actual | Exact days |
| 2 | Actual/360 | Bankers’ year |
| 3 | Actual/365 | Exact days/365 |
| 4 | 30/360 (European) | Eurobond standard |
Key Features
- Accrued Interest Calculation:
- For multiple annual payments: (Nominal_Interest/Frequency) × (Days held/Days in period)
- Uses specified day-count method (Basis)
- Error Handling:
- #VALUE! – Invalid dates
- #NUMBER! – Negative values or invalid numbers
- Special Cases:
- First period (Period 0) is prorated
- When Calculation_Method=FALSE, only calculates since last payment
Practical Example
Scenario: $1,000 debt issued June 1, 2010, due December 1, 2010 (4% annual rate), sold August 9, 2010.
Solution:
- Accrued Interest (68 days held):
=ACCRINT(« 6/1/2010″, »12/1/2010″, »8/9/2010 »,4%,1000,1,4)
Returns: $7.56
Manual verification:
1000 × 4% × (68/360) = $7.56
- Present Value Calculation (112 days remaining):
- Future value: $1,020 (principal + 6mo interest)
- Discounted at 4.5%:
$1,020 / (1 + 0.045 × (112/360)) = $1,005.92
Pro Tips
- For German bonds, use Basis=4 (30/360 European)
- Combine with PRICEMAT() for full valuation:
=(PRICEMAT(…)×10) + ACCRINT(…)
- Verify calculations using:
- DAYS360() for day counts
- COUPDAYBS() for days since last coupon
Common Use Cases
- Bond transactions between coupon dates
- Loan transfers with accrued interest
- Financial reporting for interest receivables