Tiered commission calculator

[Brian88195]

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.

 

[Dave Patton]

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
	A	B	C	D	E	F
1	Premiums i	200,000.00
2	Premiums ii	-12,000.00
3	Premiums Net	188,000.00		Brackets	Rate
4	Tax	5,880.00		E2 is blank
5				0	8%	80.00
6				1,000	6%	240.00
7				5,000	4%	3,800.00
8				100,000	2%	1,760.00
9		5,880.00		1E+308		5,880.00
1a

Cell Formulas
Range	Formula
B3	B3	=SUM(B1:B2)
B4	B4	=SUMPRODUCT(--(B3>D5:D8),B3-D5:D8,E5:E8-E4:E7)
B9	B9	=SUMPRODUCT(--(B3>aB),B3-aB,aR)
D9	D9	=BigNum
F5:F8	F5	=MAX(0,MIN($B$3,D6)-D5)*E5
F9	F9	=SUM(F5:F8)

 

[Brian88195]

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]

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
	A	B	C	D	E	F
1	Premiums i	200,000.00
2	Premiums ii	75,000.00
3	Premiums Net	275,000.00		Brackets	Rate
4	Tax	7,620.00		E2 is blank
5				0	8%	80.00
6				1,000	6%	240.00
7				5,000	4%	3,800.00
8				100,000	2%	3,500.00
9		7,620.00		1.E+308		7,620.00
10
1a

Cell Formulas
Range	Formula
B3	B3	=SUM(B1:B2)
B4	B4	=SUMPRODUCT(--(B3>D5:D8),B3-D5:D8,E5:E8-E4:E7)
B9	B9	=SUMPRODUCT(--(B3>aB),B3-aB,aR)
D9	D9	=BigNum
F5:F8	F5	=MAX(0,MIN($B$3,D6)-D5)*E5
F9	F9	=SUM(F5:F8)

Commission2020.xlsm
	A	B
1	Contract 123	10,000.00
2	Contract 123	2,000.00
3	Contract 123	-500.00
4	Contract 123	-800.00
5	Contract 123	1,200.00
6	Premiums Net	11,900.00
7	Tax	596.00
8
1a

Cell Formulas
Range	Formula
B6	B6	=SUM(B1:B5)
B7	B7	=SUMPRODUCT(--(B6>aB),B6-aB,aR)

 

[Dave Patton]

Commission2020.xlsm
	A	B	C	D	E	F	G	H	I
1	Contract installments		a	b	c	d
2	Contract +/-		1,500.00	(1,000.00)	750.00	4,750.00
3	Contract accumulated		1,500.00	500.00	1,250.00	6,000.00
4	Commission on accumulated		110.00	40.00	95.00	360.00
5							Increase or (Decrease)
6	Tiers
7	Brackets	Rate	- ---- a --- ---- Accumulated --- --- --- ----				b	c	d
8	E2 is blank						(1,000.00)	750.00	4,750.00
9	0	8%	80.00	40.00	80.00	80.00	(40.00)	40.00	0.00
10	1,000	6%	30.00	0.00	15.00	240.00	(30.00)	15.00	225.00
11	5,000	4%	0.00	0.00	0.00	40.00	0.00	0.00	40.00
12	100,000	2%	0.00	0.00	0.00	0.00	0.00	0.00	0.00
13	1E+308		110.00	40.00	95.00	360.00	(70.00)	55.00	265.00
14
1a

Cell Formulas
Range	Formula
C3:F3	C3	=SUM($C$2:C2)
C4:F4	C4	=SUMPRODUCT(--(C3>aB),C3-aB,aR)
G8:I8	G8	=D2
C9:C12	C9	=MAX(0,MIN($C$3,A10)-A9)*B9
D9:D12	D9	=MAX(0,MIN($D$3,A10)-A9)*B9
E9:E12	E9	=MAX(0,MIN($E$3,A10)-A9)*B9
F9:F12	F9	=MAX(0,MIN($F$3,A10)-A9)*B9
G9:I12	G9	=D9-C9
C13:I13	C13	=SUM(C9:C12)
A13	A13	=BigNum