Build a Sales Commission Calculator in Excel

A commission calculator in Excel multiplies each rep's sales by a rate. The work is in finding the right rate when the plan has tiers, and IFS or VLOOKUP with approximate match does that for you. This guide builds a calculator for five reps with a flat rate, a tiered rate, a band-by-band version, and a cap. It is useful for sales operations, finance and HR roles, and for interview tasks that test IF logic.

How commission plans are usually structured#

Commission plans vary by company, so check the written plan before you build anything. Most use one of these patterns:

This guide uses one set of sample data for every version:

Flat-rate commission#

A flat rate is one multiplication. Put the rate in its own cell, such as F1 (5%), and in C2:

=B2*$F$1

The $ signs lock F1, so every row uses the same rate when you fill down. Ana earns 125,000 × 5% = 6,250. If the rate changes next quarter, you edit one cell.

Tiered commission with IFS#

In a tiered plan, the rate rises as sales grow. Here is the plan used in the rest of this guide:

IFS checks each test in order and returns the value for the first one that is TRUE. In C2:

=IFS(B2>=100000,8%,B2>=60000,5%,B2>=30000,3%,TRUE,0)

Check the highest tier first. If B2>=30000 came first, Ana's 125,000 would pass it and get 3%. The final TRUE,0 catches everything under 30,000, because IFS has no "otherwise" result of its own. IFS needs Excel 2019 or later; in older versions, use nested IF. The guides to nested IF and the IFS function explain both, and the IFS function guide has the full syntax.

To get the commission in one formula, multiply the sales by the IFS: =B2*IFS(B2>=100000,8%,B2>=60000,5%,B2>=30000,3%,TRUE,0).

Tiered commission with a VLOOKUP tier table#

A tier table keeps the rates out of the formula, so anyone can update them. List each tier's starting amount, smallest first, in E2:F5:

Then in C2:

=B2*VLOOKUP(B2,$E$2:$F$5,2,TRUE)

With TRUE, VLOOKUP does an approximate match: it finds the largest start value that is less than or equal to the sales, and returns that row's rate. Ben's 72,000 lands on the 60,000 row, so his rate is 5%.

Ema shows why boundaries matter: exactly 60,000 gets 5%, because the 60,000 tier starts there. The table must be sorted from smallest to largest, and its first row must start at 0, or low sales return #N/A. More detail is in VLOOKUP approximate match for tiers and bands and the VLOOKUP function guide.

Band-by-band commission#

Some plans pay each slice of sales at its own rate, like income tax bands. Ana's 125,000 is then paid 0% on the first 30,000, 3% on the next 30,000, 5% on the next 40,000 and 8% on the last 25,000:

One formula does the same. Each part takes the sales that fall inside one band, using MIN to cut off the top and MAX to stop negative numbers:

=MAX(0,MIN(B2,60000)-30000)*3%+MAX(0,MIN(B2,100000)-60000)*5%+MAX(0,B2-100000)*8%

Ben gets 1,500 this way, compared with 3,600 under the whole-amount plan. That gap is why you must know which kind of plan you are building.

Quotas, caps and rounding#

Quota attainment. If each rep has a target in column C, =B2/C2 gives the share of quota reached. Format it as a percentage, then look the attainment up in a tier table, just like sales.

Caps. Many plans limit commission per period. If the cap is in F8, wrap the formula in MIN:

=MIN(B2*VLOOKUP(B2,$E$2:$F$5,2,TRUE),$F$8)

MIN returns the smaller of the two, so no one is paid above the cap.

Rounding. Rates such as 3.5% can produce fractions of a cent. Round the final amount with =ROUND(…,2) so payroll receives exact cents.

Common commission calculator mistakes#

• Tiers checked lowest first in IFS, so everyone gets the lowest rate.
• An unsorted tier table with approximate match, which returns wrong rates without any error.
• Typing 8 instead of 8%. 8 multiplies the sales by eight; 8% (or 0.08) is the rate.
• Using > instead of >= at a boundary, so a rep on exactly 60,000 drops a tier.
• Hard-coding rates in many formulas, so a rate change means editing every row.

The same tier logic powers a KPI summary sheet, and the next project, hours worked and overtime, uses IF to pay a higher rate above a threshold.

Practise it#

Do the exercise on this page to calculate a commission with a VLOOKUP tier table. Then practise nested IF and IFS, which ends with a commission task, and practise VLOOKUP approximate match on a tier table in a live spreadsheet.