How to Use the COUPDAYBS and COUPDAYS Functions in Google Sheets to Calculate Bond Accrual Days

When valuing fixed-income debt securities—such as municipal bonds, corporate debentures, or sovereign treasury notes—settlement rarely coincides neatly with an exact coupon payment date. To settle a secondary market bond trade fairly, the buyer must compensate the seller for the interest accrued between the previous coupon date and the settlement date. Computing these exact accrued days manually across diverse international day-count conventions (such as 30/360 or Actual/Actual) is notoriously error-prone.

Google Sheets provides two essential fixed-income financial formulas designed specifically to automate day-count calculations: COUPDAYBS (Days from beginning of coupon period to settlement) and COUPDAYS (Total days in the coupon period containing settlement). This guide details their mathematical foundation, syntax rules, day-count convention standards, and practical bond accrual modeling.

Understanding Accrued Interest Day Counting

Accrued bond interest is calculated using the following financial ratio:

Accrued Interest = Coupon Payment * (Days from Last Coupon to Settlement / Total Days in Coupon Period)

The COUPDAYBS formula directly solves the numerator of this equation (Days from Beginning of coupon period to Settlement), while COUPDAYS computes the denominator (Total Days in the coupon period). Using these native functions guarantees institutional precision regardless of leap years or month-end conventions.

Syntax and Parameter Breakdown

COUPDAYBS Syntax

=COUPDAYBS(settlement, maturity, frequency, [day_count_convention])

COUPDAYS Syntax

=COUPDAYS(settlement, maturity, frequency, [day_count_convention])

Parameter Reference

• settlement: The date when the security changes ownership (e.g. DATE(2026, 4, 15)). Must be entered as a valid serial date, not plain text.
• maturity: The contractual maturity date when the principal bond balance is redeemed. Must occur strictly after settlement.
• frequency: The number of coupon payments per annual cycle (1 for annual, 2 for semi-annual, 4 for quarterly).
• day_count_convention (optional): The day-count standard:
  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/Actual (standard for US Treasuries)
  • 2: Actual/360 (standard for money market paper)
  • 3: Actual/365 (standard for UK Gilts and Commonwealth bonds)
  • 4: European 30/360 (ISMA)

Step-by-Step Practical Implementation

Consider a 10-year corporate bond issued with semi-annual coupons:

• Settlement Date: 15 April 2026
• Maturity Date: 31 December 2030
• Frequency: 2 (Semi-annual)
• Day Count Basis: 0 (US 30/360)

Organise your Google Sheet cells as follows:

A1: Settlement Date        | B1: =DATE(2026, 4, 15)
A2: Maturity Date          | B2: =DATE(2030, 12, 31)
A3: Frequency              | B3: 2
A4: Basis                  | B4: 0
A5: Accrual Days Elapsed   | B5: =COUPDAYBS(B1, B2, B3, B4)
A6: Total Period Days      | B6: =COUPDAYS(B1, B2, B3, B4)
A7: Accrual Fraction       | B7: =B5 / B6

In this example, Google Sheets establishes the previous coupon date was 31 December 2025. COUPDAYBS calculates exactly 105 days elapsed under the 30/360 convention, and COUPDAYS returns 180 days. The resulting accrual fraction is 105 / 180 = 0.5833 (58.33% of the semi-annual coupon amount).

Day Count Basis Comparison Table

Basis Code	Standard Name	Month Basis	Year Basis	Common Asset Class
0	US (NASD) 30/360	30 days	360 days	US Corporate and Municipal Bonds
1	Actual/Actual	Exact calendar	Exact calendar	US Treasury Bonds and Notes
2	Actual/360	Exact calendar	360 days	Commercial Paper, Eurobonds
3	Actual/365	Exact calendar	365 days	UK Gilts, Sterling Corporate Debt
4	European 30/360	30 days	360 days	Continental European Bonds

Troubleshooting Common Errors

• #NUM! Error: Occurs when settlement >= maturity, when frequency is any value other than 1, 2, or 4, or when basis is outside the range 0–4.
• #VALUE! Error: Occurs when dates are supplied as raw unparsed strings (e.g. "15/04/2026") instead of actual date serial numbers generated by DATE() or cell references.