COUPPCD

What it does

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

COUPPCD(settlement, maturity, frequency, [basis]) returns the coupon date on or before the settlement date — the start of the current coupon period — as an Excel date serial.

It is the mirror of COUPNCD, and the two together bracket the period that COUPDAYBS, COUPDAYSNC and COUPDAYS measure. The schedule it selects from is described once, on COUPNCD: coupon dates run backwards from maturity in steps of 12/frequency months, with month-end anchoring, and there is no issue date to stop them.

The previous coupon date is notional, not historical. The schedule is generated from maturity alone, so COUPPCD will happily return a date before the security existed. That is the intended behaviour for accrual arithmetic on a seasoned bond, and it is a trap for anyone using the function to ask "when was the last coupon actually paid".

A documentation gap worth recording

The quoted one-line description is Microsoft's text, from its support article https://support.microsoft.com/en-us/office/couppcd-function-2eb50473-6ee9-4052-a206-77a9a385d5b3.

Arguments

COUPPCD(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. A coupon date is a calendar date; no day-count convention moves it. basis is nevertheless validated, so an out-of-range value converts a well-defined date into an error. The same non-participating-but-fatal argument appears on COUPNCD and COUPNUM.

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

Result and edge cases

• Month-end maturities produce month-end previous-coupon dates: a 31 August maturity at frequency 2 gives 28 or 29 February, not the 28th of some other month.
• Early settlements are accepted; the schedule extends backwards past any plausible issue date, and when the coupon before settlement falls before 1900 Excel returns 0 (FN-COUPNUM-001).
• settlement ≥ maturity is a documented error, so the function never returns maturity itself as a previous coupon date.
• 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

1. The answer is an integer-valued date serial; there is no floating-point content.
2. Month-end anchoring is a modelling decision with no documentary support, and it is where two careful implementations will part company. State the rule explicitly rather than inheriting it from whatever a date library happens to do.
3. Validate basis and then ignore it. Computing this date through a day-count routine would introduce a dependency the function does not have.
4. The roll-forward branch decides the equality case and should be settled before anything else, because it also fixes COUPDAYBS's zero and COUPNUM's count on the same input.

Rust code

15 couppcd_fn "COUPPCD" (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::couppcd_kernel(settlement, maturity, frequency, b))
}

pub fn couppcd_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.previous 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 forward end of the same period, and the page that hosts the shared schedule description.
• COUPDAYBS — the day count from this date to settlement. On bases 1, 2 and 3 it equals settlement − COUPPCD; on the 30/360 bases it does not.
• COUPDAYS — the period length. COUPNCD − COUPPCD is the actual length and equals COUPDAYS only under basis 1, and not always there (FN-COUPDAYS-001).
• COUPNUM — the count of remaining coupons on the same ladder.
• EDATE — the general month-stepping function. EDATE clamps the day of month but does not make month-ends sticky, so EDATE(maturity, −n × 12/frequency) reproduces COUPPCD for ordinary maturities and diverges for month-end ones.
• ACCRINT — the function that does know an issue date, for cases where the notional previous coupon date is the wrong accrual anchor.
• Confused with: "the date of the last coupon actually paid", which this function does not answer for a bond issued after the notional date it returns.

Named variants

No named variants are recorded yet.