A commission tracking spreadsheet for brokers
Expected against received, line by line, so a short payment is something you notice in February rather than something you find in November. Free CSV, opens in Excel or Google Sheets, no email address required.
Commission gets missed in the gap between two systems: the carrier knows what it paid, you know what you expected, and nothing compares the two. This sheet is that comparison, and it is deliberately boring.
What CovioIQ does and does not do here
Worth saying plainly, because this is a page about commission on a CRM's website. CovioIQ records a commission rate and amount against each policy and can total commission by carrier across your book. That is useful, and it is the whole of it.
CovioIQ does not reconcile carrier statements, does not do accounting and is not a commission engine. Reconciliation lives in your broker management system or your accounting software, or in a sheet like this one. We would rather hand you a working spreadsheet than imply a feature we have not built.
The columns
Twelve columns. The first six describe what you expected, the next three what happened, and the last three what you did about it.
| Column | What goes in it |
|---|---|
| statement_period | The carrier statement this line belongs to, as YYYY-MM. |
| carrier | Who owes the commission. |
| policy_ref | The policy number or record id in your BMS or CRM. Not the client name. |
| policy_type | Auto, home, business and so on. Useful when a rate applies by line. |
| premium_basis | The premium the commission was calculated on. Disagreements usually start here. |
| commission_rate_pct | The rate you expect, as a percentage. |
| expected_commission | premium_basis x rate. What should arrive. |
| received_commission | What actually arrived. Leave blank until it does. |
| variance | received minus expected. Negative is short, positive is over. |
| status | received, short, over, missing or disputed. Keep the set small. |
| followed_up_on | YYYY-MM-DD of when you raised it. Blank means nobody has. |
| notes | What the carrier said, or what you still need to check. |
The five statuses
- receivedExpected and received match. Nothing to do.
- shortLess arrived than expected. Usually a premium-basis or rate difference.
- overMore arrived than expected. Often a correction to an earlier month, not a gift.
- missingNothing arrived. New business and mid-term endorsements are the usual causes.
- disputedYou have raised it with the carrier and are waiting.
Why the sample rows are messy
The ten example rows include a short payment caused by a premium-basis disagreement, a policy that goes missing in January and arrives in March, and an overpayment that turns out to be the correction for an earlier shortfall. That last one matters: the two lines net to zero, and a tracker that flags the overpayment without connecting it to the shortfall sends you to argue with a carrier that already fixed it. Delete the samples before you use the file.
How to use it each month
- Before the statement arrives, fill in expected commission for the policies you bound or renewed. If you wait until the statement is in front of you, you will reconcile against their numbers instead of yours, which is the whole problem.
- When it arrives, enter received against each line and let variance fall out.
- Work only the non-zero variances. Set a threshold you will actually honour: chasing four dollars costs more than four dollars.
- Check whether an overpayment settles an earlier shortfall before raising anything.
- Record the follow-up date. A variance with no date beside it is one nobody owns.
The rate and premium basis on each policy are the two fields worth keeping accurate in whatever system holds them, because everything above is arithmetic on those two numbers. CovioIQ stores both against a policy, which is what policy tracking is for.
More broker templates
All free, all real files, none of them asking for an email address.
Related: how commission management works, client and policy tracking sheet.