Formula for Tiered Commission Structure--Need help quick

[bsimonburton]

I am trying to figure out a formula to help my reps caculate how much money they can make based on their commission plan which is in tranches/tiers. All quotas are different so I have the quota in B4 and the amount they have currently sold to date in B3. The comp structure is as follows:

Tranche 1 0-20% of quota is paid at 4.89%
Tranche 2 21%-40% of quota paid at 7.33%
Tranche 3 41%-60% of quota paid at 8.80%
Tranche 4 61%-80% of quota paid at 11.73%
Tranche 5 81%-100% of quota paid at 16.13%
Above 100% of quota paid at 20.16%

The other kicker is that we have multi-year deals that are paid at different percentages within these Tranches also (i.e.: Tranche 1 multi- year paid at 2.34%, Tranche 2 at 3.51%, Tranche 3 at 4.21%, Tranche 4 at 5.61%, Tranche 5 at 7.72%, and above 100% at 9.65%. I will almost need them to manually break out the 2 total sales numbers (one for total yearly sold and one for the total of the out years--but that I can figure out), it is the formulas on the Tranches/Tiers that I need help with.

I have started with and =if B3>(B4*.2) but from here I'm not sure how to say THEN B4 needs to be multiplied by .0489. I'm also not sure if this is on the right track so any help would be greatly appreciated.

THANKS SO MUCH.

 

[oldbrewer]

you will get to a very complicated if statement

if(b3=0,0,if(b3<B4*.2,b3*.0489,if(b3<b4*.4,b4*.2*.0489+(b4-(b4.2))*.0733

and that is less than half way there - will think about a different approach for you

 

[bsimonburton]

I am trying to figure out a formula to help my reps caculate how much money they can make based on their commission plan which is in tranches/tiers. All quotas are different so I have the quota in B4 and the amount they have currently sold to date in B3. The comp structure is as follows:

Tranche 1 0-20% of quota is paid at 4.89%
Tranche 2 21%-40% of quota paid at 7.33%
Tranche 3 41%-60% of quota paid at 8.80%
Tranche 4 61%-80% of quota paid at 11.73%
Tranche 5 81%-100% of quota paid at 16.13%
Above 100% of quota paid at 20.16%

The other kicker is that we have multi-year deals that are paid at different percentages within these Tranches also (i.e.: Tranche 1 multi- year paid at 2.34%, Tranche 2 at 3.51%, Tranche 3 at 4.21%, Tranche 4 at 5.61%, Tranche 5 at 7.72%, and above 100% at 9.65%. I will almost need them to manually break out the 2 total sales numbers (one for total yearly sold and one for the total of the out years--but that I can figure out), it is the formulas on the Tranches/Tiers that I need help with.

I have started with and =if B3>(B4*.2) but from here I'm not sure how to say THEN B4 needs to be multiplied by .0489. I'm also not sure if this is on the right track so any help would be greatly appreciated.

THANKS SO MUCH.

I tried the following formula
=SUMPRODUCT(--(B3>{0;(B4*.2);(B4*.4);(B4*.6);(B4*.8);(B4*1)}), (B3-{0;(B4*.2);(B4*.4);(B4*.6);(B4*.8);(B4*1)},{.0489;.0244;.0147;.0293;.044;.0403})

but it says I have an error any ideas what my error is--as I think I'm on the right track here. I am trying to take my threshold values and my DiffRates and calculate based on that. I am not so worried about the muti-year at this point, just the main formula.

Any thoughts?

 

[AlphaFrog]

Another method.

=(B3*4.89%) + MAX(B3-B4*20%,0)*(7.33%-4.89%)
+ MAX(B3-B4*40%,0)*(8.8%-7.33%) + MAX(B3-B4*60%,0)*(11.73%-8.8%)
+ MAX(B3-B4*80%,0)*(16.13%-11.73%) + MAX(B3-B4,0)*(20.16%-16.13%)

 

[oldbrewer]

sales	970
quota	1200
commission	80.213
%_of_quota	rate
0-20	0.0489		0	240	0.0489	11.736	11.736
21-40	0.0733		241	480	0.0733	17.592	17.592
41-60	0.088		481	720	0.088	21.12	21.12
61-80	0.1173		721	960	0.1173	28.152	28.152
81-100	0.1613		961	1200	0.1613	38.712	1.613
101 plus	0.2016		1201	99999	0.2016		0
							80.213

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

this table generates automatically from the sales figure, quota figure and tiered rates
Pm me with Email if you want the spreadsheet sending - it handles any figures so if you change your % rates etc it handles it.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G
1					Tranche	Rate	Rate_Diff
2					0	4.89%	4.89%
3	Sold	120,000.00		20%	20,000.00	7.33%	2.44%
4	Quota	100,000.00		40%	40,000.00	8.80%	1.47%
5	Commission	$13,808.00		60%	60,000.00	11.73%	2.93%
6				80%	80,000.00	16.13%	4.40%
7				100%	100,000.00	20.16%	4.03%
8
1a

Excel 2003

Cell Formulas
Range	Formula
G2	=F2-N(F1)
E3	=D3*$B$4
B5	=SUMPRODUCT(--(B3>rB),B3-rB,rRate)

Excel Workbook
Name	Refers To
rB	='1a'!$E$2:$E$7
rRate	='1a'!$G$2:$G$7
Workbook Defined Names