Tiered Commission - With A Max / Cap for each Tier

crazy_tom · Jun 28, 2022

I am trying to set up a formula to help me calculate the following Commission Schedule on Monthly Revenue.
Each month total revenue is looked up against this table to determine the rate to be paid on it.
Sales people are paid a rate based on monthly revenue in increments of 100,000.

0 - 100K is paid 10% up to 100,000, then
100K - 200K is paid 11% up to 200,000 and so on until 800,000+ when the sales person is paid 17% on all revenue - so no cap to the incremental 100,000.

In one year you can see below that a salesperson cannot make more than $108,000 in total from commissions on revenue up to 800,000.

Kind of funky so trying to figure out how to do this calculation. Thanks!

			Increment	Rate
-	$0.0	$100,000.0	$100,000.0	10%	$10,000.0
>	$100,000.0	$200,000.0	$100,000.0	11%	$11,000.0
>	$200,000.0	$300,000.0	$100,000.0	12%	$12,000.0
>	$300,000.0	$400,000.0	$100,000.0	13%	$13,000.0
>	$400,000.0	$500,000.0	$100,000.0	14%	$14,000.0
>	$500,000.0	$600,000.0	$100,000.0	15%	$15,000.0
>	$600,000.0	$700,000.0	$100,000.0	16%	$16,000.0
>	$700,000.0	$800,000.0	$100,000.0	17%	$17,000.0
					$108,000.00

etaf · Jun 28, 2022

i think you want a SUMIF () so you are summing the amounts based on the revenue

if the revenue is 300,000
then the commission is , 10,11,12,13% , + 10,000 + 11,000 +12,000 +13,000 = 46K

OR it maybe i need to change the column slightly
So instead of 10000, 20000 30000 etc we just change that to 10,001, 20,001, 30,001
then it would not pay out the 13% tier

BUT lets start to see if this is right - 3 alternatives below

Sum in column L

Book7

A

B

C

D

E

F

G

H

I

J

K

L

1

Increment

Rate

revenue

%

Rate

Sum rate

2

-

$0

$100,000.0

10%

$10,000.00

$300,000.00

13%

$13,000.00

$46,000.00

3

>

$100,000

$200,000.0

$100,000.0

11%

$11,000.00

4

>

$200,000

$300,000.0

$100,000.0

12%

$12,000.00

5

>

$300,000

$400,000.0

$100,000.0

13%

$13,000.00

6

>

$400,000

$500,000.0

$100,000.0

14%

$14,000.00

7

>

$500,000

$600,000.0

$100,000.0

15%

$15,000.00

8

>

$600,000

$700,000.0

$100,000.0

16%

$16,000.00

9

>

$700,000

$800,000.0

$100,000.0

17%

$17,000.00

10

$108,000.00

Sheet1

Cell Formulas
Range	Formula
I2	I2	=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2	J2	=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2	L2	=SUMIF(B2:B9,"<="&H2,F2:F9)
F10	F10	=SUM(F2:F9)

OR

Book7

A

B

C

D

E

F

G

H

I

J

K

L

1

Increment

Rate

revenue

%

Rate

Sum rate

2

-

$0

$100,000.0

10%

$10,000.00

$300,000.00

12%

$12,000.00

$33,000.00

3

>

$100,001

$200,000.0

$100,000.0

11%

$11,000.00

4

>

$200,001

$300,000.0

$100,000.0

12%

$12,000.00

5

>

$300,001

$400,000.0

$100,000.0

13%

$13,000.00

6

>

$400,001

$500,000.0

$100,000.0

14%

$14,000.00

7

>

$500,001

$600,000.0

$100,000.0

15%

$15,000.00

8

>

$600,001

$700,000.0

$100,000.0

16%

$16,000.00

9

>

$700,001

$800,000.0

$100,000.0

17%

$17,000.00

10

$108,000.00

Sheet1

Cell Formulas
Range	Formula
I2	I2	=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2	J2	=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2	L2	=SUMIF(B2:B9,"<="&H2,F2:F9)
F10	F10	=SUM(F2:F9)

OR Add an accumulated column and lookup that

see column G added and lookup in M

Book7

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Increment

Rate

revenue

%

Rate

Sum rate

lookup rate

2

-

$0

$100,000.0

10%

$10,000.00

$300,000.00

12%

$12,000.00

$33,000.00

3

>

$100,001

$200,000.0

$100,000.0

11%

$11,000.00

$21,000.00

4

>

$200,001

$300,000.0

$100,000.0

12%

$12,000.00

$33,000.00

5

>

$300,001

$400,000.0

$100,000.0

13%

$13,000.00

$46,000.00

6

>

$400,001

$500,000.0

$100,000.0

14%

$14,000.00

$60,000.00

7

>

$500,001

$600,000.0

$100,000.0

15%

$15,000.00

$75,000.00

8

>

$600,001

$700,000.0

$100,000.0

16%

$16,000.00

$91,000.00

9

>

$700,001

$800,000.0

$100,000.0

17%

$17,000.00

$108,000.00

10

$108,000.00

11

Sheet1

Cell Formulas
Range	Formula
I2	I2	=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2	J2	=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2	L2	=SUMIF(B2:B9,"<="&H2,F2:F9)
M2	M2	=INDEX(G2:G9,MATCH(H2,B2:B9,1))
G2:G9	G2	=SUM($F$2:F2)
F10	F10	=SUM(F2:F9)

Dave Patton · Jun 28, 2022

The formulas in G2 and H2 use named arrays. The array of Bracket data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;100000;200000;300000;400000;500000;600000;700000;800000}
- The array of Rate Differentials is named aR Refers to ={0.1;0.01;0.01;0.01;0.01;0.01;0.01;0.01;0.01}
Advantages
The formulas are easier to read.
The table is not required if the formulas use the named arrays.

Try the alternative that you prefer.

Commission2022.xlsm

A

B

C

D

E

F

G

H

1

Brackets

Rates

Revenue

Commission

With Excel 365 &Named arrays

2

800,000.00

108,000.00

3

0

10%

4

100,000.00

11%

5

200,000.00

12%

6

300,000.00

13%

7

400,000.00

14%

8

500,000.00

15%

9

600,000.00

16%

10

700,000.00

17%

11

800,000.00

18%

5a

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--(E2>B3:B11),E2-B3:B9:B11,C3:C11-C2:C10)
G2	G2	=SUM((E2>aB)(E2-aB)aR)
H2	H2	=SUMPRODUCT(--(E2>aB),E2-aB,aR)

Commission2022.xlsm

A

B

C

13

50,000.00

$5,000.00

14

425,000.00

$49,500.00

15

1,200,000.00

$180,000.00

16

5a

Cell Formulas
Range	Formula
C13:C15	C13	=SUM((B13>aB)(B13-aB)aR)

Dave Patton · Jun 29, 2022

Edit The previous post included a bracket above 800,000.

Commission2022.xlsm

A

B

C

D

E

1

Revenue

Commission

With Excel 365 & Named arrays

2

800,000.00

$108,000.00

3

4

Brackets

Rates

--- Arithmetic ---

5

By Bracket

Cumulative

6

10%

10,000.00

7

100,000.00

11%

11,000.00

21,000.00

8

200,000.00

12%

12,000.00

33,000.00

9

300,000.00

13%

13,000.00

46,000.00

10

400,000.00

14%

14,000.00

60,000.00

11

500,000.00

15%

15,000.00

75,000.00

12

600,000.00

16%

16,000.00

91,000.00

13

700,000.00

17%

17,000.00

108,000.00

14

1.00E+308

15

16

17

50,000.00

$5,000.00

18

125,000.00

$12,750.00

19

750,000.00

$99,500.00

20

1,000,000.00

$142,000.00

21

1,200,000.00

$176,000.00

5a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B2>B6:B13),B2-B6:B13,C6:C13-C5:C12)
D2	D2	=SUM((B2>aB)(B2-aB)aR)
E2	E2	=SUMPRODUCT(--(B2>aB),B2-aB,aR)
D6:D13	D6	=MAX(0,MIN($B$2,B7)-B6)*C6
E6:E13	E6	=MAX(0,MIN($B$2,B7)-B6)*C6+N(E5)
B14	B14	=BigNum
C17:C21	C17	=SUM((B17>aB)(B17-aB)aR)

crazy_tom · Jul 10, 2022

etaf said:
i think you want a SUMIF () so you are summing the amounts based on the revenue

if the revenue is 300,000
then the commission is , 10,11,12,13% , + 10,000 + 11,000 +12,000 +13,000 = 46K

OR it maybe i need to change the column slightly
So instead of 10000, 20000 30000 etc we just change that to 10,001, 20,001, 30,001
then it would not pay out the 13% tier

BUT lets start to see if this is right - 3 alternatives below

Sum in column L

Book7
A B C D E F G H I J K L
1 Increment Rate revenue % Rate Sum rate
2 - $0 $100,000.0 $100,000.0 10% $10,000.00 $300,000.00 13% $13,000.00 $46,000.00
3 > $100,000 $200,000.0 $100,000.0 11% $11,000.00
4 > $200,000 $300,000.0 $100,000.0 12% $12,000.00
5 > $300,000 $400,000.0 $100,000.0 13% $13,000.00
6 > $400,000 $500,000.0 $100,000.0 14% $14,000.00
7 > $500,000 $600,000.0 $100,000.0 15% $15,000.00
8 > $600,000 $700,000.0 $100,000.0 16% $16,000.00
9 > $700,000 $800,000.0 $100,000.0 17% $17,000.00
10 $108,000.00
Sheet1
Cell Formulas
Range Formula
I2 I2 =INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2 J2 =INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2 L2 =SUMIF(B2:B9,"<="&H2,F2:F9)
F10 F10 =SUM(F2:F9)

OR

Book7
A B C D E F G H I J K L
1 Increment Rate revenue % Rate Sum rate
2 - $0 $100,000.0 $100,000.0 10% $10,000.00 $300,000.00 12% $12,000.00 $33,000.00
3 > $100,001 $200,000.0 $100,000.0 11% $11,000.00
4 > $200,001 $300,000.0 $100,000.0 12% $12,000.00
5 > $300,001 $400,000.0 $100,000.0 13% $13,000.00
6 > $400,001 $500,000.0 $100,000.0 14% $14,000.00
7 > $500,001 $600,000.0 $100,000.0 15% $15,000.00
8 > $600,001 $700,000.0 $100,000.0 16% $16,000.00
9 > $700,001 $800,000.0 $100,000.0 17% $17,000.00
10 $108,000.00
Sheet1
Cell Formulas
Range Formula
I2 I2 =INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2 J2 =INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2 L2 =SUMIF(B2:B9,"<="&H2,F2:F9)
F10 F10 =SUM(F2:F9)

OR Add an accumulated column and lookup that

see column G added and lookup in M

Book7
A B C D E F G H I J K L M
1 Increment Rate revenue % Rate Sum rate lookup rate
2 - $0 $100,000.0 $100,000.0 10% $10,000.00 $10,000.00 $300,000.00 12% $12,000.00 $33,000.00 $33,000.00
3 > $100,001 $200,000.0 $100,000.0 11% $11,000.00 $21,000.00
4 > $200,001 $300,000.0 $100,000.0 12% $12,000.00 $33,000.00
5 > $300,001 $400,000.0 $100,000.0 13% $13,000.00 $46,000.00
6 > $400,001 $500,000.0 $100,000.0 14% $14,000.00 $60,000.00
7 > $500,001 $600,000.0 $100,000.0 15% $15,000.00 $75,000.00
8 > $600,001 $700,000.0 $100,000.0 16% $16,000.00 $91,000.00
9 > $700,001 $800,000.0 $100,000.0 17% $17,000.00 $108,000.00
10 $108,000.00
11
Sheet1
Cell Formulas
Range Formula
I2 I2 =INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2 J2 =INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2 L2 =SUMIF(B2:B9,"<="&H2,F2:F9)
M2 M2 =INDEX(G2:G9,MATCH(H2,B2:B9,1))
G2:G9 G2 =SUM($F$2:F2)
F10 F10 =SUM(F2:F9)

Thank you very much! I can use this. Appreciate the time you took to reply

crazy_tom · Jul 10, 2022

Dave Patton said:
Edit The previous post included a bracket above 800,000.

Commission2022.xlsm
A B C D E
1 Revenue Commission With Excel 365 & Named arrays
2 800,000.00 $108,000.00 $108,000.00 $108,000.00
3
4 Brackets Rates --- Arithmetic ---
5 By Bracket Cumulative
6 10% 10,000.00 10,000.00
7 100,000.00 11% 11,000.00 21,000.00
8 200,000.00 12% 12,000.00 33,000.00
9 300,000.00 13% 13,000.00 46,000.00
10 400,000.00 14% 14,000.00 60,000.00
11 500,000.00 15% 15,000.00 75,000.00
12 600,000.00 16% 16,000.00 91,000.00
13 700,000.00 17% 17,000.00 108,000.00
14 1.00E+308
15
16
17 50,000.00 $5,000.00
18 125,000.00 $12,750.00
19 750,000.00 $99,500.00
20 1,000,000.00 $142,000.00
21 1,200,000.00 $176,000.00
5a
Cell Formulas
Range Formula
C2 C2 =SUMPRODUCT(--(B2>B6:B13),B2-B6:B13,C6:C13-C5:C12)
D2 D2 =SUM((B2>aB)*(B2-aB)*aR)
E2 E2 =SUMPRODUCT(--(B2>aB),B2-aB,aR)
D6:D13 D6 =MAX(0,MIN($B$2,B7)-B6)*C6
E6:E13 E6 =MAX(0,MIN($B$2,B7)-B6)*C6+N(E5)
B14 B14 =BigNum
C17:C21 C17 =SUM((B17>aB)*(B17-aB)*aR)

Thank you for helping this a great refinement. Appreciate it!

Tiered Commission - With A Max / Cap for each Tier

crazy_tom

New Member

Excel Facts

etaf

Well-known Member

Dave Patton

Well-known Member

Dave Patton

Well-known Member

crazy_tom

New Member

crazy_tom

New Member

Forum statistics

Share this page

Tiered Commission - With A Max / Cap for each Tier

crazy_tom

New Member

Excel Facts

etaf

Well-known Member

Dave Patton

Well-known Member

Dave Patton

Well-known Member

crazy_tom

New Member

crazy_tom

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock