How to Calculate Sales Commissions in Excel (Step-by-Step)

What you need before you start

Every commission calculation is driven by four inputs:

• Quota — the revenue target for each rep in the period
• Closed ARR — the actual revenue each rep closed
• Base commission rate — e.g., 8% of ARR closed
• Accelerator tiers — optional multipliers for hitting or exceeding quota

Once you have these, the spreadsheet structure is straightforward.

Step 1: Set up your columns

Start with one row per rep and these columns:

Keep your commission rates in a separate config area at the top of the sheet — not hard-coded into formulas. That way you can update a rate once and have it flow through every row.

Step 2: Calculate quota attainment

In column D, the formula is simply:

Format column D as a percentage. This number drives everything else — the accelerator tier applied and, for ramp reps, whether a floor or ceiling applies.

Step 3: Apply a flat commission rate

If your plan has no accelerators, commission is straightforward:

A rep who closed $62,000 at an 8% rate earns $62,000 × 0.08 = $4,960. Simple.

Step 4: Add tiered accelerators

This is where most homegrown spreadsheets break. A typical SaaS comp plan has multiple tiers:

• Below 75% quota attainment → 50% of base rate
• 75–99% quota attainment → base rate (1×)
• 100%+ quota attainment → 1.5× base rate

In Excel, you'd write this as a nested IF:

This works, but it's fragile. The moment you add a fourth tier, or need different tiers for different roles, you're rewriting formulas in every cell. A better approach is to keep your tier thresholds and multipliers in a named config table, then reference them with IFS or VLOOKUP.

Step 5: Track clawbacks

A clawback lets you recover commissions on deals that churn within a set window — typically 90 to 180 days after close. You need two things:

1. A separate Clawback Log tab where you record churned deals, the original close date, and the commission already paid
2. A net commission column on the main calculator that subtracts any pending clawbacks:

Step 6: Build a payout summary

If your deal data is in a log (one row per deal, not per rep), use SUMIF to roll up commission totals by rep:

This gives you a clean summary tab with one row per rep and their total payout for the period — useful for payroll and for sharing with reps.

Where a from-scratch spreadsheet breaks down

For 2–3 reps and a simple plan, this works fine. Past that, a few problems compound:

• Formula fragility — changing a tier boundary means updating formulas across rows
• Clawback date logic — manual review is error-prone; automated date checks get complex
• Per-rep statements — generating a clean printable breakdown for each rep means copy-pasting data into separate tabs
• No audit trail — when a rep disputes their number, there's no clean record of what was paid and when
• Multi-period tracking — managing Q1 vs Q2 in the same file without overwriting prior-period data requires discipline most teams don't have time for

These aren't unsolvable, but each one is a project. And the more complex your plan, the more time you spend maintaining the spreadsheet instead of running your team.