Stair Step commissions question

[Serg]

I am to begin calculating rep commissions in the coming months based
on a "tiered" or "step" method. The rep commissions will be based on
YTD sales and their commissions % will increase as they surpasse four
different "steps"--0%-100%, 100.01-125%, 125.01-150% & >150% of YTD
sales. I have attempted multiple variations using the IF formula and
can't get this thing to work out how I want it, specifically the
problem I am having is that I cannot calculate commissions for someone
who makes their first goal ($0-$138,000) at that % (20%), and then
calculate future sales at the next step ($138,00.01-206,999.99 @ 25%)
and so on without calculating a total YTD figure at one commissions
%. Has anyone encountered a similar problem and is their an easy
formula I can use to calculate this? Here is the copy of the formula
I was using:

=IF(U2<V2,U2*AA2,IF(U2<W2,U2*AB2,IF(U2<X2,U2*AC2,IF(U2<Y2,U2*AD2))))
where
U2=YTD Sales
V2=$138,000
W2=$172,500
X2=$207,000
Y2=$207,014
AA2=30%
AB=25%
AC=27%
AD=30%

Here is an example of a few columns:

1st goal @ 20% 2nd goal @ 25% 3rd goal @
27% 4th goal @30%

 

[JE McGimpsey]

See if you can adapt what's suggested here:

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

 

[Peo Sjoblom]

Use this method instead

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

 

[George Nicholson]

1) Create a table. Name it Commisions.

Target /Rate / Adjustment
0 / 0.20 / 0
138,000 / .25 / (6900)
207,000 / .27 / (11040)
300,000/ .30 / (20040)

Adjustment formula is:
=(((CurrentRate-PreviousRate) * CurrentTarget) + PreviousAdjustment) * -1

2) Assuming your Rep sales figures are in A2, the Commision for that Rep
would be:
(A2*VLOOKUP(A2,Commisions,2, True))+VLOOKUP(A2,Commisions,3,True)

(The True argument is actually unnecessary, since the argument is optional
and True is the Default). True specifies that VLookup will look for an
approximate match in an ascending list, stopping at the "last" value that is
less or equal to the value it is looking for.

One problem you encountered is resolved by using the adjustment value.
Calculate the entire commision at the "highest" applicable rate and then
"back out" the known overstatement.

This approach is simply a variation of tables similar to:
http://www.irs.gov/publications/p15/10000w36.html

One advantage is that this is much easier to maintain than a endlessly
nested formula. You *know* Targets and Rates will change in the future.
Just update the table and you're done. Some reps will be using a different
scale? Create a 2nd table. Boss wants to have 20 tiers? No problem.

HTH,