Tiered Commission Formula

[sarahabb]

Hi Board,

I'm trying to create a formula to calculate a tiered commission schedule. The schedule is:
50% for up to $10,000
60% for $10,001-$15,000
70% above $15,000

So far I have figured out a formula that calculates all the commissions but I can't get it to stop taking the 50% after 10,000 or the 60% after 15,000. Please help!

thanks!
Sarah

 

[Aladin Akyurek]

Hi, I have a similar issue but cannot get the final formula adopted onto my scheme...

my comms structure has a threshold where no commission is paid, see below...

0-40000 - ZERO%
40001-100000 - 20%
100001-200000 - 25%
200001-300000 - 30%
300001-350000 - 40%
350001+ - 50%

E.G. if the sales were 250,000, the final figure would be:

40001-100000 = 12000
100000-200000 = 25000
200001-250000 = 15000
TOTAL 52,000

The field in question is F23 - any suggestions???

Many thanks in advance!

Create in A1:C7 on a sheet called Admin...

<table style="width: 164pt; border-collapse: collapse;" width="219" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 45pt;" width="60"><col style="width: 48pt;" width="64"><col style="width: 71pt;" width="95"></colgroup><tbody><tr style="height: 13.2pt;" height="18"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 45pt; height: 13.2pt;" class="xl63" width="60" height="18">threshold</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 48pt;" class="xl64" width="64">rate</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: transparent; width: 71pt;" class="xl63" width="95">differential rate</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">0</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl66" align="right">0.00%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">40000</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl66" align="right">20.00%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0.2</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">100000</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl66" align="right">25.00%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0.05</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">200000</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl67" align="right">30%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0.05</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">300000</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl67" align="right">40%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0.1</td></tr><tr style="height: 13.2pt;" height="18"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 13.2pt;" class="xl65" align="right" height="18">350000</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl67" align="right">50%</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65" align="right">0.1</td></tr></tbody></table>

Name A2:A7 ThresholdValues, C2:C7 DiffRates using the Name Box.

C2, copy down:

=B2-N(B1)

F2: 250,000

G2:

=SUMPRODUCT(--(F2>ThresholdValues), (F2-ThresholdValues), DiffRates)

 

[jamiew01]

Create in A1:C7 on a sheet called Admin...

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=219><COLGROUP><COL style="WIDTH: 45pt" width=60><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt" width=95></COLGROUP><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 45pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=60>threshold</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=95>differential rate</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>40000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>20.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.2</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>100000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>25.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.05</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>200000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>30%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.05</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>300000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>40%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.1</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 align=right>350000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>50%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.1</TD></TR></TBODY></TABLE>

Name A2:A7 ThresholdValues, C2:C7 DiffRates using the Name Box.

C2, copy down:

=B2-N(B1)

F2: 250,000

G2:

=SUMPRODUCT(--(F2>ThresholdValues), (F2-ThresholdValues), DiffRates)

Thanks Aladin, however this is simply giving me 'FALSE'. Im using excel 2007 which shouldn't make a difference for this formula surely... any suggestions?

 

[Aladin Akyurek]

Thanks Aladin, however this is simply giving me 'FALSE'. Im using excel 2007 which shouldn't make a difference for this formula surely... any suggestions?

You need create the table I suggested on a new sheet, named Admin, and name the ranges as suggested.

Otherwise, just try the equivalent:

=SUMPRODUCT(--(F2>{0;40000;100000;200000;300000;350000}), (F2-{0;40000;100000;200000;300000;350000}), {0;0.2;0.05;0.05;0.1;0.1})

where F2 houses 250,000. Note that the foregoing formula includes the table under discussion in a hard-coded form.

 

[jamiew01]

You need create the table I suggested on a new sheet, named Admin, and name the ranges as suggested.

Otherwise, just try the equivalent:

=SUMPRODUCT(--(F2>{0;40000;100000;200000;300000;350000}), (F2-{0;40000;100000;200000;300000;350000}), {0;0.2;0.05;0.05;0.1;0.1})

where F2 houses 250,000. Note that the foregoing formula includes the table under discussion in a hard-coded form.

the full formula worked a treat - thanks a lot for you help, much appreciated

 

[bsuperior]

Calculating tiered commission is not only based on how much you’re making like you get 60% for $10,001-$15,000. The time range plays a role in here too. For example, many companies only consider your annual sale. If you made between $10,001-$15,000 only in this year, you would fall in 60% tier. Also some companies drop their agents by one tier every year like if you’re in tier level 2 on last day of december, your tier level would be 1 on the first day of january. And other factors could complicate calculation like taxes and bonuses. As of my experience, you can’t implement these complicated calculation using only formulas. I would say you need to write some VBA. We actually made an Excel file to calculate tier and it’s a complicated calculation.