ACCRINT
The interest a security that pays periodically has accrued by settlement.
Group: Financial. Compatible with DAX.
Syntax
ACCRINT ( <issue>, <first_interest>, <settlement>, <rate>, <par>, <frequency> [, <basis>] [, <calc_method>] )| Parameter | Type | Description |
|---|---|---|
issue | Date | The day the security was issued and began to earn interest. |
first_interest | Date | The day of its first coupon. |
settlement | Date | The day the buyer takes the security over; later than its issue. |
rate | Number | The coupon rate a year, as a decimal. |
par | Number | The face value the coupon is paid on. |
frequency | Whole number | Coupons a year: 1 for yearly, 2 for half-yearly, 4 for quarterly. |
basis | Whole number optional | The day count, by number: US 30/360 (0, when left out), actual/actual (1), actual/360 (2), actual/365 (3) or European 30/360 (4). |
calc_method | Boolean optional | For a settlement after the first coupon: TRUE (when left out) counts the interest from the issue date; FALSE counts it as Power BI and Excel do with FALSE. |
Returns
Number. The interest the security has earned by settlement, in the currency of par.
Remarks
- This is the interest a buyer pays the seller on top of the price when a bond changes hands between coupons: par times the coupon rate, for the share of the coupon periods that has run.
- An issue date on or after settlement, or a rate or par of 0 or less, is an error.
- The dates are taken as whole days. frequency and basis are rounded to whole numbers first; a frequency other than 1, 2 or 4, or a basis outside 0 to 4, is an error.
- With calc_method FALSE, OpenBI gives Power BI's answers, which depend on the frequency and the day count in ways the usual description, interest from the first coupon only, does not tell: often the same as TRUE, sometimes more or less (example 3). Keep TRUE unless FALSE is what your figures were made with.
- On 30/360, from the last day of one February to the last day of another is a whole number of years, as Power BI counts it: 28 February 2023 to 29 February 2024 is one year (example 3).
Examples
Between the issue and the first coupon
A 3.5 % bond of 100,000, issued 1 March, first coupon 1 September, bought on 15 June. On 30/360 that is 104 days of interest; actual/actual counts 106 of the 184 days in the period.
EVALUATE
ROW (
"30/360", ACCRINT ( DATE ( 2026, 3, 1 ), DATE ( 2026, 9, 1 ), DATE ( 2026, 6, 15 ), 0.035, 100000, 2, 0 ),
"Actual/actual", ACCRINT ( DATE ( 2026, 3, 1 ), DATE ( 2026, 9, 1 ), DATE ( 2026, 6, 15 ), 0.035, 100000, 2, 1 )
)| 30/360 | Actual/actual |
|---|---|
| 1011.11111111111 | 1008.15217391304 |
After several coupons
Counted from the issue date: 647 days on 30/360, at 4 % on 1,000.
EVALUATE
ROW (
"Accrued", ACCRINT ( DATE ( 2025, 1, 15 ), DATE ( 2025, 7, 15 ), DATE ( 2026, 11, 2 ), 0.04, 1000, 2, 0 )
)| Accrued |
|---|
| 71.8888888888889 |
A year to 29 February, and calc_method
10 % on 1,000 from 28 February 2023 to 29 February 2024 on 30/360 is a whole year: 100. Then a bond settled after its first coupon, on actual/360: TRUE and FALSE differ a little.
EVALUATE
ROW (
"To 29 February", ACCRINT ( DATE ( 2023, 2, 28 ), DATE ( 2024, 2, 29 ), DATE ( 2024, 2, 29 ), 0.1, 1000, 1, 0 ),
"TRUE", ACCRINT ( DATE ( 2023, 2, 28 ), DATE ( 2024, 6, 15 ), DATE ( 2025, 1, 20 ), 0.1, 1000, 1, 2 ),
"FALSE", ACCRINT ( DATE ( 2023, 2, 28 ), DATE ( 2024, 6, 15 ), DATE ( 2025, 1, 20 ), 0.1, 1000, 1, 2, FALSE )
)| To 29 February | TRUE | FALSE |
|---|---|---|
| 100 | 190.555555555556 | 192.222222222222 |