When you are architecting a complex fixed-income portfolio in Microsoft Excel, tracking a bond that pays periodic interest is mathematically straightforward. However, if you purchase or sell that bond in the absolute middle of a payment cycle, you must calculate exactly how much interest has mathematically accrued between the last payment date and the exact day of the transaction. To force the Excel engine to calculate this fragmented payout, you must use the highly specialized ACCRINT function.
Understanding the ACCRINT Architecture
The ACCRINT (Accrued Interest) function is a rigid financial engine. It calculates the exact accrued interest for a security that pays periodic interest, precisely prorated to the exact day of settlement.
The syntax requires six mandatory arguments and two optional arguments: =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
- issue: The absolute original issue date of the security.
- first_interest: The exact date the security pays its very first coupon.
- settlement: The exact date the transaction occurs (the date the buyer takes possession).
- rate: The security’s guaranteed annual interest rate.
- par: The absolute par (face) value of the security.
- frequency: The mathematical number of coupon payments per year (1, 2, or 4).
- [basis]: An optional integer (0 to 4) dictating the day-count methodology (e.g., 0 for US 30/360, 1 for Actual/Actual).
- [calc_method]: A boolean logic switch (TRUE/FALSE) determining how to calculate interest if the settlement date occurs after the first interest date. (Default is TRUE).
Executing the Financial Calculation
Imagine a bond was issued on January 1, 2023 (A2), and its first interest payment is July 1, 2023 (A3). You are purchasing it on April 15, 2024 (A4). The annual rate is 6% (A5), the par value is $10,000 (A6), and it pays semi-annually (A7, value 2). You want to use the US 30/360 basis (A8, value 0).
To calculate the exact accrued interest you owe the seller, type:
=ACCRINT(A2, A3, A4, A5, A6, A7, A8, TRUE)The exact millisecond you press Enter, the engine calculates the exact number of days between the last payment and April 15, prorates the 6% rate against the $10,000 par value, and outputs the pristine currency value (e.g., $175.00).