Google Sheets Accrued Interest Bond Calculator

A Google Sheets accrued-interest bond calculator estimates the interest earned since a bond’s last coupon date, usually to determine the amount a buyer pays the seller at settlement. Enter the bond’s annual coupon rate, face value, coupon period, and days elapsed; the calculator returns accrued interest under a simple day-count convention.

Formula

This calculator applies a simple, linear accrued-interest formula using a semiannual coupon: AI = F × (c / 100) × (d / D) ÷ 2. Here, AI is accrued interest in dollars; F is face value in dollars; c is the annual coupon rate in percent; d is the number of elapsed days in the coupon period; and D is the total number of days in that period. The division by two assumes two coupon payments per year. The second output assumes the bond’s clean price is exactly its face value: invoice price = clean price + accrued interest.

In Google Sheets, the first calculation can be entered as =B2*(B3/100)*B4/(B5*2) if the inputs are in cells B2 through B5 in the order shown. Format the result as currency and keep the rate as a number such as 5 for 5%, not as a percentage-formatted cell containing 5% unless you adjust the formula. If the cell contains 5%, use =B2*B3*B4/(B5*2) instead.

Worked Example

Using the default inputs—$1,000 face value, a 5% annual coupon, 90 elapsed days, and a 180-day coupon period:

1. Convert the coupon rate to a decimal: 5 ÷ 100 = 0.05.
2. Calculate the annual coupon: $1,000 × 0.05 = $50.
3. Calculate the semiannual coupon: $50 ÷ 2 = $25.
4. Find the elapsed fraction of the period: 90 ÷ 180 = 0.5.
5. Accrued interest: $25 × 0.5 = $12.50.

If the clean price is at par, the illustrative invoice price is $1,000 + $12.50 = $1,012.50. A real transaction’s clean price may be above or below face value, and its day-count rules may differ.

Assumptions

• The bond pays fixed coupons twice per year; the annual coupon is divided into two equal payments.
• Accrued interest grows linearly between coupon dates, based on the entered elapsed days and period length.
• The example invoice-price output assumes a clean price equal to face value; it is not a market quote.
• The calculation excludes transaction charges, taxes, settlement conventions, and any bond-specific adjustments.

What Accrued Interest Means

A bond’s coupon is interest paid to its owner on scheduled dates. When a bond changes hands between those dates, the seller has held it for part of the current coupon period. In a typical secondary-market transaction, the buyer compensates the seller for that earned portion by paying accrued interest along with the bond’s quoted clean price. The resulting amount is commonly called the dirty price or invoice price.

This does not mean the issuer makes an extra coupon payment at the time of sale. The buyer generally receives the next scheduled coupon, while the accrued-interest adjustment accounts for the time the seller owned the bond during that coupon period. The exact mechanics and quotation conventions depend on the security and transaction.

Day-Count Conventions and Google Sheets

The calculator’s fraction, elapsed days divided by period days, is a useful illustration but not a universal bond-market convention. Bonds may calculate accrued interest using Actual/Actual, Actual/360, 30/360, or another specified convention. These methods can count days differently, including how month lengths and coupon periods are treated. Some securities also have irregular first or final coupon periods.

To reproduce a transaction figure, identify the bond’s coupon frequency, the last and next coupon dates, the settlement date, and the governing day-count convention. Read those details from the bond documentation or a reliable transaction statement; do not assume every bond uses 30/360 or a fixed 180-day half-year. In Sheets, date subtraction can provide actual elapsed calendar days, but it cannot by itself implement every convention correctly.

For a simple actual-days version, place settlement date in B4, previous coupon date in B5, and next coupon date in B6. Use =B4-B5 for elapsed days and =B6-B5 for the period’s calendar days, then substitute those results for d and D. This remains an Actual/Actual-style fraction only for a simplified period-based calculation; a formal convention may require additional rules.

Common Errors and Limits

• Mixing percentage formats: If the rate is entered as 5, divide by 100. If it is stored as 5%, do not divide by 100 again.
• Using the wrong coupon frequency: The formula divides the annual coupon by two. For quarterly or annual coupons, replace that divisor with the correct number of payments per year.
• Confusing clean price and invoice price: Accrued interest is added to the clean price, not necessarily to face value. The second output is only a par-price illustration.
• Counting the wrong dates: The settlement date and coupon dates determine elapsed time. A one-day difference can affect the result.
• Assuming all bonds accrue linearly: Market conventions and security-specific terms can change the calculation, so this simplified sheet is not a settlement quote.

For a bond with a clean price quoted per 100 of face value, convert that quote to dollars before adding accrued interest. For example, a clean quote of 98.5 means $985 per $1,000 face value; under the default example’s simplified assumptions, the illustrative invoice amount would be $985 + $12.50 = $997.50.

Frequently Asked Questions

How do I calculate accrued interest on a bond in Google Sheets?
For a semiannual bond under a simple period fraction, multiply face value by the annual coupon rate as a decimal, divide by two, then multiply by elapsed days divided by period days. The sheet above calculates that amount.

Is accrued interest the same as the bond’s price?
No. Accrued interest is an adjustment added to the clean price to produce the invoice or dirty price. A bond’s clean price can be higher or lower than face value.

Should I use 360 or 365 days?
Use the convention specified for the bond. Actual/360 and Actual/Actual can produce different amounts; choosing 360 or 365 without checking the terms may not match the transaction.

Can Google Sheets calculate the exact bond settlement amount?
It can, if the correct dates, clean price, coupon schedule, and day-count convention are entered and implemented. The simple formula here is an estimate and does not cover every convention or irregular coupon schedule.

Why is accrued interest added when buying a bond?
It compensates the seller for the portion of the current coupon period during which they owned the bond. The buyer typically receives the next scheduled coupon payment.

This result is an estimate; the bond’s documentation and transaction disclosure determine the applicable calculation and exact amount.

Related

• Accrued Interest in FD: Meaning and Calculation
• Accrued Interest in Tamil: Meaning and Calculation
• Accrued Interest in Bonds: How It Works

in fv | Face value | $ | 1000 | 0 | 100000000
in cpn | Annual coupon rate | % | 5 | 0.01 | 100
in days | Days accrued in coupon period | days | 90 | 0 | 366
in period | Days in coupon period | days | 180 | 1 | 366
out ai | Accrued interest | $ | fv * (cpn / 100) * days / (period * 2) | 2
out dirty | Invoice price for a clean price at par | $ | fv + ai | 2