# Sales commission spreadsheet with variable commission rates

##### New Member
Hello,

I've been hunting around and can't find the answer to my problem anywhere. Here is the commission spreadsheet I want to make for sales staff:

Sales staff have a software sales target (eg \$500,000). Cumulative on sales through the year they receive:

- 2.5% on sales up to 25% of their target,
- 5% on sales between 25% and 75% of their target,
- 7.5% on sales between 75% and 100% of their target,
- 10% on sales over 100% of target.

Simple enough (ish) using LOOKUP right? I used the following tutorial and nearly got there Excel Magic Trick 673: Varying Commission Rate Lookup Formula SUMPRODUCT Amazing Solution!! - YouTube But it gets more complicated! Sales are made up of 'services' sold and 'licences' sold. The percentage commission stated above relate to 'licences' sold when the TOTAL sale is between the relevant percentages of target. All services are paid at 3% commission. Do you see the difficulty?

For example, if a sales member sells \$50,000 in January where \$10,000 is services and \$40,000 is licences, they would receive 3% of the \$10,000 and 2.5% of the 40,000 because the total number (\$50,000) is less than 25% of their annual target of \$500,000. If the same sales rep sells £100,000 later in January or later in the year where \$50,000 is services and \$50,000 is licences, they would receive 3% of \$50,000 for the services sold but the licences is a bit more difficult. The total sales for the year are now \$150,000 which is 30% of their annual target. Therefore, they receive 5% of licences on all amounts over a total of 25% of target - 5% on \$25,000 and 2.5% on new licences for the remaining amount below the 25% threshold - 2.5% on \$25,000.

Can anyone help me at all? I'd like to be able to simply enter the total sale amount and the split between services and licences and the commission be calculated. I could run this monthly or quarterly. Please let me know if you would like a working spreadsheet that I already have downloaded from the link above.

Many thanks,

Andy.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
as I can understand

Sheet1

 * A B C 1 target actual commission 2 50000 60000 4000

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula C2 =(B2*25%)*2.5%+((B2*75%)-(B2*25%))*5%+((B2-(B2*75%))*7.5%)+(B2-A2)*10%

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hello,

Thanks for your help but it needs to be a little more complicated I'm afraid. Your spreadsheet works very nicely when the commission is paid at the same variable rate for the whole of the sale. My difficulty is that the percentages you've used above are paid on only part of the sale - the licences sold. The services sold are paid at a flat 3%. But, the commission calculation for the licences (e.g up to 25% of target being 2.5%) is based on the TOTAL sold.

I've just realised that I can't attached a spreadsheet to this for anyone to see. Can that be true??

you can post data in this newsgroup, for which you have to add and addin to your excel. see under sticky in thread list."recommended addins" -- Excel Jeanie

Thank you. I'll try posting my spreadsheet below:

Excel 2003
ABCDEFGHIJKLM
1HurdleRateServices Rate
2
302.5%3%
4125,0005.0%
5375,0007.5%
6500,00010.0%
7
8JanFebMarAprMayJunJulAugSepOctNovDec
9Total Sales (Licences & Services)30,000.0060,000.0020,000.0050,000.0095,000.0060,000.0060,000.0030,000.0095,000.0075,000.0020,000.0050,000.00
10
11Services proportion10,000.0010,000.0010,000.0015,000.0025,000.0010,000.005,000.000.0055,000.0030,000.005,000.0020,000.00
12Formula 1750.001,500.00500.002,125.004,750.003,000.003,000.002,250.007,125.007,500.002,000.005,000.00
13
14
15
16cum30,000.0090,000.00110,000.00160,000.00255,000.00315,000.00375,000.00405,000.00500,000.00575,000.00595,000.00645,000.00
Calc
Cell Formulas
RangeFormula
B12=SUMPRODUCT(--(SUM(\$B9:B9)>=\$A\$3:\$A\$6),SUM(\$B9:B9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:A12)
C12=SUMPRODUCT(--(SUM(\$B9:C9)>=\$A\$3:\$A\$6),SUM(\$B9:C9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:B12)
D12=SUMPRODUCT(--(SUM(\$B9:D9)>=\$A\$3:\$A\$6),SUM(\$B9:D9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:C12)
E12=SUMPRODUCT(--(SUM(\$B9:E9)>=\$A\$3:\$A\$6),SUM(\$B9:E9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:D12)
F12=SUMPRODUCT(--(SUM(\$B9:F9)>=\$A\$3:\$A\$6),SUM(\$B9:F9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:E12)
G12=SUMPRODUCT(--(SUM(\$B9:G9)>=\$A\$3:\$A\$6),SUM(\$B9:G9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:F12)
H12=SUMPRODUCT(--(SUM(\$B9:H9)>=\$A\$3:\$A\$6),SUM(\$B9:H9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:G12)
I12=SUMPRODUCT(--(SUM(\$B9:I9)>=\$A\$3:\$A\$6),SUM(\$B9:I9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:H12)
J12=SUMPRODUCT(--(SUM(\$B9:J9)>=\$A\$3:\$A\$6),SUM(\$B9:J9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:I12)
K12=SUMPRODUCT(--(SUM(\$B9:K9)>=\$A\$3:\$A\$6),SUM(\$B9:K9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:J12)
L12=SUMPRODUCT(--(SUM(\$B9:L9)>=\$A\$3:\$A\$6),SUM(\$B9:L9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:K12)
M12=SUMPRODUCT(--(SUM(\$B9:M9)>=\$A\$3:\$A\$6),SUM(\$B9:M9)-\$A\$3:\$A\$6,\$B\$3:\$B\$6-\$B\$2:\$B\$5)-SUM(\$A12:L12)

Good, posting the spreadsheet worked (above). So you can see that the row 'formula 1' is calculating the commission based on the amount sold and the variable percentages using the data in row 9. What I want it to do now is also consider the 'services' sold in row 11. Services are paid at 3% commission, a flat rate. The 2.5%, 5%, 7.5% and 10% commission relates only to the licences sold (calculated as total sold minus services sold) and those percentages are applicable when the TOTAL (services and licences combined) is at the amounts specified (up to 125,000 at 2.5%, 1250,000 - 375,000 at 5%, 375,000 - 500,000 at 7.5%, over 500,000 at 10%).

I hope that's better explained now. Anyone got any ideas?

Can anyone help with this?

Anyone got any ideas how to do this?

i am looking similar one, if any has readily available please share with me or mail me happytitus@gmail.com

N.B. The named range rRate is the rate differential.
Row 13 Calculate the commission based on the total

=SUMPRODUCT(--(SUM(\$B\$9:B9)>rB),SUM(\$B\$9:B9)-rB,rRate)-SUM(\$A\$13:A13)

The percentage of sales License/Total is the License comm line 20,000/30000 * total line 13
3% of Services = service commission line
Sum of the two lines is the total commission

Replies
1
Views
624
Replies
1
Views
398
Replies
7
Views
867
Replies
11
Views
823
Replies
0
Views
401

1,203,430
Messages
6,055,331
Members
444,781
Latest member
rishivar

### 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.

### Which adblocker are you using?

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