Monthly commission formula

[the_liquid_8er]

Hi To all the experts

I am desperately seeking an excel formula that would accurately calculate a monthly commission based on the following crtiteria:

[TABLE="width: 432"]
<tbody>[TR]
[TD]Monthly Revenue[/TD]
[TD] Percentage [/TD]
[TD] Monthly Remuneration [/TD]
[/TR]
[TR]
[TD]$83,333.33[/TD]
[TD]0.00%[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]$166,666.67[/TD]
[TD]1.00%[/TD]
[TD]$833[/TD]
[/TR]
[TR]
[TD]$250,000.00[/TD]
[TD]1.25%[/TD]
[TD]$1,042[/TD]
[/TR]
[TR]
[TD]$333,333.33[/TD]
[TD]1.50%[/TD]
[TD]$1,250[/TD]
[/TR]
[TR]
[TD]$416,666.67[/TD]
[TD]1.75%[/TD]
[TD]$1,458[/TD]
[/TR]
[TR]
[TD]$500,000.00[/TD]
[TD]2.00%[/TD]
[TD]$1,667[/TD]
[/TR]
[TR]
[TD]$583,333.33[/TD]
[TD]2.25%[/TD]
[TD]$1,875[/TD]
[/TR]
</tbody>[/TABLE]

Can somebody offer there informed opinion please?

 

[AhoyNC]

Take a look at this link on commissions.
If this is not right it would help if you could give an example where you show an expect result based on a revenue.

http://www.mcgimpsey.com/excel/variablerate.html

Based on their formula and your data:

Excel Workbook
	A	B	C	D	E
1	Monthly Revenue	Percentage	Diff. Rate		Revenue
2	0	0.00%	$0.00		$ 425,000.00
3	$83,333.33	1.00%	1.00%		Commission
4	$166,666.67	1.25%	0.25%		$ 4,750.00
5	$250,000.00	1.50%	0.25%
6	$333,333.33	1.75%	0.25%
7	$416,666.67	2.00%	0.25%
8	$500,000.00	2.25%	0.25%
Sheet

 

[DanteAmor]

You can put a couple of examples of what you have and what you expect from the result

 

[jondavis1987]

What is the math you're trying to do? Give me an example of the math you'd do to calculate the commission and I may be able to give you a formula for it

 

[the_liquid_8er]

Thanks DanteAmor

Say the sales person sells $420,000 for the month, the commission would be:

Nil for $0-$83,333.33;
$833 for $83,333.33 to $166,666.67;
$1,042 for $166,666.67 to $249,999.99;
$1250 for $250,000.00 to $333,333.32;
$1,458 for $333,333.33 to $416,666.66
$66.66 for $416,666.67 up to $420,000.00 (being 2% of $3,333.33)

This would be a total bonus on $420,000 of $4,649.66

Can this somehow be converted to a Single Cell Formula?

Gracias de antemano desde Australia mi amigo

 

[tyija1995]

Post 2 formula is correct - make sure that you make a % difference column as illustrated by ahoyNC.

So yes a single cell formula works (i.e. SUMPRODUCT)

 

[DanteAmor]

Check this option


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:91.25px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">For</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">To</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Remuneration</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Sell</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Bonus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$83,333.33</td><td style="text-align:right; ">$0.00</td><td > </td><td style="text-align:right; ">$50,000.00</td><td style="text-align:right; ">$0.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">$83,333.33</td><td style="text-align:right; ">$166,666.67</td><td style="text-align:right; ">$833.00</td><td > </td><td style="text-align:right; ">$150,000.00</td><td style="text-align:right; ">$833.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">$166,666.67</td><td style="text-align:right; ">$249,999.99</td><td style="text-align:right; ">$1,042.00</td><td > </td><td style="text-align:right; ">$170,000.00</td><td style="text-align:right; ">$1,875.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">$250,000.00</td><td style="text-align:right; ">$333,333.32</td><td style="text-align:right; ">$1,250.00</td><td > </td><td style="text-align:right; ">$250,000.00</td><td style="text-align:right; ">$3,125.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">$333,333.33</td><td style="text-align:right; ">$416,666.66</td><td style="text-align:right; ">$1,458.00</td><td > </td><td style="text-align:right; ">$340,000.00</td><td style="text-align:right; ">$4,583.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">$416,666.67</td><td style="text-align:right; ">$420,000.00</td><td style="text-align:right; ">$66.66</td><td > </td><td style="text-align:right; ">$417,000.00</td><td style="text-align:right; ">$4,649.66</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">$421,000.00</td><td style="text-align:right; ">$4,649.66</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=SUM(OFFSET($C$2,,,MATCH(E2,$A$2:$A$7,1)))</td></tr></table></td></tr></table>

Saludos amigo!

 

[AhoyNC]

There is a difference in my formula in post #2 and DanteAmor in post #7 .

At sells of $170,000 I'm assuming the first $166,666.67 is paid at $833.33. Then the balance of $3,333.33 ($170,000-$166,666.67) is paid at 1.25% or $41.66.
So total commission would be $833.33 + 41.67 = $875.00

DanteAmor formula assumes the same for the first $166,666.67 or $833.33, but then it looks like he assumes the balance would be paid at $1,042 since it moved into the next tier for a total commission of $1,875.00