How to reconcile an insurance commission statement

Reconciling a commission statement means answering one question: does the money you received match the money you earned? Carriers answer the first half for you. The second half is your job, and most agencies skip it, because checking three hundred lines against expected rates by hand is miserable. This post is the full process, the same one our reconciliation engine runs, written out so you can do it with nothing but a spreadsheet and stubbornness.

Step 0: know what you expect to be paid

You cannot find underpayments without knowing the right number. Build a small reference table before you reconcile anything:

• Medicare Advantage and PDP: CMS publishes maximum compensation amounts every year, split by initial and renewal and by state group. Renewals generally pay about half the initial amount. Put the current year's figures for your states in a table. We keep a reference of these on the Medicare commission rates pages, each showing what a rate gap is worth across a book.
• Med Supp, life, and other percentage products: the rate comes from your contract with each carrier. Initial and renewal percentages, by product. Dig out the contracts once; reuse the table forever.

This table is your measuring stick. Every check below compares a statement line against it.

Step 1: total the statement yourself

Before looking at any individual line, sum the commission column and compare it to the total printed on the statement. If the stated total does not equal the sum of the lines, stop: either lines are missing from the document, something is double-counted, or the header is simply wrong. This is the cheapest check in reconciliation and it catches real money.

Step 2: check every line against the expected rate

For each commission line, compute what it should have paid:

• Flat-amount products (MA, PDP): compare the paid amount to the CMS figure for that product, state, year, and new-versus-renewal status.
• Percentage products: multiply the premium by your contracted rate and compare.

Flag anything that pays below your expected number beyond a small rounding tolerance. Also flag anything paying above the CMS maximum on Medicare products: overpayments feel pleasant and then get clawed back later, usually at the least convenient moment.

Step 3: check the new-versus-renewal coding

A policy effective in 2019 should not be paying at the new-business rate in 2026, and a policy written three months ago should not be paying as a renewal. Since new business typically pays roughly double the renewal amount on Medicare products, a miscode in the wrong direction quietly halves your commission on that policy. Compare each line's payment type against the policy's effective date.

Step 4: audit the chargebacks

Negative lines deserve their own pass:

• Does each chargeback have a matching original? A clawback for a policy you were never paid on is a phantom chargeback and merits a dispute.
• Is anything charged back twice for the same policy and period?
• Is the amount proportional? Mid-year disenrollments on Medicare products are generally recovered pro rata, not in full.

Step 5: compare against last period

The sneakiest discrepancy is the line that is not there. A policy that paid last month and simply vanishes this month, with no chargeback and no explanation, is easy to miss because there is nothing on the page to see. Keep last period's policy list and diff it against this period's. Investigate the disappearances.

Step 6: write down what you found, then actually follow up

A reconciliation that ends in a spreadsheet tab nobody reopens recovered zero dollars. For each discrepancy, record the policy, the expected and actual amounts, and the reason, then send the list to the carrier's commissions desk in writing. Carriers fix documented errors routinely. Undocumented grumbling fixes nothing.

When the spreadsheet stops being enough

The process above works. It also takes hours per statement, scales linearly with your book, and depends on the one person who knows where the rate table lives never going on vacation. The breaking point usually arrives with multi-carrier growth: five carriers means five formats, five rate tables, and five chances per month for an error to slide through unexamined.

That is the job Pineapple Split automates: upload the statement in whatever format the carrier sent, and every check in this post runs in seconds, with the discrepancies ranked by severity and the dollars at stake totaled. The free tier exists precisely so you can test it against a statement you have already reconciled by hand and see whether it catches anything you missed.