COUPDAYS

What it does

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

COUPDAYS(settlement, maturity, frequency, [basis]) returns the number of days in the coupon period that contains the settlement date.

It is the denominator of every accrual fraction in bond arithmetic. Accrued interest is COUPDAYBS / COUPDAYS of a coupon; the discounting exponent in PRICE is COUPDAYSNC / COUPDAYS. Get this function wrong and every price and yield built on it is wrong by the same proportion.

The coupon period is determined by the schedule described on COUPNCD: coupon dates run backwards from maturity in steps of 12/frequency months, and settlement selects one period from that ladder. On basis 1 Excel's period comes from its own walk back from maturity (FN-COUPDAYS-001).

Arguments

COUPDAYS(settlement, maturity, frequency, [basis]) — the family's shared four arguments; Microsoft's argument descriptions and the five-row basis table are quoted on COUPNCD.

Unlike its five siblings, COUPDAYS genuinely depends on basis in the strongest possible way: basis chooses between "measure the calendar" and "quote a convention". Omitting basis asks for 30/360, and gets a flat 360/frequency for every period of every bond.

Microsoft documents that "All arguments are truncated to integers."

Try it

Quirks

Why the answer is often not a whole number of days

Here is the thing that surprises readers: COUPDAYS does not always count days. For four of the five bases it returns a convention, and the convention need not be an integer.

So at frequency 2 the answers are 180, 180, 182.5 and 180 for bases 0, 2, 3 and 4 — and 182.5 is not a number of days that any calendar contains. Under basis 1 the answer really is a day count, and it varies from period to period: semiannual periods are 181, 182, 183 or 184 days long depending on which months they span and whether a leap day falls inside.

The logic is coherent once stated: a /360 or /365 basis declares a year length, and the coupon period is by definition a 1/frequency share of that declared year. Only actual/actual declines to declare one and measures the calendar instead. But the practical consequence is worth stating plainly: basis 2 is "actual/360", and yet COUPDAYS under basis 2 ignores the actual calendar entirely. The "actual" in the name governs the numerator, not the denominator.

The additivity that fails

The obvious identity is

COUPDAYBS + COUPDAYSNC  =  COUPDAYS

The reason follows directly from the table above and the corresponding one on COUPDAYBS: under bases 2 and 3 the two numerator functions count real calendar days, while COUPDAYS returns a declared 180 or 182.5. A 184-day real period reported as a 180-day period cannot have its two halves add up.

This is not a defect in the family so much as a fault line running through it, and it has already produced observable divergence upstream. The Handbook's research record documents a case in the same bond model where Excel's PRICE derives its days-to-next-coupon as period length minus accrued days rather than counting them directly — which agrees with COUPDAYSNC on 30/360 and actual/actual bases, apart from the basis 0 cases of FN-COUPDAYSNC-001, and disagrees with it, by whole days, on actual/360 and actual/365. Excel published both numbers, in the same workbook, for the same bond. That episode is exactly this additivity failure, seen from the consumer's side.

The reader's practical rule: on bases 0, 2 and 3, do not derive any of the three day counts from the other two. Ask for each one.

Result and edge cases

Returns a Number, which may be fractional (182.5 at basis 3, frequency 2) and is not always a possible day count.

• Basis 1 across a leap day gives a longer period than the same schedule position in a non-leap year. This is the only basis under which COUPDAYS varies with the settlement date at all.
• frequency 1, 2, 4 are the only admitted values, so the constants are 360, 180, 90 (bases 0, 2, 4) and 365, 182.5, 91.25 (basis 3).
• 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. Three of the five answers are exact in binary; one is not. 360, 180, 90, 365 and 91.25 are all exactly representable; 365/2 = 182.5 is exact too. In fact all ten constants are exactly representable, so COUPDAYS itself has no rounding error — but the quotients built from it do, and forming a / (365/frequency) is not the same as forming a × frequency / 365. A consumer targeting Excel compatibility must pin which.
2. Basis 1's answer is an integer day count and can be computed in integer arithmetic throughout. Doing so removes a whole class of bugs.
3. The period-start anchor for basis 1 is a decision. Whether the actual length is measured from the clamped period start or from the raw backward step is a real fork in the reference engine's own code. Excel's rule is in FN-COUPDAYS-001: a clamped walk back from maturity, with month ends kept only on the last step.
4. Do not implement COUPDAYS as a subtraction of the two other counts, or vice versa. The additivity failure above means each of the three has to be computed on its own terms. The upstream research record shows what happens when a production implementation takes the shortcut.
5. There is no argument reduction, no cancellation and no transcendental here. Every difficulty in this function is definitional.

Rust code

11 coupdays_fn "COUPDAYS" (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::coupdays_kernel(settlement, maturity, frequency, b))
}

fn coupon_day_count(
    start: i64,
    end: i64,
    basis: CouponBasis,
    frequency: i64,
) -> Result<f64, WorksheetErrorCode> {
    match basis {
        CouponBasis::Us30_360 => Ok(360.0 / frequency as f64),
        CouponBasis::ActualActual => Ok(actual_days(start, end)),
        CouponBasis::Actual360 => Ok(360.0 / frequency as f64),
        CouponBasis::Actual365 => Ok(365.0 / frequency as f64),
        CouponBasis::European30_360 => Ok(360.0 / frequency as f64),
    }
}

pub fn coupdays_kernel(
    settlement: f64,
    maturity: f64,
    frequency: f64,
    basis: Option<f64>,
) -> Result<f64, WorksheetErrorCode> {
    let ctx = parse_coupon_context(settlement, maturity, frequency, basis)?;
    if ctx.basis == CouponBasis::ActualActual {
        // COUPDAYS has its own recorded calendar interval. Walk backwards
        // with ordinary clamped month shifts; a February visit can reduce
        // the day used by every earlier step. At the next coupon, measure
        // one period backwards with month-end preservation. Reusing the
        // anchored COUPNCD/COUPPCD pair changes these integer day counts.
        // Original Excel20131/20430 readings:965/965 Actual/Actual outcomes in the
        // R24 finite Gregorian study. No numerical tolerance enters this rule.
        let mut next = ctx;
        loop {
            let previous = coupon_date_before_maturity(next, 1, false)
                .ok_or(WorksheetErrorCode::Num)?;
            if previous <= ctx.settlement {
                let start = coupon_date_before_maturity(next, 1, true)
                    .ok_or(WorksheetErrorCode::Num)?;
                return Ok(actual_days(start, next.maturity));
            }
            next.maturity = previous;
        }
    }
    let period = locate_coupon_period(ctx)?;
    coupon_day_count(period.previous, period.next, ctx.basis, ctx.frequency)
}

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

• COUPDAYBS and COUPDAYSNC — the numerators this function is the denominator of. Read the additivity section above before assuming they sum to it.
• COUPNCD and COUPPCD — the two dates that bound the period whose length this function reports. Note that COUPNCD − COUPPCD is the actual length, which equals COUPDAYS only under basis 1, and not always there: FN-COUPDAYS-001 records 184 against 183.
• COUPNUM — how many such periods remain.
• PRICE, YIELD, DURATION, MDURATION, ODDFPRICE — the consumers, all of which form COUPDAYSNC / COUPDAYS as a discounting exponent or COUPDAYBS / COUPDAYS as an accrual fraction.
• YEARFRAC — the general day-count engine. COUPDAYS is not YEARFRAC scaled: YEARFRAC always measures a real interval, while COUPDAYS mostly quotes a convention.
• Confused with: COUPNCD(...) − COUPPCD(...), which is the actual number of days in the period and differs from COUPDAYS on every basis except 1, and on basis 1 in the cases FN-COUPDAYS-001 records.

Named variants

No named variants are recorded yet.