Interesting Commission Tier Calculator Wrinkle

liter123

New Member
I've been looking through multiple posts and am familiar with utilizing SUMPRODUCT to calculate commission tiers that pay out different rates based on sales thresholds.
As an example: Pay 10% from \$0-\$99; 15% from \$100-\$199; 20% from \$200-\$299

We have a new wrinkle in the comp plans this year that requires different rate payouts for each sale. Each now has a recurring component and a miscellaneous component that are paid at different rates (in addition to taking the tiers into account). I can't figure out how to accommodate it. Here's an example:

The more straightforward part first. Commission is paid out on a monthly basis. There is a running cumulative total for sales that is to be paid out based on the following tiers:
• Tier 1 (14% Rate) \$0.00
• Tier 2 (25% Rate) \$196,000.00
• Tier 3 (40% Rate) \$392,000.00

So if \$190,000 is closed in January, commission is paid at 14%. Then with another \$100,000 closed in February, the running total is now \$290,000.00. \$6,000.00 is paid at 14% and \$94,000 is paid at 25%, etc for March, April, May, and so on.

The wrinkle is with the 2 components applied to every sale. #1 ) Recurring (to be paid at the tier rates ie. 14%, 25%, 40%) #2 Miscellaneous at 5%.

So now if you close \$190,000 in January the breakdown components could be \$150,000 as Recurring paid at 14%, and \$40,000 as Miscellaneous paid at 5%. Once in February, The \$100,000 closed at \$80,000 Recurring and \$20,000 Miscellaneous, would still pay the \$20,000 at 5%, but the \$80,000 would have to take into account the old tier of 14% and the new one of 25%. The way the different from Tier 1 to Tier 2 of \$6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February, chopping up the \$6,000 as 80% (paid at 14%) and 20%, then moving into Tier 2 with the 25% payout number for the remaining amount.

It's pretty confusing. I'd like to hopefully find a way to manage this with Excel. Thank you!

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome to the forums. Can you please copy/paste from excel an example of your data in terms of what it looks like and also what you want your results to be?

I assume that the tier breakpoints are based on total sales, not the 80% allocated to "recurring" commission.

The following demonstrates one design.

ABCD
1RecurringMisc
2Allocation80%20%
3
4Misc rate5%
5Recurring rate
6Sales >=...RateDiff rate
7\$0.0014.00%14.00%
8\$196,000.0025.00%11.00%
9\$392,000.0040.00%15.00%
10
11SaleRecurring
Comm
Misc
Comm
12Jan\$190,000.00\$21,280.00\$1,900.00
13Feb\$100,000.00\$19,472.00\$1,000.00
14Mar\$200,000.00\$51,760.00\$2,000.00

<tbody>
</tbody>
Code:
``````Formulas
C7: =B7 - N(B6)
Copy C7 into C8:C9

C12: =ROUND(\$B\$2*SUMPRODUCT((SUM(\$B\$12:B12)>=\$A\$7:\$A\$9)*(SUM(\$B\$12:B12)-\$A\$7:\$A\$9),\$C\$7:\$C\$9) - SUM(\$C\$11:C11), 2)
D12: =ROUND(B12*\$C\$2*\$B\$4, 2)
Copy C12:D12 into C13:D14``````

The SUMPRODUCT expression calculates the cumulative commission based on cumulative sales. But only 80% applies to "recurring" commissions, less the sum of the previous monthly "recurring" commissions.

20% of the monthly sales earns commissions at the "misc" rate.

To demonstrate the correctness, the following produces the same results based on manual calculations.

Code:
``````Jan 190,000       Recurring                     Misc
---------------------------   ------------------------
Tier1 = 190,000   *80% = 152,000*14% = 21,280   *20% = 38,000*5% = 1,900

Feb 100,000                           Recurring                    Misc
Cuml  = 100,000 + 190,000 = 290,000   --------------------------   ------------------------
Tier1 = 196,000 - 190,000 =   6,000   *80% =  4,800*14% =    672   *20% =  1,200*5% =    60
Tier2 = 290,000 - 196,000 =  94,000   *80% = 75,200*25% = 18,800   *20% = 18,800*5% =   940
-------          ------       ------          ------      -----
100,000   *80% = 80,000       19,472   *20% = 20,000*5% = 1,000

Mar 200,000                           Recurring                     Misc
Cuml  = 200,000 + 290,000 = 490,000   ---------------------------   ------------------------
Tier2 = 392,000 - 290,000 = 102,000   *80% =  81,600*25% = 20,400   *20% = 20,400*5% = 1,020
Tier3 = 490,000 - 392,000 =  98,000   *80% =  78,400*40% = 31,360   *20% = 19,600*5% =   980
-------          -------       ------          ------      -----
200,000   *80% = 160,000       51,760   *20% = 40,000*5% = 2,000``````

Note that you were wrong about the 80/20% amounts for Jan: \$152,000 instead of \$150,000; \$38,000 instead of \$40,000.

Last edited:
The way the different from Tier 1 to Tier 2 of \$6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February
Note that you were wrong about the 80/20% amounts for Jan: \$152,000 instead of \$150,000; \$38,000 instead of \$40,000.

No: my mistake. I did not notice that the allocation between "recurring" and misc might vary each month.

The following takes that into account.

 A B C D E F 1 Misc rate 5% 2 Recurring rate 3 Sales >=... Rate Diff rate 4 \$0.00 14% 14% 5 \$196,000.00 25% 11% 6 \$392,000.00 40% 15% 7 8 Sales Recurring Allocation Misc Allocation Recurring Comm Misc Comm 9 Jan \$190,000.00 \$150,000.00 \$40,000.00 \$21,000.00 \$2,000.00 10 Feb \$100,000.00 \$80,000.00 \$20,000.00 \$19,472.00 \$1,000.00 11 Mar \$200,000.00 \$40,000.00 \$160,000.00 \$12,940.00 \$8,000.00

<tbody>
</tbody>

Formulas:
Code:
``````C4: =B4 - N(B3)
D9: =ROUND(B9 - C9, 2)
E9: =ROUND((SUMPRODUCT((SUM(\$B\$8:B9)>=\$A\$4:\$A\$6) * (SUM(\$B\$8:B9)-\$A\$4:\$A\$6), \$C\$4:\$C\$6)
- SUMPRODUCT((SUM(\$B\$8:B8)>=\$A\$4:\$A\$6) * (SUM(\$B\$8:B8)-\$A\$4:\$A\$6), \$C\$4:\$C\$6)) * C9/B9, 2)
F9: =ROUND(D9*\$B\$1, 2)
Copy C4 into C5:C6
Copy D9:F9 into D10:F11``````

The SUMPRODUCT expression is the difference between the current month and previous month total commissions (not prorated). The difference is prorated by the "recurring" allocation factor.

To minimize quantization "error" due to rounding, I would record the "recurring" and misc allocation amounts, not their percentages -- although you might calculate one as a percentage of sales, explicitly rounding. But the other (misc, in my case) should be calculated by the difference, again to minimize quantization error.

(The purpose of rounding in D9 is to avoid binary arithmetic anomalies: infinitesimal "errors" that arise because most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses to represent numeric values internally.)

The following manual calculations produce the same results.

Code:
``````Jan 190,000       Recurring              Misc
--------------------   ------------------------
Tier1 = 190,000   150,000*14% = 21,000   40,000*5% = 2,000

Feb 100,000                           Recurring                             Misc
Cuml  = 100,000 + 190,000 = 290,000   -----------------------------------   ---------------------------------
Tier1 = 196,000 - 190,000 =   6,000   *80000/100000 =  4,800*14% =    672   *20000/100000 =  1,200*5% =    60
Tier2 = 290,000 - 196,000 =  94,000   *80000/100000 = 75,200*25% = 18,800   *20000/100000 = 18,800*5% =   940
-------                   ------       ------                   ------      -----
100,000                   80,000       19,472                   20,000*5% = 1,000

Mar 200,000                           Recurring                              Misc
Cuml  = 200,000 + 290,000 = 490,000   ------------------------------------   -----------------------------------
Tier2 = 392,000 - 290,000 = 102,000   *40000/200000 =  20,400*25% =  5,100   *160000/200000 =  81,600*5% = 4,080
Tier3 = 490,000 - 392,000 =  98,000   *40000/200000 =  19,600*40% =  7,840   *160000/200000 =  78,400*5% = 3,920
-------                    ------       ------                    -------      -----
200,000                    40,000       12,940                    160,000*5% = 8,000``````

(Re: my Mar example.... I suspect that "recurring" allocation is always greater than misc. But I made it less for demonstration purposes, since it exposed a design flaw in an intermediate solution of mine.)

Replies
9
Views
492
Replies
1
Views
613
Replies
13
Views
1K
Replies
1
Views
160
Replies
3
Views
958

1,202,905
Messages
6,052,481
Members
444,586
Latest member
Godtymer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back