Insurance Commissions: Reconciling Policy, Payment, and Expected Commission

Commission reconciliation is a three-way match: the agency's policy record, the premium payment event, and the commission line received from the carrier. The three sources arrive under different keys, at different cadences, and cut into different periods. The mechanism joins them and produces a variance queue rather than a single final number.

This is a technical description of data and workflow, not financial advice and not a statement about entitlement, commission rates or regulatory duties.

Three record sets, three keys

Each source has its own shape, and they cannot be treated as one table:

• The policy record. Created at the agency: policy number, client id, carrier, line of business, start date, status, and the calculation parameters that were agreed. This is the only record the agency fully controls.
• The premium payment event. Arrives from the carrier or a collection report: date, amount, payment method, and the coverage period it relates to. It is identified by a collection reference, which is not always the policy number.
• The statement commission line. Arrives in a periodic file: line id, line type, amount, and sometimes a policy reference formatted differently from the one you hold.

Everything rests on one identifier mapping table: the policy number as you hold it against the string as each carrier sends it. Without that table, one carrier changing its format takes down the whole run.

What expected commission is, as a data object

Expected commission is a record your system created, not an external fact. It should carry at least these fields: the policy it relates to, the payment event that triggered it, the period, the computed amount, the identity of the parameters used in the calculation, and a creation timestamp.

Versioning is the critical part. Calculation parameters change over time, and if they are stored only as a current field on the policy, every historical figure shifts retroactively the moment somebody edits them. Keeping a reference to the parameter version inside the expected-commission record is the difference between a report you can explain a year later and one that changes on every reload. The same failure is familiar from reconciling bank and card transactions, where a price or fee overwritten on the master record rewrites an entire history.

Why does a commission statement never close line for line?

Because the three sources do not speak in the same unit. These are the common sources of a gap:

The last row is not trivial at scale. Rounding differences repeat across thousands of lines and add up to a figure that looks like a real error, which is why you want a defined variance threshold below which a line closes automatically with a marker.

Which match class does a line fall into, and what does it trigger?

• Full match. Policy, payment event and commission line all found, amount within threshold. The line closes and locks.
• Amount-only match. The amount is right but the policy id was not found. This line joins the mapping queue, not the amounts queue.
• Partial match. Policy and payment found, and the amount differs from expectation beyond the threshold. This is the line that needs a real enquiry.
• Line with no expectation. A commission arrived on a policy that has no expected-commission record. Do not delete it and do not assume it is a gift.
• Expectation with no line. An expected commission was created and never appeared in any statement. After a defined number of periods it moves to enquiry.
• Orphaned negative line. A reversal with no identifiable original. This is the most dangerous class, because it reduces a figure with no explanation.

Negative events: cancellation, refund and reversal

A negative statement line is an event, not a correction. It carries three details that must be preserved: what it reverses, which period it relates to, and the reason as it appeared in the statement. All three are needed to answer, later, why the monthly figure dropped.

• A backward link. Every negative line points at an existing commission record. Without the link you cannot tell whether a given reversal has already been counted.
• A period window. A reversal can relate to a period you already closed. Closing a period on your side must allow a late event to be recorded, rather than an edit to the closure.
• The effect on balances. If the amount has already been drawn, the negative event creates a receivable, which puts it in the same family of processes as managing open debts and collection.

The rule is short: a negative event never deletes the positive one. Both records live side by side, and the balance is their sum.

What makes a variance queue you can actually work

• An owner on every line, because a variance with no name never closes.
• An age in days, so you can see what has been sitting for a fortnight.
• Absolute amount for sorting, because the five largest lines usually explain most of the gap.
• The match class, so mapping problems are handled as one group instead of one at a time.
• A reference to the source statement and line, including the filename and the load date.
• A defined variance threshold, below which closure is automatic and flagged rather than manual.
• A lock on a closed period, so that a correction is a new record rather than an edit.

That queue is also the operational layer described in automation for insurance agencies: the system surfaces what does not reconcile, and a person decides.

Sources

• Israel Capital Market, Insurance and Savings Authority