Tiered Commissions that pay difference back to dollar one.

[spkinosh]

Hello. I need help with a tiered commission formula that would pay commissions back to dollar one as the rep reached new tiers. For instance, for sales up to $1,000,000 they get a 3.5% commission. Once they hit $1,000,001, the commissions are 6.00%, and they get paid on the first 1,000,000 an additional 2.5%. I have found a lot of tiered commission but none that go back and calculate the additional commission. Here's the full grid:

Hurdle Rate Dif Rate

0 3.50% 0.00%
$1,000,001 6.00% 2.50%
$2,500,001 6.50% 0.50%
$3,500,001 7.00% 0.50%

 

[shg]

	A​	B​	C​
1​	Amt​	Rate​
2​	$ -​	3.50%​
3​	$ 1,000,001​	6.00%​
4​	$ 2,500,001​	6.50%​
5​	$ 3,500,001​	7.00%​
6​
7​	Amt​	Comm​
8​	$ 1,000,000​	$ 35,000​	B8: =LOOKUP(A8,$A$2:$B$5) * A8
9​	$ 1,000,001​	$ 60,000​
10​	$ 2,500,000​	$ 150,000​
11​	$ 2,500,001​	$ 162,500​
12​	$ 3,500,000​	$ 227,500​
13​	$ 3,500,001​	$ 245,000​

 

[spkinosh]

Not quite - let me clarify. January they make $800,000 - commissions are 3.5%. February they make 1,000,000 - commissions are 6.0% PLUS I need to go back and pay an additional 2.5% on February's commissions. It will continue at 6% until I hit the next tier of 2,500,001 at which point I'll need to start paying at 6.5% AND go back to any months previously paid and pay an additional .5%.

 

[shg]

That's a different question, but OK:

	A​	B​	C​	D​
1​		Amt​	Rate​
2​		$0​	3.50%​
3​		$1,000,001​	6.00%​
4​		$2,500,001​	6.50%​
5​		$3,500,001​	7.00%​
6​
7​		Amt​	Payout​
8​	Jan	$500,000​	$ 17,500​	C8: =LOOKUP(SUM(B$7:B8), $B$2:$C$5) * SUM(B$7:B8) - SUM(C$7:C7)
9​	Feb	$500,000​	$ 17,500​
10​	Mar	$500,000​	$ 55,000​
11​	Apr	$500,000​	$ 30,000​
12​	May	$500,000​	$ 30,000​
13​	Jun	$500,000​	$ 45,000​
14​	Jul	$500,000​	$ 32,500​
15​	Aug	$500,000​	$ 52,500​
16​	Sep	$500,000​	$ 35,000​
17​	Oct	$500,000​	$ 35,000​

 

[spkinosh]

Thank you! This is exactly what I have been looking for!

 

[shg]

You're welcome.