In this guide 6 sections
Match each statement line to a policy in your own register, using policy number and transaction type as the key. Anything issued but absent from the statement is missing. Anything paid at a different amount is short. Log both with evidence and raise one query per carrier.
Get your own register right before touching the statement
You cannot find a short payment if you never worked out what you expected to receive from each carrier on each policy this month. Build it from your own issued policies.
For each policy you need the agreed rate, the premium basis and the type of transaction, because new business, renewal, endorsement and cancellation often pay on different terms.
Rate cards change. Keep the agreement version in force on issue. A policy sold in March is paid on March terms even when the rate card moved in April, and the statement will not tell you that.
- Policy number, insured and issue date
- Carrier, product and agreed rate on that date
- Premium excluding tax, as the carrier defines it
- Transaction type: new, renewal, endorsement, cancellation
- Expected commission and expected pay month
Match line by line, then by exception
Load the statement beside your register and match on policy number plus transaction type, using a lookup formula or an import rule rather than your eyes. Most lines pair off. Work only on the rest.
Three buckets appear. Lines with no policy of yours are unknown. Policies with no line are missing. Variances are lines whose amount differs.
Policy numbers are where matching usually breaks. Carriers add prefixes, drop leading zeros or reissue numbers after an endorsement, so normalise the format in both lists before you compare a single row.
Spot the usual causes of short payment
A short payment is rarely random. Look for a pattern across the whole statement and across the last three months before you chase any individual line, because patterns point to causes.
Clawbacks on cancelled policies are legitimate but must trace to a named policy, and a flat deduction with no reference is not acceptable until the carrier explains it.
Timing differences are normal. Some carriers pay commission only after the client’s premium reaches them, so a policy issued in one month may appear two statements later.
- Old rate applied after a rate card change
- Commission calculated on premium including tax
- Endorsement premium ignored
- Cancellation clawback with no policy reference
- Policy paid late because the client premium was late
Raise disputes with evidence
Send one query per carrier. Attach a table of policy number, issue date, expected commission, paid commission, difference and the clause of the agreement you rely on.
Keep a dispute log with the date raised, the carrier contact, the reply and the date the money arrived, so nothing depends on anyone’s inbox. It shows who pays slowly.
Never net disputed amounts off later statements unless the carrier agrees in writing to that adjustment and names the amount involved. Carry them as receivable until the carrier confirms.
Make reconciliation a monthly close step
Reconcile within a week of receiving each statement. Old queries are harder to prove, and carriers can limit how far back they will look, so a gap left alone may become one you absorb.
Tie the result to your books, so that matched lines, variances and unknowns add up to the amount that reached the bank, to the last unit.
Next step: take your largest carrier’s latest statement and match fifty lines by hand, then write down which cause appears most often and who should own it.
Commission reconciliation sheet
A worksheet of three tabs. The first holds expected commission per policy. The second takes the pasted carrier statement lines. The third lists exceptions, with columns for bucket, difference, suspected cause, date raised, carrier reply, date settled and the colleague who chased it.
Open a blank worksheet to printQuestions owners ask
How often should we reconcile carrier statements?
Once per statement, shortly after it arrives. Monthly statements mean a monthly routine. Quarterly statements still deserve a check as soon as they land, because disputes age badly and evidence gets harder to find.
What if the carrier’s policy numbers do not match ours?
Add a column holding the carrier’s number as it appears on the statement and match on that, since the statement is the document the carrier will argue from. Keep both numbers.
Should we chase every small difference?
Chase patterns first. A small gap repeated across many policies points to a wrong rate or a wrong premium basis, and one letter can fix all of them. A lone rounding gap rarely merits a letter. Log it anyway.
Does software help with this?
Yes. It can hold the rate card by date, calculate expected commission when a policy is issued and flag unmatched lines on import. It will not replace the call, and it will not argue with a carrier on your behalf.