Commission calculation template
By the Analistable team · Updated · 2 min read
List closed deals on the Deals sheet and commission rates per product line on the Rates sheet. Each deal looks up its rate with INDEX/MATCH and calculates commission; the Payout sheet totals sales and commission per rep, compares sales with quota, and adds a 2% accelerator on sales above quota.
Part of our guide: How to join spreadsheets on a common column
What the template does
Commission is a join: deals from the CRM on one side, the commission plan on the other. Keeping the plan in its own small Rates table means a rate change is one cell, not a hunt through formulas. The template then rolls deals up per rep and applies a simple quota accelerator. The lookup pattern is the one in the joining spreadsheets guide; VLOOKUP vs XLOOKUP vs INDEX/MATCH explains why INDEX/MATCH is used here.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Deals | Deal ID, Rep, Product line, Amount, Rate, Commission | Yes (first four) |
| Rates | Product line, Rate | Yes |
| Payout | Rep, Deals, Sales, Base commission, Quota, Attainment %, Accelerator, Total payout | Rep names and Quota |
How to fill it in
- Export closed-won deals for the period and paste ID, rep, product line and amount on Deals. Only include deals that qualify under your plan (for example, paid invoices; see matching CRM deals to invoices).
- Enter each product line's rate on Rates as a decimal (0.1 = 10%).
- On Payout, list each rep and type their quota for the period.
How the formulas work
Rate lookup and commission per deal:
=IFERROR(INDEX(Rates!$B$2:$B$50,MATCH(C2,Rates!$A$2:$A$50,0)),0)
=ROUND(D2*E2,2)The accelerator pays 2% on sales above quota; change 0.02 to match your plan:
=IF(C2>E2,ROUND((C2-E2)*0.02,2),0)A product line missing from Rates gets a rate of 0, so the deal earns nothing until you fix it; look for zeros in the Rate column before paying out.
Worked example
| Rep | Deals | Sales | Base commission | Quota | Attainment % | Accelerator | Total payout |
|---|---|---|---|---|---|---|---|
| Mia | 4 | 29000 | 2280 | 25000 | 116 | 80 | 2360 |
| Noah | 3 | 20500 | 1900 | 22000 | 93.2 | 0 | 1900 |
| Omar | 3 | 26000 | 1250 | 25000 | 104 | 20 | 1270 |
| Total | 10 | 75500 | 5430 | 72000 | 104.9 | 100 | 5530 |
Omar sells more than Noah but earns less base commission because most of his sales are hardware at 3%, which is exactly what a product-line rate is designed to do.
Limits and when to move on
- Tiered plans (different rates as attainment rises) need a tier table and an approximate MATCH; this template uses one flat accelerator.
- Splits between two reps need one Deals row per rep with their share of the amount.
- If deals come from several regional files, combine them first, or load them into Analistable and ask for commission by rep; see ranking reps across regional files.
Frequently asked questions
- Can rates differ by rep?
- Yes. Add a Rep column to Rates, build a combined key (rep and product line) on both sheets, and match on that.
- Should commission use bookings or payments?
- That is a plan decision. The template calculates on whatever amounts you paste in, so filter the deals to the qualifying ones first.
- Does it work in Google Sheets?
- Yes. INDEX/MATCH, SUMIFS, COUNTIFS, ROUND and IF all work the same way.