Sales

Commission Calculation Engine

Calculates sales commissions using tiered rate structures, quota attainment multipliers, and accelerators. Handles multiple reps, variable plans, and SPIFs to produce accurate payout amounts.

Sales - Commission Calculation Engine.xlsx

Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice

Download Free

What This Spreadsheet Solves

Manual commission calculations that are error-prone and time-consuming
No structured way to handle tiered rates and accelerators above quota
Difficulty modeling the cost of different commission plan designs
Disputes over commission amounts due to lack of transparent calculations
Inability to project commission expense for budget planning

Who This Is For

Sales operations managers calculating monthly payouts
HR/Compensation teams designing commission structures
Finance teams budgeting for variable compensation expense
Sales reps verifying their commission statements

Inputs

textRep Name
$Quota Amount
$Actual Revenue Closed
%Base Commission Rate
%Accelerator Rate (above quota)
$SPIF/Bonus Amount

Outputs

Quota Attainment %
Base Commission Earned
Accelerator Commission Earned
Total Commission Payout
Effective Commission Rate
Total Commission Expense (all reps)

How Calculations Work

Revenue up to quota is multiplied by the base commission rate. Revenue above quota is multiplied by the accelerator rate. SPIF bonuses are added on top. The effective rate is total commission divided by total revenue. The model handles multiple tiers: you can define different rates for 0-80%, 80-100%, and 100%+ of quota in CONFIG.

Example Use Case

Scenario: Rep A: $200K quota, $260K closed, 8% base rate, 12% accelerator above quota, $2K SPIF. Rep B: $150K quota, $120K closed, 8% base rate, 12% accelerator, no SPIF.

Result: Rep A: 130% attainment. Base: $16K (on $200K). Accelerator: $7.2K (on $60K). SPIF: $2K. Total payout: $25.2K. Effective rate: 9.7%. Rep B: 80% attainment. Base: $9.6K. No accelerator. Total: $9.6K. Effective rate: 8%. Total expense: $34.8K.

What You Get — 5 Sheets

READMEDescribes supported commission structures, how tiers and accelerators work, and how to add new reps.

INPUTEnter each rep's name, quota, closed revenue, and any SPIFs or bonuses.

LOGICCalculates attainment, applies tiered rates, adds accelerators and SPIFs, computes effective rates and totals.

OUTPUTDisplays a commission statement per rep, team summary, total expense, and attainment distribution.

CONFIGDefine commission tiers, accelerator thresholds, rate percentages, SPIF rules, and clawback conditions.

Technical Details

File Format:.xlsx (Open XML)

Macros:None — pure formulas

Compatibility:Excel 2016+, Google Sheets, LibreOffice

Input Cells:Clearly marked with blue background

Formulas:All outputs are live Excel formulas

Protection:LOGIC sheet formulas protected, INPUT cells editable

Frequently Asked Questions

Can I model different commission plans for different roles?

Yes. Define separate tier structures in CONFIG and assign each rep to a plan. The LOGIC sheet applies the correct plan per rep.

How are multi-year deals handled?

Enter the amount recognized in the current period. If the full deal value is commissioned upfront, enter the total. If it is spread, enter only the current period's recognized revenue.

What are clawbacks?

If a customer churns within a defined period, the commission is reversed. Configure the clawback window and conditions in CONFIG. The model will subtract clawed-back amounts from the total.

Can I project next quarter's commission expense?

Enter projected revenue by rep in INPUT and the model calculates projected payouts. This is useful for budgeting.

How do I handle split deals between two reps?

Enter the deal twice, once per rep, with each rep's share of the revenue. The model calculates commission on each rep's portion independently.

Download Commission Calculation Engine

Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.

Download Free (.xlsx)Browse all 100 templates