Commission Tracking Spreadsheet: Free Template + Why You'll Outgrow It

A commission tracking spreadsheet works for a 3-rep team on a flat rate. At 15 reps with tiered plans, a CSM layer, and three mid-year plan changes, the same spreadsheet becomes a liability.

This guide covers what a commission spreadsheet needs to do, the exact columns and formulas to build one, and the specific signals that tell you it's time to move on.

What a commission spreadsheet needs to do

A commission spreadsheet has one job: tell each rep what they earned this period and show them exactly how it was calculated.

To do that, it needs to:

• Pull closed-won deals by rep and period
• Apply the correct commission rate or tier structure
• Account for adjustments — splits, clawbacks, ramp quotas
• Produce a payout number the rep can verify

Most spreadsheets start clean and accumulate complexity until the original structure no longer supports the plan. Build for that from the start.

The template: three tabs

Tab 1: Deal log

One row per deal. This is the source of truth.

Column notes:

• Split % defaults to 100%. For co-sells, enter the rep's share (70%, 30%).
• Commission rate is populated by a VLOOKUP to Tab 2 using rep name + period as the key — not hardcoded.
• Clawback is entered manually when a deal is flagged for recovery. Keep a notes column with the original deal ID and reason.

Tab 2: Plan summary

One row per rep per period. Add a new row when a rep changes plans — never overwrite existing rows.

The period column is what lets you maintain an accurate history. When VLOOKUP uses rep name & period as the key, you always get the right plan version for any given deal.

Tab 3: Payout calculator

One row per rep per period. Summarizes what each rep is owed.

The tiered commission formula

For a three-tier plan (base rate below quota, first accelerator at 100–125%, second accelerator above 125%), this formula calculates the full payout in one cell:

=MIN(C2, D2) * VLOOKUP(A2&B2, PlanSummary!A:G, 4, FALSE)
+ MAX(0, MIN(C2, D2*1.25) - D2) * VLOOKUP(A2&B2, PlanSummary!A:G, 5, FALSE)
+ MAX(0, C2 - D2*1.25) * VLOOKUP(A2&B2, PlanSummary!A:G, 6, FALSE)

What each term does:

Each band multiplies by its rate from the Plan Summary (columns 4, 5, 6). The VLOOKUP ensures rate changes take effect for new periods without touching the formula.

For the underlying commission math, see commission formulas for every plan type.

Common formula mistakes

VLOOKUP returning the wrong plan version. If you look up commission rates by rep name alone, VLOOKUP returns the last matching row — which might be from a different period. Fix: concatenate rep name and period (=A2&B2) as the lookup key. Every row in the plan summary needs the same concatenated key in column A.

Splits not reducing quota credit. Payout correctly reflects a 70% split, but the attainment formula uses the full deal value. A rep who co-closes a $100,000 deal with a 70% split should get $70,000 toward quota, not $100,000. The deal log needs =D2*F2 for both the commission earned column and any quota credit rollup.

Clawback applied to the wrong period. A deal closes in Q4, the customer cancels in Q1. The clawback should reduce Q1 commission, not restate Q4 history. Enter clawbacks in the current period's deal log as a negative adjustment row — don't edit prior periods.

Hardcoded rates. Some spreadsheets type commission rates directly into payout cells. When a rate changes, every affected cell needs a manual update. One missed cell means a wrong payout that takes days to find. Always look up rates from the plan summary tab.

When a spreadsheet stops working

A spreadsheet handles:

• Up to roughly 10–15 reps
• One or two plan types running simultaneously
• Flat or simple tiered rates with up to three tiers
• Monthly or quarterly pay periods
• Manual data entry from CRM exports

It starts breaking under:

Multiple simultaneous plan types. An SMB flat-rate plan, an enterprise tiered plan, and a quarterly SPIFFs layer running in parallel. The Plan Summary tab becomes something that requires documentation to navigate, and the payout formula needs branching logic to know which rate structure to apply.

Partial-year adjustments. Reps who join mid-quarter, change territories in month 2, or receive retroactive quota adjustments create edge cases the standard formula doesn't handle. Each one requires a manual override — and manual overrides don't document themselves.

More than three tiers. Three tiers means two MAX(0, ...) calculations in the formula above. Four tiers means three. At five tiers, the formula is longer than what most people can debug without working through it on paper.

Real-time rep questions. A spreadsheet is a snapshot updated at period close. When reps ask "what will I earn if I close this deal this week?" you need either a maintained what-if tab or you answer the question manually each time. In a 15-rep team, fielding this question a few times a week is a half-day of ops time per month.

Concurrent editing. Google Sheets handles simultaneous edits better than Excel, but conflicts still happen. Two people updating the same deal row at the same time can silently overwrite each other's work.

ASC 606 reporting. If finance needs to capitalize and amortize commission costs as contract acquisition costs, the deal log format isn't the right data structure for that. You'll need to export and reprocess for accounting. See commission accounting under ASC 606 for what the reporting requires.

The signal to move on

The clearest sign is when reps stop trusting the spreadsheet and start keeping their own. When everyone on the sales team has a personal tracking sheet to verify their payout — what ops teams call shadow accounting — the spreadsheet has already failed its core job.

Secondary signals:

• Closing commissions takes more than 2 business days after period-end
• You've had at least one dispute that took more than an hour to resolve
• A plan change requires updating formulas in more than 3 places
• A second person has to review the spreadsheet before numbers go to payroll

None of these mean the spreadsheet approach was wrong. Most teams start here, and for a small team on a simple plan it's the right call. The question is recognizing when you've crossed the threshold.

What comes next

Commission software connects directly to your CRM, applies plan rules to every deal automatically, and shows reps a real-time view of their earnings without waiting for period close.

The tradeoff is setup and subscription cost. Mid-market commission software typically runs $20–50/rep/month. For a 10-rep team, that's $2,400–$6,000/year. Weigh that against how many ops hours per month go into the spreadsheet — and how much of that time is reactive (disputes, errors, rep questions) rather than planned.

For a side-by-side comparison of available tools, see the guide to commission tracking software. For the specific calculation mistakes that generate most disputes, see commission errors: the most common mistakes.

Carvd imports closed-won deals from your CRM and applies your commission plan rules to produce a payout summary reps can verify themselves. If you've been tracking deals cleanly in a spreadsheet, migration is straightforward — the deal log tab maps directly to what Carvd imports.

Related reading

• Commission formula for every plan type — flat, tiered, unit-based, and margin-based formulas with worked examples
• How to calculate sales commission — step-by-step calculation for the most common plan types
• Commission errors: the most common mistakes — where manual calculation breaks down and what to do about it
• Commission tracking software: buyer's guide (2026) — how to evaluate commission software when you've outgrown the spreadsheet