Tiered Commission Rates

[Baroda32]

I know this has been discussed before but I can't apply the solutions to my example. We have several different commission bands and each of those bands has a tiered commission rate e.g. Band1, First 0-10K = 1.95%, then from 10k to 20K = .8%, then all the rest is .5%. So what I am looking for is a formula that first of all looks at the band rate and then looks at the sale value and works out the commission based on these bands. I have a sample file but can't see how to upload it here. Any help gratefully received!

 

[Momentman]

You can upload it to dropbox, google drive e.t.c and post the link here

 

[joeu2004]

I know this has been discussed before but I can't apply the solutions to my example. We have several different commission bands and each of those bands has a tiered commission rate e.g. Band1, First 0-10K = 1.95%, then from 10k to 20K = .8%, then all the rest is .5%. So what I am looking for is a formula that first of all looks at the band rate and then looks at the sale value and works out the commission based on these bands. I have a sample file but can't see how to upload it here. Any help gratefully received!

Upload an example Excel file (removing or replacing any private ID data) to a file-sharing website, then post the URL of the "shared" file here. The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows Live Skydrive: http://skydrive.live.com

 

[Baroda32]

thank you - here is the link
Once the formula is worked out I'll have to also include a VLOOKUP to match the relevant code - there are approx 20 of them - thanks for any help!
https://drive.google.com/file/d/0ByknpuRVr2tSRGhTbDk1eTl3b2s/edit?usp=sharing

 

[joeu2004]

thank you - here is the link [....]
https://drive.google.com/file/d/0ByknpuRVr2tSRGhTbDk1eTl3b2s/edit?usp=sharing

That URL requires that I log into google.com and that you grant permission to access the file. I also wonder if that will be a Google spreadsheet, which is not the same as an Excel file.

What I had in mind is: upload a ".xls" or ".xlsx" file, make it publicly sharable (depends on the GUI), and provide the URL for that.

If done correctly, I should not need to log in in order to use the URL. And I should be able to either download the Excel file to my local computer (preferred) or at least open the file in my local Excel program.

You can test that yourself before posting the URL here.

 

[Baroda32]

sorry about that - I usually use Dropbox - hopefully this link should work!

https://dl.dropboxusercontent.com/u/35590039/Test Data for Tiered Commission.xlsx

 

[joeu2004]

hopefully this link should work!
https://dl.dropboxusercontent.com/u/35590039/Test%20Data%20for%20Tiered%20Commission.xlsx

Perfect! Some questions....

each of those bands has a tiered commission rate e.g. Band1, First 0-10K = 1.95%, then from 10k to 20K = .8%, then all the rest is .5%.

But I find the following in the Bands worksheet, for example:

[TABLE="class: grid, width: 162"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Band 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]1.95%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]0.8%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"] 100,000.00[/TD]
[TD="align: right"]0.5%[/TD]
[/TR]
</tbody>[/TABLE]

It seems that column A is the upperbound of the tier. But you wrote that the rate for "all the rest" over 20,000.00 is 0.5%.

So why do you have 100,000.00? Is there really an upperbound for the 3rd tier? Or is that just a mistake?

I assume the latter for now.

In Bands!A19, you wrote:
All bands have a minimum commission rate [sic] of £25

One interpretation is: if the computed commission is less than 25.00, award a commission of 25.00. But that would seem odd to me; for example, it seems odd that you would award a commission of 25.00 on sales of 25.00.

Instead, I assume you mean: if the calculated commission is less than 25.00, no commission is awarded. For Band 1, that means no commission is awarded on amounts under 1282.06 (25 / 1.95% rounded up). We can incorporate that into the tables below.

---

For the solution below, I suggest that you construct tables with the lowerbound in column A, not the upperbound, in the Bands worksheet.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Band 1[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"] 1,282.05[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.95%[/TD]
[TD="align: right"]1.95%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]0.80%[/TD]
[TD="align: right"]-1.15%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.30%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Band 2[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"] 2,499.99[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]1.00%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.50%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Band 3[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"] 1,428.57[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.75%[/TD]
[TD="align: right"]1.75%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]-0.75%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.50%[/TD]
[/TR]
</tbody>[/TABLE]

The formulas are:

A2: =ROUNDUP(25/C2,2)-0.01
A8: =ROUNDUP(25/C8,2)-0.01
A14: =ROUNDUP(25/C14,2)-0.01

B3: =A3
B4: =A4
B9: =A9
B15: =A15
B16: =A16

D2: =C2
D3: =C3-C2
D4: =C4-C3
D8: =C8
D9: =C9-C8
D14: =C14
D15: =C15-C14
D16: =C16-C15

Then the commission formula in Data!E2, for example, is:

=ROUND(CHOOSE(MATCH(C2,{"Band 1","Band 2","Band 3"},0),
SUMPRODUCT(--(D2>Bands!$A$2:$A$4),D2-Bands!$B$2:$B$4,Bands!$D$2:$D$4),
SUMPRODUCT(--(D2>Bands!$A$8:$A$9),D2-Bands!$B$8:$B$9,Bands!$D$8:$D$9),
SUMPRODUCT(--(D2>Bands!$A$14:$A$16),D2-Bands!$B$14:$B$16,Bands!$D$14:$D$16)),2)

MATCH returns the index 1, 2 or 3, which selects the 1st, 2nd or 3rd SUMPRODUCT expression.

Although the use of CHOOSE might look complicated, it might be the most efficient implementation because Excel calculates only the applicable SUMPRODUCT expression based on the MATCH result, not all of them.

But if you actually have a lot more bands, we might consider a very different implementation. Let me know.

For an explanation of the SUMPRODUCT expressions, see http://www.mcgimpsey.com/excel/variablerate.html.

But note the differences in the tables above. Column A is the lowerbound for the amount that earns commission. Column B is the lowerbound for the commission calculation. They differ only in the first row of each table.

 

[Baroda32]

Thank you joeu2004 - I am working through your response now. I had some degree of success at 3am this morning! You are correct in your assumption. I adjusted my table to have 4 columns, Low Threshold, High Threshold, Commission, and Differential. There is commission on everything. In theory there should not be any instance where the commission is less than the consideration - these are share deals. So I need to build in something that says if the answer is less than £25 then put £25 but you have probably mentioned this further down in the reply!

I have about 22 bands in total and there are quite a few that have identical tiering but not all - the third tier isn't always .5%.

This is what I have done:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Low[/TD]
[TD]High[/TD]
[TD]Rate[/TD]
[TD]Diff[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]10,000[/TD]
[TD]1%[/TD]
[TD]1%[/TD]
[/TR]
[TR]
[TD]10,000[/TD]
[TD]20,000[/TD]
[TD].5%[/TD]
[TD]-.5%[/TD]
[/TR]
[TR]
[TD]20,000[/TD]
[TD]50,000[/TD]
[TD].35%[/TD]
[TD]-.15%
[/TD]
[/TR]
</tbody>[/TABLE]

Assuming the columns are A, B, C and D

The formula in D2 is =C2
The formula in D3 is =C3-C2
The formula in D4 is =C4-C3

In theory there is no upper band - commission is charged on everything - I didn't know that you could leave it blank! I teach Excel but I learn every day!

I then used the following formula:

=SUMPRODUCT(--(CommCharge>$A$2:$A$4),(CommCharge-$A$2:$A$4),$D$2:$D$4)

CommCharge = cell ref where I have typed the commission (I haven't named the range - it is of course a variable reference). So this gave me the correct commission but I then had to go and filter all those less than £25 and adjust them. I basically did a Pivot Table to create extra sheets for each commission band and just adjusted the table - a long way around I now but got around the problem for now but I am going to work through your suggestions to see if can all be kept in one or two formulas and on the same sheet.

I am going to grab myself a cup of tea and try out your suggestions for which I thank you.

 

[joeu2004]

I have about 22 bands in total and there are quite a few that have identical tiering

In that case, my use of CHOOSE is not a good one. It is feasible; but the formula is long and tedious to write, even one time.

I will consider a different organization of the tables that lends itself to a single SUMPRODUCT formula. However, I think it is important to avoid volatile functions like OFFSET and INDIRECT in this case.

 

[Baroda32]

Thanks - I spent so long on it in the early hours of this morning and then again earlier that I am having a break from it at the moment as I often find "Fresh Eyes" give you better ideas. I have taught Excel for years but am always finding out new things. I want to try and get this down to 1 or 2 formulas to reduce the margin of error and then it can be adapted for other users that I know may have a similar requirement.