Automate commission calculations with multi-tier structures and team bonuses
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Commission disputes come from ambiguity, not arithmetic. This calculator puts the tier table, the attainment basis and the accelerators in named cells so the number can be explained line by line.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Rep | Quota | Closed | Attain % | Rate | Commission |
| 2 | A. Duval | 120,000 | 148,200 | 123.5% | 12.0% | 19,584 |
| 3 | K. Owens | 120,000 | 97,400 | 81.2% | 6.0% | 5,844 |
| 4 | M. Ferrer | 90,000 | 91,800 | 102.0% | 10.0% | 9,180 |
| 5 | S. Novak | 90,000 | 44,100 | 49.0% | 0.0% | 0 |
Why each one is written the way it is
=IF([@Quota]=0,"",[@Closed]/[@Quota])
Attainment against quota. The whole scheme hangs on this one definition, which is why it is calculated in one place and referenced everywhere.
=LOOKUP([@[Attain %]], Tiers[Floor], Tiers[Rate])
The tier rate from a table rather than nested IFs. Changing the scheme becomes editing four rows instead of auditing forty formulas.
=[@Closed]*[@Rate]
Payout on total closed value. The alternative — paying the accelerator only on the excess above quota — is a different scheme and the template supports both; the choice is a single flag.
=SUMIFS(Deals[Value],Deals[Rep],[@Rep],Deals[Stage],"Closed won",Deals[Close],">="&PeriodStart)
Closed value from the deal register, so commission and pipeline can never disagree.
A worked example with real numbers
A 12-person sales team disputed commission every quarter. The scheme document said accelerators applied "above quota", which finance read as applying to the excess and reps read as applying to everything. On €148k of closings the difference was €6,300 per rep. Putting the flag in the sheet and showing both figures made the choice explicit; leadership picked one, wrote it down, and the disputes stopped.
Tab by tab
| Tab | Contents |
|---|---|
| Commission | Per-rep attainment, rate and payout. |
| Tiers | The rate table — floor, rate and accelerator basis. |
| Deals | Closed deals feeding the attainment calculation. |
| History | Prior periods, retained for year-to-date and true-ups. |
| README | The scheme in words, next to the cells that implement it. |
What is included, and what to look at next
Specific to this workbook, not generic
Either is defensible; ambiguity is not. Applying to all revenue is a stronger incentive and more expensive. Pick one, write it in the plan document, and set the flag to match.
As negative rows in the Deals sheet with the original close date, so the period the revenue was credited to is the period it is reversed from. Adjusting the current period hides the pattern.
Yes — add a team attainment column and a second rate. Keep the two payouts as separate columns so a rep can see which part of their number came from where.
"This commission calculator has eliminated errors and disputes. Our sales team loves the transparency, and payroll processing is now a breeze."