COUPNUM

What it does

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

COUPNUM(settlement, maturity, frequency, [basis]) returns the number of coupons payable between the settlement date and the maturity date. Microsoft's Learn page adds the decisive clause: "rounded up to the nearest whole coupon".

That clause is the whole definition. A bond settled part-way through a coupon period still has that period's coupon ahead of it, so a partial period counts as a whole one. Equivalently, on the schedule described on COUPNCD — coupon dates counted backwards from maturity in 12/frequency-month steps — COUPNUM is the number of schedule dates strictly after settlement, up to and including maturity.

When it returns a number, that number is a positive integer, and it is the number of terms in the cash-flow sums that PRICE, YIELD, DURATION and MDURATION evaluate. An off-by-one here is an off-by-one coupon in every one of them.

The relationship to the rest of the family:

COUPNUM = 1 + (whole coupon periods between COUPNCD and maturity)

so the first of the counted coupons falls on COUPNCD and the last on maturity.

Arguments

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

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

basis cannot change the answer. Counting coupon dates is calendar arithmetic; no day-count convention adds or removes a date from the schedule. basis is nevertheless validated and an out-of-range value turns a well-defined count into an error — the same non-participating-but-fatal argument that appears on COUPNCD and COUPPCD.

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

Try it

Quirks

Result and edge cases

Returns a Number: a positive integer, or #NUM! when the coupon before settlement falls before 1900 (FN-COUPNUM-001).

• settlement one day before maturity returns 1: the maturity coupon.
• The answer is independent of basis on every input where it is defined.
• settlement ≥ maturity is a documented error, which is why the answer is never 0.
• Month-end maturities change which dates the coupons fall on but not how many there are, except at the boundary where a shifted date crosses settlement.
• 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. The answer is an integer and should be computed as one. Deriving it from a year fraction and rounding is the classic way to introduce an off-by-one at period boundaries: a floating-point year fraction that lands a hair below an integer rounds the wrong way.
2. The roll-forward branch changes the answer by a whole unit, and the limit at 1900 in FN-COUPNUM-001 is a second boundary. The branch is decided before any counting, and it is shared with the rest of the family — one probe fixes all six functions.
3. Cost is linear in the answer. For a synthetic instrument maturing centuries out, an iterating implementation does real work; a consumer computing COUPNUM inside a solver loop will feel it.
4. Validate basis and ignore it. There is nothing for a day-count routine to do here.

Rust code

14 coupnum_fn "COUPNUM" (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::coupnum_kernel(settlement, maturity, frequency, b))
}

pub fn coupnum_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)?;
    // Argument errors are resolved before this calendar limit. Fn4 qd225..qd236 and
    // fn7 COUPPCD/COUPNUM paired controls retain the zero-previous-coupon boundary.
    if period.previous == 0 {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(period.remaining_coupons 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

• COUPNCD — the date of the first of the counted coupons, and the page that hosts the shared schedule description.
• COUPPCD — the date of the coupon not counted, immediately before settlement.
• COUPDAYS, COUPDAYBS, COUPDAYSNC — the day counts within the first of the remaining periods. COUPNUM counts whole periods; those three subdivide the current one.
• PRICE, YIELD, DURATION, MDURATION — the consumers. Each evaluates a sum with COUPNUM terms, discounted at exponents built from COUPDAYSNC / COUPDAYS plus whole periods.
• NPER — the annuity-count function, which answers a superficially similar question (how many periods) from rates and payments rather than from dates. The two are not substitutes.
• Confused with: (maturity − settlement) / 365 × frequency rounded up, which agrees with COUPNUM most of the time and disagrees exactly where the schedule's month arithmetic matters.

Named variants

No named variants are recorded yet.