Help with Graduated Commission Formula

[qualker12]

After more Googling and experimenting then I care to discuss, I have run into a brick wall attempting to create a formula that can help me through my objective. I found a few threads with similar questions, but no answers, so am hopeful someone can provide direction. I am comparing several commission structures, one of which is outlined below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Gross Commission ($)[/TD]
[TD]Net Commission (%)[/TD]
[/TR]
[TR]
[TD]0-50,000[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]50,001 - 100,000[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]100,001 - 300,000[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]300,001+[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]

This is a graduated, or step up fee structure. For example, if the gross commission is $40,000, the net would be $4,000. Pretty simple. But if the fee is $400,000, then the first $50,000 earns only 10%, the next $40,000 earns 20%, the next $200,000 earns 30%, and the last $100,000 would earn 40%, which totals $5,000+$8,000+$60,000+$40,000=$113,000.

I'd like to enter the gross commission into a cell, and have the net commission calculated. We will be evaluating several structures with similar design, so theoretically I want to have probably 10 different net commission scenarios computed by entering the gross commission. If someone can provide direction on the above example, I am sure I can figure out the rest.

Thank you in advance.

 

[thisoldman]

I think this may help:

Excel Workbook
	A	B	C	D	E	F
1	Threshold	Rate	Differential		My Sales	My Commission
2	0	10%	10%		110,000	18,000.00
3	50,000	20%	10%
4	100,000	30%	10%
5	300,000	40%	10%
Sheet

The thresholds and the commission rates can be changed and the computed commission will update.

 

[witsonjoyet]

Enter your gross commission in A1 cell

=+IF(AND(A1>0,$A$1<=50000),$A$1*10%,IF(AND(A1>50000,A1<=100000),(A1-50000)*10%+(A1-50000)*20%,IF(AND(A1>100000,A1<=300000),((A1-100000)*30%)+((50000)*10%)+(50000*20%),IF(A1>300000,((A1-300000)*40%)+((50000*10%)+(50000*20%)+(200000*30%)),""))))

Regards
Witson

 

[Unpopular]

Hi, similar to this, can someone help me with this:

how can I set a formula where the minimum commission is $25 for sales of $0 but is gradually increasing based in the amount paid, in which the maximum amount of sales is $500 and the maximum amount of commission of 15% of the maximum amount of sales ($75).

Sales Commission
$0 - $25 (minimum)
$500 - 15% of sales ($75)

thank you very much in advance guys.