FunctionsLookup — compatibility and validation
81 functions: 10 lookup & reference, 22 dynamic-array, 49 financial. Every registered function passes
all of its reference cases (swift test, 86 965 formula cases + 1 397 YEARFRAC rows + hand-written tests).
Reference sources
| tag | source | cases |
|---|---|---|
| XL | Values computed by Microsoft Excel: test data of ExcelFinancialFunctions (fsprojects, Copyright 2010-2013, Apache License 2.0), converted by Generators/gen_excel_reference.py into Fixtures/excel_reference.json (dates → serials, enumerations → Excel codes; nothing else changed, nothing thinned) |
68 075 + 1 397 YEARFRAC |
| LO | LibreOffice Calc 26.8.2 headless recalculation of generated workbooks (Generators/lo_harness.py) |
14 223 |
| PY | Independent Python computations (Generators/pyref.py, gen_arrays.py): closed forms, period-by-period simulations, explicit cash-flow discounting, scipy.optimize.brentq |
17 874 (13 207 of them also confirmed by LO) |
| DOC | Worked examples of Microsoft's function reference, typed by hand in FinancialTests.swift |
90 |
| HW | Hand-written Excel-semantics tests (LookupTests, DynamicArrayTests, FinancialTests, PublicAPITests) |
≈ 500 assertions |
The generators fail when LibreOffice and Python disagree without a recorded reason. Where LibreOffice
is known not to match Excel, the case keeps the Python value only and the fixture records the
LibreOffice value and the reason (4 472 cases, listed per function below). Each such reason was
confirmed against the Excel data. LibreOffice's xlsx export reports most errors as #VALUE! and
logical values as 1/0, so fixtures check "is an error" (exact kind only for #N/A and #DIV/0!);
exact Excel error kinds are asserted in the hand-written tests.
Comparison tolerance: relative 1e-9 (1e-6…1e-8 for iterative results against Excel/LibreOffice, whose solvers stop earlier than ours).
Lookup & reference
| function | validation | notes / deviations from Excel |
|---|---|---|
| VLOOKUP, HLOOKUP | LO 198, HW | Approximate match = binary search on ascending data (last of equal items); exact match linear with *, ?, ~. An empty range_lookup placeholder (VLOOKUP(a,b,2,)) means FALSE. Text never matches numbers ("5" ≠ 5) and logical values only match logical values — LibreOffice differs on both. |
| LOOKUP | LO 77, HW | Vector and array forms. A result vector shorter than the match position gives #N/A (Excel reads past the end of a range). |
| MATCH | LO 130, HW | Types 1, 0, −1. Type −1 is a binary search on descending data. On unsorted data approximate modes return a position that may differ from Excel's (undefined behaviour in both). Blank cells and errors inside the range are skipped by the binary search. |
| XMATCH, XLOOKUP | LO 365, HW | Match modes 0, −1, 1, 2; search modes 1, −1, 2, −2. Binary modes return the first of several equal items on ascending data and the last on descending data (LibreOffice's behaviour; Excel documents none). Modes −1/1 with a linear search compare only items of the lookup value's type. An empty lookup value matches empty cells and "". XLOOKUP returns a reference when return_array is a reference and there is one lookup value. With several lookup values each result is the first cell of the matching row/column. |
| ADDRESS | LO 66, HW | abs_num 5–8 accepted as 1–4. Sheet names are quoted when they are not plain identifiers. |
| AREAS | LO 3, HW | Always 1: SheetCore has no multi-area (union) references. |
| HYPERLINK | HW | Returns the friendly name (or the location); the link itself is not stored — cell hyperlinks are a model feature. LibreOffice returns a two-cell array and is not used as reference. |
| TRANSPOSE | LO 7, HW | — |
Text comparison is case-insensitive and uses a fixed collation shared with the core (TextOrder): accented letters sort next to their base letter but stay distinct ("é" ≠ "e", punctuation is
significant); Excel uses the system locale's collation. Performance: whole-column references are
clamped to the used range and only the lookup column and the hit row are read. A hash index is built
when one call carries at least 4 lookup values against at least 16 cells (XLOOKUP(E2:E5000, A:A, B:B)
is one index build + 5 000 probes; measured: 2 000 lifted lookups over 200 000 rows < 1.5 s in a debug
build, a single whole-column lookup < 0.4 s). A cache shared between cells is not implemented: it
cannot be invalidated safely without a recalculation generation in SheetCore (see the report).
Dynamic arrays
| function | validation | notes / deviations from Excel |
|---|---|---|
| FILTER | LO+PY 22, HW | include must be a column of rows items or a row of columns items; numbers ≠ 0 count as TRUE; text is #VALUE!. Empty result: if_empty or #CALC!. |
| SORT, SORTBY | LO+PY 35, PY 6, HW | Stable. Order: numbers < text < FALSE < TRUE < errors, empty cells last in both directions (the rule of Excel's Sort command; the position of errors and empty cells was not verified against the SORT function). Mixed-type cases are Python-only (LibreOffice sorts logical values as numbers). |
| UNIQUE | LO+PY 13, PY 2, HW | Case-insensitive; 1 ≠ "1"; an empty cell, "" and 0 are three distinct values (Excel's handling of empty cells versus "" was not verified). |
| SEQUENCE | LO+PY 11, PY 2, HW | SEQUENCE(0) → #CALC!, negative size → #VALUE!. |
| TAKE, DROP | LO+PY 68, HW | Zero count / nothing left → #CALC!. |
| CHOOSEROWS, CHOOSECOLS | LO+PY 23, HW | Index 0 or out of range → #VALUE!. |
| EXPAND | LO+PY 9, PY 1, HW | Smaller target → #VALUE!; default padding #N/A. |
| HSTACK, VSTACK | LO+PY 13, HW | Padding #N/A. |
| TOCOL, TOROW | LO+PY 21, PY 1, HW | Everything ignored → #CALC!. |
| WRAPROWS, WRAPCOLS | LO+PY 27, HW | Non-vector → #VALUE!; wrap_count < 1 → #NUM!. |
| MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY | PY 40, HW | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
Scalars are accepted wherever an array is (SORT(5)), unlike LibreOffice. Empty cells inside results
display 0 once spilled (SheetCore rule, same as Excel).
Financial
"XL" = number of Excel-computed cases passing; "LO+PY" = cases on which LibreOffice and the Python reference agree and we match both; "PY (LO≠)" = Python-only cases where LibreOffice is known to differ from Excel.
| function | XL | LO+PY | PY (LO≠) | other | notes / deviations from Excel |
|---|---|---|---|---|---|
| PMT / PV / FV | 1 752 / 1 842 / 2 026 | 483 / 450 / 451 | DOC | — | |
| NPER | 855 | 99 | DOC | No solution → #NUM!. |
|
| RATE | 8 | 449 | PY 73, DOC, HW | Root always found when one exists (Excel gives #NUM! after 20 Newton steps). 73 cases where LibreOffice's solver fails or stops on a non-root are Python-only. |
|
| IPMT / PPMT | 4 552 / 4 552 | 363 / 362 | DOC | — | |
| CUMIPMT / CUMPRINC | 210 / 210 | 103 / 103 | DOC | Fractional bounds: ⌈start⌉…⌊end⌋ (Excel). | |
| ISPMT | 626 | 48 | DOC | — | |
| EFFECT / NOMINAL | 14 / 14 | 38 / 38 | DOC | Rate ≤ 0 → #NUM! (LibreOffice returns 0). |
|
| PDURATION / RRI | 23 / 31 | 40 / 50 | DOC | RRI(n, 0, 0) = 0. |
|
| DOLLARDE / DOLLARFR | 14 / 14 | 91 / 91 | DOC | — | |
| NPV | 23 | 96 | DOC, HW | — | |
| IRR | 11 | 41 | DOC, HW | See "iterative functions" in FINANCIAL-CONVENTIONS.md. With multiple roots the result may differ from Excel's when the guess is far from every root. | |
| MIRR | 149 | 49 | DOC | — | |
| XNPV | — | 61 | DOC, HW | No Excel data set: LibreOffice + Python + documentation example. Rates > −1 accepted. | |
| XIRR | 26 | 21 | DOC, HW | — | |
| FVSCHEDULE | 14 | 7 | DOC | — | |
| SLN / SYD | 26 / 134 | 8 / 53 | DOC | SYD accepts 0 < per ≤ life (fractions too). | |
| DB | 398 | 236 | DOC | Period life + 1 allowed when month < 12. |
|
| DDB | 458 | 215 | DOC | Fractional periods follow Excel (LibreOffice differs). | |
| VDB | 2 546 | 297 | LO 168, DOC | — | |
| COUPDAYBS / COUPDAYS / COUPDAYSNC | 917 / 17 / 917 | 474 / 456 / 433 | 0 / 0 / 23 | DOC | COUPDAYSNC in 30/360 around the end of February: Excel's own count (LibreOffice differs). |
| COUPNCD / COUPPCD / COUPNUM | 917 / 917 / 917 | 474 / 456 / 474 | DOC | Coupon dates are derived from maturity directly (see below). | |
| PRICE | 10 982 | 1 409 | 848 | DOC | Time to next coupon (E − A)/E; simple interest in the last period. yld < 0 → #NUM!. |
| YIELD | 441 | 1 400 | 751 | DOC | The Excel set covers negative yields; positive yields rest on PRICE (10 982 Excel cases) being inverted, LO+PY and the documentation example. |
| DURATION / MDURATION | 5 492 / 5 492 | 585 / 585 | 1 220 / 1 220 | DOC | LibreOffice measures time with YEARFRAC × frequency and misses Excel's documented 5.993775. |
| ACCRINT | 1 923 | 200 | 122 | PY 45, DOC | Excel data covers settlement ≤ first_interest with calc_method TRUE. Not covered by Excel data: settlement after first_interest (TRUE: same per-period rule continued; FALSE: accrual from first_interest, as documented) — Python model only. |
| ACCRINTM | 362 | 125 | 20 | DOC | — |
| DISC / YIELDDISC | 2 747 / 2 747 | 213 / 211 | DOC | — | |
| INTRATE / RECEIVED / PRICEDISC | 2 749 / 1 290 / 3 662 | 121 / 121 / 121 | 20 / 20 / 20 | DOC | RECEIVED with discount × years ≥ 1 → #NUM!. |
| PRICEMAT / YIELDMAT | 1 942 / 2 912 | 359 / 359 | 62 / 62 | DOC | DSM = DIM − A, B from issue → settlement (Excel; LibreOffice uses three YEARFRACs). |
| TBILLEQ / TBILLPRICE / TBILLYIELD | 50 / 83 / 71 | 14 / 14 / 14 | 20 / 32 / 32 | PY 18, DOC | Actual days; TBILLEQ switches formula beyond 182 days (Excel data includes such bills). Maturity equal to settlement → #NUM!. |
Known limits and deviations (financial)
- Dates given as text (
"2024-01-31") are#VALUE!; Excel parses them. Use DATE() or a cell. - Coupon dates when maturity falls on the 29th or 30th and is not a month end (e.g. 30 Aug): we keep the maturity day wherever the month allows (28 Feb, 30 Aug, 28 Feb, 30 Aug …), as LibreOffice does. Neither the Excel data set nor the documentation contains such a maturity; an Excel-compatible library derives each date from the previous one (28 Feb, 28 Aug, …). Unverified against Excel.
- Iterative results are more precise than Excel's (Excel stops at 1e-7) and are found in cases
where Excel reports
#NUM!. - Error kinds for invalid arguments follow Excel's documentation (
#NUM!); the Excel data set contains valid inputs only (plus PDURATION/RRI failures), so error cases are validated by documentation and LibreOffice ("is an error"). PRICErejects negative yields (documented Excel behaviour); recent Excel builds accept them.- ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC, AMORLINC and GETPIVOTDATA are out of
scope and not registered. ISOMITTED is not part of this package either, but it exists in the
application: FunctionsNumeric registers it (
Information.swift).
Attribution
Tests/FunctionsLookupTests/Fixtures/excel_reference.json is derived from the test data of
ExcelFinancialFunctions (https://github.com/fsprojects/ExcelFinancialFunctions), licensed under the
Apache License, Version 2.0 (http://www.apache.org/licenses/LICENSE-2.0). The file states its origin
and the changes made. The day-count rules that Excel applies inside its bond functions (US 30/360
variants, (E − A)/E, ACCRINT per quasi-coupon period, the T-bill formula beyond 182 days) were
learned from that project's documentation of Excel's behaviour and re-implemented here in Swift.
Regenerating the fixtures
cd Tests/FunctionsLookupTests/Generators
/opt/homebrew/bin/python3 gen_lookup.py # LibreOffice
/opt/homebrew/bin/python3 gen_arrays.py # Python model + LibreOffice
/opt/homebrew/bin/python3 gen_financial.py # LibreOffice + Python
/opt/homebrew/bin/python3 gen_securities.py # LibreOffice + Python
/opt/homebrew/bin/python3 -I gen_excel_reference.py <dir with the downloaded Excel data> # see the script header