Comparison
Spreadsheet vs commission reconciliation software
An honest comparison for independent agencies: what a spreadsheet does well, where it runs out, and what dedicated software adds and leaves out.
In short
- A spreadsheet can reconcile commissions. It costs nothing to start and does exactly what you build, but every new statement means copying, cleaning and matching by hand.
- Dedicated software reads statements as they arrive, matches them to the book, and keeps the rules and the history in one place. It costs money and does only what it was built to do.
- The deciding question is usually not cost but coverage: how many carriers, how many policies, and whether anyone checks for commission that never arrived.
Most independent agencies that reconcile commissions at all do it in a spreadsheet. That is a reasonable choice, and for a small book it may be the right one. This page sets out, without exaggeration, what each approach does well and where each runs out. It compares categories, not named products.
Side by side
| Task | Spreadsheet | Lapidar |
|---|---|---|
| Getting statement lines in | Copy or import each statement; PDFs retyped or converted by hand | Upload CSV, XLSX or text PDF as sent; columns matched once per carrier layout |
| Matching to policies | Lookup formulas; formatting differences need cleanup first | By carrier and policy number, ignoring dashes, spaces, renewal suffixes and leading zeros (after any prefix you list) |
| Expected commission | Formulas you write and maintain | Book premium times your schedule rate by carrier, line and new/renewal; installments handled |
| Commission never paid | Only if you start from the book and check each policy | Reported as Missing for policies due in the period |
| Paid twice | Manual review or conditional formatting | Flagged automatically, citing the repeated line |
| Chargebacks | Search earlier months by hand | Paired with the original payment on these statements or ones checked in an earlier run |
| Audit trail | Whatever you record | Every finding cites the file, page and row; states open, raised, resolved |
| Letter to the carrier | Written by hand | Printable report per carrier, sent by you |
| Flexibility | Unlimited; you can model anything | Limited to what it supports (no producer splits, no bank matching) |
| Cost | Staff time | $99 a month plus tax (founding pilot), plus the time to review findings |
Where a spreadsheet works
With a handful of carriers that send CSV, a book of a few hundred policies, and someone who knows the formulas, a spreadsheet is cheap and transparent. You can see every number, change any rule, and add checks no software anticipated, such as producer splits or bank deposits. If you reconcile regularly and nothing slips, there may be little reason to change.
Where it tends to break
- PDF statements. Retyping or converting them is where most of the hours go, and where errors creep in.
- Layout changes. When a carrier moves a column, the formulas keep producing numbers. They are just the wrong ones.
- The missing renewal. A statement-first sheet checks what was paid. A policy that should have paid and didn’t leaves no row to check. See missing renewal commissions.
- Late chargebacks. Tying a reversal to a payment three months earlier means searching old tabs.
- One person. The workbook usually lives in one head. When that person is away, the check stops.
Our guide on what a careful reconciliation has to check goes through the steps either approach has to cover.
What software adds, and what it doesn’t
Software like Lapidar reads the statements, applies the same rules every month, starts from the book as well as the statements, and keeps each finding tied to its source line. It doesn’t replace judgment: a finding is a reason to look, not proof the carrier owes you. It also does less than a spreadsheet in some ways. Lapidar has no AMS integration, can’t read scanned PDFs, and doesn’t cover producer pay or bank deposits. The product page lists exactly what it reads and what it doesn’t.
How to choose
- Count how many hours a month the spreadsheet takes, including PDFs.
- Ask whether your current process would catch a renewal that never paid.
- Look at the sample report and decide whether that output would save you the work, or whether your sheet already does it.
Frequently asked questions
Can I reconcile insurance commissions in Excel?
Yes. Many agencies do. It works best with few carriers that send CSV and a small book. The effort grows with PDF statements, layout changes, and the need to start from the book to find commission that was never paid.
What does commission reconciliation software do that a spreadsheet doesn’t?
It reads statements in the formats carriers send, matches them to the book with the same rules every month, flags missing, underpaid, duplicate and charged-back commission, and keeps each finding tied to its source line. A spreadsheet can do these things only if someone builds and maintains them.
When is a spreadsheet the better choice?
When the book is small, statements arrive as clean data, and you need checks a product doesn’t offer, such as producer splits or bank deposit matching. Lapidar does not cover those.