Quick Answer

COUPDAYBS Function

Calculates the number of days from the beginning of the coupon period to the settlement date.

Syntax

COUPDAYBS

(settlement, maturity, frequency, [basis])

Parameters

Parameter	Description	Required
settlement	Date the security is purchased.	Required
maturity	Date the security expires.	Required
frequency	Number of coupon payments per year (1, 2, or 4).	Required
basis	Day-count convention (0 = US 30/360, default).	Optional

Basic Example

Days from coupon start to settlement

=COUPDAYBS(DATE(2024,4,1), DATE(2034,4,1), 2)

ResultDays since coupon period began

For a bond purchased on April 1, 2024 with semi-annual coupons, this returns the number of days since the coupon period began.

Advanced Examples

Example 1: With actual/actual basis

Treasury bond

Use basis 1 for actual/actual

=COUPDAYBS(DATE(2024,4,1), DATE(2034,4,1), 2, 1)

Result: Days using actual/actual

Basis 1 counts actual days in the actual year.

How COUPDAYBS Works

COUPDAYBS is used in bond pricing to determine accrued interest. It identifies where the settlement date falls within the current coupon period.

1

Enter settlement

Type the settlement

2

Enter maturity

Type the maturity

3

Enter frequency

Type the frequency

4

Enter basis

Type the basis

5

Calculate

Press Enter to get the result.

Important Notes & Limitations

Settlement date must be before maturity date.
Frequency must be 1, 2, or 4.
Basis affects the day count.

Common Errors & Fixes

#NUM! errorInvalid dates, frequency, or basis.

Fix: Verify all inputs.

#VALUE! errorDates are not recognized.

Fix: Use DATE function or valid date serial numbers.

Download Practice File

Practice COUPDAYBS with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the COUPDAYBS function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ACCRINT

Calculates accrued interest for a security that pays periodic interest.

ACCRINTM

Calculates accrued interest for a security that pays interest at maturity.

AMORDEGRC

Calculates depreciation for a specific period using the French declining balance method.

AMORLINC

Calculates depreciation for a specific period using the French straight-line method.

COUPDAYS

Calculates the number of days in the coupon period that contains the settlement date.

COUPDAYSNC

Calculates the number of days from the settlement date to the next coupon date.

Frequently Asked Questions

What is the difference between COUPDAYBS and COUPDAYS?

COUPDAYBS counts days from the coupon start to settlement. COUPDAYS counts days in the entire coupon period.

Why is COUPDAYBS used?

It is used to calculate accrued interest for bond trades.