Commission sheet template: how to calculate tiered sales commission in Excel

Commission day is when a spreadsheet gets tested. A rep closes a big quarter, crosses a quota line halfway through the month, shares one deal with a colleague and has another refunded two weeks later. Somebody now has to work out one number, and that number has to survive a rep who checks it twice.

A commission sheet turns the plan into arithmetic you can show. At its simplest it lists deals, applies a rate and adds them up. Real plans add quota, tiers, accelerators, caps, split credit and clawbacks, and that is where hand-built sheets start to break. This guide explains how each plan type works, with the formulas and a worked example you can copy into Excel, and which parts of a commission calculation deserve the most care.

The three plan types

Most commission plans fall into one of three shapes. Choosing between them is a business decision, and it changes what a rep earns by thousands.

Flat commission

One rate on all sales. A rep earns 5 percent of everything they close.

=Sales * Rate

It is easy to explain and to calculate. It does not reward a rep for going past quota, and it does not protect the company if a few big deals land in one month.

Tiered commission

The rate rises as the rep covers more of their quota, and each rate applies only to the sales inside its own band. This is the most common structure for sales teams.

Retroactive commission

The highest tier reached applies to all sales in the period, including those already counted at lower tiers. Crossing a threshold raises the payout on everything sold so far. It motivates hard at the finish line, and it can produce big jumps in cost, so the rates are usually lower than in a tiered plan.

Quota and attainment

Quota is the sales target for a period. Attainment is how much of it the rep has reached:

=Credit / Quota

A rep with a 210,000 quarterly quota and 268,000 of credited sales has an attainment of 268,000 / 210,000 = 127.6 percent.

Tiers are normally defined as a share of quota, not as fixed dollar amounts, so one plan can serve reps with different quotas. If a business is seasonal, quota can be phased by month, so December carries more than February.

A worked example: tiered commission

Here is a plan with four tiers. The rates are example figures. The rep has a 210,000 quota and closes 268,000.

| Tier | Band as a share of quota | Band in sales | Rate | |---|---|---|---| | 1 | 0 to 50 percent | 0 to 105,000 | 3 percent | | 2 | 50 to 100 percent | 105,000 to 210,000 | 5 percent | | 3 | 100 to 125 percent | 210,000 to 262,500 | 8 percent | | 4 | Above 125 percent | Above 262,500 | 12 percent |

Tiers 3 and 4 are accelerators, rates that kick in after quota is met.

Now split the 268,000 into bands and apply each rate to its own band:

• Tier 1: 105,000 × 3 percent = 3,150
• Tier 2: 105,000 × 5 percent = 5,250
• Tier 3: 52,500 × 8 percent = 4,200
• Tier 4: (268,000 − 262,500) = 5,500 × 12 percent = 660

Total commission: 3,150 + 5,250 + 4,200 + 660 = 13,260.

For comparison, the same sales on a flat 5 percent plan pay 13,400. The tiered plan pays a little less here, because most of the rep's sales fall in the lower bands. A rep who reached 150 percent of quota would earn noticeably more on the tiered plan than on the flat one.

The same sales on a retroactive plan

Now take the same table but apply it retroactively. The rep reached tier 4, so 12 percent applies to everything:

268,000 × 12 percent = 32,160

That is more than double the tiered result for the same sales, which shows why retroactive plans usually use smaller rates at the top and why the structure needs testing before it goes live. The rates in this example are only meant to show the mechanics.

Always model your plan on real sales numbers before you announce it. A what-if calculation comparing flat, tiered and retroactive on the same sales takes minutes and can prevent an expensive surprise.

Why commission is calculated on period-to-date sales

A subtle point that causes many errors: commission in a tiered plan should be calculated on cumulative credit for the period, with the amount already paid taken off.

Suppose a rep sells 90,000 in January, 80,000 in February and 98,000 in March on the plan above. At the end of March the cumulative credit is 268,000 and the commission earned for the quarter is 13,260. Commission earned to date was 2,700 after January (90,000 × 3 percent) and 6,400 after February (3,150 + 65,000 × 5 percent), so those months paid 2,700 and 3,700. March pays the difference, 13,260 − 6,400 = 6,860.

Calculating each month separately would under-pay a rep who crosses a tier in the third month. Working period-to-date means the crossing pays in the month it happens.

Credit rules: splits, deal types and timing

Deals are rarely simple. Decide the rules before the first payout:

• Split deals. When two reps work one deal, share the credit. A 50,000 deal split 60/40 gives 30,000 to one rep and 20,000 to the other.
• Different credit by deal type. New business may count in full while renewals count at 50 percent, so a 20,000 renewal gives 10,000 of credit.
• Revenue or margin. Some plans pay on gross margin so that deep discounts do not pay as well as full-price sales.
• When commission is earned. Either on the deal date or when the customer pays. Paying on cash received protects the company from bad debt, but reps wait longer.
• Credit and payout caps. A cap limits credit per deal or total commission per period. Caps protect you from windfalls, and reps should know about them in advance.

Clawbacks and draws

Two situations cause most payroll arguments.

Clawbacks

When a customer cancels or gets a refund soon after a sale, the company may take back commission already paid. Define a clawback window, for example 60 days, and say what happens with a partial refund.

Example: a rep sold a 20,000 deal at a 5 percent rate. The customer refunds 8,000 inside the window. Clawback: 8,000 × 5 percent = 400, deducted in the month of the refund. Use the rate the rep earned in the month of the original sale, because rates change as tiers are crossed.

Draws

A draw is an advance against future commission, often used while a new rep ramps up. A recoverable draw guarantees a minimum payment and carries any shortfall forward until it is earned back.

Example: the draw is 3,000 a month. In month one the rep earns 1,800 in commission, so 3,000 is paid and 1,200 is owed back. Once the ramp period ends and the draw stops, a month earning 4,000 first repays the 1,200 and pays 2,800.

Put draw terms in writing. Rules on recovering unearned amounts vary by country and state, and some places restrict deductions from pay.

Manager overrides, SPIFFs and adjustments

Beyond the plan itself you will need a place for:

• Manager overrides: a percentage of the team's credit paid to the manager
• SPIFFs and bonuses: short-term incentives for a particular product or week
• Corrections: fixes for earlier months, with a note explaining why

Keep these on a separate list that feeds the monthly total, rather than typing over calculated cells. That leaves a clear record for anyone who asks why a number changed.

The monthly statement: the fastest way to end disputes

The best protection against commission arguments is transparency. Give every rep a monthly statement that shows:

• Each deal with its date, customer, amount and credit
• Quota, attainment and the tier reached
• Commission earned, previous payments and the amount now payable
• Clawbacks, draws and adjustments, each on its own line

When a rep can recompute the figure from the statement, most questions never reach you.

Commission invoice format for agents

Independent agents and referral partners often bill you instead of being on payroll. A commission invoice should include:

• The agent's details and your company's details
• Invoice number and date
• The period and the deals it covers
• Commission earned, adjustments and clawbacks
• A tax line if the agent is registered for it
• Payment terms and bank details

Producing it from the same data as the calculation avoids the chance of the invoice and the sheet disagreeing.

Mistakes that break commission sheets

• Calculating each month on its own. A tier crossed in month three is missed.
• Typing rates into formulas. Rates belong in a plan table, so one change updates everything.
• No quota phasing for seasonal businesses.
• Applying today's rate to old clawbacks. Use the rate of the original month.
• Forgetting cancelled and refunded deals.
• Hard-coding deal splits. Store each rep's share of a deal.
• Overwriting formulas. Lock the calculated cells and leave only inputs open.
• No statement for reps. Without one, every payout becomes a conversation.

A ready-made commission calculator in Excel

You can build the logic above yourself, but linking plans, quota, tiers, splits, clawbacks and statements without a broken reference takes many hours, and commission errors are costly in both money and trust. The Sales Commission Calculator is an Excel workbook with the whole process built in.

Set up each rep once with a plan, annual quota, manager and draw, then log deals as they close. Plans can be flat, tiered or retroactive, measured monthly, quarterly or yearly, and paid on revenue or margin, with up to 6 tiers, accelerators and caps. The Commission Run tab shows the calculation line by line for every rep, taking off what has already been earned in the period, so crossing a tier pays in the month it happens.

Deal types credit at different rates, splits share credit between two reps, and clawbacks handle cancellations and partial refunds inside your window. Recoverable draws carry any shortfall forward, and the Adjustments tab holds overrides, SPIFFs and corrections. The What-If Calculator tests a plan before you roll it out, showing the commission band by band and the same sales under flat, tiered and retroactive structures. Each rep gets a printable monthly statement, agents get a commission invoice, and the Dashboard shows credit, earned, payable, cost of sales and a leaderboard. It covers 40 reps, 10 plans and 2,000 deals for one fiscal year, locks the formulas without a password and contains no macros.

For a simpler setup, these templates may be enough:

Common questions

How do you calculate tiered commission in Excel?

Split the sales into bands by tier, multiply each band by its own rate and add the results. In the example above, 268,000 of sales on a 210,000 quota pays 3,150 + 5,250 + 4,200 + 660 = 13,260.

What is the difference between tiered and retroactive commission?

With tiered commission, each rate applies only to the sales inside its band. With retroactive commission, the top tier reached applies to all sales in the period, so crossing a tier raises the payout on everything already sold.

What is a commission accelerator?

An accelerator is a higher rate that applies once a rep passes quota. In a tiered plan, you create it with a tier that starts above 100 percent of quota.

How do I handle commission on cancelled or refunded deals?

Define a clawback window and take back commission at the rate earned in the month of the original sale. For a partial refund, claw back only the refunded part. Check how your country and state treat deductions from pay.

Should commission be paid when the deal closes or when the customer pays?

Both are common. Paying on cash received protects you from bad debt but delays the rep's payout. Whatever you choose, write it into the plan so there is no argument later.

Does it work in Google Sheets?

The workbook is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over, so the locked formulas are easier to overwrite there by accident.

A clear plan and a transparent statement do more for sales morale than a bigger rate. You can preview the Sales Commission Calculator free; downloading it is included in the OpenWorksheet monthly plan, along with the rest of the library. All rates and figures in this guide are examples, and commission plans should follow your contracts and local employment law.