Tiered commission calculator

Brian88195

New Member
Joined
Mar 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,666
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
1Premiums i200,000.00
2Premiums ii-12,000.00
3Premiums Net188,000.00BracketsRate
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
Joined
Mar 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
1616693576987.png
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,666
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
1Premiums i200,000.00
2Premiums ii75,000.00
3Premiums Net275,000.00BracketsRate
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
6Premiums Net11,900.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
Joined
Feb 15, 2002
Messages
4,666
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top