Sales Compensation Plan Change Impact Calculator
A structured template that models exactly how a proposed comp plan change affects rep take-home pay across five performance bands, flags flight-risk reps, and shows total payroll cost under both plans before you commit to anything. Built for sales leaders and RevOps analysts who need to answer the CFO's actual question: who wins, who loses, and what does it cost?
How to use it
Fill in the Current Plan and Proposed Plan input tables with your real mechanics, then enter your rep roster with each person's quota and recent attainment band. The delta tables and flag columns populate from there, giving you a side-by-side view you can drop straight into a comp review presentation or a CRO approval pack.
What's inside
- Current Plan input block: base salary, OTE, commission rate(s), accelerator thresholds, and cap (if any)
- Proposed Plan input block: same fields, structured identically for direct comparison
- Five-band earnings model: projected take-home for both plans at 50%, 75%, 100%, 125%, and 150% of quota
- Delta column: absolute (£) and percentage difference in earnings at each band under new vs. old plan
- Break-even attainment finder: the exact quota percentage at which the new plan first pays out more than the old one
- Flight-risk flag: automatic trigger when a rep in the 75-125% band takes home materially less under the new plan
- Payroll cost summary: total commission and OTE cost under each plan at a given attainment distribution
- Attainment distribution input: enter how many reps sit in each band to calculate org-level cost impact
- Accelerator comparison table: side-by-side view of multiplier rates and trigger points across both plans
- Worked example: a fully populated illustration using a £70k base / £120k OTE structure
- Named failure modes: four common modelling errors that corrupt the output
- When NOT to use this: honest scope limits so you apply it only where it fits
What This Template Does
Before a comp plan change goes live, two questions need clean answers:
- Which reps are better off, which are worse off, and by how much?
- What does the new plan cost the org at realistic attainment levels?
This template answers both simultaneously. It is not a general comp design guide. It does one thing: model the before-and-after financial impact on individual reps and on total payroll, so you can walk into a CRO or CFO review with numbers, not intentions.
How to Read This Template
The template has four working sections:
- Section 1: Plan inputs (current and proposed)
- Section 2: Five-band earnings model with deltas
- Section 3: Org-level payroll cost model
- Section 4: Flags, break-even point, and failure modes
Each table is designed to be replicated in a spreadsheet (Excel or Google Sheets). Columns marked [INPUT] require your data. Columns marked [CALC] are formulae you build from the inputs shown.
Section 1: Plan Input Tables
Complete one table for each plan. Use the same structure so the comparison is clean.
Table 1A: Current Plan
| Field | Value |
|---|---|
| Annual base salary | [INPUT: e.g., £70,000] |
| On-target earnings (OTE) | [INPUT: e.g., £120,000] |
| On-target commission (OTE minus base) | [CALC: OTE - Base] |
| Annual quota | [INPUT: e.g., £1,200,000] |
| Standard commission rate (at 100% quota) | [CALC: On-target commission / Annual quota] |
| Accelerator 1: triggers at | [INPUT: e.g., 100% of quota] |
| Accelerator 1: multiplier | [INPUT: e.g., 1.5x standard rate] |
| Accelerator 2: triggers at | [INPUT: e.g., 125% of quota] |
| Accelerator 2: multiplier | [INPUT: e.g., 2.0x standard rate] |
| Commission cap (if any) | [INPUT: e.g., None, or £80,000] |
| Clawback terms | [INPUT: e.g., 90-day clawback on cancellations] |
Table 1B: Proposed Plan
| Field | Value |
|---|---|
| Annual base salary | [INPUT] |
| On-target earnings (OTE) | [INPUT] |
| On-target commission (OTE minus base) | [CALC] |
| Annual quota | [INPUT] |
| Standard commission rate (at 100% quota) | [CALC] |
| Accelerator 1: triggers at | [INPUT] |
| Accelerator 1: multiplier | [INPUT] |
| Accelerator 2: triggers at | [INPUT] |
| Accelerator 2: multiplier | [INPUT] |
| Commission cap (if any) | [INPUT] |
| Clawback terms | [INPUT] |
Section 2: Five-Band Earnings Model
How Commission Is Calculated at Each Band
For each attainment band, calculate revenue booked as: Quota x Attainment %. Then apply the commission rate and any accelerators that have triggered at that level.
Example formula logic (current plan, 125% band):
- Revenue at 125%: £1,200,000 x 1.25 = £1,500,000
- First £1,200,000 at standard rate (4.17%): £50,000
- Next £300,000 at accelerator rate (1.5x = 6.25%): £18,750
- Total commission: £68,750
- Total earnings: £70,000 base + £68,750 = £138,750
Replicate this logic for both plans at each band.
Table 2: Earnings Delta by Attainment Band
| Attainment Band | Revenue Booked [CALC] | Current Plan Commission [CALC] | Current Plan Total Earnings [CALC] | Proposed Plan Commission [CALC] | Proposed Plan Total Earnings [CALC] | Delta (£) [CALC] | Delta (%) [CALC] | Flight-Risk Flag [CALC] |
|---|---|---|---|---|---|---|---|---|
| 50% | ||||||||
| 75% | ||||||||
| 100% | ||||||||
| 125% | ||||||||
| 150% |
Flight-Risk Flag logic: Mark "FLAG" in the final column if Delta (£) is less than -£3,000 for any rep whose typical attainment falls in the 75-125% band. Adjust the threshold to suit your org, but document whatever number you use. Anything below -£3,000 for a solid performer warrants a retention conversation before the plan launches, not after.
Break-Even Attainment Point
This is the quota attainment percentage at which Proposed Plan Total Earnings first equals or exceeds Current Plan Total Earnings.
Calculate it by testing attainment in 1% increments (or use Goal Seek in Excel). Record the result here:
| Break-Even Point | [CALC: e.g., 87% of quota] |
|---|---|
| Interpretation | Below this point, reps earn more under the old plan. Above it, the new plan pays more. |
If the break-even point is above 100%, your new plan pays less at quota than the old one. That is a retention problem, not a design feature, unless you are deliberately raising the bar with full awareness of the consequences.
Section 3: Org-Level Payroll Cost Model
Table 3A: Attainment Distribution Input
Enter the number of reps currently sitting in each band. Use trailing 12-month actuals where possible.
| Attainment Band | Number of Reps [INPUT] |
|---|---|
| Below 50% | |
| 50-74% | |
| 75-99% | |
| 100-124% | |
| 125%+ | |
| Total headcount | [CALC: sum] |
Table 3B: Total Payroll Cost Comparison
| Attainment Band | Reps in Band | Current Plan: Total Earnings Per Rep | Current Plan: Band Cost [CALC] | Proposed Plan: Total Earnings Per Rep | Proposed Plan: Band Cost [CALC] |
|---|---|---|---|---|---|
| 50% | |||||
| 75% | |||||
| 100% | |||||
| 125% | |||||
| 150% | |||||
| Total payroll cost | [CALC: sum] | [CALC: sum] | |||
| Net cost delta | [CALC: Proposed total minus Current total] |
A positive net cost delta means the new plan costs more. A negative delta means it costs less. Neither is inherently right. What matters is whether the cost change is intentional and whether it aligns with the attainment distribution you actually expect.
Section 4: Accelerator Comparison Table
Use this table to check that your accelerator curve does what you think it does at each trigger point.
| Attainment Level | Current Plan Rate | Current Plan Multiplier Active? | Proposed Plan Rate | Proposed Plan Multiplier Active? | Rate Delta |
|---|---|---|---|---|---|
| 50% | |||||
| 75% | |||||
| 100% | |||||
| 125% | |||||
| 150% |
Worked Example
Setup: 12 reps, £70,000 base, £120,000 OTE, £1,200,000 annual quota.
Current plan: 4.17% standard rate, 1.5x accelerator above 100%, 2.0x above 125%. No cap.
Proposed plan: 3.75% standard rate, 1.5x accelerator above 100%, 2.5x above 125%. No cap.
| Band | Current Earnings | Proposed Earnings | Delta (£) | Delta (%) |
|---|---|---|---|---|
| 50% | £82,500 | £82,500 | £0 | 0% |
| 75% | £107,500 | £103,750 | -£3,750 | -3.5% |
| 100% | £120,000 | £115,000 | -£5,000 | -4.2% |
| 125% | £138,750 | £140,625 | +£1,875 | +1.4% |
| 150% | £170,000 | £178,750 | +£8,750 | +5.1% |
Break-even point: approximately 122% of quota.
Flight-risk flags: Reps typically attaining 75-100% take home £3,750-£5,000 less. With 7 of 12 reps in that band, this plan has a significant retention risk at the median of your team.
Net payroll cost delta: With this attainment distribution, proposed plan saves approximately £28,000 in annual commission at the cost of flagging 7 reps as materially worse off.
That trade-off is knowable before launch. That is the point.
Named Failure Modes
1. Using OTE as a proxy for actual earnings. OTE is a target, not an average. If your median attainment is 82%, model at 82%, not 100%.
2. Ignoring mid-year changes. If this plan change takes effect in month 4, model nine months of the proposed plan and three of the current, not full-year figures.
3. Forgetting clawbacks in the cost model. Clawback recoveries reduce payroll cost on paper but destroy morale and distort the model. Track them separately.
4. Applying accelerators to total revenue instead of incremental revenue above the threshold. Accelerators almost always apply to revenue above the trigger point only. Confirm your plan language before building the formula.
When NOT to Use This Template
- When your comp plan includes non-linear SPIFs, team-based pools, or MBO components that do not tie directly to individual quota attainment. Those require a separate model.
- When you are designing a plan from scratch. This template models deltas, not first principles. Use a comp design framework first, then bring the output here.
- When quota has not been set for the new period. The model breaks if quotas are in flux. Lock quotas, then model the plan.