How to calculate tiered commission with accelerators, splits and clawbacks

Commission plans rarely stay simple. A rate steps up past quota, two reps share a deal, a customer cancels after the money was paid, and a rep’s rate changes at the start of a quarter. Each of those is where a spreadsheet starts producing disputes: a formula copied one row too far, a split entered twice, a clawback nobody can explain. This guide builds a plan that handles all four, step by step, with every figure shown, in a workspace your reps can open to see their own numbers.

The plan

• Each rep has a quarterly quota: €60,000 for Laura, €50,000 for Marco and Amira.
• Commission is 8% up to quota and 12% on everything above it, marginally: the higher rate applies only to the part above quota.
• A deal can be split between reps. The Enterprise plan deal is 60% Laura, 40% Marco.
• When a deal is cancelled, its commission is clawed back: the credit is reversed in the quarter it was earned, and the difference is recovered in the month of the cancellation.
• Laura’s base rate goes up to 9% from Q4. Her Q3 deals keep 8%.
• Each rep sees their own lines and nothing else.

Marginal or whole-period: decide first

The same words, “12% above quota”, can mean two different plans, and the difference is large. Take Laura’s quarter: €74,200 of credited sales against a €60,000 quota.

Write the plan so it says which one it means. This guide builds the marginal one, the more common of the two; the end of the post shows the one change that turns it into the other.

Step 1. Reps and their quarterly plans

Keep the terms on a plan per rep per quarter, not on the rep. That single decision is what lets a rate change without repricing anything already earned.

In Q4, Laura’s plan row says 9%. Her Q3 row still says 8%, and every Q3 sale stays linked to it.

Step 2. Deals, and credits that can be split

A deal is what was sold; a credit is who gets paid for it. Separating the two is what makes splits and clawbacks clean.

Each credit then works out a few values from its links:

• Credited = the deal’s amount × share, negative for a clawback. The €42,000 Enterprise plan credits €25,200 to Laura and €16,800 to Marco.
• Date = the deal’s close date, or its cancellation date for a clawback.
• Quota, Base rate and Rate above quota, read from the credit’s plan.
• Rep, a lookup that finds the rep whose Person matches the credit’s: the rep’s own record, which the commission lines and the payout run group by.

Step 3. Sales to date, per plan

A marginal rate depends on where a sale falls against quota, so each credit needs the rep’s running total for its quarter: all the credits on the same plan, in date order, up to and including this one. In Sterodata this is a Dynamic Table over Credits with one calculated column, Sales to date. Laura’s Q3 runs €18,000, €43,200, €74,200.

Step 4. Commission on each line

Here is the whole plan in one rule. Commission earned on sales of x is:

min(x, quota) × base rate + max(x − quota, 0) × rate above quota

and the commission on a line is what that gives for sales to date, minus what it gives for sales to date before this line. Laura’s Q3:

That is €6,504 for the quarter, exactly the marginal figure above. The same rule gives Marco €4,816 on €56,800 and Amira €3,520 on €44,000, below her quota.

Step 5. Claw back a cancelled deal

Northwind cancels the Enterprise plan on 14 October. Two clawback credits go in, one per original share, each on the original Q3 plan and dated the day of the cancellation. Because they are part of the quarter they reverse, the same rule recalculates the quarter as if the deal had never closed:

Notice that Laura’s clawback is more than 8% of her €25,200 share. Without that deal she never reached quota, so the €14,200 she earned at 12% on her September deal drops back to 8% too. Reversing only the original line at its own rate would have under-recovered. This is why, on a tiered plan, a clawback should recalculate its period rather than reverse one line.

Step 6. Pay by month

A Summary over the commission lines, one row per rep and one column per month, is the payout run:

October nets each clawback against that month’s new commission. Laura’s new €18,000 deal earns €1,620, at her Q4 rate of 9%, against her €2,584 clawback, leaving −€964. Most plans carry a negative month forward and recover it from the next payouts rather than ask for money back; write which one yours does.

Step 7. Each rep sees their own

Save a view of the commission lines filtered to lines whose Person is the current user, and give the reps’ role that view and nothing else. Laura opens it and sees her lines, her clawback and how each figure was reached. She never sees Marco’s, the plans table or the payout run. Every credit also shows who entered it and when, so a disputed line has an answer. See how to give each person access to only what they need.

Build it with your assistant

With ChatGPT or Claude connected to your workspace, describe the plan the way you would to a new finance hire:

We pay sales commission quarterly. Each rep has a quota; we pay 8% up to quota and 12% on the part above it. Deals can be split between reps, and a cancelled deal is clawed back in the quarter it was earned. Rates can change per quarter. Set up reps, quarterly plans, deals and credits, and the commission on every line.

Then check it the way this post did: pick one rep, one quarter, and work the figures out by hand. You can change any of it yourself afterwards, without code.

Good to know

• Whole-period instead of marginal: a line before quota pays the base rate, a line after it pays the rate above quota, and the line that crosses quota also carries the top-up on everything before it: (rate above quota − base rate) × sales to date before the line. Decide which plan you run before you build it.
• More tiers: each extra threshold is one more min and max term in the rule.
• Caps: cap a rep’s quarter by capping the commission earned in the rule: min(commission earned, cap).
• Draws and guarantees can be a separate table of advances, netted in the payout run.

Questions

What is the difference between tiered commission and an accelerator?

Both pay a higher rate once a rep passes a threshold. With marginal tiers, the higher rate applies only to the sales above the threshold. With a whole-period (retroactive) tier, the highest rate reached applies to every sale in the period. On €74,200 of sales against a €60,000 quota, at 8% and 12%, marginal pays €6,504 and whole-period pays €8,904.

How do you calculate a clawback on a tiered commission plan?

Reverse the credit in the period it was originally earned, recalculate that period’s commission, and recover the difference. Applying the original line’s rate is not enough on a tiered plan, because removing the sale can drop the rep back below quota and change the rate on other sales too.

How do you split commission between two reps?

Give the deal one credit line per rep, each with its share, so the shares add up to 100%. Each line counts toward its own rep’s quota and is paid at that rep’s rates. A clawback on a split deal reverses each line at its original share.

How do you change a commission rate without repricing past deals?

Keep the terms per period, not per person: one plan row per rep per quarter, with that quarter’s quota and rates, and link each credit to the plan it was earned under. A new rate is a new row for the new quarter; earlier lines keep reading their own.