Hanna Pad via Pexels
Most people searching for a sales commission Excel template want one file that will survive the next two years. That file does not exist — but three simple ones do, and knowing which of the three you need (and the specific event that will kill it) is worth more than any download.
TL;DR: There are only three commission spreadsheet builds worth using: a flat-rate sheet (revenue × rate), a tiered sheet (rate steps by attainment band), and an accelerator sheet (base rate to quota, enhanced rate above it). Each has a predictable failure point: the flat-rate sheet breaks at around 10 reps or at your first clawback, the tiered sheet breaks at your first mid-month hire, and the accelerator sheet breaks at your first dispute over how attainment was calculated. All three are fine for calculating gross commission; none of them handle the UK payroll consequences, because HMRC treats commission as earnings subject to PAYE and Class 1 National Insurance through payroll (GOV.UK), and a clawback does not simply reverse that. Below are the exact formulas for all three, a worked £95,000 example, and the point at which you should stop rebuilding the sheet.
- Quota attainment
- 80%
- On-target commission (100%)
- £48,000
- Earned up to quota
- £38,400
- Accelerated (above quota)
- £0
Illustrative only — real plans add caps, clawbacks, splits and draws that a spreadsheet quietly gets wrong. See how Commit automates the whole calculation →
Use the calculator above to sanity-check whatever your own sheet produces for a single rep before you read on. If the two numbers disagree, the interesting question is which rule your spreadsheet is quietly applying differently — that gap is where nearly every commission error lives.
Which sales commission Excel template should you use?
| Build | Best for | Core formula | Breaks when |
|---|---|---|---|
| 1. Flat rate | 1–8 reps, one product, one rate, no quota | =ROUND(revenue*rate,2) | You pass ~10 reps, or you have to reverse a payment |
| 2. Tiered | Reps carrying a quota with rate steps by attainment | =SUMPRODUCT across band boundaries | Someone joins, leaves or changes quota mid-period |
| 3. Accelerator | Reps who regularly exceed quota; enterprise deals | =MIN(rev,quota)*base + MAX(0,rev-quota)*accel | Two people disagree on what counts toward attainment |
The honest position: a spreadsheet is an excellent calculator and a terrible system of record. Excel will multiply two numbers correctly forever. What it cannot do is tell you, eight months later, which version of which plan produced the £6,375 that landed in a rep's August payslip — and that is the question you actually get asked.
Excel is a fine commission calculator and a hopeless commission system of record. The problem is never the maths — it's the memory.
What goes in each of the three templates?
Build 1: the flat-rate sales commission template
The flat-rate build is four columns wide: deal reference, customer, recognised value, commission. One locked cell holds the rate. Take a rep on £45,000 base plus £15,000 variable (a 75/25 split on £60,000 OTE) paid 5% of new revenue: at £300,000 booked for the year, the sheet returns £15,000 and everyone can check it in their head.
The build rules that matter more than the formula: put the rate in a single named cell (=ROUND(D2*$Rate,2)), never retype it per row; store the recognised value, not the invoice headline; and add a Payroll month column from day one, because that column — not the deal date — is what reconciles to Xero later.
Build 2: the tiered commission template
Tiering pays different rates in different attainment bands — say 3% up to 80% of quota, 5% from 80% to 100%, and 8% above. The mistake in nine out of ten downloadable templates is nested IF statements, which apply the top rate to the whole number and produce cliff-edge payouts that reps learn to game.
Build it as marginal bands instead. Lay out the band floors in one row (0, 80%, 100%) and the incremental rate uplifts in the next (3%, 2%, 3%), then use one formula:
=SUMPRODUCT((attainment>floors)*(attainment-floors)*upl ifts)*quota_value
One rep on a £250,000 annual quota who lands £275,000 gets paid band by band, not on a single lookup. If you want the design logic behind where to put the band floors, that's a separate question from the spreadsheet — see commission accelerators: design principles for UK plans.
Build 3: the accelerator template (and the worked example)
The accelerator build is the simplest of the three to write and the most expensive to get wrong:
=MIN(revenue,quota)*base_rate + MAX(0,revenue-quota)*accel_rate
Take a rep on a £62,500 quarterly quota, 5% to quota and 10% above it. They close £95,000 — 152% attainment. The sheet pays £3,125 on the first £62,500 and £3,250 on the £32,500 of over-attainment: £6,375. On top of that, the employer carries Class 1 secondary NICs at 15% on earnings above the secondary threshold, which HMRC sets at £417 a month for 2026 to 2027 (Rates and thresholds for employers 2026 to 2027) — roughly £956 of employer NIC on that single payout.
Now assume a £40,000 module inside that deal carried a 30-day opt-out, and the client exercised it. Real attainment was £55,000, or 88% of quota — below the accelerator entirely. Correct commission: £2,750. The overpayment is £3,625, and £3,250 of it exists purely because the accelerator rate was applied to revenue that never locked in. That is the mechanism worth internalising: an accelerator does not just magnify upside, it magnifies the size of any later correction.
When an employee repays correctly-paid commission under a contractual clawback, HMRC treats it as negative earnings: there is no relief or repayment of National Insurance contributions, you cannot amend the original payroll records, and the employee has to approach HMRC themselves for any Income Tax refund (GOV.UK negative earnings guidance). On the £3,625 above, the employer NIC — about £544 at 15% — is simply gone. No spreadsheet template models that asymmetry, which is why "we'll claw it back" is a much weaker safety net than finance teams assume.
When does each Excel template break?
Why the flat-rate template breaks at ~10 reps or the first clawback
A flat-rate commission spreadsheet breaks on headcount for a boring arithmetic reason: tabs multiply faster than reps. Ten reps across quarterly periods with a summary sheet is 40-plus interdependent ranges maintained by one person, usually at 7pm on the 25th. It breaks on clawbacks for a structural reason: the sheet holds only the current state. Overwrite a cell to correct an error and the previous number — the one the rep saw and budgeted against — no longer exists anywhere except in their screenshot.
In practice, that is where the cultural damage happens rather than in the maths. We have seen a top AE receive a life-changing accelerator payout on a Closed-Won deal, then face a brutal clawback weeks later when the client exercised a 30-day opt-out on one expensive module and the ARR dropped. Nobody acted in bad faith — finance followed the numbers — but the rep felt punished for a deal structure management had signed off, his momentum went, and he left within the quarter. The root cause was paying on gross signing-day value before the opt-out window closed, not the spreadsheet formula. Fix the timing rule first, then write it down: how to write a commission clawback policy that survives contact with reality.
Why the tiered template breaks at the first mid-month hire
A tiered commission template assumes a full period and a fixed quota. A rep who starts on 12 August has neither. You now need a pro-rating rule — say 34 of 65 working days in the quarter, giving a £32,692 quota against the £62,500 standard — and every band floor in every downstream formula has to move with it. Most teams paste the standard quota in for month one, promise to fix it, and never do.
The mid-period problem doesn't stop at quota. UK holiday pay for regular-hours workers must include commission payments intrinsically linked to contractual tasks in at least the four weeks of normal (Regulation 13) leave, per the Government's holiday pay and entitlement reforms guidance (GOV.UK), and where pay varies the reference period is the last 52 paid weeks. A rep with three months of service has no 52-week history — so your sheet needs a shortened reference period it was never designed to hold. We cover the mechanics in holiday pay on commission in the UK.
Why the accelerator template breaks at the first attainment dispute
Accelerator sheets break on definitions, not formulas. Once a rep is arguing about whether they hit 100%, the questions are: which date does a deal count on, signature or invoice? Gross or net of the discount? Does the annual value of a 30-month contract count once or pro-rata? What happens to the credit note issued in month two?
Contract complexity is what tips this over. In mid-market, once a deal crosses roughly £100,000 it stops being a clean closed-won number — custom clauses, opt-outs, security reviews and legal redlines appear, and that's exactly where commission errors hide. Comp logic that works on a £10,000 deal closed in 14 days does not survive a £350,000 multi-threaded one. HMRC's employer guidance is also blunt about extra payments such as commission: they are treated as part of total pay at the time they are paid, regardless of when they were earned, and NICs are recalculated on the total for that earnings period (CWG2 2026 to 2027) — so "we'll just move it to next month" changes the tax outcome, not only the timing. When the dispute arrives, see handling commission disputes.
What should you check before payroll runs?
Protecting a sales team isn't only about helping them close — it's about shielding them from internal operational mistakes. A wrong number that is paid and then corrected does far more damage than a payment that lands two days late and right.
- Reconcile the deal list to the contract, not the CRM. For anything over £100,000, open the signed document and look for opt-out, ramp, phased-start and cancellation clauses.
- Strip out unlocked value. Calculate on the value actually committed, not the signing-day headline. If a module can be cancelled inside 30 days, it does not belong in this month's attainment.
- Recompute attainment independently. Total the qualifying revenue by hand (or in the calculator above) and compare with the sheet. Investigate any variance over 1%, however small in pounds.
- Flag every payout that crosses an accelerator boundary. These are the rows where a later reduction hurts most, so they get the closest read.
- Show the rep their number before payroll, not after. Two minutes of "does this match what you expected?" prevents most disputes — and reps who are checking their own numbers stop keeping shadow spreadsheets.
Should you stay in Excel or move off it?
| Situation | Excel is fine | Time to move |
|---|---|---|
| Reps on plan | Up to ~8–10 | 10+, or multiple plan types |
| Plan mechanics | Flat rate or simple tiers | Accelerators, splits, overrides |
| Deal complexity | Clean one-line bookings | Opt-outs, ramps, phased starts |
| Corrections | None yet | You've run one clawback |
| Audit questions | "What did I earn?" | "What was I shown in March?" |
The trigger is rarely rep count on its own. It is the first time someone asks a question about the past that your file cannot answer. If you want the numbers behind that decision rather than a vibe, we've costed it out in spreadsheet vs commission software: the true cost comparison.
A one-rep flat-rate sheet remains the fastest way to pressure-test a new plan before you roll it out. Model three attainment scenarios — 70%, 100%, 140% — and check the cost per pound of revenue at each. If the 140% column makes you flinch, the plan is wrong, and no software will fix that.
Frequently asked questions
Is there a free sales commission Excel template for UK teams?
Yes — and you can build any of the three in this article in under fifteen minutes, which is faster than evaluating downloads. The formulas above (flat rate, marginal SUMPRODUCT tiers, and the MIN/MAX accelerator) are the whole substance of almost every paid template on the market; the value is in the definitions you put around them, not the file.
How do you calculate tiered commission in Excel without nested IF statements?
Lay out your band floors and the incremental rate uplift for each band in two adjacent rows, then use a single SUMPRODUCT that multiplies the amount above each floor by that band's uplift. This pays marginally, avoids the cliff-edge behaviour nested IF formulas create, and is far easier to audit because one formula covers every band.
Is commission taxed differently from salary in the UK?
No. Commission counts as earnings, so it goes through payroll with PAYE and Class 1 National Insurance in the normal way, according to GOV.UK employer guidance. It can feel different because a large one-off payment pushes that month's pay into higher bands, but the annual position evens out through the cumulative PAYE calculation.
What happens to tax and NICs when commission is clawed back?
Where a contractual clawback applies to correctly-paid commission, HMRC's negative earnings guidance states that there is no relief or repayment of National Insurance contributions, the original payroll records stand, and the employee must contact HMRC directly about any Income Tax refund. That is precisely why holding payment until the value is firm beats recovering it afterwards.
At how many reps does a commission spreadsheet stop working?
Around ten reps is the practical ceiling for a single maintained workbook, but headcount is a weaker signal than complexity. One rep on an accelerated plan closing contracts with opt-out clauses will break a spreadsheet faster than fifteen SDRs on a flat per-meeting rate.
Ready to fix your commission process?
See your own comp plan running in Commit. 20 minutes, no slides.