COUPDAYBS

What it does

COUPDAYBS(settlement, maturity, frequency, [basis])

COUPDAYBS(settlement, maturity, frequency, [basis]) returns the number of days from the beginning of the current coupon period to the settlement date — the days the seller has already earned.

Its whole economic purpose is one quotient:

accrued interest  =  coupon  ×  COUPDAYBS / COUPDAYS

which is why the denominator's page, COUPDAYS, is required reading alongside this one: the two functions do not measure time the same way on every basis.

The "beginning of the current coupon period" is the previous coupon date, which is exactly what COUPPCD returns. The period is derived from the schedule described on COUPNCD — coupon dates counted backwards from maturity in 12/frequency-month steps.

Arguments

COUPDAYBS(settlement, maturity, frequency, [basis])

The five-row basis table is documented on this page and is the standard one — 0 or omitted = US (NASD) 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

There is no issue date argument. A security issued mid-period does not shorten the first COUPDAYBS: the function measures from the schedule's previous coupon date, real or notional, not from issuance. Accrued interest on a newly issued bond therefore needs ACCRINT, which does take an issue date, rather than this function.

Microsoft documents that "All arguments are truncated to integers." Dates should be produced by DATE or by other formulas rather than typed as text; ordinary coercion rules are in Coercion and lifting.

Try it

Quirks

How the days are counted

So COUPDAYBS is a calendar measurement on three of the five bases, and a 30/360 measurement on the other two. It is never a declared constant — which is precisely where it parts company with COUPDAYS, and why

COUPDAYBS + COUPDAYSNC  =  COUPDAYS

holds on bases 1 and 4 and fails on bases 2 and 3. On basis 0 it does not always hold: there FN-COUPDAYSNC-001 measures COUPDAYSNC as P − A, and P need not be 360/frequency. The full account of the failure on bases 2 and 3, and of the upstream case where Excel's own PRICE and COUPDAYSNC disagreed because of it, is on COUPDAYS.

Result and edge cases

Returns a Number: a day count, integral on every basis.

• settlement one day after a coupon date returns 1 on bases 1, 2 and 3, and 1 on the 30/360 bases too except where the adjustment rules bite.
• The 30/360 bases can disagree with the calendar by several days, by design: a period from 31 January to 31 July counts as 180 days on basis 0 whatever the calendar says.
• The result is bounded by the period length only on bases 0, 1 and 4. On bases 2 and 3 the count can exceed the COUPDAYS the same call reports, because that denominator is a convention.
• settlement ≥ maturity is a documented error.
• Empty, missing and error arguments follow the shared call model; see The value universe.

Errors

Documented on Microsoft's Learn page for this function, in these words (it says "generates an error" without naming a worksheet error value):

How to implement

Numerical notes

1. This is integer arithmetic pretending to be floating point. Every answer is a whole number of days on every basis. An implementation that computes it in integers and converts once at the end has no rounding to discuss; one that routes through a year fraction and multiplies back does.
2. The order of the two 30/360 adjustments is observable. Collapsing the 31st to the 30th before or after noticing that the start date was a month end changes the answer by a day on a nameable input class — and only on that class, which is why it survives large corpora and then fails in production.
3. The roll-forward convention is a branch, not a formula. Whether settlement on a coupon date yields 0 or a full period is decided before any counting happens, and it is the largest single behavioural unknown on this page.
4. No cancellation, no overflow, no transcendental. The difficulty here is entirely definitional.

Rust code

10 coupdaybs_fn "COUPDAYBS" (3, 4) [V "settlement", V "maturity", V "frequency", V "basis"] |s, a, sv| {
    let (settlement, maturity, frequency, b) = coupon_args(s, a, sv)?;
    coupon_out(s, coupon::coupdaybs_kernel(settlement, maturity, frequency, b))
}

pub fn coupdaybs_kernel(
    settlement: f64,
    maturity: f64,
    frequency: f64,
    basis: Option<f64>,
) -> Result<f64, WorksheetErrorCode> {
    let ctx = parse_coupon_context(settlement, maturity, frequency, basis)?;
    let period = locate_coupon_period(ctx)?;
    accrued_or_remaining_days(period.previous, ctx.settlement, ctx.basis)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• COUPDAYS — the denominator. Read its additivity section before combining the two.
• COUPDAYSNC — the complement, days from settlement forward to the next coupon. The pair is not guaranteed to partition the period.
• COUPPCD — the date this function counts from. settlement − COUPPCD is the actual elapsed days, which equals COUPDAYBS on bases 1, 2 and 3 and not on 0 or 4.
• ACCRINT — the function to use when accrual starts at issuance rather than at a schedule coupon date, or when the interval spans several periods.
• PRICE, YIELD — consumers. PRICE returns a clean price; the invoice price adds the accrual this function's quotient defines.
• YEARFRAC — the same day-count conventions, applied to an arbitrary date pair instead of to a coupon period.
• Confused with: settlement − COUPPCD(...) (above), and with ACCRINT, which answers a different question with a different domain.

Named variants

No named variants are recorded yet.