Calculating A Bonus Or Commission In Excel

One of the recurring type of requests that I receive through managing this blog is trying to calculate a commission or bonus structure, especially by managers that have a sales force. I did touch on the subject a bit but I thought I’d do a quick review of the different type of commissions and how I’d calculate them in Excel. I am also providing an Excel file that you can view here if you can’t wait:) Here a few types of commissions and how I’d do them:

Flat Commission

In this structure, a sales person would get a % of sales no matter what. This is the most simple case:

Flat Commission With A Floor

In this structure, a sales person would get a % of sales but only under the condition that they reach a minimum of sales:

Flat Commission With A Ceiling

As much as companies like to reward star employees, some are worried about the idea that a sales guy would end up making more than the president so they end up adding a maximum/ceiling that someone can earn:

Brackets

Here is a common system that is used. If you generate 10K in sales, you will earn X%. If you earn 25%, you will earn X% of your sales, etc. Here I will use the Nested If excel function:

=IF(A10<$A$2,$B$2*A10,IF(A10<$A$3,$B$3*A10,IF(A10<$A$4,A10*$B$4,A10*$B$5)))

Scaling Bonus

The scaling bonus looks similar but is different in the sense that if you look at the same chart:

This time, if you make $25K in sales, you will get 3% of the first 10,000 and 8% on the next 15,000. This is slightly more complicated. Here is how I will do it, step by step:

First I will determine the amounts you would make at each number of the scale:

Then, I will determine the appropriate “level” and add anything incremental by using nested if conditions:

=IF(A11<$A$3,A11*$B$2,IF(A11<$A$4,$C$3+(A11-$A$3)*$B$3,IF(A11<$A$5,$C$4+(A11-$A$4)*$B$4,IF(A11<$A$6,$C$5+(A11-$A$5)*$B$5,(A11-$A$6)*B6+$C$6))))

As is always the case, you can Bonus_Commission.