Tiered bonus structure

[bsutherland]

Trying to setup a spreadsheet to do this but here's the grid below. Bonuses are ONLY given on the amount between the threshold.

For instance, if you do 500K of commission and fee's, you get a bonus on tier 1 on 99,999 of production, then you also get a bonus on the 299,999 of production.

You get 999.99 on tier 1 + 5,999.98 on tier 2 comming to a sum of 6999.97.


A bonus of 1% of Gross Commissions and Fees between $100,000 and $199,999.*
A bonus of 2% of Gross Commissions and Fees between $200,000 and $499,999.*
A bonus of 3% of Gross Commissions and Fees between $500,000 and $749,999.*
A bonus of 4% of Gross Commissions and Fees between $750,000 and $999,999.*
A bonus of 5% of Gross Commissions and Fees between $1,000,000 and $1,999,999.*
A bonus of 6% of Gross Commissions and Fees between $2,000,000 and $2,999,999.*
A bonus of 7% of Gross Commissions and Fees between $3,000,000 and $3,999,999.*
A bonus of 8% of Gross Commissions and Fees in excess of $4,000,000.*

If someone could build this out or help me with the formula's that would be great.

 

[MARK858]

See the thread in the link below from earlier today...

https://www.mrexcel.com/forum/excel...ier-based-commission-calculating-formula.html

 

[AhoyNC]

This link may also help.
http://www.mcgimpsey.com/excel/variablerate.html

 

[Crystalyzer]

This was a real puzzle. The requirements were a little strange in that the lower and upper limits of the tiers were NOT inclusive which is assumed by the links provided by others. Below is the worksheet I put together and the formulas are listed below that. You can see in your $500,000 example that the sum of the cells G3 and G4 total your example bonus. Cell F11 would accept the input of the total commissions and fees and is a named range "Comm" in the formulas.

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Tiers[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tier Range[/TD]
[TD="align: center"]Pct[/TD]
[TD="align: center"]Comm & Fees[/TD]
[TD="align: center"]Bonus[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"] 100,000.00 [/TD]
[TD="align: center"]0%[/TD]
[TD="align: right"] $ 100,000.00 [/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"] 100,000 [/TD]
[TD="align: right"] 199,999 [/TD]
[TD="align: right"] 100,000.00 [/TD]
[TD="align: center"]1%[/TD]
[TD="align: right"] 99,999.00 [/TD]
[TD="align: right"] 999.99 [/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"] 200,000 [/TD]
[TD="align: right"] 499,999 [/TD]
[TD="align: right"] 300,000.00 [/TD]
[TD="align: center"]2%[/TD]
[TD="align: right"] 299,999.00 [/TD]
[TD="align: right"] 5,999.98 [/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"] 500,000 [/TD]
[TD="align: right"] 749,999 [/TD]
[TD="align: right"] 250,000.00 [/TD]
[TD="align: center"]3%[/TD]
[TD="align: right"] 249,999.00 [/TD]
[TD="align: right"] 7,499.97 [/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"] 750,000 [/TD]
[TD="align: right"] 1,000,000 [/TD]
[TD="align: right"] 250,000.00 [/TD]
[TD="align: center"]4%[/TD]
[TD="align: right"] 250,000.00 [/TD]
[TD="align: right"] 10,000.00 [/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"] 1,000,001 [/TD]
[TD="align: right"] 2,000,000 [/TD]
[TD="align: right"] 1,000,000.00 [/TD]
[TD="align: center"]5%[/TD]
[TD="align: right"] 999,999.00 [/TD]
[TD="align: right"] 49,999.95 [/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"] 2,000,001 [/TD]
[TD="align: right"] 3,000,000 [/TD]
[TD="align: right"] 1,000,000.00 [/TD]
[TD="align: center"]6%[/TD]
[TD="align: right"] 999,999.00 [/TD]
[TD="align: right"] 59,999.94 [/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"] 3,000,001 [/TD]
[TD="align: right"] 4,000,000 [/TD]
[TD="align: right"] 1,000,000.00 [/TD]
[TD="align: center"]7%[/TD]
[TD="align: right"] 999,999.00 [/TD]
[TD="align: right"] 69,999.93 [/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"] 4,000,000 [/TD]
[TD="align: right"] 100,000,000 [/TD]
[TD="align: right"][/TD]
[TD="align: center"]8%[/TD]
[TD="align: right"] 6,000,000.00 [/TD]
[TD="align: right"] 480,000.00 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: <a href=, align: right"]#A9D08E ;;"> $10,000,000.00 [/TD]
[TD="bgcolor: <a href=, align: right"]#A9D08E ;;"> $ 684,499.76 [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=F2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(Comm>=C2,C2,Comm)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=E2*F2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IF(Comm>C3,C3-B3,IF((Comm-SUM($D$2:D2))<0,0,(Comm-SUM($D$2:D2))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=E3*F3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=IF(Comm>C4,C4-B4,IF((Comm-SUM($D$2:D3))<0,0,(Comm-SUM($D$2:D3))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=E4*F4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=IF(Comm>C5,C5-B5,IF((Comm-SUM($D$2:D4))<0,0,(Comm-SUM($D$2:D4))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=E5*F5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=IF(Comm>C6,C6-B6,IF((Comm-SUM($D$2:D5))<0,0,(Comm-SUM($D$2:D5))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=E6*F6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=IF(Comm>C7,C7-B7,IF((Comm-SUM($D$2:D6))<0,0,(Comm-SUM($D$2:D6))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=E7*F7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=IF(Comm>C8,C8-B8,IF((Comm-SUM($D$2:D7))<0,0,(Comm-SUM($D$2:D7))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]=E8*F8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=IF(Comm>C9,C9-B9,IF((Comm-SUM($D$2:D8))<0,0,(Comm-SUM($D$2:D8))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G9[/TH]
[TD="align: left"]=E9*F9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=IF(Comm>C10,C10-B10,IF((Comm-SUM($D$2:D9))<0,0,(Comm-SUM($D$2:D9))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G10[/TH]
[TD="align: left"]=E10*F10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G11[/TH]
[TD="align: left"]=SUM(G2:G10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Comm[/TH]
[TD="align: left"]=Sheet1!$F$11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Care to explain which parts are not inclusive? or exactly what you mean by inclusive?

 

[Crystalyzer]

You get 999.99 on tier 1 + 5,999.98 on tier 2 comming to a sum of 6999.97.


A bonus of 1% of Gross Commissions and Fees between $100,000 and $199,999.*
A bonus of 2% of Gross Commissions and Fees between $200,000 and $499,999.*
A bonus of 3% of Gross Commissions and Fees between $500,000 and $749,999.*

1. The first $100,000 of Commissions and Fees pay 0% bonus
2. Your example of $500,000 resulting in a bonus of $6,999.97 is calculated as (100,000 * 0.00) + (99,999 * .01) + (299,999 * .02) = 6,999.97
   1. The total Commissions and Fees in that calculation is $100,000 + 99,999 + 299,999 = 499,998 and not 500,000
   2. As you progress down the tiers you lose $1 for each tier.

 

[Dave Patton]

Please check the actual definition of the brackets and confirm the expected results with say 10,000,000.

Excel 2010
	A	B	C
1	Brackets	Rate	Rate Diff
2	0	0%	0%
3	100,000	1%	1%
4	200,000	2%	1%
5	500,000	3%	1%
6	750,000	4%	1%
7	1,000,000	5%	1%
8	2,000,000	6%	1%
9	3,000,000	7%	1%
10	4,000,000	8%	1%
11
12	10,000,000	684,500
3bb

Cell Formulas
Range	Formula
C2	=B2-N(B1)
B12	=SUMPRODUCT(--(A12>A2:A10),A12-A2:A10,C2:C10)

 

[MARK858]

[*]As you progress down the tiers you lose $1 for each tier.

I see what you mean, someone who doesn't know how to set up a commission, wait for the salesmen to put in a claim at a later date when they realise

 

[bsutherland]

Solved - thank you guys. [TABLE="width: 782"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]