AI for Insurance Brokerage: Reconcile Insurer Commission Statements

Yes, AI can help reconcile insurer commission statements against a brokerage policy ledger and flag commission that has not been paid. It reads each statement into rows, a spreadsheet matches those rows to your ledger by policy number, and the AI explains the exceptions and drafts the chasing email. A person confirms every exception before anything goes to an insurer.

Splitting the job matters. Matching and arithmetic belong in formulas that give the same answer every time; reading messy statements and writing summaries belong to the AI. A brokerage that asks a chatbot to "reconcile this" and accepts the answer will get a confident list with invented policy numbers. The routine below keeps the tool on the tasks it is good at and makes every figure traceable to a source row.

Follow me on Instagram@sagnikteaches

What reconciliation is actually checking

Each month an insurer pays commission and sends a statement listing policies, premiums and the commission due. The brokerage's own ledger (in an agency management system or a spreadsheet) records what it expects to receive: policy number, insurer, premium, commission rate, effective date and any adjustments. Reconciliation asks four questions of every line.

Connect on LinkedInSagnik Bhattacharya

1. Is it on both sides? A policy in the ledger but not on the statement may be unpaid. One on the statement but not the ledger may be a missing record or a mis-keyed number.
2. Is the premium the same? Different premium usually means an endorsement, a mid-term adjustment or an input error.
3. Is the commission rate right? A rate lower than agreed is an easy loss to miss, because nothing on the statement looks wrong.
4. Is the commission amount arithmetically right? Premium times rate should equal commission within a rounding tolerance.

Everything on the exception list falls into one of those four, plus cancellations and chargebacks. That is why the routine can be so mechanical.

Subscribe on YouTube@sagnikteaches

A worked example: a four-person brokerage, one month, three insurers

Say a small brokerage receives statements from three insurers for the month. Illustrative figures: Insurer A sends a 6-page PDF with 118 lines, Insurer B a spreadsheet with 74 lines and Insurer C a PDF scan of 41 lines. The ledger shows 247 policies due commission this month. Total expected commission: $18,420. Total received across the three bank payments: $17,311. The gap is $1,109, and today nobody knows where it sits.

Done by hand, matching 233 statement lines to 247 ledger lines takes an experienced person a day or more, and the work tends to be put off. The routine below takes about two hours once set up: 20 minutes to extract the statements, 30 minutes to run the matching, 45 minutes to review exceptions, and 25 minutes to send chasers. In the example it finds 14 exceptions: 11 underpayments worth $1,228 and three overpayments worth $119, which net to the $1,109 gap. Figures are illustrative; your own first run will show the real picture.

Step one: read each statement into the same columns

Insurers send statements as spreadsheets, CSV files, text PDFs and scans. Your target is one table with the same columns for every insurer:

For spreadsheet statements this is renaming columns. For PDFs, give the AI the file or its pasted text with an extraction prompt.

Extract every line of this commission statement into a table with
columns: policy_no, transaction_type, premium, commission_rate,
commission_paid. Keep policy numbers exactly as printed, including
prefixes and dashes. Use negative numbers for chargebacks. Do not
calculate or correct anything. If a value is unreadable write
"UNREADABLE" and give the line number. After the table, state:
the number of lines and the sum of commission_paid, and compare with
the statement total printed on page [n].

Statement text:
[paste]

The final instruction is the control. Suppose the assistant returns 118 lines summing to $5,894.20 and the statement's printed total is $5,902.70. The $8.50 difference means a line was dropped or misread. Find it (often a wrapped line or a footnote row) before you match anything. A scanned statement like Insurer C's is where this check fails most; ask the insurer for a spreadsheet version, which some will send on request.

Step two: match to the ledger with formulas, not with chat

Put the combined statement table on one sheet, the ledger on another, and add check columns to the statement sheet. Ask the AI to write the formulas for your column layout (and tell it the program: Excel or Google Sheets), then test them on five rows you can verify by eye. A pattern for the statement sheet, assuming the seven columns in the table above sit in A to G (policy number in C, premium in E, rate in F, commission paid in G), the check columns start at H, and the ledger holds policy numbers in column A, premium in D and the agreed rate in E:

H  In ledger?      =IF(COUNTIF(Ledger!A:A, C2)>0, "yes", "NOT IN LEDGER")
I  Ledger premium  =XLOOKUP(C2, Ledger!A:A, Ledger!D:D, "")
J  Premium diff    =IF(I2="","",E2-I2)
K  Expected comm.  =E2*F2/100
L  Comm. diff      =ROUND(G2-K2, 2)
M  Rate in ledger  =XLOOKUP(C2, Ledger!A:A, Ledger!E:E, "")
N  Rate diff       =IF(M2="","",F2-M2)

Column L tests the insurer's own arithmetic (premium times its stated rate), and column N catches a stated rate that differs from the rate in your ledger. XLOOKUP's fourth argument, the value to return when nothing matches, works the same way in current Excel and in Google Sheets.

On the ledger sheet, add the reverse check: does each policy due this month appear on any statement?

On a statement? =IF(COUNTIF(Statements!C:C, A2)>0, "yes", "NOT PAID")

Two practical tips. First, normalise policy numbers before matching: insurers print "PC-448201", "PC448201" or "448201", while your ledger uses one form. Use a helper column with =UPPER(SUBSTITUTE(SUBSTITUTE(C2,"-","")," ","")) on both sides. Second, a policy can appear twice on one statement (a new business line plus an endorsement). If so, sum by policy number first, or you will flag a false exception. For more formula help see Google Sheets AI prompts and cleaning messy data.

Step three: classify the exceptions

An exception list is only useful if each line has a reason and an owner. Use a fixed set of categories, so you can count them over time.

Set a tolerance for rounding, say $0.50 per line or 0.1 per cent, so you are not chasing pennies. Also set a materiality rule for chasing: below $25 you note it and batch it; above, you chase. These figures are for illustration; agree yours with the brokerage's principal.

Now the AI earns its keep. Paste the filtered exception rows (policy number, insurer, category, figures) and ask for a one-line explanation per exception and a proposed action, with an instruction not to guess causes. A realistic reply: "PC-448201 (RT): statement rate 10.0, ledger rate 12.5, premium $1,840, shortfall $46.00. Check whether the insurer applied the new-business tier rather than the renewal tier agreed in the agency agreement." The suggestion is a hypothesis to verify, not a finding.

Step four: chase the insurer with evidence

A chasing email works when it is specific, short and makes the insurer's work easy. Include the statement date, the policy numbers, the expected and received amounts and the clause of the agreement that supports your figure. Draft it once and reuse.

Subject: Commission query, statement dated [date]

Hello [first name],

On the [month] statement, I found the following differences against
our records. All figures in USD.

Policy | Issue | Expected | Received | Difference
PC-448201 | Rate 10.0% vs agreed 12.5% | 230.00 | 184.00 | 46.00
PC-451977 | Not on statement (renewed [date]) | 96.50 | 0.00 | 96.50

Could you confirm the cause and arrange payment or an explanation
by [date]? Our agency agreement, section [n], sets the rate for
this class. Supporting records attached.

Thanks,
[your name]

Keep a chase log with date sent, reply received, outcome. After three months it shows which insurers pay late or make mistakes often, which is useful in a negotiation. Before sending anything, check the agreement: payment terms, reporting deadlines and dispute windows differ, and some close after a set period. That is a contract question for the brokerage owner or its adviser, not for the AI.

Four more cases the routine has to handle

A policy that renewed with a different insurer. The ledger says the account is due commission from Insurer A, but the client moved at renewal. The AI's "not paid" flag is correct arithmetically and wrong in fact. Before chasing, always check that the ledger still reflects reality. This is why chasing needs a human step.

A before and after on the ledger. Before: the ledger records "12.5" for every policy with Insurer A. After the first reconciliation, 9 policies in the motor class show 10.0 on statements. The cause turns out to be a different agreed rate for motor. The fix is two ledger rates, not nine chasers.

A mistake that showed up late. A broker asked the assistant to total all unpaid commission directly from the raw statements. It returned a figure that looked right but included two chargebacks as positive amounts. Totals came out $380 high. The control that caught it was the check against the insurer's printed total.

A quick sum on the payoff. If a first reconciliation recovers $1,109 of unpaid commission in a month and the routine takes two hours, the value is plain. Even if only half of it is recovered after the insurer's own checks, that is $550 for two hours. Illustrative again; expect the first run to recover more than later ones, because it clears a backlog of old errors.

Tidy the ledger before the first run

The first reconciliation usually shows more about your ledger than about the insurers. A morning of preparation avoids a flood of false exceptions.

• One row per policy term. If renewals overwrite the previous term, you lose the history you need to explain a late payment. Add an effective date and expiry date to each row.
• A rate per class per insurer, taken from the agency agreement, held in a small lookup table rather than typed on each policy. The ledger formula then pulls the rate, and a wrong rate becomes visible in one place.
• Status. Mark policies as live, cancelled, transferred out or pending. Without it, the AI and the formulas both flag cancelled policies as unpaid.
• Policy number format. Decide on one form and fix the ledger, since you cannot fix the insurers' statements.
• Producer or sub-agent splits. If part of the commission goes to another party, record the split, so the reconciliation can also feed the payout.

Ask the AI to audit the ledger: "List every policy number that appears more than once, has no expiry date, or has a rate outside the range 5 to 25 per cent." It will not fix them, but it gives you a clean-up list in a minute. Rows that fail are usually typing errors, and each one you fix removes a recurring false exception.

Timing differences are not errors

Many unpaid items are simply early. Insurers pay on their own cycles: some pay on policy inception, some when the client pays the premium, some quarterly in arrears. A new policy bound on the 28th may not appear until the next statement, or the one after. If your reconciliation treats every absence as a loss, you will chase insurers for money they do not yet owe and lose credibility.

Handle it with an "age" column on the ledger: days since inception or renewal. Then set a rule per insurer based on its agreed payment terms: for example, Insurer A pays on premium receipt, so anything with premium paid more than 30 days ago and no commission is an exception, while anything newer is "pending". Ask the AI to read the payment clause of each agency agreement and summarise the trigger, then confirm it yourself. A table of insurers with their payment trigger and grace period is one of the most useful things to come out of the exercise.

A worked review of five exception lines

To see how the judgement works, here are five illustrative exceptions from the example month and how each resolves.

Of the five, only two lead to a chase, one corrects the ledger, one is a legitimate chargeback and one is noise. Expect a similar mix on a first run: much of it is about getting your own records right. The value of the AI's explanation column is that you read five sentences rather than reopen five policies.

Statement quirks that break extraction

Know the patterns that trip up a model reading a PDF, and fix them at the source or in your prompt.

• Wrapped lines. A long insured name wraps to a second line and the numbers sit on the first. The extraction splits one policy into two rows. Prompt: "A line without a policy number belongs to the line above."
• Subtotals in the middle. Insurers insert class subtotals. These should not be extracted as policies; tell the AI to skip lines beginning with "Total" or "Subtotal".
• Brackets and trailing minus signs. Chargebacks appear as (46.00) or 46.00-. The prompt must say both mean negative.
• Different date formats. 03/10 means 3 October for some insurers and 10 March for others. Ask for ISO dates and check one row per statement by eye.
• Multiple currencies. If an insurer pays in another currency, keep the original amount and currency in separate columns and do not let the tool convert.

After you refine the prompt for one insurer, save it as that insurer's extraction prompt. Next month's statement then takes minutes. Keep a note of which insurers will supply a spreadsheet, and ask the others to, since structured files remove most of this trouble.

Data handling and what to ask your systems vendor

Statements contain client names, policy numbers and commission terms. Check your agency agreement for confidentiality terms before uploading anything. Where a policy number is enough to match, remove client names before pasting. Use a business plan whose terms say content is not used for training: as of November 2026, OpenAI's help centre says this of ChatGPT Business and Anthropic's privacy centre says it of Claude Team, while individual plans on both may train on chats unless the setting is off. Read the terms on the vendor's own page before the first upload. If your agency management system already offers reconciliation, ask how it handles statement formats and exceptions before building your own: questions to ask about AI in agency management systems lists what to ask. The same matching idea appears in travel agent commission tracking, which is worth a skim for ideas on exception handling.

Running it monthly and checking it works

Put a fixed day in the diary: the third working day after the last statement arrives. Keep a one-page checklist beside the spreadsheet.

1. Save each statement unchanged into a dated folder.
2. Extract to the standard table and confirm line count and total against each statement.
3. Run the matching formulas and filter the exception categories.
4. Review each exception for a real cause; fix ledger errors first.
5. Send chasers for items over the materiality limit and log them.
6. Record the month's numbers: expected, received, gap, exceptions by code, recovered to date.

Judge it by three numbers over a quarter: the unexplained gap (expected minus received minus identified exceptions) should trend to near zero, the average days from exception to resolution should fall, and the number of repeat exceptions by insurer should fall as ledger errors get fixed. If the unexplained gap stays large, the extraction or the ledger is the problem, not the insurer. For where else a small brokerage saves time, see where AI saves time in a small insurance brokerage and the costs in what AI costs a small brokerage in year one.

Questions brokers ask about AI and commission statements

Should AI do the matching or should a spreadsheet?

Use the spreadsheet for matching and arithmetic, and the AI for reading messy statements into rows and for explaining exceptions. Formulas are repeatable and auditable; an assistant's arithmetic on hundreds of rows is not. Ask it to write the formulas, then run them yourself.

How accurate is AI at reading statement PDFs?

Good on clean, text-based PDFs, weaker on scans and multi-column layouts. Always check that the extracted rows sum to the statement's printed total before matching. A mismatch means a row was dropped or misread, so fix the extraction first.

Is it safe to upload commission statements?

They contain client names, policy numbers and your commission rates, which insurers may treat as confidential. Check your agency agreement, use a business plan that does not train on your content, and strip client names where the policy number is enough to match.

How long should I wait before chasing a missing commission?

Follow your agency agreement's reporting and payment terms. Many brokers set an internal rule, such as flagging anything not paid by the second statement after inception or renewal, then chasing in writing with the policy details and the expected amount.

Further reads

• AI Submission Intake: Stop Re-Keying Data Into Insurer Portals — Stop re-keying client data into insurer portals.
• How to Automate Insurance Renewals With AI and Keep More Clients — Renewal handling, which drives much of the commission.
• How Brokers Use AI to Compare Policy Wordings at Renewal — Comparing policy wordings at renewal.
• Can AI Answer Calls and Qualify Leads for an Insurance Agency? — Answering calls and qualifying leads for an agency.
• Compliance Calendar: Track Licences, Insurance and Certificates — A calendar for licences and certificates.
• AI Landlord Updates and Owner Statements in Minutes — Generating owner statements, another statement-driven task.
• ChatGPT for Accounting With Excel: Seven Tasks, Each With Its Check — Seven Excel accounting tasks for ChatGPT, each with a worked example and the check that proves the answer before you rely on it.
• Fund Administration Software for a Small Administrator: What AI Adds — Where AI helps a small fund administrator (statements, reconciliations, review notes) and which checks must stay with a named human reviewer.
• Software for Insurance Agents and Brokers: Where AI Fits the Stack — Where AI sits beside your agency management system, e-signature and quoting portals, with an integration checklist and a cost table.
• AI in General Insurance: What a Small Broker Can Use Today — Renewal reminders, quote summaries, claims chasing and client emails: what a small non-life broker can hand to AI today, and what stays with a person.
• Can AI Read Loss Runs? Carrier PDFs to Remarketing Summaries — Turn carrier loss-run PDFs into a frequency and severity table and a remarketing summary, with the checks that stop wrong totals reaching an underwriter.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support and Google Docs Editors Help pages for XLOOKUP; OpenAI help-centre and Anthropic privacy centre articles on model training (all checked 4 November 2026); general reconciliation practice; the agency agreement with each insurer governs commission terms. No vendor pricing is quoted.