Free Excel template · Excel for sales teams
Free tiered commission calculator template
For sales managers and reps checking commission under a tiered plan, where the rate goes up as attainment goes up. Enter each rep's quota and closed-won revenue and edit the tier table to match your plan. The template works out attainment, finds the tier rate, and calculates commission both ways plans are commonly written: the whole amount at the reached tier's rate, or each slice of revenue at its own rate.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Quota | Closed won | Attainment | Tier rate | Commission (whole amount at tier rate) | Commission (each slice at its rate) |
| 2 | Maya | 60000 | 66000 | 1.1 | 0.1 | 6600 | 4200 |
| 3 | Luis | 50000 | 42500 | 0.85 | 0.07 | 2975 | 2475 |
| 4 | Priya | 55000 | 71500 | 1.3 | 0.12 | 8580 | 5005 |
| 5 | Sam | 45000 | 19800 | 0.44 | 0.05 | 990 | 990 |
| 6 | Dana | 50000 | 50000 | 1 | 0.1 | 5000 | 3000 |
| 7 | Omar | 40000 | 46200 | 1.155 | 0.1 | 4620 | 3020 |
| 8 | Total | 300000 | 296000 | 0.98666666667 | 28765 | 18690 | |
| 9 | |||||||
| 10 | |||||||
| 11 | Attainment from | Rate | Change from tier below | ||||
| 12 | 0 | 0.05 | 0.05 | ||||
| 13 | 0.5 | 0.07 | 0.02 | ||||
| 14 | 1 | 0.1 | 0.03 | ||||
| 15 | 1.25 | 0.12 | 0.02 |
How the formulas work
Attainment
Closed-won revenue divided by quota. Maya closed $66,000 against $60,000, so she is at 110%. The IF avoids #DIV/0! for a rep with no quota set yet.
Tier rate from a lookup table
With TRUE, VLOOKUP finds the largest "Attainment from" value that is not above the rep's attainment, so 110% lands on the 100% row and returns 10%. The table must be sorted from lowest to highest. To change the plan, edit the table, not the formulas.
Commission: whole amount at the tier rate
Every dollar is paid at the rate of the tier the rep reached. Simple, but it has a cliff: a rep at 99% earns $59,400 x 7% = $4,158, and one more deal to reach 100% jumps it to $6,000. Some plans want that push; others avoid it.
Commission: each slice at its own rate
This pays the first 50% of quota at 5%, the next 50% at 7%, and so on, like tax brackets. The trick is column C, which holds how much each tier's rate goes up from the one below. For every tier the rep has passed, SUMPRODUCT multiplies the attainment above that tier's start by the rate increase, then the total is multiplied by quota to turn it into dollars. Maya, with a $60,000 quota at 110%: the first $30,000 at 5% ($1,500), the next $30,000 at 7% ($2,100) and the $6,000 above quota at 10% ($600), for $4,200.
Make it yours
- Your comp plan document decides which method applies, plus details like caps, draws and clawbacks. Use the column that matches your plan and hide the other.
- Add a tier by inserting a row inside the table (above row 15), keeping it sorted, and filling the change-from-below formula down. Then widen the ranges in columns E and G if the new row is outside them.
- Check a result by hand once: for the slice method, work out each slice of revenue times its rate on paper and compare.
- Commission on paid invoices instead of closed deals? Put collected revenue in column C instead of closed-won.
FAQ
How do I calculate tiered commission in Excel?
Put the tiers in a table sorted by the starting threshold. For a single rate on the whole amount, VLOOKUP the rate with TRUE and multiply. For a rate per slice, use SUMPRODUCT with the rate differences, as in column G of this template.
What is the difference between the two commission columns?
Column F pays all revenue at the rate of the highest tier reached. Column G pays each slice of revenue at the rate for that slice. When rates rise with each tier, column G is always the same or lower for the same attainment, and it has no sudden jump at a tier boundary. Maya earns $6,600 one way and $4,200 the other.
Can I use thresholds in dollars instead of attainment?
Yes. Put dollar thresholds in column A, look up closed-won revenue instead of attainment, and in column G drop the final *B2, because the slices are already in dollars.
Functions used
IFPicks a result from a testVLOOKUPVertical lookupROUNDRounds to digitsSUMPRODUCTMultiply then addSUMAdds numbers
Related guides
- How to write a nested IF in Excel
- How to calculate percentage in Excel
- Absolute vs relative cell references in Excel
- VLOOKUP not working: 7 causes and fixes
More free templates
- Sales pipeline tracker templateFor reps and managers who want a forecast they can explain.Free .xlsx
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
Updated