Expected versus paid commission reconciler
Open your book-of-business export and a commission statement. Policies are matched only where the policy numbers are exactly the same, then sorted into five buckets: paid as expected, short-paid, over-paid, paid but not in book, and in book but not paid. Near matches are listed for you to review and are never accepted automatically.
Both files are read in your browser and stay on this device. Nothing is uploaded.
Both files are read in this browser and are not uploaded.
These are matched from the header words. Check them and change any that are wrong. The book needs a policy number, and either an expected commission amount or a premium and an expected rate.
Summary
Possible matches
Policies left unmatched on both sides whose numbers are the same once punctuation and leading zeros are dropped, or whose insured names are the same. They are listed for review and are not matched: each side is still counted as unmatched above.
What gets checked
- Paid as expected plus short-paid plus over-paid plus not paid equals every book row, by count and by expected amount, added in an independent pass.
- Paid as expected plus short-paid plus over-paid plus not in book equals every statement line, by count and by amount paid, added in an independent pass.
- No book row and no statement line is used twice.
- Rows are grouped only where the policy numbers are exactly the same, and every exact match was made.
- Each policy's expected and paid amounts are the sums of its own rows, and each policy is in the bucket those amounts call for.
- Possible matches are listed only: both sides of each one are still counted as unmatched.
- Every row with data in both files was read. A row that could not be read is listed with the reason and the result is marked not verified.
How it works
- Open the two files. Drop the book-of-business export in the first box and the commission statement (or the normalized file from the statement tool) in the second. Both are read in the browser.
- Check the column mapping. The book needs a policy number and either an expected commission amount or a premium and an expected rate; when both are present the amount in the file is used. The statement needs a policy number and a commission amount. Any column choice can be changed.
- Reconcile. Rows are grouped by exact policy number (ignoring upper and lower case and extra spaces). Several statement lines for one policy are added together, and the paid total is compared with the expected total to the cent.
- Review and export. Each bucket is listed with its policies, expected and paid amounts, and differences, along with possible matches and the certificate. Download Excel or CSV, or print.
Worked example
A made-up book of six policies, compared with a statement that paid $600.00 on POL-10021, $125.00 on POL-10022, $250.00 on POL-10023, -$36.00 on POL-10024, and $125.00 on POL-10025.
| Policy number | Insured | Premium | Expected rate | Expected commission |
|---|---|---|---|---|
| POL-10021 | Maple Street Bakery LLC | $4,800.00 | 12.5% | $600.00 (calculated) |
| POL-10022 | Jordan Rivera | $1,250.00 | 12% | $150.00 (calculated) |
| POL-10023 | Northside Dental PC | $3,333.33 | 7.5% | $250.00 |
| POL-10024 | Avery Chen | -$40.00 | ||
| POL 10025 | Harbor Cycle Shop | $999.99 | 12.5% | $125.00 (calculated) |
| POL-10030 | Sunrise Yoga Studio | $2,000.00 | 10% | $200.00 (calculated) |
Paid as expected: POL-10021 and POL-10023 ($850.00 expected, $850.00 paid). Short-paid: POL-10022, expected $150.00, paid $125.00, difference -$25.00. Over-paid: POL-10024, expected -$40.00, paid -$36.00, difference $4.00. In book but not paid: POL 10025 and POL-10030 ($325.00 expected). Paid but not in book: POL-10025 ($125.00 paid). Book rows: 2 + 1 + 1 + 2 = 6, and 850.00 + 150.00 − 40.00 + 325.00 = $1,285.00 expected. Statement lines: 2 + 1 + 1 + 1 = 5, and 850.00 + 125.00 − 36.00 + 125.00 = $1,064.00 paid. One possible match is listed for review: the statement's POL-10025 and the book's POL 10025 are the same once punctuation is dropped. They stay in their unmatched buckets.
The example uses made-up names and numbers.
Questions
Why was a near match not accepted?
A number that is almost the same may belong to a different policy. The tool matches only on the exact number and lists near matches (the same number once punctuation and leading zeros are dropped, or the same insured name under a different number) so that a person decides. Correcting the number in one of the files and running again matches the two.
What counts as the exact same policy number?
The numbers are compared after trimming spaces at the ends, collapsing repeated spaces, and ignoring upper and lower case. Hyphens, slashes, and leading zeros are part of the number.
How are several statement lines for one policy handled?
They are added together, including negative lines such as chargebacks, and the total paid is compared with the total expected for that policy. Several book rows for one policy are added together the same way.
Where does the expected commission come from?
From your book export. If a row has an expected commission amount, that amount is used. Otherwise it is premium × expected rate, rounded to the cent. A row with neither is listed as a row that could not be read.
Does the tool say whether a carrier owes the agency money?
No. It shows the arithmetic difference between what your book expected and what the statement paid. It makes no statement about what any contract or commission schedule requires.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.