ACCRINT function
ACCRINT works out the interest a bond has built up between its issue date and the settlement date, returned in currency units. It is useful when pricing or booking a bond bought between coupon payments.
Example
Read it as a sentence: for a $1,000 bond paying 6% a year in two coupons, issued January 1, 2024, with first interest on July 1, 2024, find the interest built up by the April 1, 2024 settlement date.
Syntax
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
Arguments in [square brackets] are optional.
-
issue(required) - Date the security was issued.
-
first_interest(required) - Date of the first interest payment.
-
settlement(required) - Date the buyer takes ownership.
-
rate(required) - Annual coupon rate.
-
par(required) - Face value; defaults to 1,000 if omitted.
-
frequency(required) - Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
-
basis(optional) - Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.
-
calc_method(optional) - TRUE (default) accrues from issue date; FALSE accrues from first_interest date.
Returns: Interest accrued so far on a periodic-interest security, in currency units.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,5,1),0.1,1000,2,0) | 16.66666667 | Typical |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,5,1),0.1,1000,2,0,FALSE) | 16.66666667 | calc_method FALSE |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,5,1),0.1,1000,2,0,TRUE) | 16.66666667 | calc_method TRUE |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2025,5,1),0.1,1000,2,0,FALSE) | 116.6666667 | Settlement long after (FALSE vs TRUE) |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,5,1),0.1,,2) | 16.66666667 | Par omitted |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,5,1),0.1,1000,4) | 16.94444444 | Frequency 4 |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,3,1),0.1,1000,2) | #NUM! | Issue = settlement |
=ACCRINT(DATE(2024,3,1),DATE(2024,8,31),DATE(2024,2,1),0.1,1000,2) | #NUM! | Settlement before issue |
Watch out for
- Pass dates with DATE() or cell references; text dates can misbehave and invalid dates return #VALUE!.
- Settlement before issue or a rate of 0 or less returns #NUM!.
Where it works
- Excel
- ACCRINT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .