Tiered commission in Excel: sliding-scale formulas that match your plan

Two plans, one table, different payouts

"Tiered" and "sliding scale" are used for two different plans, and the plan document is the only place that tells you which one you have. In a whole-amount plan (often called retroactive), reaching the 8% tier means every dollar of the quarter is paid at 8%. In a marginal plan (often called stepped or bracketed), only the dollars above $50,000 get 8%; the first $25,000 still earns 4% and the next $25,000 earns 6%.

The table is the same for both: the tier start in F, the tier end in G (blank for the top tier) and the rate in H. Keep the rates in cells, never inside the formula, so when next year's plan changes the rates you edit three cells and every rep's payout updates. The rates used here are only an example; type your own plan's rates.

Jonas sold $86,250. Whole-amount: 86,250 x 8% = $6,900. Marginal: $1,000 + $1,500 + $2,900 = $5,400. A $1,500 gap on one rep is why payroll disputes start here.

Whole-amount tiers: the lookup and its cliff

XLOOKUP with match mode -1 finds the exact value or the next smaller one, so it returns the rate of the highest tier the sales have reached. It does not need the table sorted. In Excel 2019 or older, use =B3*VLOOKUP(B3,$F$2:$H$4,3,TRUE), which does need the From column sorted smallest to largest and starting at 0.

Watch the boundary. Marcus sold $49,999 and is paid 6%: $2,999.94. Elena sold $50,000 and is paid 8%: $4,000. One extra dollar of sales is worth $1,000.06 of commission. Reps on whole-amount plans notice this and pull deals into the quarter to cross a threshold, so if you are designing a plan rather than calculating one, the marginal version avoids the cliff.

The lookup treats each From value as inclusive: exactly $50,000 gets 8%. If the plan says "over $50,000", start that tier at 50000.01 so exactly $50,000 stays at 6%.

Marginal tiers with a per-tier breakdown

For a marginal plan, work out how much of the sales falls inside each tier, then multiply by that tier's rate. The amount in one tier is the sales capped at the tier's end, minus the tier's start, and never below zero: =MAX(0,MIN($B$5,G2)-F2) in I2, filled down. MIN ignores the blank To cell in the top tier, so the top tier takes everything above $50,000.

The breakdown column is worth keeping even when you also have a one-cell formula. It is what reps ask to see on their statement, and it makes checking easy: the column must add up to the sales figure. For Jonas it shows $25,000, $25,000 and $36,250, which totals $86,250.

Marginal tiers in one cell

To fill one formula down the whole team, use the rate-step trick: for every tier the sales have passed, multiply the sales above that tier's start by how much the rate goes up at that tier. VSTACK(0,$H$2:$H$3) builds the list of previous rates (0, 4%, 6%), so $H$2:$H$4-VSTACK(0,$H$2:$H$3) gives the steps 4%, 2%, 2% straight from the rate column. Nobody has to maintain a separate step column by hand, which is where these sheets usually go wrong after a rate change.

VSTACK needs Excel 365 or 2024. In older versions, add a helper column with the step for each tier (=H2 in the first row, =H3-H2 below it) and multiply by that column instead.

Tiers on quota attainment (accelerators)

Many plans set the rate by how much of quota the rep hit, not by dollar amount: 3% below quota, 6% from 100% and 9% from 120%. The tier table then holds attainment as a decimal (0, 1, 1.2) and you look up sales divided by quota. Type the thresholds as 100% and 120%, or as 1 and 1.2; typing 100 would mean 10,000%.

Marcus hit 49,999 / 45,000 = 111% of quota, so he is paid 6% on everything. If the plan is marginal on attainment, use the breakdown or rate-step method from above, with the attainment thresholds multiplied by the rep's quota as the tier starts.

Paying monthly against quarterly tiers

When tiers apply to quarter-to-date or year-to-date sales but you pay every month, do not run the tier formula on the month's sales alone. September's $37,500 on its own would be paid at 4% and 6% ($1,750), but Jonas had already passed $48,750 by then. Instead, calculate the commission on sales to date, subtract the commission on sales to the end of last month, and pay the difference.

LET keeps the formula readable: ytd is the running total, prev is the same total without this month. Put the formula next to each month with O$2 anchored so the running total grows as you fill down. The three monthly payouts add up to exactly the $5,400 the quarter is worth.