Commission Excel template: the short answer
Our free commission Excel template calculates commission per rep per month with a flat rate, a tiered model or an accelerator above quota. It deducts clawbacks, respects a cap and produces both a payroll file with a pay code and a statement you can give the rep. Everything is ordinary formulas with no macros.
Download the commission Excel template (xlsx, free)
The template is for sales teams of up to 10 reps who calculate commission in a spreadsheet today, or who want to get started without building the formulas themselves. It contains example data for 3 reps and 20 deals, so you can see how it works before you enter your own numbers. It works in Excel, Google Sheets, Numbers and LibreOffice. Amounts are plain numbers in your currency; the example uses DKK.
What is in the template?
The workbook has six sheets. You only type in the lavender cells. Everything else is formulas that update when you change a number.
| Sheet | What it does | What you do |
|---|---|---|
| Instructions | Short guide and an explanation of how the workbook calculates | Read it once |
| Settings | Year, payroll month, pay code and one row per rep with model, basis, quota, rates, tiers and cap | Fill in reps and rates |
| Sales | One row per deal: date, rep, customer, deal ID, amount, gross margin, status and clawback | Add the month's deals |
| Calculation | Sales, attainment, commission under the model, accelerator amount, cap, clawbacks and final commission per rep per month | Nothing, it is for checking |
| Payroll | One line per rep with employee number, pay code, description and amount for the payroll month | Enter or import into payroll |
| Statement | A print-ready statement for a chosen rep and month with deals and clawbacks | Pick a rep, save as PDF |
Settings: three commission models
Each rep gets their own row, so you can have different models in the same team:
- Flat rate: commission = basis × rate, for example 6% of revenue.
- Tiered model with three tiers: each unit of currency earns the rate of the tier it falls in. This is called marginal calculation and is the most common form of tiered commission. The limits apply per month.
- Accelerator above quota: a base rate up to the monthly quota and a higher rate on everything above. The accelerator amount is shown in its own column, so the rep can see what overperformance pays.
For each rep you also choose whether commission is paid on revenue or gross margin. Quota and tier limits are entered in the same basis. A monthly cap is optional.
Sales: status and clawback
Each deal has a status and a clawback field. The combination controls how the deal counts:
| Status | Clawback | What happens? |
|---|---|---|
| Won | No | The deal counts in the month it was sold |
| Cancelled | No | Cancelled before commission was paid. Does not count |
| Cancelled | Yes | Commission was paid. The deal stays in its original month, and the commission is deducted in the month given in "Cancelled date" |
This means earlier months do not change when a customer cancels. What was paid still stands, and the correction shows up as a clawback in the month it happens.
Calculation: the order of the formulas
For each rep and month, the workbook calculates in this order:
- Sales and basis are summed from the Sales sheet.
- Attainment = basis ÷ monthly quota.
- Commission under the rep's model (flat rate, tiered or accelerator).
- The cap is applied, if there is one.
- Clawbacks are offset. If they are larger than the month's commission, the rest carries to the next month, so commission never turns negative.
- Final commission and effective rate (final commission before clawbacks ÷ basis).
The clawback amount is the basis of the cancelled deal multiplied by the rep's effective rate in the month the deal originally counted in. It is a simple, transparent rule that also works in a tiered model, where a single deal does not have one fixed rate.
Payroll file and statement
The payroll sheet collects final commission for the payroll month with one line per rep. The statement shows sales, basis, quota, attainment, commission before and after cap, clawbacks and the deals that counted for a chosen rep. It follows the fields a good commission statement should have and is set up to print on one A4 page. If you need draws, approvals and adjustments on the statement, see our commission statement template.
How to use the template
- Settings: pick the year and payroll month. Enter the reps with model, basis, quota, rates and any cap. Enter the pay code.
- Sales: delete the example data and add your deals. Gross margin is only needed for reps paid on margin.
- Calculation: check the numbers for the month. Use the filter in the header to see one rep at a time.
- Payroll: approve the amounts and enter or import them into payroll.
- Statement: pick each rep in the drop-down and save the statement as a PDF.
- Save a copy each month: so you can always show what was paid and on what basis.
Worked example: March with three reps
Example: the template's example data covers January to March 2026 for three reps, in DKK. The payroll month is March. This is how the reps are set up:
| Rep | Model | Basis | Quota per month | Rates | Cap |
|---|---|---|---|---|---|
| Anna Holm | Flat rate | Revenue | DKK 250,000 | 6% | None |
| Bo Jensen | Tiered | Revenue | DKK 300,000 | 4% up to DKK 150,000, 7% up to DKK 300,000, 10% above | DKK 25,000 |
| Camilla Nørgaard | Accelerator | Gross margin | DKK 120,000 (margin) | 15% up to quota, 25% above | DKK 35,000 |
Anna: flat rate and a clawback
Anna sells DKK 130,000 and DKK 95,000 in March, DKK 225,000 in total. A third deal of DKK 75,000 was cancelled before commission was paid, so it does not count. Commission is 225,000 × 6% = DKK 13,500, and attainment is 225,000 ÷ 250,000 = 90%.
At the same time, a customer from January cancels an agreement of DKK 60,000 that Anna has already been paid commission on. Her effective rate in January was 6%, so the clawback is 60,000 × 6% = DKK 3,600. Final commission for March is 13,500 − 3,600 = DKK 9,900.
Bo: tiered model and cap
Bo sells DKK 230,000 and DKK 190,000, DKK 420,000 in total. With marginal calculation:
- Tier 1: 150,000 × 4% = DKK 6,000
- Tier 2: 150,000 × 7% = DKK 10,500
- Tier 3: 120,000 × 10% = DKK 12,000
DKK 28,500 in total. The cap is DKK 25,000, so Bo receives DKK 25,000. In Excel, the formula for the tiered model looks like this, with the basis in F6: =MIN(F6,150000)*4%+MAX(0,MIN(F6,300000)-150000)*7%+MAX(0,F6-300000)*10%. In the template, the formula takes limits and rates from Settings, so you do not need to touch it.
Camilla: accelerator on gross margin
Camilla is paid commission on gross margin. In March she has three deals with a total margin of 61,200 + 50,400 + 48,400 = DKK 160,000. Quota is DKK 120,000, so attainment is 133.3%.
- Base up to quota: 120,000 × 15% = DKK 18,000
- Accelerator amount above quota: 40,000 × 25% = DKK 10,000
DKK 28,000 in total, which is below the DKK 35,000 cap.
Result in the payroll file for March
| Rep | Commission before cap | After cap | Clawback | Payable |
|---|---|---|---|---|
| Anna Holm | DKK 13,500 | DKK 13,500 | −DKK 3,600 | DKK 9,900 |
| Bo Jensen | DKK 28,500 | DKK 25,000 | DKK 0 | DKK 25,000 |
| Camilla Nørgaard | DKK 28,000 | DKK 28,000 | DKK 0 | DKK 28,000 |
| Total | DKK 62,900 |
For more formulas, for example for split deals and draws, see our guide to commission calculations with formulas and examples.
Payroll file and pay code
In the payroll system, commission is recorded under a specific pay code, and the code differs from system to system. It comes from your payroll system or provider, not from the template. Enter it in Settings and it appears on every line of the payroll file. Until it is filled in, the sheet shows "Enter pay code" in red.
At the same time, agree with your payroll system or provider how the pay code is set up for tax, labour market contribution, pension and holiday pay. It is a one-time setup, but it causes errors in every payroll run if it is wrong.
When is Excel not enough?
A spreadsheet is a good start, and for a small team with a simple plan it may be all you need. But there are limits, and it is better to know them in advance:
- Version control: who has the right file? When the spreadsheet is copied, emailed and edited by several people, it is hard to show which version a payout is based on.
- Errors in formulas and data: a row that falls outside a range, or a deal copied wrongly from the CRM, causes an error without an error message. The sheet just keeps calculating.
- Visibility for reps: the rep sees their commission once a month, when the statement arrives. They cannot follow along or see what the next deal will pay.
- Approvals and audit trail: a spreadsheet does not remember who approved what, or why a number was changed.
- Complexity: splits, several plans per rep, quarterly targets, holiday pay calculation and draws can be built in Excel, but every new rule makes the sheet harder to check.
Read more about the steps from Excel to automated commission.
Write the rules down in the commission agreement
The spreadsheet only calculates what you have decided. When a sale counts, how clawbacks are offset and how commission during holiday and sickness is calculated should be in a written agreement with the rep, so the spreadsheet and the agreement say the same thing. We have also made a free commission agreement template that matches the models in the spreadsheet. You can find all our templates in the overview of free commission and sales compensation templates.
Prowi: commission without spreadsheets
Prowi calculates commission continuously from your CRM data, so reps can follow their earnings in a mobile app. Admins approve the statements, and Prowi passes the approved amounts on to payroll, straight into Zenegy or as a payroll file for systems such as Danløn and Visma Dataløn. Adjustments and clawbacks are handled as rules instead of manual edits. Book a demo to see how it looks with your own plan.
FAQ
How do you calculate commission in Excel?
Make one row per deal with date, rep and amount, sum sales per rep and month with SUMIFS, and multiply by the rate. For a tiered model, calculate each tier separately with MIN and MAX. The template has it all built in.
Does the template work in Google Sheets?
Yes. The template uses only ordinary formulas and no macros, so it works in Google Sheets, Numbers and LibreOffice. Upload the xlsx file to Google Drive and open it with Google Sheets.
How do you handle clawbacks in a spreadsheet?
Leave the original month unchanged, and deduct the commission in the month the customer cancels. The template does it automatically when a deal has status Cancelled, clawback Yes and a cancelled date.
What is a pay code?
A pay code is the code the payroll system uses to know what kind of pay a line is and how it is taxed and reported. The code for commission comes from your payroll system or provider.
Is the commission Excel template free?
Yes. You can download and use it freely without creating an account. Adapt it to your own plan.