Tiered Bonus & Commission in Excel

[jenni554]

Hi,
I need to calculate the bonus that will be applied in addition to the normal 3% commission. The tiers are:

35000-50000 3.5% (bonus is only on amount within 35-50k)
50000-75000 5.0%
75000-100000 10.0%
100000-125000 15.0%

I need to be able to say for example: 65000 commission would be a bonus of 1275.00. Total payout: 66275. What would be the best way to set up a formula for this?

 

[SpillerBD]

Could you run that calculation again and show the work.
I can't get to 1275.00 from you description.

 

[jenni554]

Could you run that calculation again and show the work.
I can't get to 1275.00 from you description.

50000-35000=15000
15000*3.5%= 525

65000-50000=10000
10000*5%=500

Total commission: $1025
Total Payout: $66025

My apologies! I was not correct in previous post! Thank you for pointing out.

 

[Eric W]

50000-35000=15000
15000*3.5%= 525

65000-50000=10000
10000*5%=500

Total commission: $1025
Total Payout: $66025

My apologies! I was not correct in previous post! Thank you for pointing out.

I think you had it right the first time. (See the line in red.) If so, you can try this formula:

	A	B	C	D	E
Amount	Bonus	Tier	Rate	Differential Rate

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]1275[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]3.50%[/TD]
[TD="align: right"]3.50%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]1.50%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]5%[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]5%[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2>$C$2:$C$6),(A2-$C$2:$C$6),$E$2:$E$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=D3-D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Build a table like C1:E6. Put your rates in D, and put the formulas in E to create the differentials. Then the formula in B2 will give you your bonus.

Hope this helps!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	65000	1275		Brackets	Rate	rDiff
2				0	0.0%	0.00%
3				35000	3.5%	3.50%
4				50000	5.0%	1.50%
5				75000	10.0%	5.00%
6				100000	15.0%	5.00%
7
8	0	-	35000	0
9	35000	-	50000	525
10	50000	-	65000	750
11				1275
5a

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(A1>Brackets),A1-Brackets,Diff)
F3	=E3-E2
D9	=(C9-A9)*E3
D10	=(C10-A10)*E4
D11	=D9+D10

Named Ranges
Name	Refers To	Cells
Brackets	='5a'!$D$2:$D$6
Diff	='5a'!$F$2:$F$6

 

[jenni554]

I think you had it right the first time. (See the line in red.) If so, you can try this formula:

	A	B	C	D	E
Amount	Bonus	Tier	Rate	Differential Rate

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]1275[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]3.50%[/TD]
[TD="align: right"]3.50%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]1.50%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]5%[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]5%[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2>$C$2:$C$6),(A2-$C$2:$C$6),$E$2:$E$6)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=D2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=D3-D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Build a table like C1:E6. Put your rates in D, and put the formulas in E to create the differentials. Then the formula in B2 will give you your bonus.

Hope this helps!

That is perfect! Thank you so much!

 

[Eric W]

Glad we could help!

 

[snakel87]

I have a sales team that gets paid based on units sold not dollars sold and then a percentage for bonus. I'm trying to figure out a formula that would allow them to track sales and see potential commissions.
For basic math sake they need to sell 60 units a quarter.
They only get paid if they hit the 50% threshold (30 units) at $125 until 60 units. Everything after 60 units is $200 each with no cap.
I'm trying to find a formula that as they enter their weekly totals it calculates at the correct rate... is this even possible?

Thanks,

David

 

[Dave Patton]

Please show what you have tried.

Suggestions
- if formula
- logic formula
- above suggestion

If the person sells 500 units what will he be paid?

 

[Joe4]

I have a sales team that gets paid based on units sold not dollars sold and then a percentage for bonus. I'm trying to figure out a formula that would allow them to track sales and see potential commissions.
For basic math sake they need to sell 60 units a quarter.
They only get paid if they hit the 50% threshold (30 units) at $125 until 60 units. Everything after 60 units is $200 each with no cap.
I'm trying to find a formula that as they enter their weekly totals it calculates at the correct rate... is this even possible?

For future reference, when posting your questions, unless it is a question about an existing thread, it is best to ask your questions in a new thread (instead of posting to an existing one).
Posting to existing threads will not show up in the "Zero Reply Posts" listing that many of us use to look for new unanswered questions! So your thread will get less "looks" and have a less chance of being answered by posting to existing threads (as opposed to posting to your own new thread).