Monthly commission audit: a reconciliation checklist and the most common mistakes to catch

Most agencies don't lose commission money in one dramatic event. They lose it in $40 and $120 chunks spread across dozens of statements, month after month, until it adds up to real money nobody can trace back to a source. The frustrating part is that the discrepancies are almost always sitting right there in the data. Nobody's looking, or the person looking doesn't have a repeatable way to do it.

This is a routine, not a philosophy. Below is the reconciliation process that actually holds up when an agency runs it every single month — the spreadsheet logic to catch the common problems, and the escalation rules that decide what's worth chasing and what isn't.

Start with the four buckets, not the whole statement

The mistake most agency owners make is trying to reconcile the entire commission statement line by line against their AMS. That's how you end up spending six hours and giving up somewhere around policy number 200.

Every commission discrepancy falls into one of four buckets, and each has a different root cause and a different fix:

The reason this matters: timing lags account for a huge share of what looks like "missing" commission on any given statement. If you don't separate those out first, you'll chase carriers for money that's just going to show up next cycle — and you'll burn credibility on the calls that actually matter.

So the first move in any monthly commission reconciliation for an insurance agency is sorting every anomaly into these four buckets before you contact anyone.

The base reconciliation query logic

You don't need a fancy tool to run this. A commission export from your carriers and a policy export from your AMS, dropped into two tabs of the same spreadsheet, gets you 80% of the way there.

Here's the core comparison. Assume your commission statement is on a tab called Statement and your AMS export is on AMS, and both share a policy number column.

Match policies and flag the gaps:

=XLOOKUP(A2, Statement!A:A, Statement!D:D, "NO COMMISSION FOUND")

Where A2 is the policy number in your AMS tab and Statement!D:D is the commission amount column. Anything that returns "NO COMMISSION FOUND" is a missing commission candidate — but only after you filter out policies with an effective date in the last 45 days, which are almost always timing lags.

This diagram shows the matching and bucketing workflow at a glance.

Catch rate mismatches:

=IF(ABS(Premium * ExpectedRate - PaidCommission) > 5, "RATE MISMATCH", "OK")

The > 5 tolerance is deliberate. Sub-$5 rounding differences aren't worth anyone's time — you'll drive yourself crazy chasing pennies that come from how carriers round mid-term endorsements. Set your tolerance to a real dollar threshold and let the small stuff go.

Catch ghost commissions:

=XLOOKUP(Statement!A2, AMS!A:A, AMS!B:B, "NOT IN AMS")

"NOT IN AMS" results are your ghost bucket. Some are legitimate — a policy cancelled last month can still generate a clawback or a final commission line. But this is also where you catch genuinely valuable stuff: commission paid on a policy you already wrote off, or a split you're getting overpaid on that will eventually get clawed back with interest.

Where the real money hides

After running these buckets a few months in a row, you start noticing the same three or four patterns causing most of the recoverable dollars. These are worth building muscle memory around.

New-business rate applied to a renewal. This is the single most common rate mismatch. A policy renews and the carrier's system pays renewal commission — except sometimes it doesn't, and it re-pays at new-business rate, or vice versa. On personal auto this might be a 12% vs. 10% difference on a $1,400 premium, so around $28. Trivial once. But if a carrier has a systematic issue affecting 60 of your renewals a month, that's roughly $1,600–$1,700 monthly walking out the door, and it'll keep happening until someone flags it.

Bonus and profit-share not reflected in monthly statements. A lot of contingency and volume bonuses show up on separate schedules or quarterly true-ups, not the monthly statement. Agencies routinely miss these because they only reconcile the monthly and assume the bonus tab is "handled by the carrier." It usually is — but the year you hit a new tier is often the year it quietly isn't, because your production crossed a threshold and their system didn't recalculate.

Split-commission drift on transferred books. When policies move between producers, the commission split on the carrier's side and the split in your AMS fall out of sync. One producer keeps getting credited after they should've stopped. This ties directly to how clean your handoffs are in the first place — the same discipline that prevents issuance errors during agent transitions is what keeps commission splits accurate. If your renewal and transfer process is messy, your commission statements will be messy too, usually one to two months later.

Cancelled-but-paying policies. A policy cancels, but the carrier's commission engine doesn't get the memo for a cycle or two. You get paid. Then three months later, a clawback hits for the full amount. If you're not tracking these, the clawback lands as a mystery deduction that throws off that month's reconciliation, and you spend an afternoon figuring out something you could've flagged months earlier.

The monthly reconciliation checklist

Run this in the same order every month. The order matters — eliminate the noise before you spend time on the signal.

1. 1
   - [ ] Export current-month commission statements from every carrier (same file format each time — consistency makes the queries reusable)
2. 2
   - [ ] Export active + recently-cancelled policies from your AMS, including effective dates and writing agent codes
3. 3
   - [ ] Filter out all policies with effective dates in the last 45 days — tag them "timing watch," don't chase them yet
4. 4
   - [ ] Run the missing-commission lookup; list every "NO COMMISSION FOUND" that's past the 45-day window
5. 5
   - [ ] Run the rate-mismatch column with your $5 tolerance; list everything flagged
6. 6
   - [ ] Run the reverse lookup for ghost commissions; separate legitimate final-commission lines from true ghosts
7. 7
   - [ ] Cross-check any policy that changed producers this month against its commission split
8. 8
   - [ ] Reconcile last month's "timing watch" list — did the expected commissions actually show up?
9. 9
   - [ ] Total the recoverable dollars by carrier before deciding what to escalate

That second-to-last step is the one people skip and shouldn't. Your timing-watch list from last month is where you confirm whether a lag was really a lag or a genuinely missing payment you almost let slide.

Escalation rules: what to chase and what to let go

Not every discrepancy is worth a phone call. The time spent reconciling with a carrier rep costs more than a $15 recovery. You need rules that decide this automatically.

A workable escalation framework:

1. 1
   Under $25, single occurrence, one carrier — log it, don't chase. Watch for a pattern.
2. 2
   $25–$150, single occurrence — batch it. Include it in a single monthly email to the carrier's commission desk, don't call.
3. 3
   Any amount appearing 3+ months on the same policy — escalate regardless of size. A recurring $18 error means their system is misconfigured, and it'll keep paying wrong until someone forces a fix. Recovering the back months often adds up more than you'd expect.
4. 4
   Systematic pattern across 10+ policies — this goes to your carrier rep or marketing rep, not the commission desk. It's a contract or coding problem, not a data-entry fix.
5. 5
   Over $500, any single item — escalate immediately, don't wait for the monthly batch.

Rule 3 is the one most agencies get wrong. They dismiss small recurring errors because each instance feels too minor to bother with. But recurring small errors are the most valuable to catch — fixing the root cause stops all future occurrences, not just the one you noticed.

A real scenario

A mid-sized personal lines agency with around 3,200 active policies across five carriers hadn't run a structured commission audit in over a year. They assumed their AMS-to-statement matching was close enough.

When they finally ran the four-bucket process, the first month surfaced roughly $2,800 in genuinely recoverable discrepancies. Most of it came down to two patterns: one carrier systematically applying new-business rates to about 40 renewals a month, and a batch of cancelled policies that had been overpaying and were heading toward a painful clawback.

The recurring rate error was the real find. It had been running for most of the year — call it eight or nine months of roughly $600–$700 monthly. Once flagged as a systematic coding issue and escalated to the carrier rep rather than the commission desk, the carrier corrected the mapping and paid back a chunk of the arrears. The one-time recovery landed somewhere around $4k–$5k, and the monthly leak stopped entirely.

The cancelled-policy overpayments were less fun — those had to be set aside for the coming clawbacks. But knowing about them in advance meant no surprise deductions blowing up a future reconciliation.

Making it repeatable instead of heroic

The reason most agencies don't do this consistently isn't that they don't understand it. It's that doing it manually is genuinely tedious, and the person who set up the spreadsheet leaves or gets pulled into other things, and three months later nobody remembers which tab does what.

The routine survives only if it's boring and identical every month. Same exports, same file formats, same query order, same escalation thresholds. The moment it becomes a creative project each cycle, it stops happening.

This is where operational software that keeps your AMS data and commission records reconciled continuously — rather than in a monthly panic — earns its keep. When the matching runs automatically and flags only the exceptions that break your tolerance rules, the monthly audit shrinks from a half-day project to a 30-minute review of a pre-filtered exception list. The bucketing, the timing-lag filtering, the recurring-error detection across months — that's exactly the kind of pattern-matching that's tedious by hand and straightforward to automate. But the logic above works with nothing more than two spreadsheet tabs, and it's worth running manually at least once so you actually understand what any automation would be checking.

The one thing to take away

The discrepancies that hurt agencies aren't the big obvious ones — those get caught. It's the recurring $20 errors that feel too small to chase, the timing lags that mask real missing payments, and the split drift on transferred books that nobody reconciles because it lives in two systems at once.

Build the routine so those three patterns can't hide. Sort into buckets before you chase anything, filter timing lags out first, and escalate recurring errors by frequency rather than dollar amount. Do that every month, the same way, and the money that used to leak out in small pieces starts staying where it belongs.