One salary review, three implementations, one referee

I built a salary review cost calculator for compensation teams. First it closes the part of the gender pay gap that grade and tenure don't explain. Then it works out merit increases person by person and shows what the review costs this year and in next year's budget. The page runs in the browser and makes no network requests.

Live page: https://olgadimitrovagit.github.io/comp-budget-lab/merit-matrix-calculator/

Reward teams check numbers in Excel, so the calculator also ships as a workbook with live formulas, one row per employee. That gives me the same rules written three times: a JavaScript engine behind the page, Excel formulas, and a Python reference. If the page and the workbook disagree by a euro, the reader stops trusting both.

The referee

reference_calc.py is plain Python with no interface. The page and the workbook are never compared with each other. Each is compared with the reference.

• The workbook. build_model.py writes it with openpyxl. openpyxl stores formulas but not their results, so a freshly built file has no computed values at all. I recalculate it in real Excel. Then check_model.py reads the values back and fails on any cell that starts with #. It also compares the summary with the reference: one cent for money, 1e-6 for shares.
• The page. check-engine.mjs runs the JavaScript engine and the reference on five scenarios: the demo with default settings, a January review with no cap, a December 2028 review with custom rating targets, a November 2026 review with different contribution rates, and every eighth employee of the demo. It compares 5,645 values and finds no differences.
• The check of the check. check-engine.mjs --selftest changes one rate on purpose and expects to fail. It does, with 16 differences. A check that passed after that change would also miss a real error.

What the referee missed

While reviewing the workbook, I changed the review date on the Inputs sheet and the calculation broke.

Eligibility depends on complete months since hire and since the last pay change. In Excel that is DATEDIF, and DATEDIF returns #NUM! when the start date is later than the end date. Some demo employees had a hire or pay change date after the date I picked. The error ran along their rows and from there into the totals.

The Python reference has the same rule and no such problem. Its month count just goes negative. A negative count is below the minimum, so the person is not eligible. The two implementations agreed on every input the checks used, because none of those inputs had a date after the review date. The reference only catches differences on the data you run through it.

The fix was small:

=IF(hire > EffDate, -1, DATEDIF(hire, EffDate, "m"))

A date after the review date now means "not eligible for merit" in all three places. The demo data no longer has dates after October 2026, and the review date is picked from a list that starts in November 2026.

What I'd keep

• One reference that both the page and the workbook are checked against, instead of comparing them with each other.
• Real Excel for the recalculation. Without it the workbook check reads empty cells.
• A self-test for every check.
• Edge cases from the person who will use the tool. Mine showed up when I changed one date, which is the first thing a compensation manager does with a workbook like this.

The data is synthetic: 212 employees, Spain, salary ranges for 2026, contribution rates for Spain 2026.

Repo, workbook, sample CSV files, reference and checks: https://github.com/OlgaDimitrovaGit/comp-budget-lab/tree/main/merit-matrix-calculator