Cumulative Commission Calc

[zoltar73]

Hello People,

Hoping a good fellow might be able to help me please.
I am trying to create a commission calculator.

Commission it to be based on Sales achievement against a Target such that :
Up to 100% of Target achieved is paid at a Rate
Between 101% - 125% is paid at Rate x 1.25
Over 125% (i.e. 126% and above) is paid at Rate x 1.5

I could not achieve this with 1 elegant formula so tried to use helper columns. This got me closer but The trouble is when deal takes the cumulative through the Rate threshold. e.g. when a sales rep has a target of 100k, if he currently has 90k achieved and the next deal is 20k, there should be 10k at Rate 1 and 10k at Rate 2.
Indeed if the next deal was 50k it would need to be 10k @ Rate 1, 25k @ Rate 2 and the remainder at Rate 3
I am not able to achieve the elegance needed here - please help !

I have attached the file.

Helper columns with formulae in G,H & I - ideally just 1 column here with 1 formula would be great but I don't mind having the helper column's to achieve the correct result.
Many Thanks in advance.

 

[Peter Bartholomew]

The array formula in G24 is pretty unpleasant and uses a 365 LET function to organize the calculation.

= LET(
  limit, IFERROR(INDEX(threshold, {2,3,4}),"∞"),
  upper, Cumulative,
  lower, upper - winValue,
  minUpper, IF(limit<upper, limit, upper),
  maxLower, IF(threshold>lower, threshold, lower),
  overlap, minUpper - maxLower,
  IF(overlap>0, overlap*TRANSPOSE(rate), 0))

It is based on the idea that the overlap between two ranges is the difference between the min upper value and the max lower value.

 

[pecoflyer]

Have a look at https://www.mcgimpsey.com/excel/variablerate1.html

 

[Peter Bartholomew]

Do you really need the line by line breakdown of commission by customer/opportunity. Each line of the calculation is more complicated than simply evaluating the total commission (or commission by band prior to adding)?

 

[Excel Wizard]

Please try at O24 for each customer

=SUMPRODUCT(TEXT(F24-E24*{0,1}-$B$11*{0;1;1.25},"0.00;\0")*($M$11:$M$13-N(+$M$10:$M$12))*{1,-1})


Total
=SUMPRODUCT(TEXT(SUM(E24:E33)-$B$11*{0;1;1.25},"0.00;\0")*($M$11:$M$13-N(+$M$10:$M$12)))

 

[zoltar73]

Please try at O24 for each customer

=SUMPRODUCT(TEXT(F24-E24*{0,1}-$B$11*{0;1;1.25},"0.00;\0")*($M$11:$M$13-N(+$M$10:$M$12))*{1,-1})


Total
=SUMPRODUCT(TEXT(SUM(E24:E33)-$B$11*{0;1;1.25},"0.00;\0")*($M$11:$M$13-N(+$M$10:$M$12)))

Thank you so much!!!! This works a treat , BIG thanks sir !!