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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$ -​

[/td][td]

3.50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$ 1,000,001​

[/td][td]

6.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$ 2,500,001​

[/td][td]

6.50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

$ 3,500,001​

[/td][td]

7.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Comm​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

$ 1,000,000​

[/td][td="bgcolor:#E5E5E5"]

$ 35,000​

[/td][td]B8: =LOOKUP(A8,$A$2:$B$5) * A8[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

$ 1,000,001​

[/td][td="bgcolor:#E5E5E5"]

$ 60,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$ 2,500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 150,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$ 2,500,001​

[/td][td="bgcolor:#E5E5E5"]

$ 162,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$ 3,500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 227,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

$ 3,500,001​

[/td][td="bgcolor:#E5E5E5"]

$ 245,000​

[/td][td][/td][/tr]
[/table]

 

[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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td]

$0​

[/td][td]

3.50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td]

$1,000,001​

[/td][td]

6.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td]

$2,500,001​

[/td][td]

6.50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td]

$3,500,001​

[/td][td]

7.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Payout​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Jan[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 17,500​

[/td][td]C8: =LOOKUP(SUM(B$7:B8), $B$2:$C$5) * SUM(B$7:B8) - SUM(C$7:C7)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Feb[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 17,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Mar[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 55,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]Apr[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 30,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]May[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 30,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Jun[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 45,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Jul[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 32,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Aug[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 52,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]Sep[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 35,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]Oct[/td][td]

$500,000​

[/td][td="bgcolor:#E5E5E5"]

$ 35,000​

[/td][td][/td][/tr]
[/table]

 

[spkinosh]

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

 

[shg]

You're welcome.