What's inside
- Plan: tier mode (marginal or retroactive), up to five tiers, optional cap, guaranteed minimum and clawback rate.
- Deals: one row per rep credit. A split deal appears once per rep with each rep's share; refunds and cancellations are rows with status
clawback. - Payouts: one row per rep; every column is a formula.
Formulas
- Credited revenue:
SUMPRODUCT((Rep=A2)*(Status="won")*Amount*Share) - Tier n, marginal:
MAX(0, MIN(Credited, To) − From) × Rate(blank To = open-ended) - Gross, retroactive:
Credited × rate of the tier where From ≤ Credited < To - Clawbacks:
−SUMPRODUCT((Rep=A2)*(Status="clawback")*Amount*Share) × Clawback rate - Payout:
ROUND(MAX(MIN(Gross, Cap) + Clawbacks, Minimum), 2)
These rules match the online commission calculator and the methodology.
When a spreadsheet stops being enough
Spreadsheets break down with many plans, frequent mid-period changes, multi-currency deals or audit requirements. If that sounds familiar, run our six-question check — it may also tell you to keep the spreadsheet.
Frequently asked questions
Does the template work in Google Sheets?
Yes. Upload the .xlsx to Google Drive and open it with Google Sheets; SUMPRODUCT, MIN, MAX and ROUND behave the same way.
How do I record a split deal?
Add one row per rep on the Deals sheet with the same deal ID and each rep's credit share (0.5 = 50%). Shares should add up to 1 per deal.
How are refunds and cancellations handled?
Enter them as rows with status "clawback". They are recovered at the clawback rate set on the Plan sheet.