Calculating commission in Excel is simple when the plan pays a flat rate: multiply eligible sales by the commission rate. Once you add tiers, clawbacks, quotas or manual adjustments, write the rules down first and test every calculation. This guide covers the formulas, how to structure a commission tracker, and how to tell when it’s time to move beyond spreadsheets.
What is a sales commission?
A sales commission is the variable part of a rep’s pay that depends on a metric defined in the plan, such as closed deals, invoiced revenue or cash collected. Before you calculate it, be clear on which deals count, which period they belong to, and which adjustments apply.
The most common structures are:
- Flat rate: one percentage on all eligible sales.
- Tiered commission: different rates for different sales bands.
- Accelerators: higher rates once a rep passes quota.
- Base + commission: a fixed salary plus a variable component.
The right formula depends on the approved rule, not just the amount sold. For the fundamentals, see our guide on how to calculate commission.
Commission formula in Excel: flat rate
For a simple commission, multiply commissionable sales by the agreed rate. If sales are in B2 and the rate is in C2, enter =B2*C2 in D2 and copy it down. For example, $10,000 in eligible sales at 5% earns $500.
New to Excel? Microsoft’s official Excel guide covers the formatting basics.
How to set up a commission tracker in Excel
A basic commission tracker needs these columns:
| Sales rep | Period | Eligible sales | Commission rate | Commission earned |
|---|---|---|---|---|
| John | April | $10,000 | 5% | $500 |
| Maria | April | $80,000 | 12% | $9,600 |
Two rules keep the tracker trustworthy:
- State whether the rate applies to all sales or only to sales above a threshold.
- Never mix closed, invoiced and collected revenue in the same column. The plan decides which one earns credit.
Tiered Commission Calculator in Excel (With Formula)
In a progressive (marginal) plan, each rate applies only to its own band. With 5% on the first $20,000, 7% on the next $30,000 and 10% on anything above $50,000, use:
Formula:Â =IF(B2<=20000,B2*5%,IF(B2<=50000,20000*5%+(B2-20000)*7%,20000*5%+30000*7%+(B2-50000)*10%))
| Sales | Calculation | Commission |
|---|---|---|
| $15,000 | $15,000 Ă— 5% | $750 |
| $35,000 | $20,000 Ă— 5% + $15,000 Ă— 7% | $2,050 |
| $60,000 | $20,000 Ă— 5% + $30,000 Ă— 7% + $10,000 Ă— 10% | $4,100 |
Test values just below and just above each threshold before you use the sheet for a real payout. If your Excel uses a semicolon as the argument separator (common in European locales), swap the commas.
Retroactive tiers with VLOOKUP
Some plans work differently: once a rep crosses a threshold, the new rate applies to all their sales, not just the band above it. For that, keep the rates in a lookup table and use VLOOKUP with approximate match.
Rate table in F2:G4:
| Threshold (F) | Rate (G) |
|---|---|
| $0 | 5% |
| $20,000 | 7% |
| $50,000 | 10% |
Formula: =B2*VLOOKUP(B2,$F$2:$G$4,2,TRUE)
With this rule, $35,000 earns $2,450 (all at 7%) and $60,000 earns $6,000 (all at 10%). Compare that with $2,050 and $4,100 under the progressive plan above. Same sales, very different payout. Confirm which rule your plan uses before you build the sheet.
Should you replace Excel for commission payouts?
You don’t need to replace Excel just because alternatives exist. The decision depends on your plan rules, your data sources and how much work each close takes to review.
| Your situation | Option worth evaluating | What to check |
|---|---|---|
| Few, stable rules; data from one source; calculations easy to review | Keep Excel, with clear controls | Sheet owner, permissions, locked formulas, data validation, change log |
| The sheet handles the maths, but data loading, approvals or reporting happen elsewhere | Improve the workflow or connect existing tools | Which system is the source of truth, who maintains the rules, approval trail |
| Multiple rules, products, territories, currencies or exceptions; repeated reviews or disputes every close | Evaluate a dedicated commission management platform | Rule fit, integrations, audit trail, permissions, approvals, rep experience, implementation effort |
| A highly specific process or strict internal controls | Compare a custom-built app with a dedicated platform | Who builds and maintains it, how changes are tested, who owns incidents |
A new tool should fix a specific problem in your current process. Compare options using the same plans, data and edge cases, and ask for a demo of your own rules, not a feature list. If you’re considering automation, see how Remuner’s automated commission calculations work.
The cost of calculating commission in Excel: a model to fill in
To compare staying in Excel with any alternative, log the real hours and costs of your process. Use the same period for both scenarios and count each task once.
| Input | Your team’s value | How to get it |
|---|---|---|
| Hours per close preparing and validating data | hours | Observed time |
| Hours per close reconciling calculations and resolving differences | hours | Observed time |
| Hours per close on disputes, corrections and approvals | hours | Observed time |
| Fully loaded labour cost per hour | $/hour | Internal rate used by Finance |
| Closes per year | Annual frequency | |
| Annual cost of current tools tied to the process | $/year | Licences or other attributable costs |
| Annual cost of the current process | $/year | (Total hours per close Ă— cost per hour Ă— closes per year) + tools |
- Estimated annual operating cost: total task hours per close Ă— cost per hour Ă— closes per year.
- Estimated current annual cost: annual operating cost + annual tool cost.
To assess an alternative, add annual licences, implementation costs spread over the period your company chooses, and internal admin and maintenance hours. The difference between scenarios is an estimate based on your data, not a promised saving or guaranteed ROI.
How to migrate commission calculations out of Excel
Before you retire the spreadsheets, confirm that rules, data and results have moved over correctly:
- Inventory the real process. Collect every sheet, hidden tab, formula, macro, manual adjustment, off-sheet rule and the owner of each step.
- Write the rules in plain language. Define metrics, dates, tiers, exceptions, clawbacks, territory changes, approvals and rounding.
- Assign a source to every data point. Document which system feeds sales, invoices, payments, quotas and hierarchies. Fix duplicates and missing data before automating.
- Set up access, validations and approvals. Decide who can change rules, who reviews issues and who signs off the close.
- Recalculate historical periods. Compare the new setup against reviewed payouts. Investigate every difference; don’t tweak a rule just to force a match.
- Run one period in parallel. Calculate the same period in Excel and in the new solution. Reconcile totals and per-person results, and get sign-off from the owners.
- Verify the cutover. Check data feeds, approved amounts, the export to payroll or accounting, permissions and the incident path. Keep a read-only copy of the old sheets in line with company policy.
Fracttal documented that preparing and adjusting Excel files used to add one or two days to every close. After moving to Remuner, the company describes the operational time involved as practically zero. These are Fracttal’s results, not a general benchmark or a forecast for other companies. Read the Fracttal story.
Commission tracking software: Remuner
Excel works at the start, but manual calculations get complex and error-prone as teams, plans and exceptions grow. Remuner automates commission calculations, gives reps real-time visibility into what they’ve earned, and runs approvals and disputes in one place. With Remu AI, you can also build a plan from scratch, ask compensation questions and simulate payouts before you roll a change out.
- Accurate calculations, even with complex structures
- Transparent, easy-to-follow tracking for every rep
- Approval and close workflows built in
- Fewer errors and less time spent reconciling
Book a demo to see your own plans running in Remuner.
FAQs on calculating commission in Excel
How do you calculate commission in Excel?
For a flat commission, multiply eligible sales by the agreed rate, for example =B2*C2. If the plan has tiers, quotas, clawbacks or exceptions, write those rules down and test them before using the sheet for a payout.
What’s the formula for tiered commission in Excel?
For progressive tiers, use nested IF statements that apply each rate only to its band. For retroactive tiers, where one rate applies to all sales once a threshold is crossed, use VLOOKUP against a rate table. Confirm which rule your plan uses first.
How do I make a commission tracker in Excel?
Include the rep, period, eligible sales, rate or tier, adjustments and commission earned. Record where each data point comes from and lock formula cells so changes can be reviewed.
How do you calculate commission when there are several rules?
Separate the commission base, rate, thresholds and adjustments. Apply each rule in the order the plan specifies and check the result with test cases, including values around each threshold.
What commission percentage should I use?
There’s no universal percentage. The rate should match the approved plan and specify the base, period, thresholds and exceptions. Keep that definition next to the tracker so every amount can be explained.
When should you replace Excel for commission payouts?
Consider alternatives when each close requires consolidating several data sources, applying many rules or exceptions, repeatedly resolving differences, or explaining calculations to several stakeholders. Compare options using your own plans and data before choosing.
Excel can still be enough if your rules are stable and every calculation is easy to review. If your close depends on consolidating data, handling exceptions or fixing the same differences every month, measure the hours and errors first with the model above. Then compare a commission platform like Remuner, the tools you already have, and the cost of keeping things as they are. The right option is the one that reproduces your rules and lets you verify every payout. For the basics, read our guide on how to calculate commission, or book a free demo to see your comp plans in Remuner.