Reconcile Payroll to the General Ledger

Payroll is usually the largest single cost a business posts each month, and it is posted from one system — the payroll software — into another — the general ledger — through a journal that someone imports or types. Errors are rarely in the payroll calculation itself; they are in the posting: a new employee's cost centre mapped to the wrong department, employer pension posted to the wrong account, a manual adjustment made in payroll after the journal was exported. The monthly payroll reconciliation catches them by proving three things: that every payslip adds up, that the ledger received exactly what the payroll register says by account and cost centre, and that the net pay sent to the bank equals the net pay in the register. This page does all three with pandas and writes a workbook in which every difference is listed and explained. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Three files for September 2026: the payroll register (one row per employee with cost centre, gross pay, deductions, net pay and employer costs), the payroll journal as it was posted to the general ledger (account, cost centre, amount — debits positive, credits negative) and the bank payment file of net pay per employee. They come from three different systems, as they usually do.

Step 1 — Every payslip adds up

import pandas as pd

reg = pd.read_excel("payroll_register_2026-09.xlsx")
reg["Calculated net"] = (reg["Gross"] - reg["Tax"] - reg["Employee NI"] - reg["Employee pension"]).round(2)
reg["Net difference"] = (reg["Net"] - reg["Calculated net"]).round(2)
bad_slips = reg[reg["Net difference"] != 0]
print(f"{len(reg)} employees, gross {reg['Gross'].sum():,.2f}, net {reg['Net'].sum():,.2f}")
print(bad_slips[["Employee", "Gross", "Net", "Calculated net", "Net difference"]].to_string(index=False))

Gross pay minus tax, employee National Insurance and employee pension should equal net pay for every employee. One payslip does not: E107's net pay is 25.00 less than its components imply — typically a deduction such as a loan repayment or a salary sacrifice that the register export does not show as a column. It may be correct, but it must be explained, because it is exactly the kind of manual adjustment that later causes a ledger difference.

Step 2 — Map the register to the accounts it should hit

POSTINGS = [
    ("6100 Salaries", "Gross", 1, True),
    ("6110 Employer NI", "Employer NI", 1, True),
    ("6120 Employer pension", "Employer pension", 1, True),
    ("2210 PAYE and NI payable", ["Tax", "Employee NI", "Employer NI"], -1, False),
    ("2220 Pension payable", ["Employee pension", "Employer pension"], -1, False),
    ("2230 Net wages payable", "Net", -1, False),
]
expected = []
for account, cols, sign, by_cc in POSTINGS:
    amount = reg[cols].sum(axis=1) if isinstance(cols, list) else reg[cols]
    grouped = (reg.assign(a=amount).groupby("Cost centre")["a"].sum() if by_cc
               else pd.Series({"": amount.sum()}))
    for cc, value in grouped.items():
        expected.append({"Account": account, "Cost centre": cc, "Expected": round(sign * value, 2)})
expected = pd.DataFrame(expected)
print(expected.to_string(index=False))
print("expected journal balances to:", round(expected["Expected"].sum(), 2))

The mapping is the rule book of the payroll journal, written as data: gross pay and employer costs are expenses posted by cost centre; tax, National Insurance (employee and employer), pensions (employee and employer) and net pay are liabilities posted in total, as credits. Applied to the register, it produces the journal that should have been posted. Like any journal it should balance — debits equal credits — because every pound of gross pay and employer cost ends up owed to someone: the employee, the tax authority or the pension scheme. The expected journal is out of balance by exactly 25.00: E107's unexplained deduction from Step 1 is money taken from gross pay that the register does not assign to any liability, so the rules cannot post it. That is the second sign of the same problem, found before the ledger has even been looked at.

Step 3 — Compare with what was posted

gl = pd.read_excel("gl_payroll_journal_2026-09.xlsx").fillna({"Cost centre": ""})
posted = gl.groupby(["Account", "Cost centre"], as_index=False)["Debit"].sum().rename(columns={"Debit": "Posted"})
recon = expected.merge(posted, on=["Account", "Cost centre"], how="outer").fillna({"Expected": 0, "Posted": 0})
recon["Difference"] = (recon["Posted"] - recon["Expected"]).round(2)
differences = recon[recon["Difference"] != 0]
print(differences.to_string(index=False))
print("posted journal balances to:", round(posted["Posted"].sum(), 2))

An outer merge on account and cost centre keeps lines that appear on only one side — an account the journal used but the rules did not expect, or the other way round. Three groups of differences appear. Salaries for CC20 are too high and CC30 too low by the same amount: one employee's pay went to the wrong cost centre, exactly E112's gross pay, which points to a cost-centre mapping in the journal export that was not updated when the employee moved. Employer pension is missing from 6120 and the same total sits in 6130 Staff welfare: the right amount on the wrong account. And net wages payable was credited 25.00 more than the register's net pay — the same 25.00 as the unexplained payslip in Step 1: whoever built the posted journal balanced it by putting the missing amount into net wages. The posted journal balances perfectly, which is why none of this would be noticed without the reconciliation.

Step 4 — Net pay against the bank file, and the workbook

bank = pd.read_excel("bank_payment_file_2026-09.xlsx")
pay_check = reg[["Employee", "Net"]].merge(bank, on="Employee", how="outer", indicator=True)
pay_check["Difference"] = (pay_check["Amount"].fillna(0) - pay_check["Net"].fillna(0)).round(2)
print(f"bank file {bank['Amount'].sum():,.2f} vs register net {reg['Net'].sum():,.2f};",
      "unmatched employees:", int((pay_check["_merge"] != "both").sum()), "| amount differences:", int((pay_check["Difference"] != 0).sum()))

with pd.ExcelWriter("payroll_recon_2026-09.xlsx", engine="openpyxl") as xw:
    recon.to_excel(xw, sheet_name="GL reconciliation", index=False)
    EXPLAIN = {"6100 Salaries": "E112 posted to CC20, belongs to CC30",
               "6120 Employer pension": "employer pension posted to 6130 instead of 6120",
               "6130 Staff welfare": "employer pension posted to 6130 instead of 6120",
               "2230 Net wages payable": "net pay credited 25.00 above register — see payslip E107"}
    differences.assign(Explanation=differences["Account"].map(EXPLAIN)).to_excel(xw, sheet_name="Differences", index=False)
    bad_slips.to_excel(xw, sheet_name="Payslip checks", index=False)
    pay_check.drop(columns="_merge").to_excel(xw, sheet_name="Bank file", index=False)

The bank file matches the register employee by employee: every net payment that left the bank is in the register at the same amount, so the 25.00 difference is in the ledger journal, not in what was paid — and once the bank payment is posted, 25.00 will be left sitting on the net wages control account, where it would otherwise surface months later as an unexplained balance. The workbook gathers the full comparison by account and cost centre, the differences with an explanation for each, the payslip that does not add up and the bank comparison. The corrections — move E112's salary between cost centres, reclassify employer pension, investigate the 25.00 — go into an adjusting journal, and next month's run should show no differences at all. The same compare-two-sources structure underlies Build a Bank Reconciliation Report.

Making it a monthly routine

The value of this reconciliation comes from running it every month, the same way, before the ledger is closed. Keep the posting rules in a version-controlled file rather than in the script, so a change in pension provider or a new benefit is one reviewed edit. Store each month's workbook alongside the payroll reports; auditors ask for exactly this evidence. And track the differences themselves over time: a cost-centre mapping error that recurs every time someone changes department is a process problem in the payroll system, not twelve separate mistakes, and the reconciliation history is what reveals it.

Common pitfalls

• Comparing totals only. Totals can agree while cost centres and accounts are wrong.
• An inner merge. Use an outer merge so lines on only one side appear.
• Ignoring payslip arithmetic. Unexplained deductions resurface as ledger differences.
• Skipping the bank file. Prove that what was paid equals what was posted.
• Rules buried in code. Keep the posting rules as data and review them.

Conclusion

Reconcile payroll to the general ledger in three steps: check that every payslip's net pay equals gross minus deductions, apply the posting rules to the payroll register to build the journal that should have been posted and compare it with the actual journal by account and cost centre using an outer merge, and tie register net pay to the bank payment file. Write the comparison, the explained differences and the supporting checks to a workbook, and correct the ledger before closing the month.

Frequently asked questions

How do I reconcile payroll to the general ledger? Build the expected journal from the payroll register using the posting rules for each pay element, then compare it with the posted journal by account and cost centre and investigate each difference.

Why does my payroll journal balance but the reconciliation shows differences? A journal can balance while amounts sit on the wrong account or cost centre; comparing by account and cost centre finds those misclassifications.

What checks should a payroll reconciliation include? Payslip arithmetic, register against ledger by account and cost centre, and register net pay against the bank payment file.

How do I find lines that are only in the ledger or only in payroll? Merge the expected and posted amounts with how="outer" and treat missing values as zero, so one-sided lines show as differences.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Trial Balance from a General Ledger — the ledger side.
• Build a Headcount and Attrition Report — people data from the same source.
• Compare Two Excel Files for Differences with Python — general comparison techniques.