# Tiered commission calculator

#### Brian88195

##### New Member
Hi, I have searched and tried many samples but couldn’t find any solutions.

I am trying to create a sliding scale calculator for positive and negative premiums earned by a broker. They receive 8% for the first \$1,000, 6% for the next \$4,000, 4% for the next \$95,000 and 2% for everything over \$100,000

a policy can have several changes which impact the commission paid. These changes can be positive and negative amounts. I would like to type the amounts in one column and see the amounts go into the buckets listed above and the commission paid for each bucket.

the tricky part is when a negative change occurs. That amount should be subtracted from the previous buckets.
so say if the first amount is \$1500, \$1000 is in the first bucket and \$500 in the second. If a negative \$1000 follows it would take \$500 from the second bucket first then \$500 from the first. If the third amount is a positive \$750, \$500 would go in the first bucket and \$250 in the second.

any suggestions are greatly appreciated. Thanks.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Dave Patton

##### Well-known Member
If you prefer, you can name the ranges or the arrays that they yield.
The detail of the calculation shows in F5:F8.

Commission2020.xlsm
ABCDEF
4Tax5,880.00E2 is blank
508%80.00
61,0006%240.00
75,0004%3,800.00
8100,0002%1,760.00
95,880.001E+3085,880.00
1a
Cell Formulas
RangeFormula
B3B3=SUM(B1:B2)
B4B4=SUMPRODUCT(--(B3>D5:D8),B3-D5:D8,E5:E8-E4:E7)
B9B9=SUMPRODUCT(--(B3>aB),B3-aB,aR)
D9D9=BigNum
F5:F8F5=MAX(0,MIN(\$B\$3,D6)-D5)*E5
F9F9=SUM(F5:F8)

#### Brian88195

##### New Member
Thanks for the input. I did try this and made some slight edits to see if I could get it to work for my need but it’s not. The issue i am still having is getting the amounts to go into the correct buckets. To go with the values that you used if i add a positive \$75,000 it should be listed in the Amount 4 area. If a negative \$200,000 followed it should take it should removed 175,000 from the amount 4 area and 25,000 from the amount 3 area.

#### Dave Patton

##### Well-known Member
If you add 200,000 and 75,000, the total is 275000 and the calculation yields 7620.00.
You would need columns for total calculation, preliminary, and final to see the changes in each bracket.
The formula shows the calculation for the accumulated amount.

Commission2020.xlsm
ABCDEF
4Tax7,620.00E2 is blank
508%80.00
61,0006%240.00
75,0004%3,800.00
8100,0002%3,500.00
97,620.001.E+3087,620.00
10
1a
Cell Formulas
RangeFormula
B3B3=SUM(B1:B2)
B4B4=SUMPRODUCT(--(B3>D5:D8),B3-D5:D8,E5:E8-E4:E7)
B9B9=SUMPRODUCT(--(B3>aB),B3-aB,aR)
D9D9=BigNum
F5:F8F5=MAX(0,MIN(\$B\$3,D6)-D5)*E5
F9F9=SUM(F5:F8)

Commission2020.xlsm
AB
1Contract 12310,000.00
2Contract 1232,000.00
3Contract 123-500.00
4Contract 123-800.00
5Contract 1231,200.00
7Tax596.00
8
1a
Cell Formulas
RangeFormula
B6B6=SUM(B1:B5)
B7B7=SUMPRODUCT(--(B6>aB),B6-aB,aR)

Last edited:

#### Dave Patton

##### Well-known Member
Commission2020.xlsm
ABCDEFGHI
1Contract installmentsabcd
2Contract +/-1,500.00(1,000.00)750.004,750.00
3Contract accumulated1,500.00500.001,250.006,000.00
4Commission on accumulated110.0040.0095.00360.00
5Increase or (Decrease)
6Tiers
7BracketsRate - ---- a --- ---- Accumulated --- --- --- ----bcd
8E2 is blank(1,000.00)750.004,750.00
908%80.0040.0080.0080.00(40.00)40.000.00
101,0006%30.000.0015.00240.00(30.00)15.00225.00
115,0004%0.000.000.0040.000.000.0040.00
12100,0002%0.000.000.000.000.000.000.00
131E+308110.0040.0095.00360.00(70.00)55.00265.00
14
1a
Cell Formulas
RangeFormula
C3:F3C3=SUM(\$C\$2:C2)
C4:F4C4=SUMPRODUCT(--(C3>aB),C3-aB,aR)
G8:I8G8=D2
C9:C12C9=MAX(0,MIN(\$C\$3,A10)-A9)*B9
D9:D12D9=MAX(0,MIN(\$D\$3,A10)-A9)*B9
E9:E12E9=MAX(0,MIN(\$E\$3,A10)-A9)*B9
F9:F12F9=MAX(0,MIN(\$F\$3,A10)-A9)*B9
G9:I12G9=D9-C9
C13:I13C13=SUM(C9:C12)
A13A13=BigNum

Replies
11
Views
392
Replies
13
Views
520
Replies
2
Views
2K
Replies
3
Views
236
Replies
3
Views
748

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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