Part of the free Module 5: Excel Formulas and Functions · Function 1 of 102 · Full Excel course
The Excel ACCRINT function calculates the accrued interest on a security that pays periodic interest, such as a bond, from its issue date (or last coupon date) up to the settlement date. ACCRINT in Excel returns a currency amount, so a buyer knows how much interest to pay the seller on top of the quoted price.
ACCRINT syntax
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])Arguments
| Argument | Required | Meaning |
|---|---|---|
| issue | Required | The security’s issue date. |
| first_interest | Required | The date of the first coupon payment. |
| settlement | Required | The date the buyer takes ownership; must be after issue. |
| rate | Required | Annual coupon rate, e.g. 5% or 0.05. |
| par | Required | Face value. If omitted Excel assumes 1,000. |
| frequency | Required | Coupons per year: 1 annual, 2 semi-annual, 4 quarterly. |
| basis | Optional | Day-count convention: 0 or omitted US (NASD) 30/360; 1 actual/actual; 2 actual/360; 3 actual/365; 4 European 30/360. |
| calc_method | Optional | TRUE or omitted: accrue from issue to settlement. FALSE: accrue from first_interest to settlement. Only matters when settlement is after first_interest. |
Enter all dates with DATE() or as cell references holding real dates, never as text. ACCRINT is built into Excel 2007 and later (it needed the Analysis ToolPak before that).
Step-by-step example
A bond issued on 1 January 2023 pays 5% semi-annually on a face value of 1,000, first coupon 1 July 2023. It is sold on 1 May 2023 using the 30/360 basis.
=ACCRINT(DATE(2023,1,1), DATE(2023,7,1), DATE(2023,5,1), 5%, 1000, 2, 0)- Excel counts the days from issue to settlement on a 30/360 basis: 4 months × 30 = 120 days.
- It computes par × rate × days/360 = 1000 × 0.05 × 120/360.
- The result is 16.67: the buyer owes the seller 16.67 of accrued interest.
Practical use cases
1. Actual/actual basis for government bonds
=ACCRINT(B2, B3, B4, B5, B6, 2, 1)With dates and rate in B2:B6, basis 1 counts real calendar days in both numerator and denominator.
2. Quarterly coupon on a corporate note
=ACCRINT(DATE(2023,1,1), DATE(2023,4,1), DATE(2023,3,15), 9%, 1000, 4, 0)3. Settlement after the first coupon
=ACCRINT(DATE(2023,1,1), DATE(2023,7,1), DATE(2023,10,1), 6%, 1000, 2, 0, FALSE)calc_method FALSE accrues only from 1 July to 1 October (90 days = 15.00) because the January-July coupon has already been paid. With TRUE the function would return interest from January (270 days = 45.00).
4. Dirty price of a bond
=PRICE(...) + ACCRINT(...)The clean price from PRICE plus accrued interest gives the amount actually paid.
5. Interest at maturity for a single-payment security
Use ACCRINTM instead; ACCRINT is for periodic coupons.
Common mistakes and errors
- #VALUE! – a date argument is text, or rate/par/frequency is not numeric. Wrap dates in DATE() or DATEVALUE().
- #NUM! – rate or par is 0 or negative; frequency is not 1, 2 or 4; basis is outside 0-4; or issue is on or after settlement.
- Wrong basis – US corporates commonly use 30/360 (0); US Treasuries actual/actual (1); money-market instruments actual/360 (2). Check the prospectus.
- Percent as whole number – typing 5 instead of 5% or 0.05 inflates the result 100 times.
- Regional date formats – 01/05/2023 is 1 May in Europe and 5 January in the US. Always use DATE(year, month, day).
Related functions
- PMT – periodic loan or annuity payment.
- DATE and DAYS360 – build dates and reproduce the 30/360 day count manually.
- YEARFRAC – fraction of a year between two dates under the same basis codes.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
What does the basis argument in ACCRINT do?
It sets the day-count convention: 0 for US 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365 and 4 for European 30/360. The same result can differ by a few cents depending on the basis.
What happens when calc_method is TRUE?
Interest accrues from the issue date to settlement, the default. Set it to FALSE to accrue from the first_interest date instead, which applies when settlement falls after the first coupon has been paid.
Why does ACCRINT return #NUM!?
One of the numeric rules is broken: rate or par is not positive, frequency is not 1, 2 or 4, basis is not 0 to 4, or the settlement date is not after the issue date.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.