COUPPCD in Collabora Office: Find the Coupon Date Before Settlement
Use Collabora COUPPCD to identify a regular coupon-period start, compare payment frequencies, and audit accrued-day calculations with explicit dates.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =COUPPCD(DATE(2001;1;25);DATE(2001;11;15);2;3) has documented date result 2000-11-15. It returns the interest-payment date prior to settlement, not the issue date or the settlement date itself.
Syntax and schedule inputs
=COUPPCD(Settlement; Maturity; Frequency [; Basis])
Settlement is the purchase/settlement date. Maturity is the expiry date. Frequency is required and permits 1, 2, or 4 payments per year. Basis is optional and defaults to zero.
| Basis | Documented convention |
|---|---|
| 0 or omitted | US NASD, twelve 30-day months |
| 1 | Actual days in months and actual days in the year |
| 2 | Actual days in months, 360-day year |
| 3 | Actual days in months, 365-day year |
| 4 | European method, twelve 30-day months |
Use genuine date values or DATE formulas. The examples below use explicit bases and ordinary dates so the date and year conventions remain visible. A day-count basis is part of the calculation model, not a display preference.
Annual, semiannual, and quarterly frequencies are 1, 2, and 4. Do not enter 6 to mean a six-month interval; the number represents payments per year, so a six-month cycle uses 2.
The function has no issue-date, first-coupon, or holiday argument. It works from the supplied regular schedule inputs; it cannot infer a special first coupon or an observed bank-processing date.
Example: reproduce the documented prior coupon
Enter =DATE(2001;1;25) in A2, =DATE(2001;11;15) in B2, 2 in C2, and 3 in D2. In E2 enter =COUPPCD(A2;B2;C2;D2) and apply YYYY-MM-DD date formatting.
The documented display is 2000-11-15. The following regular coupon is 2001-05-15, six months later. January 25 falls between those two coupon dates.
As an independent check, =A2-E2 gives 71 actual days. That agrees with the companion COUPDAYBS control in the same vendor help. Keep this day-count audit in a separate numeric cell rather than formatting the returned coupon date as a general number.
Example: compare three frequencies for one settlement
For settlement 2026-10-17, maturity 2028-01-15, and basis three, independently modeled regular schedules give:
| Frequency | Prior coupon date |
|---|---|
| 1 | 2026-01-15 |
| 2 | 2026-07-15 |
| 4 | 2026-10-15 |
Put the frequencies in C5:C7 and use corresponding COUPPCD formulas. These are independent schedule expectations, not native execution results. Frequency changes the period containing settlement even though the maturity date stays the same.
For the quarterly row, October 15 is just two actual days before October 17. For the semiannual row, July 15 is 94 actual days before settlement. A smaller elapsed interval is a consequence of a different coupon schedule, not necessarily a formula mistake.
Advanced application: build a period-audit block
For the semiannual 2026 example, store the independently modeled prior date 2026-07-15 and next date 2027-01-15 in E5 and F5. Then inspect:
=A5-E5
=F5-A5
=F5-E5
The independent results are 94, 90, and 184 actual days. The first two sum to the third. This audit helps identify a wrong assumed boundary before a later interest calculation uses it.
Do not force that calendar total to equal a convention-defined COUPDAYS denominator: the vendor’s basis-three semiannual example uses 365/2, or 182.5, as its period measure. Actual dates and denominator conventions answer different questions.
Advanced use: check a returned date’s position
For these nonboundary examples, =AND(E5<A5;E5<B5) should be TRUE. AND combines the checks. This simple guard can catch accidentally swapped input dates or a wrong comparison column.
It is not a complete validation of the schedule. A date months too early could still satisfy both inequalities. Preserve frequency, maturity, and the expected pattern of month/day boundaries for a stronger review.
MONTH and YEAR can expose date components for auditing, but a month number alone loses the year and cannot reliably order a schedule across New Year.
Errors and limitations
If the result appears as an integer, apply date formatting. Verify genuine date inputs, settlement before maturity, and a permitted frequency. The reviewed section does not comprehensively define text coercion, exact error codes, month-end behavior, or settlement exactly on a coupon date.
The worked cases deliberately avoid those boundaries. For an irregular first or last coupon, obtain the actual schedule instead of treating a regular prior date as proof that a payment really occurred on that date.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.
The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.
References
- Collabora Office 26.04: COUPPCD (checked October 17, 2026).
- Editorial testing standards.