Need help with a formula for calculating commissions

[Rushmore]

I need help creating a formula to calculate commissions on revenue
Tier 1: 0 -1,680,000 = 3.5%
Tier 2: 1,680,001 -3,360,000 = 4%
Tier 3: 3,360,001+ =5%

Once the revenue reaches a particular tier all commissions are paid at that level. For example asssume current revenue is at $1M and I close a deal for $2M, then the first $680K of that $2M will be paid at 3.5% (Tier 1) and the remaining $1,320,000 will be paid at the Tier 2 level of 4%

[Armando Montes]

Here is one way:
http://www.mcgimpsey.com/excel/variablerate.html

[Ace_XL]

Try

Please Login or Register  to view this content.

[Rushmore]

Ace_XL, I used your formula, however I had to make an adjustment because it was not calculating any commissions for less than $1.68M so I now have
=SUMPRODUCT(--(M13>{0,1680000,3360000,999999999999999}), (F13-{0,1680000,3360000,999999999999999}), {0.035,0.035,0.005,0.01})

M13 is the total revenue that qualifies me for the particular commission level and F13 is the revenue that the commission tier is paid on. However, it doesn't seem to be calculating correctly, what am I missing?

[Ace_XL]

Apologies for that, try

Please Login or Register  to view this content.

[Rushmore]

Thanks Ace_Xl,....you live up to your name!!

[Rushmore]

I need help with calculating a new commission plan I have.
Revenue Tiers Commission Payout
$0 - $199,999 8.10%
$200,000 - $250,000 9.63%
$250,001 - $299,999 10.59%
$300,000 -$374,999 11.65%
$375,000+ 12.93%

The way my commission works is the commission % is based on the aggregate revenue for the quarter. For example, if i sell Deal 1 for 100,000 I get paid at 8.10% but if I sell Deal 2 at 250,000 my aggregate is now $350,000 and Deal 2 is paid at 11.65% and Deal 1 is also paid at 11.65%, or since I was already paid 8.10% on Deal 1 I would get paid again the difference of 8.10% and 11.65% or 3.55%.

I want to build a simple spreadsheet with 2 columns and the rows would be the revenue from each individual deal. The Commission column I want to have the formulas that would calculate the commission based on what the aggregate sold is.
Deals Revenue Commission
Deal 1 $$
Deal 2 $$
Aggregate $$$

Hopefully this makes sense?