COUPNCD

What it does

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

COUPNCD(settlement, maturity, frequency, [basis]) returns the next coupon date strictly after the settlement date — as a date serial, which Excel's date formatting renders as a date.

Microsoft's Learn page describes it as returning "a number that represents the next coupon date after the settlement date", and declares the return type as Double. It is a date-valued function returning a plain number.

The whole content of the function is the schedule it derives, so this page hosts the description that the other five coupon functions refer to.

Arguments

COUPNCD(settlement, maturity, frequency, [basis]) — the same four arguments as every other member of the family.

The argument descriptions are quoted from Microsoft's Learn page.

basis is documented on this page with the standard five-row table:

basis cannot change this function's answer. A coupon date is a calendar date; no day-count convention moves it. COUPNCD nevertheless accepts basis and validates it, so an out-of-range basis turns a perfectly well-defined date into an error. That is a real, observable dependency on an argument that does not participate in the computation — worth knowing when a COUPNCD formula starts erroring after someone edits a shared basis cell.

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

The coupon schedule

None of the six coupon functions is given a coupon schedule. They are given a maturity date and a frequency, and they reconstruct the schedule from those two facts:

the coupon dates are  maturity,  maturity − p,  maturity − 2p,  maturity − 3p, …
where p = 12 / frequency  months

Coupon dates are therefore anchored at maturity, counted backwards, and there is no issue date to stop the schedule; FN-COUPNUM-001 records what Excel does when it runs back past the start of 1900. settlement selects a period from that ladder: the one whose start is at or before settlement and whose end is after it.

Three consequences do most of the work on all six pages:

1. The day of the month comes from maturity, not from settlement. A bond maturing on the 15th has coupons on the 15th. Everything else about the trade is irrelevant to the schedule.

The schedule is quasi-periodic, not periodic: because the month-end rule clamps, stepping back n periods is not always the same as stepping back one period n times from an intermediate date. Implementations that step iteratively and implementations that compute the offset in one jump can disagree.

Result and edge cases

• settlement on a coupon date returns the following coupon date under the reference engine, not settlement itself. See the schedule section above; this is undocumented.
• settlement one day before maturity returns maturity, which is always a coupon date by construction.
• settlement ≥ maturity is a documented error.
• Month-end maturities propagate month-end coupon dates: a 31 August maturity at frequency 2 gives a 28 or 29 February coupon, not a 28th-of-the-month coupon.
• Empty, missing and error arguments follow the shared call model.

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

There is no floating-point content here worth the name: the answer is an integer-valued date serial. The engineering content is all in the schedule.

1. Iterate or jump, but be consistent. Stepping back one period at a time and computing the period index directly give different answers under a clamping month rule. The reference engine iterates.
2. The month-end predicate is a decision, not a fact. "Is this date the last day of its month?" is cheap; deciding that the answer should propagate to every generated date is a modelling choice with no documentary support.
3. Cost is linear in the settlement-to-maturity gap under an iterating implementation. A 30-year quarterly bond settled at issue costs 120 iterations, which is fine; a synthetic test with a maturity centuries out is not.
4. Do not route this through a day-count routine. basis must be validated and then ignored. An implementation that computes the answer via a year fraction will introduce basis-dependence into a function that has none.

Rust code

13 coupncd_fn "COUPNCD" (3, 4) [V "settlement", V "maturity", V "frequency", V "basis"] |s, a, sv| {
    let (settlement, maturity, frequency, b) = coupon_args(s, a, sv)?;
    date_out(s, sv, coupon::coupncd_kernel(settlement, maturity, frequency, b))
}

pub fn coupncd_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)?;
    Ok(period.next as f64)
}

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

• COUPPCD — the backward end of the same period. COUPPCD and COUPNCD bracket settlement, and together they define the current coupon period.
• COUPDAYBS, COUPDAYSNC, COUPDAYS — the three day counts taken against those two dates, though on basis 1 COUPDAYS can use another interval (FN-COUPDAYS-001). Note that the three do not always add up; see the additivity discussion on COUPDAYS.
• COUPNUM — how many coupon dates remain from settlement to maturity on the same ladder.
• PRICE, YIELD, DURATION, MDURATION — the consumers. They rebuild this same schedule internally rather than calling these functions, which is why a schedule disagreement between Excel's COUPNCD and Excel's PRICE is possible in principle and has been observed for the day counts in this family; see COUPDAYS.
• EDATE and EOMONTH — the general-purpose month-stepping functions. EDATE clamps the day of month; it does not implement "month-end sticks to month-end". Rebuilding a coupon schedule with EDATE therefore reproduces COUPNCD for ordinary maturities and diverges for month-end ones.
• Confused with: EDATE(maturity, ±n × 12/frequency), for exactly that reason.

Named variants

No named variants are recorded yet.