Variable Commission Rate Calculations

[sglancy]

I need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.

They get 7.5% of the value when between $1 - $100,000, example: sales of $50,000 = $3,750 in commission.

If the value in H39 is > $100,000 but <$250,000 they get 7.5% on the first $100,000 plus 5% on the remainder between $100,001 and $250,000, ex: sales of $200,000 = $7,500 (7.5% on first $100,00) plus $5,000 (5% on remaining $100,000) for a total of $12,500.

If the value in H39 is > $250,000 but <$500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000, example: sales of $400,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $4,500 (3% on sales between $250,001 and $500,000) for a total of $12,500.

If the value in H39 is > $500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000 plus 2% on any amount >$500,000 example: sales of $800,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $7,500 (3% on the next $250,000) plus $6,000 (2% on the sales >$500,000 for a total of $28,500.



any help is appreciated......

 

[Jeff Meyers]

If the value in H39 is > $250,000 but <$500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000, example: sales of $400,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $4,500 (3% on sales between $250,001 and $500,000) for a total of $12,500.

The above example / scenario has an error. The total should be $19,500.

Here's a formula to calculate commissions for your sales reps:

=IF(H39>500000,((H39-500000)*0.02)+22500,IF(H39>250000,((H39-250000)*0.03)+15000,IF(H39>100000,((H39-100000)*0.05)+7500,H39*0.075)))

 

[theBardd]

Try this formula

=SUMPRODUCT(--(B2>={0;100000;250000;500000})*(B2-{0;100000;250000;500000})*{0.075;-0.025;-0.02;-0.01})

 

[Dave Patton]

Excel Workbook
	F	G	H
31	Table - Commission rates		Rate
32	Brackets		Differential
33	0	7.50%	7.5%
34	100,000.00	5.00%	-2.5%
35	250,000.00	3.00%	-2.0%
36	500,000.00	2.00%	-1.0%
37
38
39			$800,000.00
40			28,500.00
2d

Excel 2003

Cell Formulas
Range	Formula
H33	=G33-G32
H34	=G34-G33
H35	=G35-G34
H36	=G36-G35
H40	=SUMPRODUCT(--(H39>rB),H39-rB,rRates)

Excel Workbook
Name	Refers To
rB	='2d'!$F$33:$F$36
rRates	='2d'!$H$33:$H$36
Workbook Defined Names

If you do not want to use the Lookup table, see Bardd's formula.

 

[Jeff Meyers]

I prefer theBardd's formula (as compared to mine). Very clean looking and much easier to update for commission rate / threshold changes.

 

[barry houdini]

One more way......

=IF(H39="","",H39*2%+MIN(500000,H39)*1%+MIN(250000,H39)*2%+MIN(100000,H39)*2.5%)