Sales Commissions staggered incrementally

[johneirc]

Hi,

I trying to create a commission structure whereby a sales team gets commission based on increasing percentages, but the relevant percentage is only paid on the incremental range. It's best explained by an example.

0 - 15,000 paid at 20%
15,001 - 25,000 paid at 25%
25,001 - 35,000 paid at 30%
35,001 - 40,000 paid at 35%
40,001+ at 40%.

So, if a sales rep had 50,000 sales in the month, the first 15,000 would be paid at 20%, the next 10,000 paid at 25% (25,000 - 15,001), the next 10,000 paid at 30% (35,000 - 25,001) and so on.

I tried using a lookup table but it seems to apply the whole amount at a single percentage only, and using an IF statements and lookup tables just became too complicated.

I have been informed by better users of Excel (although, they did not know how) that it might be best to calculate the above using a function generated in VBA. However I do know where to start, as I have only limited experience in VBA.

All help gratefully accepted.

Thanks in advance,
John

 

[Domenic]

Assuming that A1 contains the sales amount, try...

=SUMPRODUCT(--(A1>{0,15000,25000,35000,40000}),A1-{0,15000,25000,35000,40000},{0.2,0.05,0.05,0.05,0.05})

Hope this helps!

 

[Joe4]

Here is another way to do it, simply by using a series of Excel functions for each step.

Assuming your amount is in cell A1:
=(MIN(A1,15000)*20%)+(MAX(MIN(A1-15000,10000),0)*25%)+(MAX(MIN(A1-25000,10000),0)*30%)+(MAX(MIN(A1-35000,5000),0)*35%)+(MAX(A1-40000,0)*40%)

Though, Domenic's way is a little cleaner!

 

[johneirc]

Thank you both for your help.

Kind Regards,
John

 

[johneirc]

Domenic,

On the method you provided, is it possible to replace the values between { } with cell references which contain the various commission values and percentages? I tried but it comes back with an error.

Regards,
John

 

[Domenic]

Let C1:D5 contain the following table...

0	0.2
15000	0.05
25000	0.05
35000	0.05
40000	0.05

Then, use the following formula...

=SUMPRODUCT(--(A1>C1:C5),A1-C1:C5,D1:D5)

Hope this helps!

 

[johneirc]

Thanks Domenic

 

[websmile]

Just discovered this old thread and applied to my requirements for a volume discount scheme incl. calculator sheet, excellent, many thanks Domenic!