COUPDAYSNC

What it does

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

COUPDAYSNC(settlement, maturity, frequency, [basis]) returns the number of days from the settlement date to the next coupon date.

It measures the forward span while COUPDAYBS measures the accrued backward span. Bond pricing uses a related remaining fraction, but its internal numerator cannot always be replaced by the published COUPDAYSNC answer. The retained discrepancy below explains why COUPDAYSNC / COUPDAYS is not an unconditional PRICE discount recipe.

The next coupon date is what COUPNCD returns, derived from the schedule described on COUPNCD.

Microsoft's page documents the basis names, not this mapping; the table is the reference engine's.

Arguments

COUPDAYSNC(settlement, maturity, frequency, [basis]) — the family's shared four arguments, with Microsoft's wording quoted on COUPDAYBS.

The five-row basis table is documented on this page in the standard form — 0 or omitted = US (NASD) 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360 — and Microsoft documents that "All arguments are truncated to integers."

There is no issue date and no first-coupon date: like every member of this family, the function knows only maturity and frequency, so an odd first period is invisible to it. Odd-period instruments need ODDFPRICE and ODDFYIELD, which take the extra dates.

Try it

Quirks

The function Excel disagrees with itself about

COUPDAYSNC is the one member of this family where the Handbook's research record contains a concrete, published case of Excel returning two different answers to the same question in the same workbook.

The quantity "days from settlement to the next coupon" can be obtained two ways:

1. Directly — count the days from settlement to COUPNCD, on the basis. This is what COUPDAYSNC does, except that on basis 0 its answers follow P − A (FN-COUPDAYSNC-001).
2. By subtraction — take the period length and remove the days already accrued: COUPDAYS − COUPDAYBS.

On basis 4 and on actual/actual these agree, because COUPDAYS there is either the same 30/360 arithmetic or the real calendar length. On basis 0 they do not always agree: COUPDAYS is always 360/frequency where P need not be, so COUPDAYSNC(39923,50919,4,0) is 39 where 90 − 50 would give 40 (FN-COUPDAYSNC-001). On actual/360 and actual/365 they do not, because COUPDAYS reports a declared 360/frequency or 365/frequency while COUPDAYBS counts real days. The subtraction then yields a number that no calendar produces.

The Handbook's research record (EV-FIN-0029) documents Excel's PRICE taking route 2 — deriving days-to-next-coupon from the period length and the accrued days for every basis — while Excel's COUPDAYSNC, on the same bond and the same arguments, publishes route 1. The two numbers differ by whole days at bases 2 and 3, and the resulting prices differ in the first decimal place, not in the last bit. Both of Excel's answers are deterministic and reproducible; they are simply different functions of the same inputs.

For a reader the operative consequence is: COUPDAYSNC is not a safe substitute for whatever PRICE is using internally. If you are reconciling a price, reconcile against the price, not against this function's day count.

The general form of this fault line — that COUPDAYBS + COUPDAYSNC ≠ COUPDAYS on bases 2 and 3 — is set out on COUPDAYS.

Result and edge cases

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

• settlement one day before maturity returns 1 on the actual bases, since maturity is always a coupon date.
• On bases 2 and 3 the answer can exceed COUPDAYS — a "days remaining" larger than the reported period length. That is not a bug in this function; it is the additivity failure seen from this side.
• The 30/360 bases can return a number that no calendar interval matches, by construction.
• 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. Integer arithmetic throughout. Every answer is a whole number of days; compute it in integers and convert once.
2. Never derive it by subtraction. The whole content of the section above is that the derived and the direct forms are different functions. An implementation that computes COUPDAYS − COUPDAYBS will match Excel's PRICE and contradict Excel's COUPDAYSNC, or the reverse — it cannot do both.
3. Share one 30/360 routine, and pin the order of its two adjustments; see COUPDAYBS.
4. The roll-forward branch dominates the boundary behaviour and should be decided and documented before any counting code is written.
5. There is no rounding, cancellation or transcendental content in this function. Its difficulty is definitional and, unusually for this Handbook, it is a difficulty Excel itself demonstrably has.

Rust code

12 coupdaysnc_fn "COUPDAYSNC" (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::coupdaysnc_kernel(settlement, maturity, frequency, b))
}

pub fn coupdaysnc_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)?;
    if ctx.basis == CouponBasis::Us30_360 {
        // The whole period normalizes both actual coupon endpoints: day 31 and Gregorian
        // February ends become day 30. It need not equal COUPDAYS' 360/frequency when a
        // non-month-end maturity's day was clamped in February. Subtract the distinct US
        // accrual count, whose end adjustment depends on its original start day.
        // Fn7 paired date/accrual/remaining-day controls cover calendar zero, fictitious
        // February 29 and independent month-end schedules; all readings are retained.
        let coupon_days = calendar_days360(
            period.previous, period.next, CalendarDayAdjustment::CouponPeriod,
        )?;
        let accrued = accrued_or_remaining_days(period.previous, ctx.settlement, ctx.basis)?;
        return Ok(coupon_days - accrued);
    }
    accrued_or_remaining_days(ctx.settlement, period.next, 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

• COUPDAYBS — the backward complement. The pair partitions the period only on bases 1 and 4, and not always on basis 0 (FN-COUPDAYSNC-001).
• COUPDAYS — the denominator, and the source of the mismatch described above.
• COUPNCD — the date this function counts to. COUPNCD − settlement is the actual remaining days, equal to COUPDAYSNC on bases 1, 2 and 3 only.
• PRICE, YIELD, DURATION, MDURATION — the consumers, and, per the research record, not necessarily consumers of this function's answer.
• ODDFPRICE, ODDFYIELD — the odd-first-period instruments, which need the extra dates this family does not take.
• Confused with: COUPDAYS − COUPDAYBS, which is a different quantity on bases 2 and 3, and on basis 0 in the cases of FN-COUPDAYSNC-001, and is the one Excel's PRICE was observed to use.

Named variants

No named variants are recorded yet.