Commissions and bonuses

Sales staff are often paid a base amount plus a share of what they sell. The share is the commission, and a bonus may sit on top for hitting a target. Each piece is one short calculation, but the pay plan's wording decides which numbers go where. This page works through a flat commission, a tiered commission and a quota bonus for one sales team, and shows where people usually go wrong.

Commission pay in plain English

• Base pay is the fixed amount a salesperson earns for the period, whatever they sell.
• Commission is pay based on sales: sales times a commission rate.
• A flat commission uses one rate for every dollar of sales.
• A tiered commission pays a higher rate on sales above a threshold. The higher rate applies only to the part of sales above the threshold, not to all of it.
• A quota bonus is a fixed amount paid when sales reach a target.
• Gross pay is everything earned before deductions: base pay plus commission plus any bonus.

The structure is always the same: work out each component on its own, then add them up. Keeping each piece in its own column makes the sheet easy to check.

Worked example: Maple Lane Catering

Maple Lane Catering pays its sales staff a monthly base plus commission. This is the pay for May. The example pay plan sits beside the payroll in its own table:

Salespeople are in rows 5 to 12, with the name in column A, base pay in B and sales for the month in C.

Flat commission

Under a flat plan, every dollar of sales earns 5%. For L. Adeyemi, with sales of $58,900 and base pay of $2,550:

• Commission: $58,900 × 5% = $2,945.00
• Gross pay: $2,550.00 + $2,945.00 = $5,495.00

In the sheet, commission in D5 is =C5*$L$4 and gross pay in E5 is =B5+D5. The dollar signs keep the rate pointing at L4 when you fill down. The first few rows:

Tiered commission

Now the plan adds a tier: sales up to $25,000 earn 5%, and only the sales above $25,000 earn 7%. Think of each person's sales as two slices.

• The lower slice is the smaller of sales and the threshold. It earns the base rate.
• The upper slice is sales minus the threshold, but never less than zero. It earns the higher rate.

For L. Adeyemi, with sales of $58,360 this time:

• Lower slice: $25,000 × 5% = $1,250.00
• Upper slice: ($58,360 − $25,000) × 7% = $33,360 × 7% = $2,335.20
• Commission: $1,250.00 + $2,335.20 = $3,585.20

For H. Ito, with sales of $22,740, there is no upper slice. The whole amount earns 5%: $1,137.00.

The MIN and MAX pattern handles both cases in one formula:

=MIN(C5,$L$5)*$L$4+MAX(0,C5-$L$5)*$L$6

Here is how the tier plays out across four of the team:

A. Rivera sold exactly $25,000. The upper slice is zero, so all of it earns 5%. A formula built on MIN and MAX gets that boundary right without any special case.

This is the same idea as overtime for hourly staff: a threshold, a normal rate below it and a higher rate only above it. If you have worked through overtime pay calculation, the formula will look familiar.

Commission plus a quota bonus

The last version of the plan uses the flat 5% commission and adds a $400 bonus for anyone whose sales reach the $40,000 quota. "Reach" includes sales exactly equal to the quota. The bonus column is a yes-or-no test:

=IF(C5>=$L$7,$L$8,0)

Gross pay then adds all three components: =B5+D5+E5.

A. Rivera's sales are exactly $40,000, so they reach the quota and earn the bonus. H. Ito is $3,870 short and earns no bonus. Note that the bonus is a fixed amount: G. Mensah, $1,600 over the quota, gets the same $400 as A. Rivera.

Why the plan lives in its own cells

Every formula above points to a cell in the plan table rather than typing 5%, $25,000 or $400 into the formula. That choice pays off in two ways. First, the sheet explains itself: anyone reviewing it can see the plan and trace each number back to it. Second, plans change. If next month's example plan raises the base rate to 6%, you change L4 once and every commission on the sheet updates. A rate typed into eight separate formulas has to be found and changed eight times, and the one that gets missed is the one that causes the error. The exercises check your results against the plan cells, so a formula that follows the plan will keep working whatever values the plan holds.

Reading a pay plan carefully

Commission errors are rarely arithmetic errors. They come from reading the plan loosely. Before writing a formula, answer three questions about the plan:

• Does the higher rate apply to all sales or only to the part above the threshold? In this example plan, only the part above.
• Does "reach the quota" include sales exactly at the quota? In this example plan, yes, so the test uses >=.
• Is the bonus a fixed amount or a percentage? In this example plan, a fixed $400.

Real pay plans answer these questions in different ways. The skill is the same: turn each sentence of the plan into one part of a formula, and test it on someone below, at and above each boundary.

Common mistakes

The exercises check for each of these and tell you which one you made.

A rate reference that moves when filled down

Writing =C5*L4 without dollar signs works in row 5, but filling it down turns row 6 into =C6*L5. F. Larsen's commission becomes $16,360 × 25,000, a nonsense figure. Worse, row 7 becomes =C7*L6, which multiplies I. Petrov's sales by 7% and gives a plausible-looking but wrong $1,898.40 instead of $1,356.00. Lock the rate with $L$4. For more on this, see absolute cell references in Excel.

Leaving the base pay out of gross pay

Gross pay of $2,945.00 for L. Adeyemi in the flat example is just the commission. Gross pay is everything earned: $2,550.00 base plus $2,945.00 commission, which is $5,495.00.

Paying the higher rate on all sales

A formula that says "if sales are above the threshold, multiply all sales by 7%" gives L. Adeyemi $58,360 × 7% = $4,085.20. Under the example plan, the first $25,000 still earns 5%, so the right commission is $3,585.20, which is $500.00 less.

Ignoring the tier

The opposite slip is to use the flat 5% on everything: $58,360 × 5% = $2,918.00. Sales above the threshold earn the higher rate in L6, so split the sales at the threshold.

Missing the bonus at exactly the quota

Testing C5>$L$7 instead of C5>=$L$7 denies A. Rivera the bonus for sales of exactly $40,000. Reaching the quota includes being equal to it.

Leaving the bonus out of gross pay

Gross pay of $5,800.00 for A. Rivera adds base and commission but forgets the bonus. A bonus is part of gross pay: $3,800.00 + $2,000.00 + $400.00 = $6,200.00.

How to check your commission payroll

• Gross pay minus base pay equals commission plus bonus, on every row.
• Under the flat plan, commission divided by sales gives exactly the rate in L4.
• Under the tiered plan, anyone at or below the threshold has commission of sales × the base rate. Anyone above has more, but less than sales × the higher rate.
• Everyone at or above the quota has the bonus, and nobody below it does. Look closely at anyone right at the boundary.

Hourly staff are covered in overtime pay calculation and salaried staff in salary per pay period. To take gross pay through to net pay, see payroll calculations in Excel, and to record it, see payroll journal entries.