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?

 

[snakel87]

Please show what you have tried.

Suggestions
- if formula
- logic formula
- above suggestion

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

If the person sells 500 units and $200 per they would be paid $100,000

All I have is a simple lookup that tells them the rate at which they are being paid out

=VLOOKUP(C10,$C$4:$D$6,2,TRUE)

Sales Tiers	Tier Minimum	Payout Rate
0-30	0	$0
31-59	31	$125
$60	60	$200

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I'm trying to figure out a way that as they go along week by week and add in their numbers that it would figure out commissions based on the tiers and spit out a number... less than 30 they get no commission 31 plus $125 each and so on.

 

[Eric W]

You could use a table similar to the one above:

	B	C	D	E	F	G	H	I	J	K
1	Tier	Rate	Differential			Week	Sales	Sales to date	Bonus	Bonus to date
2	0	0	0			1	5	5	0	0
3	31	125	125			2	10	15	0	0
4	61	200	75			3	13	28	0	0
5						4	7	35	500	500
6						5	10	45	1250	1750
7						6	19	64	2600	4350
8						7	21	85	4200	8550
9						8	1	86	200	8750
10						9	414	500	82800	91550
11						10
12						11
13						12
14						13

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula I2 =IF(H2<>"",SUM($H$2:$H2),"") J2 =IFERROR(K2-K1,K2) K2 =IF(H2<>"",SUMPRODUCT(--(SUM($H$2:$H2)>$B$2:$B$4),(SUM($H$2:$H2)-$B$2:$B$4),$D$2:$D$4),"") <tbody> </tbody>

<tbody>
</tbody>

Put the tiers and rates in B1:D4. Then you can create the table in G1:K14. Put the formulas in I2:K2, then drag down to row 14. Now as they enter their sales in column H, the totals in I:K will update automatically.

Note that this calculates a bonus of $91,550 for 500 sales. This is because the first 30 sales earned $0, and the next 30 earned $125 each. If you really do want a bonus of $100,000, then I can adjust the formulas.

 

[Dave Patton]

Your information is not clear.
What is the amount for 72 units

What is the amount for 500 units.

Excel 2010
	A	B	C	D
1		Month 1	Month 2	Month 3
2	Units	2	70	428
3	Cumulative	2	72	500
4	Payment total	0	6,150	91,750
5		0	6,150	85,600
6
7		0	6,150	91,750
8	Prior	0	6,150	85,600
9
7a

Cell Formulas
Range	Formula
B3	=SUM($B$2:B2)
B4	=(B3>30)*B3*125+(B3>60)*(B3-30)*(200-125)
B5	=B4-SUM($A$4:A4)
B7	=IF(B3>30,B3*125,0)+(IF(B3>60,(B3-60)*(200-125),0))
B8	=B7-SUM($A$7:A7)
C3	=SUM($B$2:C2)
C4	=(C3>30)*((C3-30)*125)+(C3>60)*(C3-60)*(200-125)
C5	=C4-SUM($A$4:B4)
C7	=IF(C3>30,(C3-30)*125,0)+(IF(C3>60,(C3-60)*(200-125),0))
C8	=C7-SUM($A$7:B7)
D3	=SUM($B$2:D2)
D4	=(D3>30)*((D3-30)*125)+(D3>60)*(D3-60)*(200-125)
D5	=D4-SUM($A$4:C4)
D7	=IF(D3>30,(D3-30)*125,0)+(IF(D3>60,(D3-60)*(200-125),0))
D8	=D7-SUM($A$7:C7)

Excel 2010
	A	B	C	D	E	F
1	Tier	Rate	Diff		Cumulative	Bonus
2	0	0	0		30	0.00
3	30	125	125		72	6,150.00
4	60	200	75		500	91,750.00
5
7aa

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(E2>$A$2:$A$4),(E2-$A$2:$A$4),$C$2:$C$4)
F3	=SUMPRODUCT(--(E3>$A$2:$A$4),(E3-$A$2:$A$4),$C$2:$C$4)
F4	=SUMPRODUCT(--(E4>$A$2:$A$4),(E4-$A$2:$A$4),$C$2:$C$4)

 

[Dave Patton]

=500*200-(60*200)+30*125

 

[snakel87]

72 units would equal $10,500

500 units would equal $95,500 because the first 60 are at the $125 rate.

Your information is not clear.
What is the amount for 72 units

What is the amount for 500 units

Excel 2010

	A	B	C	D
1		Month 1	Month 2	Month 3
2	Units	2	70	428
3	Cumulative	2	72	500
4	Payment total	0	6,150	91,750
5		0	6,150	85,600
6
7		0	6,150	91,750
8	Prior	0	6,150	85,600
9

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

7a​

Worksheet Formulas Cell Formula B3 =SUM($B$2:B2) C3 =SUM($B$2:C2) D3 =SUM($B$2:D2) B4 =(B3>30)*B3*125+(B3>60)*(B3-30)*(200-125) C4 =(C3>30)*((C3-30)*125)+(C3>60)*(C3-60)*(200-125) D4 =(D3>30)*((D3-30)*125)+(D3>60)*(D3-60)*(200-125) B5 =B4-SUM($A$4:A4) C5 =C4-SUM($A$4:B4) D5 =D4-SUM($A$4:C4) B7 =IF(B3>30,B3*125,0)+(IF(B3>60,(B3-60)*(200-125),0)) C7 =IF(C3>30,(C3-30)*125,0)+(IF(C3>60,(C3-60)*(200-125),0)) D7 =IF(D3>30,(D3-30)*125,0)+(IF(D3>60,(D3-60)*(200-125),0)) B8 =B7-SUM($A$7:A7) C8 =C7-SUM($A$7:B7) D8 =D7-SUM($A$7:C7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Excel 2010

	A	B	C	D	E	F
1	Tier	Rate	Diff		Cumulative	Bonus
2	0	0	0		30	0.00
3	30	125	125		72	6,150.00
4	60	200	75		500	91,750.00
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

7aa​

Worksheet Formulas Cell Formula F2 =SUMPRODUCT(--(E2>$A$2:$A$4),(E2-$A$2:$A$4),$C$2:$C$4) F3 =SUMPRODUCT(--(E3>$A$2:$A$4),(E3-$A$2:$A$4),$C$2:$C$4) F4 =SUMPRODUCT(--(E4>$A$2:$A$4),(E4-$A$2:$A$4),$C$2:$C$4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Dave Patton]

any of the three solutions can be edited to provide the correct answer.

You originally stated
"
...less than 30 they get no commission 31 plus $125 each and so on.



You have have a new post. Someone can provide an answer(s) as the question evolves!