How to calculate sales commissions without breaking your spreadsheet

Most SaaS sales teams start commission tracking in a spreadsheet. It works — until it doesn't. A formula breaks, someone edits a deal retroactively, and suddenly Finance and a rep are arguing over a number that neither can explain. This guide walks through how commission calculations typically work in spreadsheets, where they tend to break, and what to look for when the spreadsheet stops being good enough.

The basic structure of a commission spreadsheet

A functional commission spreadsheet usually has at least three tabs: a deal log (one row per closed-won deal), a rep roster (quota, rate, and role by rep), and a summary tab (total earned by rep for the period). The summary tab is where most of the formulas live and where most of the breaks happen.

• Deal log: Deal ID, rep name, close date, ARR or deal value, deal type
• Rep roster: Rep name, role, quota (monthly or quarterly), commission rate
• Summary: SUMIF or SUMIFS to pull each rep's closed-won total, then multiply by their rate

Step-by-step: calculating a flat commission rate

The simplest commission structure is a flat percentage on all closed-won ARR. If a rep's rate is 8% and they closed $120,000 in a quarter, they earn $9,600. In a spreadsheet, this looks like:

This formula works well when the deal log is clean, the dates are formatted consistently, and no one edits historical rows. In practice, all three of those conditions are regularly violated.

Calculating tiered commissions

Tiered commission plans pay a higher rate as a rep hits higher attainment bands. A common structure for SaaS teams:

In a spreadsheet, tiered commissions require nested IF statements or an IFS function. This is where most spreadsheets get brittle. A small change to the tier thresholds requires updating every rep's formula manually.

Where commission spreadsheets break

• Retroactive deal edits: A rep updates an ARR amount after month-end. The commission for that period recalculates automatically, causing a discrepancy with what was already paid.
• Date format inconsistencies: Dates entered manually in different formats cause SUMIFS to miss rows. The formula returns zero for a rep who clearly closed deals.
• Tier formula errors: The cumulative vs. absolute tier distinction is calculated incorrectly, meaning reps earn more or less than the plan specifies.
• Version control: Finance and Sales Ops are working from different copies. Which one is authoritative?
• No rep visibility: Reps cannot see the underlying data, so they cannot verify their own payout. Any discrepancy becomes a dispute.

When to stop using the spreadsheet

The spreadsheet works well enough below about five reps with a simple flat-rate plan. Beyond that, the maintenance cost starts to outweigh the flexibility. Specific triggers to watch for:

1. 1More than one payout dispute per commission period
2. 2Month-end reconciliation takes longer than two hours
3. 3You have tiered or accelerator commissions across more than three reps
4. 4Finance is reformatting your output before every payroll run
5. 5You have had at least one retroactive deal edit affect a closed period

What to look for in a replacement

A commission tracker for a SaaS team at the spreadsheet-replacement stage needs a small number of things: deal import from your CRM (or CSV), configurable commission rules, rep-facing visibility, period locking, and a payroll-ready export. It does not need to be a full enterprise compensation platform. The complexity that makes enterprise tools expensive is usually unnecessary for teams with fewer than 30 reps.