# Commission Tracking: Why a Spreadsheet Template Stops Working

Search "commission tracking spreadsheet template" and you'll find hundreds of free downloads. They all work the same way: a row per deal, a column per agent, a formula that multiplies the deal value by a hard-coded percentage.

For a single agent earning a flat 10%, the template works fine. The trouble starts when reality intrudes.

## The four scenarios that break templates

### 1. Tiered commission rates

Most real-world commission structures escalate: 5% on the first $50K closed in a quarter, 10% above that, 15% above $150K. A spreadsheet handles this with nested `IF` statements that nobody can audit.

### 2. Splits between agents

Two agents share a deal 60/40. Now you need either a separate "splits" sheet or columns like `Agent_1`, `Pct_1`, `Agent_2`, `Pct_2` — and a formula that has to sum across them and validate they add to 100.

### 3. Hierarchical relationships

In real estate, an agent's broker takes a slice of every deal. In network sales, an upline earns on every closed downline deal — sometimes recursively, three or four levels deep. Spreadsheets can model this with `VLOOKUP` chains, but verifying correctness requires manually unrolling the math.

### 4. Adjustments and clawbacks

Deals close. Then the customer cancels. Or asks for a discount that retroactively changes the commission. Or the deal moves between agents mid-cycle. Each adjustment in a spreadsheet creates a footnote, a comment, or — most often — an undocumented mutation that nobody can later explain.

## What a structured tool gives you

When commissions move from a spreadsheet to a CRM with calculated fields:

- **Tiers become rules.** "5% up to $50K, 10% to $150K, 15% above" is one rule, applied automatically to every deal.
- **Splits become relationships.** A deal has multiple `commissionableTo` agents with percentages, and the CRM enforces that they sum to 100.
- **Hierarchies become queries.** "Find all deals where this agent or any of their downline closed" is a single query, not a `VLOOKUP` chain.
- **Adjustments become events.** A clawback is a typed event with a date, a reason, and a calculated impact — fully auditable.

## When to make the move

If your commission spreadsheet has any of:

- More than 50 active deals at once
- More than one agent on most deals
- Tiered or accelerator structures
- Manual adjustments that take hours to reconcile

…the spreadsheet is costing you more in errors and time than the CRM would cost in license fees.

## What to look for in a tool

Not all CRMs handle commissions well. The minimum bar:

- **Calculated fields** that update when underlying data changes.
- **Custom relationships** (an agent has a "broker_id" that points to another agent).
- **Field-level permissions** (the rep sees their own commission; only managers see the full payout sheet).
- **Audit logs** of every edit — required for trust, often required for compliance.

A free spreadsheet template gets you started. A structured tool keeps you running.