Cumulative Tier Commission Formula on excel

[juliojohk]

Good Evening,
Was hoping someone could please help me.

I am trying to create a formula to calculate the commission as below

<80,000 = 0%
80,001-130,000 = 30%
130,001 - 295,000 = 35%
295,001 - 495,000 - 40%
> 495,001 = 50%

so based on person making 500,000 total for the quarter, his bonus will be per below
14,999.7+57,749.65+79,999.6 + 2,499.5
total = 155,248.45

can you please help me on what formula to use to get this calculation?

 

[DRSteele]

Welcome to the forum.

I have an answer for you.

 

[DRSteele]

I think this ought to do it.

	A	B	C
1	Quarterly	500000
2
3	Hurdle	Rate	Bonus
4	-	0%	-
5	80,001	30%	14,999.70
6	130,001	35%	57,749.65
7	295,001	40%	79,999.60
8	495,001	50%	2,499.50
9		Total Bonus:	155,248.45

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

Sheet35​

Worksheet Formulas Cell Formula C4 =IF($B$1 < A4,0,IF($B$1 > A5,A5-A4-1,$B$1-A4)*B4) C5 =IF($B$1 < A5,0,IF($B$1> A6,A6-A5-1,$B$1-A5)*B5) C6 =IF($B$1 < A6,0,IF($B$1> A7,A7-A6-1,$B$1-A6)*B6) C7 =IF($B$1 < A7,0,IF($B$1> A8,A8-A7-1,$B$1-A7)*B7) C8 =IF($B$1 < A8,0,($B$1-A8)*B8) C9 =SUM(C4:C8) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Dave Patton]

Excel 2010
	B	C	D	E
1	500,000.00	155,250.00
2
3		80,000.01	30%	30%
4		130,000.01	35%	5%
5		295,000.01	40%	5%
6		495,000.01	50%	10%
2c

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(B1>rB),B1-rB,rDiff)
E3	=D3-G2

Named Ranges
Name	Refers To	Cells
rB	='2c'!$C$3:$C$6
rDiff	='2c'!$E$3:$E$6

Please review the definition of the Brackets Tiers and edit the range if necessary.

 

[Dave Patton]

=(b1>80000)*(b1-80000)*e3+(b1>130000)*(b1-130000)*e4+(b1>295000)*(b1-295000)*e5+(b1>495000)*(b1-495000)*e6

 

[juliojohk]

=(b1>80000)*(b1-80000)*e3+(b1>130000)*(b1-130000)*e4+(b1>295000)*(b1-295000)*e5+(b1>495000)*(b1-495000)*e6

Thankyou!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[juliojohk]

Thank you so much for your help!

 

[pike]

Hi,
I calculate $155,250.00 with

=SUMPRODUCT((A1>{80000,130000,295000,495000})*(A1-{80000,130000,295000,495000})*{0.3,0.05,0.05,0.1})