Commissions With Tiers Formula

[shmeetbag2]

Hello,

I need a formula that will calculate a commission payout based on a commission table. As an example, if a rep is in a specific quota category (15,000), then I need a formula that will return a tiered payout based on the quota category and how much over their quota they reached. I can get this information with the formula below, but the challenge is the higher % rate needs to be paid only on the overage amount, not the entire sales amount. Hopefully the example below will better show what I am looking for.

[TABLE="width: 78"]
<tbody>[TR]
[TD]Commission Table:

[TABLE="width: 540"]
<tbody>[TR]
[TD]Quota[/TD]
[TD]<=100%[/TD]
[TD="align: right"]110%[/TD]
[TD="align: right"]125%[/TD]
[TD="align: right"]150%[/TD]
[TD="align: right"]200%[/TD]
[TD]200% +[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]<=10000[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]7.0%[/TD]
[TD="align: right"]8.0%[/TD]
[TD="align: right"]9.0%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]11.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$ 11,250[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]7.0%[/TD]
[TD="align: right"]8.0%[/TD]
[TD="align: right"]9.0%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]11.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$ 15,000[/TD]
[TD="align: right"]5.3%[/TD]
[TD="align: right"]7.3%[/TD]
[TD="align: right"]8.3%[/TD]
[TD="align: right"]9.3%[/TD]
[TD="align: right"]10.3%[/TD]
[TD="align: right"]11.3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$ 20,000[/TD]
[TD="align: right"]5.8%[/TD]
[TD="align: right"]7.8%[/TD]
[TD="align: right"]8.8%[/TD]
[TD="align: right"]9.8%[/TD]
[TD="align: right"]10.8%[/TD]
[TD="align: right"]11.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$ 25,000[/TD]
[TD="align: right"]7.5%[/TD]
[TD="align: right"]9.0%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]11.0%[/TD]
[TD="align: right"]12.0%[/TD]
[TD="align: right"]13.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Quota[/TD]
[TD]15,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]17,500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attainment[/TD]
[TD="align: right"]117%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Payout[/TD]
[TD]973[/TD]
[TD="colspan: 4"]795 (15,000*5.3%) + 183 (2,500*7.3%)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 78"]
<tbody>[TR]
[TD]What I used to get an overall number, but this won't separate the rate and amounts as needed in the example.[/TD]
[/TR]
[TR]
[TD]INDEX($A$1:$H$7,MATCH(A10,$A$1:$A$7,1),MATCH(A11,$A$1:$H$1,1))[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Amount	Goal	Commission		Brackets	Rate	Rate_Diff
2	17,500.00	15,000.00	987.50		0.00	5.30%	5.30%
3					15,000.00	7.30%	2.00%
4					16,500.00	8.30%	1.00%
5					18,750.00	9.30%	1.00%
6					22,500.00	10.30%	1.00%
7					30,000.00	11.30%	1.00%
8
6a

Cell Formulas
Range	Formula
C2	=CHOOSE(MATCH(B2,{0;11250;15000;20000;25000}),1,2, SUMPRODUCT(--(A2>Table2[Brackets]),A2-Table2[Brackets],Table2[Rate_Diff]),4,5)
G2	=F2-N(F1)

You will probably need a separate Rate Table for each Goal.

My example just included the formula and Table for the 15,000 Goal.

It is not necessary to use a Table or Name the Range but it may make the formula easier to read.

After you prepare the other rate Tables, edit the formula and replace the numbers 1,2,4,5 with the respective SumProduct formulas.

 

[shmeetbag2]

Thanks for the solution. Your commission number of 987.50 is slightly different from my example of 973. Was my logic incorrect in getting to that number and if not, where is the discrepancy coming from?

This is a great solution for a single goal, but is there a way to create without doing a rate table for each?

 

[Dave Patton]

Excel 2010
	J	K	L
3	15,000.00	0.053	795.00
4	1,500.00	0.073	109.50
5	1,000.00	0.083	83.00
6	17,500.00		987.50
6a

Cell Formulas
Range	Formula
J4	=16500-15000
J6	=SUM(J3:J5)
L3	=J3*K3
L4	=J4*K4
L5	=J5*K5
L6	=SUM(L3:L5)

Please check the brackets.

This is a great solution for a single goal, but is there a way to create without doing a rate table for each?

I do not think so. The brackets would be different for each goal.

 

[Dave Patton]

Excel 2010
	A	B	C
1	Amount	Goal	Commission
2	17,500.00	15,000.00	987.50
3	40,000.00	15,000.00	3,342.50
4	17,500.00	10,000.00	1,165.00
5	17,500.00	11,250.00	1,091.88
6
6a

Cell Formulas
Range	Formula
C2	=CHOOSE(MATCH(B2,{0;11250;15000;20000;25000}),SUMPRODUCT(--(A2>{0;10000;11000;12500;15000;20000}),A2-{0;10000;11000;12500;15000;20000},{0.05;0.02;0.01;0.01;0.01;0.01}),SUMPRODUCT(--(A2>{0;11250;12375;14062.5;16875;22500}),A2-{0;11250;12375;14062.5;16875;22500},{0.05;0.02;0.01;0.01;0.01;0.01}), SUMPRODUCT(--(A2>{0;15000;16500;18750;22500;30000}),A2-{0;15000;16500;18750;22500;30000},{0.053;0.02;0.01;0.01;0.01;0.01}),4,5)

There are different brackets and rates for the specified Quotas
You can use range addresses, range names, Tables, or hard code the numbers for the SumProduct formulas.

N.B. Review and edit the above as required.
Add the Sumproduct parameters for the 4th and 5th Quotas.