Function note

COUPNUM is #NUM! when the coupon before settlement falls before 1900

FUNC.COUPNCD · FUNC.COUPNUM · FUNC.COUPPCD

FN-COUPNUM-001: COUPNUM is #NUM! when the coupon before settlement falls before 1900

Quirk · measured · recorded 2026-11-04

If the coupon date before settlement, counted back from maturity, falls before 1 January 1900, COUPNUM gives #NUM!. The other coupon functions still answer for the same inputs: COUPPCD returns 0, COUPNCD the next coupon, and COUPDAYS, COUPDAYBS and COUPDAYSNC a day count. With maturity serial 400 (1901-02-03) and semi-annual coupons, the coupon dates are serials 34 and 216: settlement 33 gives #NUM!, settlement 34 gives 2. With maturity 367, settlement 0 gives #NUM! and settlement 1 gives 2. This covers settlement 0 too: COUPNUM(0,m,2,0) was #NUM! for every maturity read.

Call and input routeInputsExcelCorrect valueRecorded in
COUPNUM(33, 400, 2, 0)
cell-value2		settlement: 33
0x4040800000000000
maturity: 400
0x4079000000000000
frequency: 2
0x4000000000000000
basis: 0
0x0000000000000000		#NUM!—Excel 16.0.20527.20088, Windows, bitness and CPU not recorded in this reading file
COUPNUM(34, 400, 2, 0)
cell-value2		settlement: 34
0x4041000000000000
maturity: 400
0x4079000000000000
frequency: 2
0x4000000000000000
basis: 0
0x0000000000000000		2
0x4000000000000000		—Excel 16.0.20527.20088, Windows, bitness and CPU not recorded in this reading file
COUPNUM(0, 367, 2, 0)
cell-value2		settlement: 0
0x0000000000000000
maturity: 367
0x4076f00000000000
frequency: 2
0x4000000000000000
basis: 0
0x0000000000000000		#NUM!—Excel 16.0.20527.20088, Windows, bitness and CPU not recorded in this reading file
=COUPNUM(0,182,2,0)
formula-literal		As typed#NUM!—Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), x86-64, AMD Ryzen 7 4800H, en-ZA
=COUPNUM(A1,400,2,0)
cell-value2		A1: 0
0x0000000000000000		#NUM!—Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), x86-64, AMD Ryzen 7 4800H, en-ZA
=COUPPCD(A1,400,2,0)
cell-value2		A1: 0
0x0000000000000000		0
0x0000000000000000		—Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), x86-64, AMD Ryzen 7 4800H, en-ZA
=COUPNCD(A1,400,2,0)
cell-value2		A1: 0
0x0000000000000000		34
0x4041000000000000		—Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), x86-64, AMD Ryzen 7 4800H, en-ZA

Basis: The rule matches all 429 recorded COUPNUM readings (325 #NUM!, 104 counts) when recomputed independently from the calendar, and the 24 COUPNUM readings of the date-boundary sheets. COUPPCD, COUPNCD, COUPDAYS, COUPDAYBS and COUPDAYSNC companions come from the same sheets.

Evidence and capture sources

Machine-readable note records