Tiered Commission - With A Max / Cap for each Tier

C

crazy_tom

New Member
Joined
Jun 28, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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​
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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
ABCDEFGHIJKL
1IncrementRaterevenue%RateSum rate
2-$0$100,000.0$100,000.010%$10,000.00$300,000.0013%$13,000.00$46,000.00
3>$100,000$200,000.0$100,000.011%$11,000.00
4>$200,000$300,000.0$100,000.012%$12,000.00
5>$300,000$400,000.0$100,000.013%$13,000.00
6>$400,000$500,000.0$100,000.014%$14,000.00
7>$500,000$600,000.0$100,000.015%$15,000.00
8>$600,000$700,000.0$100,000.016%$16,000.00
9>$700,000$800,000.0$100,000.017%$17,000.00
10$108,000.00
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
F10F10=SUM(F2:F9)


OR

Book7
ABCDEFGHIJKL
1IncrementRaterevenue%RateSum rate
2-$0$100,000.0$100,000.010%$10,000.00$300,000.0012%$12,000.00$33,000.00
3>$100,001$200,000.0$100,000.011%$11,000.00
4>$200,001$300,000.0$100,000.012%$12,000.00
5>$300,001$400,000.0$100,000.013%$13,000.00
6>$400,001$500,000.0$100,000.014%$14,000.00
7>$500,001$600,000.0$100,000.015%$15,000.00
8>$600,001$700,000.0$100,000.016%$16,000.00
9>$700,001$800,000.0$100,000.017%$17,000.00
10$108,000.00
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
F10F10=SUM(F2:F9)


OR Add an accumulated column and lookup that

see column G added and lookup in M

Book7
ABCDEFGHIJKLM
1IncrementRaterevenue%RateSum ratelookup rate
2-$0$100,000.0$100,000.010%$10,000.00$10,000.00$300,000.0012%$12,000.00$33,000.00$33,000.00
3>$100,001$200,000.0$100,000.011%$11,000.00$21,000.00
4>$200,001$300,000.0$100,000.012%$12,000.00$33,000.00
5>$300,001$400,000.0$100,000.013%$13,000.00$46,000.00
6>$400,001$500,000.0$100,000.014%$14,000.00$60,000.00
7>$500,001$600,000.0$100,000.015%$15,000.00$75,000.00
8>$600,001$700,000.0$100,000.016%$16,000.00$91,000.00
9>$700,001$800,000.0$100,000.017%$17,000.00$108,000.00
10$108,000.00
11
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
M2M2=INDEX(G2:G9,MATCH(H2,B2:B9,1))
G2:G9G2=SUM($F$2:F2)
F10F10=SUM(F2:F9)
 
Upvote 0
Solution
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
ABCDEFGH
1BracketsRatesRevenueCommissionWith Excel 365 &Named arrays
2800,000.00108,000.00108,000.00108,000.00
3010%
4100,000.0011%
5200,000.0012%
6300,000.0013%
7400,000.0014%
8500,000.0015%
9600,000.0016%
10700,000.0017%
11800,000.0018%
5a
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT(--(E2>B3:B11),E2-B3:B9:B11,C3:C11-C2:C10)
G2G2=SUM((E2>aB)*(E2-aB)*aR)
H2H2=SUMPRODUCT(--(E2>aB),E2-aB,aR)



Commission2022.xlsm
ABC
1350,000.00$5,000.00
14425,000.00$49,500.00
151,200,000.00$180,000.00
16
5a
Cell Formulas
RangeFormula
C13:C15C13=SUM((B13>aB)*(B13-aB)*aR)
 
Last edited:
Upvote 0
Edit The previous post included a bracket above 800,000.

Commission2022.xlsm
ABCDE
1RevenueCommissionWith Excel 365 & Named arrays
2800,000.00$108,000.00$108,000.00$108,000.00
3
4BracketsRates --- Arithmetic ---
5By BracketCumulative
610%10,000.0010,000.00
7100,000.0011%11,000.0021,000.00
8200,000.0012%12,000.0033,000.00
9300,000.0013%13,000.0046,000.00
10400,000.0014%14,000.0060,000.00
11500,000.0015%15,000.0075,000.00
12600,000.0016%16,000.0091,000.00
13700,000.0017%17,000.00108,000.00
141.00E+308
15
16
1750,000.00$5,000.00
18125,000.00$12,750.00
19750,000.00$99,500.00
201,000,000.00$142,000.00
211,200,000.00$176,000.00
5a
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(--(B2>B6:B13),B2-B6:B13,C6:C13-C5:C12)
D2D2=SUM((B2>aB)*(B2-aB)*aR)
E2E2=SUMPRODUCT(--(B2>aB),B2-aB,aR)
D6:D13D6=MAX(0,MIN($B$2,B7)-B6)*C6
E6:E13E6=MAX(0,MIN($B$2,B7)-B6)*C6+N(E5)
B14B14=BigNum
C17:C21C17=SUM((B17>aB)*(B17-aB)*aR)
 
Upvote 0
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
ABCDEFGHIJKL
1IncrementRaterevenue%RateSum rate
2-$0$100,000.0$100,000.010%$10,000.00$300,000.0013%$13,000.00$46,000.00
3>$100,000$200,000.0$100,000.011%$11,000.00
4>$200,000$300,000.0$100,000.012%$12,000.00
5>$300,000$400,000.0$100,000.013%$13,000.00
6>$400,000$500,000.0$100,000.014%$14,000.00
7>$500,000$600,000.0$100,000.015%$15,000.00
8>$600,000$700,000.0$100,000.016%$16,000.00
9>$700,000$800,000.0$100,000.017%$17,000.00
10$108,000.00
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
F10F10=SUM(F2:F9)


OR

Book7
ABCDEFGHIJKL
1IncrementRaterevenue%RateSum rate
2-$0$100,000.0$100,000.010%$10,000.00$300,000.0012%$12,000.00$33,000.00
3>$100,001$200,000.0$100,000.011%$11,000.00
4>$200,001$300,000.0$100,000.012%$12,000.00
5>$300,001$400,000.0$100,000.013%$13,000.00
6>$400,001$500,000.0$100,000.014%$14,000.00
7>$500,001$600,000.0$100,000.015%$15,000.00
8>$600,001$700,000.0$100,000.016%$16,000.00
9>$700,001$800,000.0$100,000.017%$17,000.00
10$108,000.00
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
F10F10=SUM(F2:F9)


OR Add an accumulated column and lookup that

see column G added and lookup in M

Book7
ABCDEFGHIJKLM
1IncrementRaterevenue%RateSum ratelookup rate
2-$0$100,000.0$100,000.010%$10,000.00$10,000.00$300,000.0012%$12,000.00$33,000.00$33,000.00
3>$100,001$200,000.0$100,000.011%$11,000.00$21,000.00
4>$200,001$300,000.0$100,000.012%$12,000.00$33,000.00
5>$300,001$400,000.0$100,000.013%$13,000.00$46,000.00
6>$400,001$500,000.0$100,000.014%$14,000.00$60,000.00
7>$500,001$600,000.0$100,000.015%$15,000.00$75,000.00
8>$600,001$700,000.0$100,000.016%$16,000.00$91,000.00
9>$700,001$800,000.0$100,000.017%$17,000.00$108,000.00
10$108,000.00
11
Sheet1
Cell Formulas
RangeFormula
I2I2=INDEX(E2:E9,MATCH(H2,B2:B9,1))
J2J2=INDEX(F2:F9,MATCH(H2,B2:B9,1))
L2L2=SUMIF(B2:B9,"<="&H2,F2:F9)
M2M2=INDEX(G2:G9,MATCH(H2,B2:B9,1))
G2:G9G2=SUM($F$2:F2)
F10F10=SUM(F2:F9)
Click to expand...
Thank you very much! I can use this. Appreciate the time you took to reply ;-)
 
Upvote 0
Dave Patton said:
Edit The previous post included a bracket above 800,000.

Commission2022.xlsm
ABCDE
1RevenueCommissionWith Excel 365 & Named arrays
2800,000.00$108,000.00$108,000.00$108,000.00
3
4BracketsRates --- Arithmetic ---
5By BracketCumulative
610%10,000.0010,000.00
7100,000.0011%11,000.0021,000.00
8200,000.0012%12,000.0033,000.00
9300,000.0013%13,000.0046,000.00
10400,000.0014%14,000.0060,000.00
11500,000.0015%15,000.0075,000.00
12600,000.0016%16,000.0091,000.00
13700,000.0017%17,000.00108,000.00
141.00E+308
15
16
1750,000.00$5,000.00
18125,000.00$12,750.00
19750,000.00$99,500.00
201,000,000.00$142,000.00
211,200,000.00$176,000.00
5a
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(--(B2>B6:B13),B2-B6:B13,C6:C13-C5:C12)
D2D2=SUM((B2>aB)*(B2-aB)*aR)
E2E2=SUMPRODUCT(--(B2>aB),B2-aB,aR)
D6:D13D6=MAX(0,MIN($B$2,B7)-B6)*C6
E6:E13E6=MAX(0,MIN($B$2,B7)-B6)*C6+N(E5)
B14B14=BigNum
C17:C21C17=SUM((B17>aB)*(B17-aB)*aR)
Click to expand...
Thank you for helping this a great refinement. Appreciate it! :-)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top