How to reconcile insurance commission statements, step by step

• ·The carrier statements for the period. Download them from each carrier's agent portal. If a carrier offers a CSV or Excel export, it's easier to work with than a PDF.
• ·Your book of business. An export from your agency management system with policy number, insured name, carrier, line of business, effective date, premium, and whether each policy is new business or a renewal.
• ·Your commission schedules. The rates in each carrier agreement, by line of business, for new business and renewals. These are what “expected” is based on.
• ·Your cancellations and endorsements for the period, so you can recognize chargebacks and mid-term premium changes.

For each policy that should appear on this statement, expected commission is:

premium × commission rate

Use the right rate. New business and renewal rates often differ, and rates can also differ by line, state, or program. A $2,400 homeowners renewal at 12% means you expect $288.00.

Two things change what “expected” means:

• ·Installment billing. On direct bill policies paid monthly or quarterly, many carriers pay commission as each installment is collected. One month's line will look small next to the annual premium. Compare it with the installment, not the full-term premium.
• ·Agency bill. When you collect the premium and send the carrier the net, your commission is the part you kept. You reconcile the carrier's account current statement against what you invoiced and remitted, not against a commission payment.

Each carrier lays out its statement differently. Before you compare anything, copy every line into one table with the same columns: carrier, policy number, insured name, transaction type (new, renewal, endorsement, cancellation), effective date, premium, rate, commission paid, and billing type (direct or agency).

Clean the policy numbers as you go. Carriers often add dashes, spaces, prefixes, or a term number that your management system doesn't use. In a spreadsheet, a helper column such as =UPPER(SUBSTITUTE(SUBSTITUTE(B2,"-","")," ","")) removes dashes and spaces and makes the letters consistent. Do the same to the policy numbers in your book export so both sides line up.

Match on policy number first. In a spreadsheet, =XLOOKUP(K2, Book!K:K, Book!G:G, "Not found") pulls the expected commission for each cleaned policy number. Adjust the columns to your own layout.

For lines that don't match:

• ·Check for a suffix or term number the carrier added or dropped.
• ·Try insured name plus effective date, and confirm by hand before you accept the match. Names are spelled differently across systems, and one insured often has several policies.
• ·Keep direct bill and agency bill apart. Never match a direct bill statement line to an agency bill policy, or the other way round. They're paid through different routes, and mixing them creates differences that aren't real.

Then work in the other direction: list every policy in your book that should have paid this period and has no line on any statement.

Every statement line, and every expected policy with no line, ends up in exactly one of these:

• ·Matched: paid what you expected.
• ·Short paid: paid, but less than you expected.
• ·Overpaid: paid more than you expected. Worth noting, because carriers usually find these later and take the difference back.
• ·Missing: you expected commission and nothing was paid.
• ·Unexpected: paid on a policy you can't find in your book. This is often a producer code or data entry problem on your side.
• ·Duplicate: the same transaction appears twice.

Two rules keep the result honest:

• ·A chargeback is not a short pay. When a policy cancels mid-term, the carrier takes back the unearned commission as a negative line. If your records show the cancellation, that negative line is correct. Check the amount against the return premium, but don't count it as money owed to you.
• ·Don't count duplicates as money owed. A duplicate line usually means the carrier will reverse one of them. Flag it so it doesn't surprise you later, but keep it out of the total you're asking for.

The money you can actually ask for is short paid plus missing.

Five lines from one month's reconciliation (illustrative figures):

The Chen auto renewal was paid at 12% on a $1,100 premium instead of the 15% in the agreement. The Lake Street Bakery business owner's policy never appeared on any statement. Between them, $445.50 is worth raising with the carriers.

The Okafor chargeback matches a $360 return premium at 15%, so it's correct and left alone. The second Rivera line is flagged as a duplicate and not counted.

Most differences have an ordinary cause. Check these first:

• ·Wrong rate: a renewal paid at the new business rate or the reverse, or an old schedule still in the carrier's system.
• ·Premium changed mid-term: an endorsement raised or lowered the premium, and the commission followed it.
• ·Timing: the carrier hasn't collected the premium yet, or the line will appear on next month's statement.
• ·Producer or agency code: the policy was written under a different code and paid on a different statement.
• ·Installments: a monthly payment was compared with the annual premium.

If none of these explains it, it's a real discrepancy.

Send it to the carrier's commission or agency accounting contact. For each line, include:

• ·Policy number and insured name
• ·Effective date and transaction type
• ·Statement date and the amount paid
• ·The amount you expected and how you got it: premium, rate, and where the rate comes from

Group all the lines for one carrier in one message, with a total at the bottom. A clear, complete request is easier for the carrier to process than a question about a single number.

Watch the deadline. Some carrier agreements limit how long you have to raise a commission error, and the window differs from carrier to carrier. Check each agreement, and note the date you found each issue and the date you raised it.

Keep one running list of open items: carrier, policy, amount, date raised, who you contacted, and the outcome. Close an item when the correction shows up on a statement, not when the carrier says it will.

Reconcile each statement as it arrives. A difference from last month is easy to trace; one from last year usually isn't. After a few months, the log also shows you which carriers make the same mistakes again and again.

A spreadsheet works, and the steps above are the whole method. The slow parts are reshaping every carrier's statement, matching the lines that don't match cleanly, and keeping track of every dispute deadline.

Insurance Commission Reconciliation does those parts for you. Upload your carrier statements and your book of business, and it matches every line, sorts each one into the six results above, totals what's flagged, tracks each carrier's dispute deadline, and exports a dispute packet you can send to the carrier. Your first reconciliation is free, and no card is needed.