Tracking your team's commissions in Excel
The three tables to build, the formulas that do the maths, and the rule almost every file gets wrong: a commission is calculated on cash collected, not on contract value.
What does a commission tracker actually need?
Three tables, not one. That is the first mistake in the files people show us: everything sits on a single sheet, one row per deal, with the commission typed into a column. It works until the first payment plan, and never again after that.
The reason is simple. A deal and a payment are not the same object. A $6,000 deal paid in three instalments is one deal row and three payment rows, each with its own date and its own fate. As long as both live on the same line, no formula can tell what was promised from what actually arrived.
Members
One row per person: name, role, and their rate. That rate lives here and nowhere else, so a change happens in one place.
Deals
One row per deal: client, date, total value, and above all who did what. One column for the closer, one for the setter, one for the manager. Those three columns are what make the calculation possible later.
Instalments
One row per expected payment: which deal it belongs to, its amount, its due date, and a collected column that says yes or no. It is the only column that releases money.
The formulas that do the work
Three functions are enough, and no macros. The examples below assume your three tables are formatted as Excel tables, which gives readable column names instead of letter references. On Google Sheets the names differ slightly, the logic does not.
1. The closer's commission on one instalment, and zero until it is collected
=IF([@Collected]="yes",
[@Amount] * VLOOKUP([@Closer], Members, 3, FALSE),
0)2. Cash actually collected by a rep, across every deal
=SUMIFS(Instalments[Amount],
Instalments[Closer], [@Name],
Instalments[Collected], "yes")3. Their earned commissions, adding up the column built in step 1
=SUMIFS(Instalments[Closer commission],
Instalments[Closer], [@Name],
Instalments[Collected], "yes")4. What is still owed to them, once payouts already made are removed
=[@Earned commissions]
- SUMIFS(Payouts[Amount], Payouts[Member], [@Name])The fourth assumes a fourth table, the one holding real payouts. Many teams skip it and track payments in their bank or in a message thread. That is exactly where end-of-month disagreements come from: the file knows what is owed and has no idea what has been paid.
The rule almost every file gets wrong
A commission is calculated on cash collected, not on contract value. That single choice separates a file that holds from a file that creates debts.
Take the $6,000 deal in three instalments, with a closer on 10 %. The naive file books $600 of commission on the day of signature. If the second instalment fails, you have announced $600 against $4,000 received, and someone has to hand back $200 they had counted as earned.
The correct file books three commissions of $200, each released by its own collected column. An instalment that never arrives produces nothing, with no correction needed. That is the rule Pifr applies by default, and there is nothing proprietary about it: you can write it in Excel today, it is formula number 1 above.
Where this file eventually breaks
It will hold longer than you expect. But four limits cannot be fixed with a formula, and they all arrive on the same day: the one where the team grows.
Permissions
Excel protects cells, not rows depending on who is looking. Either everyone sees what everyone else earns, or nobody can check their own number.
Versions
The file goes out by email, comes back edited, and two truths coexist. Online sharing helps, it does not prevent local copies.
Data entry
Ticking collected stays a human gesture. As long as someone has to do it by hand, there will be delays and omissions, and those are what create the gaps.
Trust
A file only you can edit stays a file only you can edit. It is the limit nobody raises in a meeting and everybody raises in private.
What this guide does not say
It does not say to leave Excel. If you sell alone, or collect in one payment, the file above will do for a long time. We wrote a separate page about the exact moment it stops being enough: Pifr or Excel.
Frequently asked questions
Which Excel formulas calculate sales commissions?
Three are enough. IF() so a commission only counts once the instalment is collected, VLOOKUP() to pull the rep's rate from the members table, and SUMIFS() to total by person and by period. No macros needed.
Should commission be calculated on contract value or on cash collected?
On cash collected. A commission based on the promised amount creates a debt the moment a payment fails, and someone has to give money back. Calculated instalment by instalment, an instalment that never arrives simply produces no commission.
How do you handle a refund in the sheet?
The simplest answer is to cancel nothing. Mark the deal as refunded for the record and leave the commissions already earned with the rep, since the work was done. Clawing back a paid commission is the fastest way to lose a good closer.
Can the file be shared without everyone seeing everyone else's numbers?
Not really. Excel protects cells, not rows depending on who is looking. The usual workarounds, one file per rep or a hidden tab, create as many files as reps and as many chances for them to drift apart.