What's inside
- Flat rate: One rate on all credited revenue.
- Marginal tiers: Each rate applies only to the revenue inside its band.
- Retroactive tiers: The rate of the highest band reached applies to all revenue.
- Accelerator: A multiplier on the rate for revenue above a quota threshold.
- Capped plan: Commission stops growing once a cap is reached.
- Recoverable draw: A monthly advance recovered from later commission above the draw.
- Split credit: Deals shared between reps by credit share.
- Gross margin: Commission on gross margin (revenue minus cost) instead of revenue.
Each sheet isolates one mechanism so the arithmetic stays readable. For a payout run that combines tiers, a cap, splits and clawbacks across a whole team, use the commission spreadsheet template.
How to use it
- Open the sheet closest to your plan and replace the yellow inputs with your own rate, quota or tiers.
- Run last period's real numbers through it and compare with what you actually paid; differences usually reveal an unwritten rule.
- Try the edge cases: a rep just below and just above a tier boundary, a huge deal, a cancelled deal, a mid-period hire.
- Model the total cost across the team with the plan simulator before you announce anything.
- Write the final rules into the plan document; the commission agreement template lists the clauses to cover.
Formulas by structure
- Flat rate:
Commission = Credited revenue × Rate - Marginal tiers:
Tier n = MAX(0, MIN(Revenue, To) − From) × Rate, summed across tiers - Retroactive tiers:
Revenue × rate of the band where From ≤ Revenue < To - Accelerator:
MIN(Revenue, Threshold) × Rate + MAX(0, Revenue − Threshold) × Rate × Multiplier - Capped plan:
MIN(Revenue × Rate, Cap), with the reduction shown as its own line - Recoverable draw:
Paid = MAX(Draw, Earned − Balance owed);New balance = Balance + Paid − Earned - Split credit:
Credited = Deal amount × Share; rep total bySUMIF - Gross margin:
MAX(0, Revenue − Cost) × Rate
To see how much the choice between marginal and retroactive tiers costs at different attainment levels, use the tiered commission calculator.
Frequently asked questions
Which commission structure should I start with?
Start with the simplest structure that pays for the behavior you want. A flat rate is easiest to explain; marginal tiers or an accelerator reward performance above quota without the cliffs of retroactive tiers. Test any candidate on last year's deals before rolling it out.
Can I combine several structures in one plan?
Yes, most real plans combine tiers, a cap or accelerator, splits and clawbacks. These sheets isolate one mechanism each so the math is easy to follow; the commission spreadsheet template and the online calculator combine them.
Do the formulas work in Google Sheets?
Yes. Upload the .xlsx to Google Drive and open it with Google Sheets. The workbook only uses common functions such as SUM, SUMIF, SUMPRODUCT, MIN, MAX, IF and ROUND.
Is the recoverable draw balance owed if a rep leaves?
It depends on the written agreement and on state law. The sheet tracks the balance; whether it is forgiven or recoverable at termination should be set out in the plan document and reviewed by counsel.