# Calculating A Bonus Or Commission In Excel

### Excel function tutorials

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.

***************************************************

***************************************************

27 Feedbacks on "Calculating A Bonus Or Commission In Excel"

winkie winkie

### Teenyweenyurl.Com

Teenyweenyurl.Com

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### john lewis plc london

john lewis plc london

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### where can i find cheap furniture in enterprise al

where can i find cheap furniture in enterprise al

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### it-42.ru

it-42.ru

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### online store india laptop

online store india laptop

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### Cheap sofas n.ireland

Cheap sofas n.ireland

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### Http://hbshxs.cn/comment/html/index.php?page=1&id=20866

http://hbshxs.cn/comment/html/index.php?page=1&id=20866

Calculating A Bonus Or Commission In Excel | Experiments in Finance

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### john lewis Ga congressman

john lewis Ga congressman

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### white bedroom furniture for teenage girls

white bedroom furniture for teenage girls

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### online shopping games for teenagers

online shopping games for teenagers

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### wiki.Lcs.com.vn

wiki.Lcs.com.vn

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### namnn.ru

namnn.ru

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### hookah online shop uk

hookah online shop uk

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### cheap furniture outlets in virginia

cheap furniture outlets in virginia

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### how to make garden furniture from logs

how to make garden furniture from logs

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### argosdiscountcodeexpert.wordpress.com

argosdiscountcodeexpert.wordpress.com

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### تمر هندي

This has been such great help to me, thanks.

### oppnarticles.com

oppnarticles.com

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### http://sns.ysten.com/space.php?uid=1240&do=blog&id=2286

http://sns.ysten.com/space.php?uid=1240&do=blog&id=2286

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### 1stwedding.cn

1stwedding.cn

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### Mypsychscene.com

Mypsychscene.com

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### ترخیص کالا

ترخیص کالا

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### Top 5 Free Online Courses With Certificates In 2019

Top 5 Free Online Courses With Certificates In 2019

Calculating A Bonus Or Commission In Excel | Experiments in Finance

### galvanized metal roofing

galvanized metal roofing

Experiments in Finance