A commission spreadsheet is a workbook that logs every closed deal, credits it to the right rep, applies the plan's rates and accelerators, and turns the result into each rep's payout and statement. This free template does that in Excel or Google Sheets, with rep splits, clawbacks, tiered rates and a monthly commission statement.
Commission spreadsheet template (.xlsx)
- 6 tabs: Start here, Plan setup, Deals, Calc, Statement, Summary
- Up to 10 reps and 300 deals, with 4 rate tiers and 5 deal types
- Splits, deal-type credit, clawback window and a payout grid by month
- Opens in Excel and Google Sheets. Sample data is illustrative.
Preview one month's payout
The same math as the template's Calc tab, for one rep and one month. The defaults are the sample rep in August (illustrative).
| This month's credit | Credit | Rate | Commission |
|---|---|---|---|
| Tier 1 (up to 100%) | $60,000 | 10.00% | $6,000 |
| Tier 2 (100% to 150%) | $60,000 | 15.00% | $9,000 |
$120,000 of credit takes this rep from 92.5% to 107.5% of the $800,000 quota. $60,000 lands in tier 1 (up to 100%) at 10.00% ($6,000), and $60,000 lands in tier 2 (100% to 150%) at 15.00% ($9,000). Commission earned this year moves from $74,000 to $89,000. This month pays $15,000.
Get The Forecast Brief
You just previewed a commission payout. The Forecast Brief is a short email from Pete Furseth, ORM's COO, built on what ORM sees in its customers' forecasts.
What is in the commission spreadsheet
Six tabs. You type into the yellow cells on two of them. The other four are formulas.
| Tab | What you enter | What it does |
|---|---|---|
| Start here | Nothing | Explains the steps, the color code and the math |
| Plan setup | Plan year start, rate tiers, deal-type credit %, clawback window, each rep's quota, target incentive and base salary | Works out each rep's base commission rate (target incentive / quota) and OTE |
| Deals | One row per closed deal: ID, close date, customer, type, amount, Rep 1 and split, Rep 2 and split, clawback date | Applies deal-type credit and splits, checks the clawback window, and flags bad rows (splits that do not add to 100%, unknown reps) |
| Calc | Nothing | One row per rep per month: credit, clawbacks, credit year to date, attainment, credit in each tier, earned, paid, payable and owed back |
| Statement | A rep and a month | A one-page commission statement with every deal credited or reversed that month, and a check that the lines tie to Calc |
| Summary | The as-of month (on Plan setup) | Year to date by rep and a rep-by-month payout grid for payroll and finance |
Most free commission sheets stop at a flat rate per deal. The tabs above cover the parts that cause disputes: who gets credit on a shared deal, which tier a dollar falls in, and what happens when a deal cancels after it was paid.
The Deals tab, column by column
Each closed deal is one row, entered once. Never delete a row to undo a deal. Add a clawback date instead, so the history stays.
| Column | What goes in | Why it matters |
|---|---|---|
| Close date | The date the deal counts as closed under your plan | Sets the month the credit lands in |
| Deal type | New business, Expansion, Renewal (or your own types) | Pulls the credit % from Plan setup, so a renewal can earn 25% credit while a new logo earns 100% |
| Booking amount | The value your plan pays on, such as first-year ACV | Pick one amount field and use it on every row |
| Rep 1, Rep 1 split | The primary rep and their share, such as 60% | Rep 1 credit = amount x credit % x split |
| Rep 2, Rep 2 split | The second rep on a shared deal, such as 40% | The two splits must add to 100%, or the Check column says so |
| Clawback date | The date a deal cancelled, if it did | Inside the window, the credit comes back out in that month. Outside it, the row is flagged and nothing changes |
| Grey columns | Nothing: they are formulas | Credit, split credit, clawback status, checks and the helper columns the Statement reads |
How to set up a commission spreadsheet, step by step
- Write the plan down first. Quota, target incentive, tiers, what counts as closed, split rules and the clawback clause belong in the plan document. ORM's sales commission plan template covers every clause. The spreadsheet only runs what the plan says.
- Fill in Plan setup. Enter the plan year start, the rate tiers (from, to and multiplier), the credit % for each deal type and the clawback window in days. Then add each rep with their annual quota and target incentive.
- Log closed deals. One row per deal, from the same CRM report every month. Use the dropdowns for type and rep so names match exactly.
- Enter splits. For a shared deal, put both reps and their shares on the same row. The rules for splitting commission on team deals should be written before the deal closes.
- Record clawbacks as dates. When a deal cancels, type the cancellation date in its row. The sheet reverses the credit in that month if it falls inside the window.
- Check the Check column. Every row should say OK before you run payroll.
- Run the statements. Pick each rep and the month on the Statement tab, confirm the line total ties to Calc, and send it.
- Tie it out and save a copy. Add up credited bookings for the period and match them to what finance reports. Then save that month's file, so you can answer a dispute later from the version that paid.
Commission formulas for Excel and Google Sheets
These are the formulas inside the template, simplified to plain cell names. They work the same in Excel and Google Sheets. Examples use the template's sample data and are illustrative.
| Measure | Formula | Excel or Sheets | Example (illustrative) |
|---|---|---|---|
| Base commission rate | Target incentive / annual quota | =D25/C25 | $80,000 / $800,000 = 10% |
| Flat commission on one deal | Amount x rate | =E2*$B$1 | $120,000 x 10% = $12,000 |
| Credit on a deal | Amount x deal-type credit % x rep split | =E2*L2*G2 | $80,000 renewal x 25% x 100% = $20,000 |
| Rep credit for a month | Sum of Rep 1 credit plus sum of Rep 2 credit, by rep and date range | =SUMIFS(N:N,F:F,rep,B:B,">="&start,B:B,"<="&end)+SUMIFS(O:O,H:H,rep,B:B,">="&start,B:B,"<="&end) | August credit = $120,000 |
| Attainment year to date | Credit year to date / annual quota | =L2/G2 | $860,000 / $800,000 = 107.5% |
| Credit inside a tier | Credit year to date that falls between the tier's lines | =MAX(0,MIN(YTD,To*Quota)-From*Quota) | Tier 2: MIN($860,000, $1,200,000) - $800,000 = $60,000 |
| Commission earned year to date | Base rate x the sum of each tier's credit x its multiplier | =H2*(N2*1+O2*1.5+P2*2) | 10% x ($800,000 x 1.0 + $60,000 x 1.5) = $89,000 |
| Payable this month | Earned year to date - already paid, never below zero | =MAX(0,R2-S2) | $89,000 - $74,000 = $15,000 |
| Owed back | Paid to date - earned year to date, never below zero | =MAX(0,U2-R2) | September: $89,000 - $87,500 = $1,500 |
| Clawback inside the window | Days from close to cancel, against the window | =IF(J2-B2<=365,"Reversed","Outside window") | June 12 to September 8 = 88 days: reversed |
Why the year-to-date method: the payout for any month is "what the plan owes so far, minus what it already paid." That one rule handles accelerators, clawbacks and rate changes without special cases, and it is a running commission true-up every month instead of one big correction at year end. If a tier has no top, leave its "To" blank and the template treats it as uncapped. Wrap divisions in IFERROR, as in =IFERROR(L2/G2,0), so a rep without a quota shows 0 rather than #DIV/0!.
Worked example: a rep crosses quota in August
These are the template's sample numbers, and they are illustrative. Jordan is an account executive with an $800,000 annual quota and an $80,000 target incentive, so the base commission rate is 10%. The rate table pays 1.0x up to 100% of quota, 1.5x from 100% to 150%, and 2.0x above 150%.
- January to July: $740,000 of credit. That includes a 60/40 split on a $150,000 deal ($90,000 to Jordan) and a $80,000 renewal that earns 25% credit ($20,000). Attainment is 92.5%. Earned and paid: $74,000.
- August: a $120,000 new business deal. Credit year to date reaches $860,000, or 107.5% of quota. The first $60,000 fills the gap to quota at 10% ($6,000). The other $60,000 sits in tier 2 at 15% ($9,000). Earned year to date is $89,000, so August pays $15,000.
- September: a $40,000 June deal cancels on September 8, 88 days after close, inside the 365-day window. A $30,000 expansion closes the same month. Net credit is -$10,000, so credit year to date falls to $850,000. Earned year to date is $80,000 + $50,000 x 15% = $87,500. Jordan has already been paid $89,000, so September pays $0 and Jordan owes back $1,500, which comes out of the next month that earns more.
Note what the clawback cost: $1,500 net, not the $4,000 that was paid on the deal in June. Removing $40,000 from a year that is past quota takes the dollars off the top, where they earned 15%, and the $30,000 expansion offsets most of it. The template recalculates the year as if the cancelled deal never closed. If your clawback policy says to recover the exact amount paid on the deal, settle the difference by hand and note it on the statement.
Tiered commission in a spreadsheet: three ways to get it wrong
The way a sheet applies tiers changes the payout more than the rates do. Here is the same August deal from the example (illustrative), under four methods.
| Method | How it works | August payout | The problem |
|---|---|---|---|
| Marginal tiers on year-to-date credit (this template) | Each dollar earns the rate of the tier it lands in | $15,000 | None. It matches how most plans word accelerators |
| Flat rate | Every dollar at 10% | $12,000 | No accelerator, so the deal after quota is worth no more than the first one |
| Rate by deal size (a lookup table) | A deal over $100,000 pays 12%, whatever the rep's attainment | $14,400 | Pays the same in January as in August. It rewards big deals, not quota attainment |
| Retroactive rate on the whole year | Once past quota, all $860,000 is re-rated at 15% | $55,000 | A cliff: one deal pays more than three times the marginal amount. Reps hold deals to cross the line |
The deal-size lookup is the method most often shown in Excel guides, using LOOKUP or VLOOKUP on the deal amount. It is fine if your plan really pays by deal size. Most quota plans do not. They pay accelerators on attainment, and that needs the year-to-date tier columns above. For how to choose the lines and multipliers, read how to structure sales commission accelerators.
Test tier 1 first. Most reps spend most of the year below quota: Ebsta and Pavilion's 2025 benchmarks put the missed quota rate at 78%, up from 69%. If the base rate is wrong, every statement is wrong.
A typical commission structure, and how it maps to the template
The common B2B SaaS structure is base salary plus a target incentive paid at 100% of quota, a base commission rate equal to target incentive divided by quota, and accelerators above quota. Each piece has a home in Plan setup.
- Quota. Pete Furseth, ORM's COO, sees most SaaS AE quotas between $500,000 and $1 million, and enterprise rep quotas at $1 million or more. The sample reps sit inside those ranges.
- Target incentive and base rate. Enter the target incentive. The sheet divides it by quota, so a rep with a larger quota and the same target incentive gets a lower rate.
- Accelerators. Tier 2 and tier 3 multipliers. A multiplier below 1.0 on tier 1 works as a decelerator.
- Deal-type credit. Renewals often earn less credit than new business. Pete also sees plans that pay a multiple when the company wants to push one product: give that product its own deal type with a credit above 100%.
- Clawback window. The sample uses 365 days, which for annual contracts runs to the first renewal. ORM's guide on how long a clawback period should be explains how to size it.
To check what a single rate and base salary pay before you build the full plan, use the commission calculator.
What a commission statement should show
A commission statement is the monthly page that tells a rep what they earned and why. The Statement tab builds one for any rep and month, with these lines:
- Rep, role and period, plus the plan terms: annual quota, target incentive, base commission rate and OTE.
- Every deal credited or reversed in the month: deal ID, close date, customer, type, booking amount, the rep's split and the credit, with a note on partial credit or a clawback.
- Credit this month, clawbacks this month, credit year to date and attainment.
- Current tier, commission earned year to date, what was paid in earlier months, the amount payable now, and any balance owed back.
- A check that the deal lines add up to the Calc tab. If it does not say "Ties to the Calc tab," do not send it.
The test of a statement is whether a rep can check every number against their own deal list. Reps who cannot do that start keeping their own copy, which is what ORM's glossary calls shadow accounting.
How to use the template in Google Sheets
- Download the .xlsx file above.
- In Google Drive, choose New, then File upload, and pick the file.
- Right-click it and choose Open with, then Google Sheets. To keep a native copy, use File, then Save as Google Sheets.
- Share the file with finance as editors. For reps, share a view-only copy or a PDF of the Statement tab, so nobody can edit the plan.
Every formula in the file (SUMIFS, COUNTIF, INDEX and MATCH, EDATE, TEXT) and every dropdown works in both Excel and Google Sheets. None of them need FILTER or dynamic arrays, so older Excel versions work too.
What Pete Furseth checks before commissions go out
Pete Furseth, ORM's COO, has seen how revenue numbers break inside customer data. Most of what he watches applies to a commission file.
- One amount field. One ORM customer had nine amount fields covering TCV, ACV, ARR, renewal amount and legacy fields nobody had filled in since 2021. It took several weeks to sort out, because no one person knew how to read them. Decide which field commissions pay on, write it in the plan, and pull only that field.
- A clear closed-won amount. Another customer could not say which field held the closed-won booking amount. It took five to seven rounds and custom logic to get it right. If your team cannot answer that in one sentence, fix it before the first payout.
- Tie to what finance reports. When ORM validates a new customer's data, the first check is that a year of bookings adds up to what the company reports internally. Run the same check on credited bookings before payroll.
- Plan for a curve, not for everyone at quota. Pete plans average attainment around 75% to 80%, with a tail below and a tail above. If the Summary tab shows nearly every rep deep in accelerators, or nearly every rep far below quota, the quota is the problem, not the spreadsheet.
- Measure ramp against full quota. New reps can be paid on a reduced ramp quota if the plan says so. When you measure productivity, Pete divides bookings by the full quota, because a reduced quota makes a new rep look more productive than they are.
When a commission spreadsheet is no longer enough
A spreadsheet works while one person can check every statement and the plan stays stable. The warning signs are more plans running at once, mid-year plan changes, and statements nobody can rebuild after the fact. Pete describes sales ops teams maintaining 37 spreadsheets with 12 tabs each just to run the forecast. Commission files grow the same way. The full comparison is in sales compensation software vs spreadsheets.
ORM's take: the spreadsheet pays the plan, it does not set it
This template pays what happened. It cannot tell you whether the quotas behind it were set at the right level, how many reps you need to hit next year's number, or what commission expense will be next quarter.
ORM is a planning platform and a forecasting platform. We model quota and sales capacity from your own reps' productivity by role and region, then forecast bookings, so finance can forecast commission expense from a number it trusts. The spreadsheet pays the plan. ORM helps you set it.
Common questions
How do I make a commission sheet?
Set up the plan first: each rep's quota and target incentive, the rate tiers and what each deal type earns. Then log one row per closed deal with the close date, amount, rep and split. Sum credit by rep and month, apply the tiers to year-to-date credit, and subtract what you already paid. The template on this page has those steps built in.
How do you calculate commission in Excel?
For a flat rate, multiply the deal amount by the rate: =B2*C2. For tiers, give each tier its own column with =MAX(0,MIN(YTD,To*Quota)-From*Quota), multiply each by its rate, and add them up. Then this month's payout is commission earned year to date minus everything already paid. SUMIFS totals credit by rep and month.
Is there a formula for commission?
The base formula is credit x commission rate, where credit is the booking amount x the deal-type credit % x the rep's split. The rate usually comes from the plan: target incentive divided by annual quota. A rep with an $80,000 target incentive on an $800,000 quota earns 10%. Accelerators then pay a multiple of that rate above quota.
What is a typical commission structure?
In B2B SaaS the common shape is base salary plus a target incentive paid when the rep hits quota. The base commission rate is the target incentive divided by quota, and accelerators raise it above 100% of quota. Pete Furseth, ORM's COO, puts most SaaS AE quotas between $500,000 and $1 million, and enterprise quotas above $1 million.
What should a commission statement include?
The period, the rep, the plan terms (quota, target incentive, base rate), every deal credited or reversed that month with the rep's split and credit, year-to-date credit and attainment, the current tier, commission earned year to date, what was already paid, and the amount payable now. A rep should be able to check every line against their own deal list.
Can I track commissions in Google Sheets?
This template works in Google Sheets. Upload the .xlsx to Google Drive and open it with Google Sheets, or start a blank sheet and use File, then Import. The formulas (SUMIFS, INDEX and MATCH, EDATE, COUNTIF) and the dropdowns carry over. Share the Statement tab as view-only so reps see their own numbers without editing the plan.
Set the plan with data, not last year's spreadsheet
This template pays your commission plan. ORM models the quotas, capacity and forecast behind it.
Schedule a Demo