Incentive Compound Formula Help!!!!

[MichaelPaul.Hernandez]

--I will paypal anyone $10 if done by tonight and it works---

The problem I am having is related to an amortization fee schedule or
a sliding tax schedule. I am trying to make a formula that will
calculate a compounded percentage when a certain benchmark is reached.

The example I am using is as follows:

1) If a person generates under $20,000 in sales, they will be be given
the 5% of whatever they bring in.

2) If a person generates between $20,000 and $35,000 they will receive
7.5% of what they bring in between the two amounts, plus the original
5% of the $20,000.

3) Anything above $35,000 and they will receive 10% of whatever is
above $35,000, plus the $15,000 at 7.5%, plus the $20,000 at 5%
percent.


Therefore:

if $10,000 is earned in March:
$500 incentive will be earned.

if $15,000 is earned in April:
$875 incentive will be earned, from the first $10,000@5% being under
20,000 ($500) and $375 from the $5,[email protected]%. (the level of things
brought in is now at 25,000)

if $15,000 is earned in May:
$1250 incentive will be earned, from the $10,[email protected]% ($750) being
under 35,000 and the remaining $5,000@10% ($500) from being over
35,000.

In all, a $40,000 income will yield 500+875+1250= $2625.00

The formulas I have work if everything is placed in one month. However
I am having trouble compounding the formula into multiple months. Any
help or advice would be much appreciated.

Thank you again,
Michael-Paul

 

[Bernard Liengme]

What do you mean by: compounding the formula into multiple months.
best wishes

 

[MichaelPaul.Hernandez]

As in the example that I gave for March, April and May.

It is basically a formula for commission with a variable rate, but I
can't get it to work.

Thanks again,
MP

 

[INTP56]

As I understand it you have this situation, as display as the first 5 rows of
a worksheet:

$35K 10% $20K 7.5% $0 5.%
Month Sales YTD Hi InH Med InM Low InL Total Incentive
Mar $10K $10K $0 $0 $0 $0 $10K $500 $500
Apr $15K $25K $0 $0 $5K $375 $10K $500 $875
May $15K $40K $5K $500 $10K $750 $0 $0 $1,250

Sales -> are monthly sales
YTD -> Year to date sales
Hi -> Amount of monthly sales eligible for Hi Rate, value over header is
cutoff
InH -> Monthly incentive for Monthly Hi amount, value over header is %
Med -> Amount of monthly sales eligile for Med Rate, value over header is
cutoff
InM -> Monthly Incentive for Monthly Med amount, value over header is %
Lo -> Amount of monthly sales eligile for Low Rate, value over header is
cutoff
InL -> Monthly Incentive for Monthly Low amount, value over header is %

For line 3, or march, the fomulas are:
Month
Sales
YTD =B3, subsequent lines are =B4+C3
Hi =MAX($C3-D$1,0)
InH =D3*E$1
Med =MIN(MAX($C3-F$1,0),B3-D3)
InM =F3*G$1
Low =MIN(MAX($C3-H$1,0),B3-D3-F3)
InL =H3*I$1
Total =E3+G3+I3

You will need to adjust for beginning/end of year transitions

HTH, Bob

 

[MichaelPaul.Hernandez]

A great crack at it, however it only works for multiple months not a
single month, how could I change that? I am using multiple inputs for
one month-

Thanks again for helping me, I´ve been struggling with this for a
while now.
MP-

 

[INTP56]

How about having one sales only worksheet per month, putting the individual
entries in the right worksheet, and make the sales cells be the sum from the
sales column for each month?

Your workbook would have 13 sheets, the summary sheet and the 12 months.

Bob

 

[INTP56]

Sorry, I misunderstood what you were trying to tell me.

Just eliminate the months altogether, and make the left column the
individual sales items. Now TotalIncentive is calculated on a per sale basis,
and how you aggregate is left up to you.

Bob