Producer split calculator
Open the paid commission lines and type the split rules: a house percentage plus one to three producers for each producer code. Every line is divided by its rule so the parts add up to the line's commission to the cent, and each producer gets a payout statement listing their lines.
The file is read in your browser and stays on this device. Nothing is uploaded. The split rules you type are kept in this browser only.
The file is read in this browser and is not uploaded.
These are matched from the header words. Check them and change any that are wrong. The producer or agent column picks the split rule for each line.
One rule per producer code as it appears on the lines. A rule with a blank code applies to every line no other rule names. The house share and the producer shares on a rule add up to 100. The rules are kept in this browser only.
| Producer code on the line | House % | Producer 1 | % | Producer 2 | % | Producer 3 | % |
|---|
Totals by payee
Lines with no split rule
Every split, line by line
What gets checked
- Each line's splits add up to the line's commission, to the cent.
- Split percentages add up to 100% on every line.
- Producer totals plus the house total equal total commission.
- All split parts, added a second time in a different way, equal total commission, and each payee's total is the sum of that payee's parts.
- Every part is the share its rule gives, recalculated line by line.
- Every line has a split rule and appears once. A line with no rule is listed, is not split, and fails the check.
- Every row with data 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 paid lines. Drop a commission statement or the normalized file from the statement tool (.csv or .xlsx). The policy number, insured, commission amount, and producer or agent columns are matched from the header words and can be changed.
- Type the split rules. Add one rule for each producer code that appears on the lines: the house percentage and up to three producers with their percentages. A rule with a blank code applies to every line no other rule names.
- Calculate. Each line is divided by its rule. Cents that do not divide evenly go to the shares with the largest remainders, so the parts always add up to the line exactly.
- Review and export. Totals by payee, a payout statement for each producer, and every split are shown with the certificate. Download Excel (one sheet per producer) or CSV, or print.
Worked example
Five made-up paid lines and three rules. Code KIM: house 40%, Kim Ortiz 60%. Code LEE: house 50%, Lee Park 30%, Sam Diaz 20%. Lines with any other code or none: house 100%.
| Policy number | Producer code | Commission | House | Kim Ortiz | Lee Park | Sam Diaz |
|---|---|---|---|---|---|---|
| POL-10021 | KIM | $600.00 | $240.00 | $360.00 | ||
| POL-10022 | KIM | $125.00 | $50.00 | $75.00 | ||
| POL-10023 | LEE | $250.00 | $125.00 | $75.00 | $50.00 | |
| POL-10024 | LEE | -$36.00 | -$18.00 | -$10.80 | -$7.20 | |
| POL-10025 | $125.00 | $125.00 |
Every line's parts add up to its commission. Kim Ortiz is paid 360.00 + 75.00 = $435.00, Lee Park 75.00 − 10.80 = $64.20, and Sam Diaz 50.00 − 7.20 = $42.80, so producers total $542.00. The house total is 240.00 + 50.00 + 125.00 − 18.00 + 125.00 = $522.00. Producers plus house: 542.00 + 522.00 = $1,064.00, the total commission on the five lines. When cents do not divide evenly, they are still placed exactly: $100.00 split 33.33% / 33.33% / 33.34% gives $33.33, $33.33, and $33.34.
The example uses made-up names and numbers.
Questions
What happens when a split does not come out to whole cents?
Each share is first rounded down to the cent, and the cents left over are handed out one at a time to the shares with the largest remainders, earlier shares first on a tie. The parts of a line always add up to the line's commission exactly.
What if a rule's percentages do not add up to 100?
The page still divides each line in proportion so nothing is lost, but the percent check fails, the result is marked not verified, and an export is labelled UNVERIFIED until the rule is corrected.
How are negative lines such as chargebacks split?
The same way as positive lines, with the same percentages, so each payee's part of a chargeback is negative and reduces their total.
How does a line find its rule?
By the producer or agent code in the mapped column, ignoring upper and lower case and extra spaces. If no rule names that code, the rule with a blank code is used. If there is no such rule, the line is listed as having no rule and is not split.
Does the tool decide how commissions are shared?
No. It applies the percentages you type and proves the arithmetic. It makes no statement about what any producer agreement or contract requires.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.