What to check when reconciling carrier commission statements, and why it’s hard

TL;DR

• Reconciling means comparing what you expected to be paid, from your own book and commission schedule, with what the carrier paid, line by line, not against the statement’s own arithmetic.
• Every difference falls into one of a few kinds: missing, underpaid, overpaid, paid twice, or not in your book.
• Many differences have ordinary causes, such as timing, installments and records that are out of date, so each has to be ruled out before anyone writes to the carrier.
• At scale the hard part isn’t the arithmetic. It’s dozens of statement formats, PDFs, policy numbers that don’t match, timing, chargebacks that land months later, and the hours all of it takes every month.

We’re building Lapidar, a service meant to take this work over. It isn’t ready yet. This guide explains what has to be checked and why it’s hard to do well, so you can judge the work involved, whoever does it. It applies to direct bill statements; agency bill needs a few extra checks, covered in a separate guide.

A carrier statement tells you what the carrier decided to pay. Reconciling it means working out, independently, what you expected to be paid, and looking hard at every place where the two disagree. Doing it across every carrier, every month, is not.

What a reconciliation draws on

• The statements for the period. They arrive as PDF, CSV or Excel files, or as a download from the carrier’s agent portal. Each carrier has its own layout, column names and transaction codes.
• Your book, from your agency management system. At minimum: policy number, named insured, carrier, line of business, effective date, transaction type (new business, renewal, endorsement, cancellation), written premium and bill type.
• Your commission schedules. The rates in your agency agreement or the carrier’s current schedule, by line of business, and separately for new business and renewals where they differ. These change, and not always with a notice.

Why it’s done carrier by carrier

Every carrier lays out its statement differently, and each one needs its own understanding of where the columns are, how its policy numbers differ from yours, and what its transaction codes mean. An agency with dozens of carriers has dozens of formats to keep straight, and a carrier that changes its layout breaks whatever worked the month before.

Getting the statement into comparable form

To compare anything you need one record per statement line, with the same fields every time: policy number, insured name, effective date, transaction type, premium, the rate the carrier applied, and commission paid. With a CSV or Excel file that is usually manageable. With a PDF it’s the slow part of the job: columns scramble, pages break lines in two, and totals have to be checked to make sure no line was dropped or doubled.

Timing: which statement a policy belongs on

Direct bill commission usually follows the carrier’s collection of premium, not the effective date, so a policy that took effect on the 28th may appear on next month’s statement. Policies on installment plans pay commission in installments. That means a policy missing from this month’s statement isn’t necessarily unpaid, and anything not yet seen has to be carried forward and watched, month after month.

Policy numbers that don’t match

Your agency management system and the carrier rarely write a policy number the same way. Carriers add prefixes, term suffixes, dashes, spaces and leading zeros, so the same policy looks like two different ones.

Normalizing these is less simple than it looks. Some carriers use the suffix to mark the policy term, so -01 and -02 are two different years of the same policy. Strip too little and nothing matches. Strip too much and last year’s term is matched to this year’s payment. Every carrier has its own rules, and they have to be learned and kept up to date.

What you expected to be paid

For each policy in your book, the expected commission is the commissionable premium times the rate on your schedule.

Expected commission = commissionable premium × rate on your schedule

Two things make this harder than a multiplication. First, the rate must come from your schedule, not the rate printed on the statement; checking the carrier’s rate against itself proves nothing. Second, not every dollar the insured pays is commissionable. Policy fees, taxes, surcharges and some assessments are often excluded, and which ones differs by carrier and agreement.

Carrier 07 · EX-10482Example data

Written premium

$2,750.00

Rate on your schedule

×15.00%

Expected commission

$412.50

Paid on the statement

$330.00

Short

−$82.50

The kinds of difference

Once expected and paid sit side by side, every policy ends up in one of a few groups. A small tolerance for rounding is normal.

In this example there is $742.50 to raise with the carrier: the $82.50 short on EX-10482 and the $660.00 missing on EX-10490. The duplicate on EX-10486 isn’t money to ask for. It’s money the carrier is likely to take back later, and it’s better to know now than to be surprised by a negative line in a few months.

• Missing: in your book, not on the statement.
• Underpaid: paid, but below expected.
• Overpaid: paid above expected. Worth knowing for the same reason as a duplicate.
• Paid twice: the same policy and period paid on two lines, on one statement or across two.
• Not in your book: on the statement, with nothing to match it. Usually a sign your book is incomplete, or a policy was never entered.

That is one small statement. A real one has hundreds or thousands of lines, and the example above is the easy case, where every policy number matched.

The innocent explanations

Before anyone writes to the carrier, the difference has to be checked for an ordinary cause. Most differences come from timing and records, not from the carrier making a mistake:

• The insured pays monthly, so this line is one installment, not the whole commission.
• The policy is a renewal and your schedule has a lower renewal rate than expected.
• An endorsement or a cancellation is on its own line, and needs to be netted against the original.
• Your book is wrong: an endorsement that was never entered, or a premium that changed at renewal.

We go through these one by one in why statements don’t match your agency management system.

Raising it with the carrier

Whoever handles commission questions at the carrier, whether that’s an accounting contact or your rep, will move faster on a specific request than on a general complaint. Each item needs the policy number and named insured, the statement date and line (or the fact that the policy is absent), the premium, the rate on your schedule, the commission you expected, what was paid and the difference.

Chargebacks and open items months later

A reconciliation is never finished with the month it covers. A chargeback can arrive months after a cancellation, and an item raised with a carrier has to be remembered until it is paid. An item that was raised and then forgotten is the same as an item that was never found. Tracking what is still open, per carrier and per policy, across many months, is a job of its own.

How often it needs doing

Ideally every statement, every month, while the details are fresh; a gap of several months makes every difference harder to trace. That’s the difficulty: for an agency with many carriers, a full pass every month takes hours, and it competes with everything else the office does.

Where Lapidar fits

None of the individual checks is mysterious. What doesn’t scale is the labor around them: reading dozens of statement formats, extracting PDFs, reconciling policy numbers, remembering rates for each carrier and line, and tracking open items. That’s the work we’re building Lapidar to take over, inside the Lapidar web app. It’s in development, it has no customers yet, and we’ll say so until that changes. If you’d like to try it when it’s ready, the early-access list is below.