How to Use the ACCRINT and ACCRINTM Functions in Google Sheets to Calculate Accrued Interest on Securities

In fixed-income portfolio management, fixed-rate coupon bonds pay interest periodically (annually, semi-annually, or quarterly) across the lifespan of the debt issue. When an investor purchases a coupon bond on the secondary market between scheduled coupon payment dates, the buyer must compensate the seller for the accrued interest earned from the last coupon payment date up to the settlement date. In financial modeling, failing to calculate this accrued interest accurately distorts both dirty pricing calculations and portfolio yield-to-maturity metrics.

In Google Sheets, calculating accrued interest manually using day-count algorithms can be exceptionally tedious and prone to leap-year errors. To automate this accounting process, Google Sheets provides two specialised financial functions: ACCRINT (for securities that pay periodic coupon interest) and ACCRINTM (for securities that pay all interest solely at maturity). This guide explains the mathematical mechanics, syntax parameters, day-count conventions, and practical spreadsheet implementations of both formulas.

Understanding Accrued Interest in Bond Valuation

Accrued interest represents the interest that has accumulated on a bond since the most recent coupon distribution date. When purchasing fixed-income instruments:

• Clean Price: The quoted price of the bond excluding any accrued interest.
• Dirty Price (Settlement Price): The total amount the buyer actually pays upon transaction settlement, equal to the clean price plus accrued interest.
• Periodic vs Maturity Interest: Most corporate bonds and government gilts pay periodic coupons (handled by ACCRINT), whereas short-term notes and certificates of deposit disburse accumulated interest in a single lump sum at expiration (handled by ACCRINTM).

Syntax and Parameter Breakdown

The ACCRINT Function

The ACCRINT function calculates the accrued interest for a security that pays periodic interest payments:

=ACCRINT(issue, first_payment, settlement, rate, redemption, frequency, [day_count_convention])

The ACCRINTM Function

The ACCRINTM function calculates accrued interest for securities that pay interest solely upon final maturity:

=ACCRINTM(issue, settlement, rate, redemption, [day_count_convention])

Parameter Definitions

• issue: The security’s original issue date when interest began accruing.
• first_payment: The date on which the security’s very first coupon payment was or will be made.
• settlement: The transaction settlement date when the security is acquired (must strictly precede maturity).
• rate: The annual nominal coupon interest rate (e.g. 0.055 for 5.5%).
• redemption: The par face value of the security (almost universally 100 or 1000).
• frequency: The number of coupon payments per calendar year:
  • 1: Annual disbursements.
  • 2: Semi-annual disbursements (standard US and UK corporate bond convention).
  • 4: Quarterly disbursements.
• day_count_convention (Optional): The financial day-count basis:
  • 0 or omitted: US (NASD) 30/360.
  • 1: Actual/Actual (standard for sovereign government bonds).
  • 2: Actual/360 (common in short-term money market paper).
  • 3: Actual/365 (standard UK commercial debt basis).
  • 4: European 30/360.

Step-by-Step Modeling Example in Google Sheets

Scenario: Calculating Accrued Interest on a Semi-Annual Corporate Bond

Suppose you acquire a £1,000 corporate bond with the following specifications:

• Issue Date: 15 March 2026
• First Payment Date: 15 September 2026
• Settlement Date: 15 June 2026
• Annual Coupon Rate: 6.00% (0.06)
• Face Value (Redemption): £1,000
• Frequency: 2 (Semi-annual)
• Day Count Basis: 3 (Actual/365)

Set up your Google Sheets model as follows:

A1: Parameter              | B1: Value
A2: Issue Date             | B2: 2026-03-15
A3: First Payment Date     | B3: 2026-09-15
A4: Settlement Date        | B4: 2026-06-15
A5: Annual Coupon Rate     | B5: 6.00%
A6: Face Value             | B6: 1000
A7: Payment Frequency      | B7: 2
A8: Day Count Basis        | B8: 3
A9: Accrued Interest (£)   | B9: =ACCRINT(B2, B3, B4, B5, B6, B7, B8)

Google Sheets evaluates the equation and returns £15.12. If the bond is currently trading at a clean price of £985.00, your total dirty settlement price payable to the seller is £985.00 + £15.12 = £1,000.12.

Day-Count Convention Impact Matrix

Selecting the correct day-count basis prevents substantial reconciliation discrepancies across institutional bond portfolios:

Basis Code	Convention Name	Primary Asset Class	Formula Example
0	US (NASD) 30/360	US corporate bonds, standard municipal debt	=ACCRINT(B2, B3, B4, B5, B6, B7, 0)
1	Actual/Actual	US Treasuries, UK sovereign gilts	=ACCRINT(B2, B3, B4, B5, B6, B7, 1)
2	Actual/360	Commercial paper, certificates of deposit	=ACCRINT(B2, B3, B4, B5, B6, B7, 2)
3	Actual/365	UK sterling corporate debt, Commonwealth paper	=ACCRINT(B2, B3, B4, B5, B6, B7, 3)
4	European 30/360	Eurobonds, continental European corporate notes	=ACCRINT(B2, B3, B4, B5, B6, B7, 4)

Troubleshooting Common Errors

• #NUM! Error: Occurs when settlement <= issue or when dates violate logical chronological ordering. Always ensure issue < settlement.
• #VALUE! Error: Occurs if dates are provided as plain text strings that Google Sheets fails to interpret. Pass dates as cell references containing date numbers or wrap them in DATE(year, month, day).
• Frequency Error: Entering frequency values other than 1, 2, or 4 causes an immediate #NUM! error.