Reconcile Payments Against Invoices

Monthly payment reconciliation is the work nobody wants to do and everybody regrets skipping. The bank says you received $X. Your invoices said you were owed $Y. The difference is rarely explained by anything interesting — it’s usually one duplicate wire, one payment coded to the wrong invoice, and one invoice that never got sent. But finding those three needles takes four hours if you’re eyeballing spreadsheets.

This tutorial walks through a formula-driven reconciliation you can run in 20 minutes. No Apps Script — just a join, three flags, and an aging column. Pair it with the Payment Reconciliation template.

Step 1: Get Both Sides Into the Same Workbook

• Invoices tab. Your invoice register: Invoice # | Date Issued | Licensee | Amount | Currency | Due Date | Status.
• Payments Received tab. Bank-side incoming batch: Payment Ref | Date Received | Payer | Amount | Currency | Invoice #.

Most banks export a CSV where “Invoice #” is buried in the payment reference or memo field. Extract it into its own column before reconciling — use a REGEXEXTRACT or a manual pass. The join only works if Invoice # is clean.

Step 2: Build the Reconciliation Sheet

On a third tab, list every Invoice # from the Invoices register. Add these formulas in the adjacent columns (for row 2, A2 = Invoice #):

Payment Found?  =IF(COUNTIF('Payments Received'!F:F, A2)>0, "YES", "NO")
Dupe Payment?   =IF(COUNTIF('Payments Received'!F:F, A2)>1, "YES", "")
Amount Invoiced =VLOOKUP(A2, Invoices!A:D, 4, FALSE)
Amount Paid     =SUMIF('Payments Received'!F:F, A2, 'Payments Received'!D:D)
Variance        =G2-F2
Aging Days      =IF(E2="NO", TODAY()-VLOOKUP(A2, Invoices!A:F, 6, FALSE), "")

Step 3: Compute Status

One column, nested IF:

=IF(E2="NO", "MISSING",
  IF(F2="YES", "DUPLICATE",
    IF(ABS(H2)>1, "MISMATCH", "OK")))

• MISSING — no payment found at all.
• DUPLICATE — two or more payments referenced this invoice.
• MISMATCH — paid amount differs from invoiced amount by more than $1.
• OK — paid in full, once.

Apply conditional formatting: MISSING = red, DUPLICATE = red, MISMATCH = amber, OK = green. One scan tells you where to focus.

Step 4: Investigate in Priority Order

1. DUPLICATE first. Someone paid twice. Refund or credit; easy money recovered.
2. MISSING, aging > 60 days. Invoice never got paid and it’s overdue. Collections.
3. MISSING, aging < 30 days. Probably in the pipeline; re-check next month.
4. MISMATCH. Usually partial payments or FX. Legitimate half the time; investigate the other half.

Step 5: Handle the Exceptions Cleanly

• Partial payments. Mark these in the Notes column. A $10k invoice paid in two $5k wires is OK, not MISMATCH — adjust the tolerance formula if your book has a lot of these.
• FX conversions. Record the bank-reported USD, not the invoice USD, to match on amount. If the bank converted EUR → USD at a different rate than your invoice, the delta belongs on an FX adjustment row, not as a MISMATCH.
• Credits and rebills. A credit note reduces the Amount Invoiced. Apply it to the Invoices tab, not by hand in the reconciliation — otherwise the formulas disagree.

Common Issues

• Invoice # encoding. Banks truncate long references. “INV-2026-04-001” might arrive as “INV-2026-04”. Pad with leading zeros and use a fixed-length ID.
• Wire fees deducted at source. The bank takes $25 off an EUR wire; the variance shows $25 MISMATCH every time. Either net wire fees out of the invoice, or add a tolerance.
• Multi-invoice wires. One wire covers three invoices. The join by Invoice # breaks because the reference field lists all three. Split the wire across three Payments Received rows manually.

Download the Template

The Payment Reconciliation template ships with all formulas pre-wired plus conditional formatting for the four statuses. Paste your invoices and bank batch; the reconciliation sheet populates itself.