Missing commission finder
Open your policy list and your commission statement lines. Each policy is matched to lines with the same policy number, the same carrier where both name one, and a date in its payable period. Every policy payable by the as-of date that no line matches is listed with its age in days and its expected commission, totalled by carrier and by age.
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. Ages are counted in days from each policy's effective or renewal date to the as-of date.
These are matched from the header words. Check them and change any that are wrong. The policy list needs a policy number, an effective or renewal date, and either an expected commission amount or a premium and an expected rate.
The statement needs a policy number and a commission amount. A carrier and a date are used when the file has them; with no date column, lines are matched on policy number and carrier alone.
Summary
Expected commission with no statement line, by carrier and age
Possible matches
Statement lines left unmatched next to listed policies with the same policy number, the same number once punctuation and leading zeros are dropped, or the same insured name. They are listed for review and are not matched: each line is still counted as not matched and each policy as listed.
What gets checked
- Matched plus listed plus payable after the as-of date equals every policy row, by count and by expected commission, added in an independent pass.
- Lines matched plus lines not matched equals every statement line, by count and by commission.
- No statement line is matched to two policies, and no policy row or statement line is counted twice.
- Each statement line sits where its policy number, carrier, and date place it, worked out again in a second pass, so no match was missed.
- A policy is listed only when no statement line matched it and it became payable on or before the as-of date, and each age is recounted in days a second way.
- The carrier totals and the age columns each add up to the expected commission on the listed policies, and the Expected commission column of the export, read back, adds up to the same amount.
- Possible matches are listed only: each line stays not matched and each policy stays listed.
- 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 policy list in the first box and the commission statement, or a commission log with lines from several carriers, in the second. The book-of-business export the expected-versus-paid reconciler reads works here too; this tool also reads its carrier and its effective or renewal date. Both files are read in the browser.
- Check the columns and the as-of date. The policy list needs a policy number, an effective or renewal date, 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, and its carrier and date are used when present. The as-of date starts as today.
- Match and age. A line is matched to a policy with exactly the same policy number (ignoring upper and lower case and extra spaces), the same carrier where both name one, and a date on or after the policy's date and before the next date listed for that policy. A policy no line matches is aged in days from its date to the as-of date, in four columns: 0–30, 31–60, 61–90, and over 90 days.
- Review and export. The listed policies, the expected commission by carrier and age, the statement lines left unmatched, possible matches, and the certificate are shown together. Download Excel or CSV, or print.
Worked example
A made-up policy list of seven policies from two carriers, as of June 30, 2026, compared with four statement lines: POL-20011 paid $600.00 dated 05/01/2026, POL-20013 paid $165.00 dated 04/20/2025, POL30022 paid $150.00 dated 05/20/2026, and POL-30023 paid $95.00 dated 06/01/2026.
| Policy number | Insured | Carrier | Effective or renewal date | Premium | Expected rate | Expected commission |
|---|---|---|---|---|---|---|
| POL-20011 | Maple Street Bakery LLC | Example Mutual | 05/01/2026 | $4,800.00 | 12.5% | $600.00 (calculated) |
| POL-20012 | Northside Dental PC | Example Mutual | 06/15/2026 | $1,999.99 | 12% | $240.00 (calculated) |
| POL-20013 | Harbor Cycle Shop | Example Mutual | 04/20/2026 | $1,500.00 | 12% | $180.00 (calculated) |
| POL-30021 | Sunrise Yoga Studio | Sample Casualty | 03/01/2026 | $2,000.00 | 10% | $200.00 (calculated) |
| POL-30022 | Jordan Rivera | Sample Casualty | 05/20/2026 | $1,250.00 | 12% | $150.00 (calculated) |
| POL-30023 | Avery Chen | Sample Casualty | 06/01/2026 | $95.00 | ||
| POL-30024 | Pine Hill Florist | Sample Casualty | 07/15/2026 | $3,000.00 | 10% | $300.00 (calculated) |
Matched: POL-20011 and POL-30023 ($695.00 expected, $695.00 on their lines). No statement line found: POL-30021, 121 days, $200.00 (over 90 days); POL-20013, 71 days, $180.00 (61–90 days), because its only line is dated 04/20/2025, before the policy's 04/20/2026 date; POL-30022, 41 days, $150.00 (31–60 days); and POL-20012, 15 days, $240.00 (0–30 days). Payable after the as-of date: POL-30024 ($300.00). Policies: 2 + 4 + 1 = 7, and 695.00 + 770.00 + 300.00 = $1,765.00 expected. By carrier: Example Mutual $420.00 and Sample Casualty $350.00, together $770.00, the same as 240.00 + 150.00 + 180.00 + 200.00 by age. Statement lines: 2 matched + 2 not matched = 4, and 695.00 + 315.00 = $1,010.00. Two possible matches are listed for review and stay unmatched: the 2025 line for POL-20013, and the line POL30022 next to the policy POL-30022.
The example uses made-up names and numbers.
Questions
What is a policy's payable period?
It starts on the effective or renewal date the policy list gives and runs until the next date listed for the same policy; the latest date's period has no end. A statement line dated in that span counts for that policy. A line dated earlier belongs to an earlier term and is listed as not matched. A line with no date counts when the policy is listed for one date only.
What if a statement shows the original effective date on every renewal line?
Lines dated before a renewal's date are not matched to that renewal, so the renewal is listed and the lines appear as dated before the policy became payable. With the statement's date column set to Not in this file, lines are matched on the policy and carrier alone.
How are carriers compared?
Carrier names are compared after trimming, collapsing spaces, and ignoring upper and lower case. A line whose carrier differs from the policy list's is not matched; it is shown next to the policy as a possible match. When either side names no carrier, the policy and date decide. A statement from one carrier with no carrier column can be given that carrier's name in the box above.
Where does the expected commission come from?
From the policy list, read the same way the expected-versus-paid reconciler reads a book of business. If a row has an expected commission amount, that amount is used. Otherwise it is premium × expected rate, rounded to the cent. Rows for the same policy and date are added together.
Does a listed policy mean a carrier owes the agency money?
No. It means only that no line in the statement file opened here was found for that policy and its date. The tool makes no statement about what any agreement or commission schedule requires.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.
Template updates by email
At most four emails a year, when the free templates and rate tables for insurance agency commissions are updated for a new quarter. Each has a one-click unsubscribe link.
Only the address typed here is sent, never anything from a file opened on this page. Nothing is added until the link in the confirm email is opened. Mathproven is a product of OpenSlot LLC, PO Box 96, Paintsville, KY 41240, USA. Privacy.