Excel Formula to Calculate Commissions with Tiered Rate Structure - Excel Campus
79

Excel Formula to Calculate Commissions with Tiered Rate Structure

In this post I will explain how to calculate a dollar or percentage commission payout in one cell using the SUMPRODUCT function.  Calculating commissions on a tiered rate structure can be difficult because you are trying to determine the cumulative payout based on different rates at each tier, and the achievement amount might fall in between one of the tier ranges.

If your commission plan tiers are not cumulative, then you might want to checkout my article on how to calculate commissions with VLOOKUP.  This is a simpler calculation then the one presented below.

Tiered Rate Table

The following is an example of a tiered rate table for sales commissions.  The first column contains the tiered ranges of Quota Attainment and the second column contains the Payout % for each tier.  If the sales person (rep) achieves sales that are at the top end of each tier, then they will receive the full payout amount in the Total Payout column.  For example, if the rep sells 40% of their quota then they will receive 20% of their commission.  If they sell 60% of quota, they will receive 35%, and so on down the table.

Tiered Rate Structure Table - Basic

The difficult part is when the attainment amount falls in between the ranges.  What if the rep sells 50% of his/her quota?  The rep would receive 20% payout for the first 40% of quota, and an additional 7.5% payout for the last 10% of quota.  The last 10% of quota attainment is calculated by finding the payout rate at each tier.  So the total payout on 50% of quota would be 27.5%.

Typically we would have to calculate the payout at every tier and then sum the payout amounts to get the total amount.  Or we could use some complicated IF statement to determine the payout all in one formula.  But there is an easier way…

The Solution

You can download the sample workbook below to follow along.

The SUMPRODUCT function can be used to calculate the entire payout.  First we have to calculate the differential payout rate for each tier.  The differential rate is the difference between the payout rates at each tier.

The payout rate at each tier is the total percentage of payout in the tier, divided by the total percent of attainment possible in the tier.  This is basically the amount paid for each percentage increase in attainment in each tier.  In the image below the Payout Rate for the 0%-40% range is 0.50.  This means that for every 1% attained, the payout will be 0.50 of the 20% total payout.

Payout Rate =([tier attainment max] – [tier attainment min]) / ([payout % this tier] – [payout % previous tier])

Tiered Rate Structure Table - Payout Rate Formula

Rate Curve

The payout rate is also known as the rate curve.  The rate curve is displayed below, and you can think of the payout rate as the slope of the curve at each tier.

Tiered Rate Structure Table - Rate Curve

Differential Rate

The differential rate is calculated by taking the difference between the payout rate in the current tier and the payout rate in the previous tier.

Differential Rate = [payout rate Current tier] – [payout rate Previous tier]

Tiered Rate Structure Table - Differential Rate Formula

This is used in the cumulative calculation of the payout percentage.  As the attainment moves up into multiple tiers, the amount of attainment left in each tier is multiplied by the differential rate.  The sum of all these is the total payout.

SUMPRODUCT Explained

The SUMPRODUCT formula for Total Payout is:

=SUMPRODUCT( (Attainment > [Tier Min]) * (Attainment –  [Tier Min]) * [Differential Rate] )

Variables in brackets [] refer to entire column in rate table.

Tiered Rate Structure Table - SUMPRODUCT Formula

The following splits the SUMPRODUCT formula into multiple columns and rows for a clearer visual of how the formula is calculating the total payout.

  1. Attain > Tier Min: Returns a “1” if the attainment is greater than the attainment tier minimum.  If attainment is 90%, then the condition is true for the first 4 rows and a value of 1 is returned.
  2. Attain – Tier Min: Finds the difference between the total attainment and attainment tier minimum. This is necessary because we are multiplying it by the differential rate.  So in the first row we are taking the entire attainment of 90% and multiplying it by the diff rate of 50%.  Each subsequent row is taking the leftover attainment for its tier range, and multiplying it by the payout rate that is leftover for its tier (the diff rate).
  3. Diff Rate: The differential rate for each tier.
  4. Product: Column 1 * Column 2 * Column 3
  5. Sumproduct: Sum of the Product column.  The total payout on 90% attainment is 87.5%

Tiered Rate Structure Table - SUMPRODUCT Explained

SUMPRODUCT Visualization

The following is a visual example of the Product column plotted on the rate curve.  Sometimes it is easier to understand when you see it visually.  The SUMPRODUCT formula finds the total payout in each tier based on the remaining balance of attainment multiplied by the payout rate in that tier.  This is basically a continuation of the rate curve at each tier to the total attainment of 90%.  In the chart below you can see that the dark grey lines follow the rate curve at each tier and then continue on the same curve to the 90% attainment (green) line.

Tiered Rate Structure Table - Product Point Rate Curve

  1. For the first tier it is (90%-0%) * (50%-0%) = 45%.  50% is the payout rate for tier 1, and 90% is the total attainment.
  2. The second tier is (90%-40%) * (75%-50%)  = 12.5%.  The 90%-40% is the remaining balance of attainment for tier 2, which is 50%.  45% (tier 1) + 12.5% (tier 2) = 57.5%
  3. Tier 3 is (90%-60%) * (200%-75%) = 37.5%.  The sum of the first three tiers is 95% (45%+12.5%+37.5%), which is higher than the actual payout of 87.5%.
  4. Tier 4 is (90%-80%) * (125%-200%) = -7.5%.  The negative product is a result of the payout rate being less in tier 4 than tier 3.  Or, the slope of the rate curve is flatter in tier 4 than tier 3.  That brings the total payout back down to 87.5%.

Negative Differential

This negative differential rate in tier 4 is important to note.  Not only does it make for a confusing calculation, it also tells you that the rate of compensation is not as great in tier 4.  For each additional percentage point of attainment, the sales rep is compensated at a lower rate than tier 3.  This might mean that there is more emphasis for the rep to attain sales on their quota in tier 3.  And the monetary motivation is not as great for attainment in tier 4.

Negative differential can also mean that the rate curve is poorly designed.  If the goal is to achieve 100% attainment of quota, then it is probably best to increase the payout rate (rate curve slope) in each tier up to 100%.  This design would give the rep more motivation (higher payout rate) as he/she gets closer to achieving their goal of 100% attainment.

Apply Formula to a List

Now that we are able to calculate the payout in one cell using one SUMPRODUCT formula, we can apply the formula to a whole list of employees in a table.  See the Total Payout % column  on Sales Table tab of the example file.  This is the major benefit of this formula.  The entire calculation can be handled in one cell and it is easily transferable to other models.  There are no hard-coded variables in your formula, or ugly IF statements.

Download

Tiered Commission Rates using SUMPRODUCT.xls (101.4 KB)

Here is a file that uses whole number (units or dollars) for the tiers and payouts, instead of percentages.

Commission Plan SUMPRODUCT Units Dollars.xlsx (10.6 KB)

Here is an alternate solution submitted by Matthew Burgos using VLOOKUP instead of SUMPRODUCT.  He explains the formula in detail in the comment below.

Tiered Commission Rates Using VLOOKUP.xlsx (12.1 KB)

Still Confused? 🙂

I was too the first time I learned this technique.  It’s definitely complex.  I’d recommend reviewing it a few times with sample workbook and then try to implement it in your own model.

I have another article on how to calculate commissions with VLOOKUP that is an easier calculation for a simple commission plan.

Please leave a comment below with any questions/comments about this technique.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 79 comments
Bonnie - November 14, 2017

I have a tired commission as follows
Commission is based on sales for instance if I sell $1,000,000 -$10,000,000 the commission is 2.5%. I subtract a 6% fee off the top and then pay the company on a tired commission as follows:
$0.00-$15,000. 40%
$15,001-$22,500. 35%
$22,501-$30,000. 30%
$30,000-??? 25%

These commissions are paid on the total sales minus 6% (I hope I am making sense…

I am trying to build a spread sheet (first time ever working on excel) and I am using your work sheet that is the tiered commission in dollars but when I try to calculate the pay out amount paid to the company, I get the payout rate but am not sure how to get the dollar amount based off of total sales when payout rates vary and at every level the commission % can adjust. I was hoping for a template to plug in all my numbers and it would calculate this for me. I am missing a step (or 10) that is not on the work sheet . Sorry, I obviously have no training in math and have never written a formula so I hope you can help

Reply
Dawn A - September 22, 2017

Hi there I know this is different than commissions but my question is for a similar type of report. I am making a report for my daughters PTA that calculates the donations made for an event. For every $40 donated their name goes into a drawing for prizes. For example a student has $90 in donations they would have 2 entries. Is there a way to calculate this and show the names and how many entries each student has? Thanks for your help.

Reply
Cathy - September 11, 2017

Hi John
I have a attainment scale below. How would I set up a formula to capture the payout target for a percentage within the range. thanks so much for your help
Target Payout of Target
95% 50%
97% 75%
100% 100%
105% 125%

Reply
S K - August 15, 2017

Dear Sir,
I have a problem where the sales incentive is to be calculated based on Payment Collection. The problem is the say for example for Customer X, there are 5 invoices raised in this month… the customer pays back in different amounts which are not equal to any single invoice. Hence the recovery against 1st invoice may be spread in 3 payments & there would be some carry forward amount which will be set-off against the 2nd invoice.

The incentive needs to be based on the average time taken to clear payments against invoice’s.

Please help how this could be automated with excel.

Reply
Natalie - July 30, 2017

This rocked!! Thank you so much for devising and sharing this formula! You saved me hours of head-scratching. 🙂

Reply
Jorge B. - May 5, 2017

Hi Jon.

Your explanations are very useful and helped me a lot with several encountered problems.

I was trying to understand if I could use Index and Match; If or SUMProduct for the following example:

I have 5 columns

1)column: A1 – Salary

2)column
E1- Salary Range(Min) $2500
E2- Salary Range(Min) $2600
E3- Salary Range(Min) $2700

E65- Salary Range(Min) $11000

3)column:
F1-Salary Range(Max) $2550
F2- Salary Range(Min) $2650
F3- Salary Range(Min) $2750

F65- Salary Range(Min) $11050

4) column
G1 – 0,01%
G2 – 0,02%
G3 – 0,03%

G65 – 0,65%

5)column
H1 – final value

What I need to do is to calculate for the cell “H1” the salary “A1” with the respective percentage “G1” accordingly to salary range found between “E1” and “F1”. I tried to do with different formulas that you mentioned before but the results are always #Value or #N/A.

Could you help me, please?

Best Regards,
Jorge

Reply
Trish - April 11, 2017

I have been trying for a week and few days to figure this out Jon
I am getting fustrated. I need to have this complete by tomorrow
using the IF method
I can’t seem to get the grasp of it.
Can you pls help me figure this out
Thank you
Trish
Winnipeg, Canada
Great Work!

If Sales Rep Sales are 15,695
I need to break this down into the various
Sales Percentage
I must be able to put any Sales number in to calculate the
correct Commission:

$0-$499 = 4%
$500-$1999 = 20%
$2000-$4999 = 22%
$5000-$9999 = 26%
$10,000 (over) = 28%

I have not been able to sleep trying to figure this out
would be wonderful to have completed
Thank you in Advance

Love your Website! Just started using Excel (Newbie) and your workbooks are very easy to follow! I will keep on recommending
your site!

Thank you soooo much!

Reply
    Jon Acampora - April 18, 2017

    Hi Trish,
    Thanks for the nice feedback. I’m not sure I understand your plan. If it is a flat rate, then you can use a Vlookup formula to calculate commissions. If the plan is cumulative, then you can use the SUMPRODUCT formula presented on this page.

    What should the result of the commission for 15,695 be if you calculated it manually? Thanks!

    Reply
Grace - April 1, 2017

Hi Jon,

This excel sheet is great, but can I ask if I can use it for the sample structure below:

Attainment Low Attainment High Commission Rate

0% 100.00% 0
100.00% 124.99% 6
125.00% 149.99% 8
150.00% Over 10%

For example:

Salesman 1:

Target is 10,000 and attainment is 16,000 so technically its 160%
at 10,000 there will be no commission

2,499 = 149.94
2,000 = 160.00
1,501 = 150.01

Total commission is 460.04

how can I do it similarly to that sheet with this calculations?

appreciate your help thank you!

Reply
Sanjeep Verma - February 18, 2017

Hi Jon,

I just visit this page of yours
https://www.excelcampus.com/modeling/calculate-commissions-with-tiered-rate-structure/

Where I found the below formula for tier payout calculation as shown below. I wanted to know if the same result can be achieved using Vlookup as well? or by any other excel function (not a big ugly formula though) ?

=SUMPRODUCT( (Attainment > [Tier Min]) * (Attainment – [Tier Min]) * [Differential Rate] )

Reply
    Jon Acampora - February 28, 2017

    Hi Sanjeep,
    For this type of cumulative commission structure, this is probably the best way to calculate it. If the commission plan is simple, you can use a Vlookup to lookup the single payout rate from a table.

    Reply
      dbiggs - June 16, 2017

      My problem is much easier than this but I’m having trouble with it. Usually I would use a nested if function, but I have 10 ranges, so that seems like a poor way to construct the formula.

      How would you use the vlookup formula if your value falls within a range?

      I simply want to find the range my value falls between and return a value from the table. The table has 3 columns. low end of range, high end of range and value to be returned. A value of 176 would return 1. Anything less than 150 returns 0

      ColA ColB ColC
      151 200 1

      Reply
Scott - January 18, 2017

Great detail and effort here (thank you), however, the sample file does not contain the same calculations as in your explanation (missing a few columns) AND the sample file returns an incorrect figure. (i added it up manually to check it… its off by $40 with the numbers given in the download)

I tried the formula on my own table too and get odd results as well (including a value of $0 when “1” is entered as the number of sales units).

I’m trying to calculate a Commission Due on the following table:
1-999 units sold = $46.50 per unit commission
1,000-2,499 units sold = $62 per unit commission
2,500-4,999 units sold = $77.50 per unit commission
5,000-9,999 units sold =$93 per unit commission
10,000+ = $108.50 per unit commission

So, sell 1 unit and you earn $46.50. Sell 999 units and you earn $46,453.50

Sell 1,500 units and you earn $77,515.5 ($31,062 for the 501 units in tier 2 + $46,453.50 for the 999 in tier 1)

Also, I assume I could have the values in my table generated by another formula vs. just using a static number?

Thanks for your help!!!

Reply
    Jon Acampora - January 20, 2017

    Hi Scott,

    I believe you are referring to the example file named “Commission Plan SUMPRODUCT Units Dollars.xlsx”? If so, you are right. The Tier Min for the first tier should be 0. I updated the file in the downloads section.

    The calculation was only off by $0.25. Here is how it breaks down.

    1,000 * 0.25 = 250.00
    4,000 * 0.20 = 800.00
    2,500 * 0.10 = 250.00

    The numbers in the Tier Max column might be a little misleading for this example. They are not actually used in the SUMPRODUCT formula. I updated those in the file as well.

    If you wanted the tier to be 1-999 then you would put 999 as the tier min for the second tier.

    I hope that helps. Let me know if you have any questions. Thanks!

    Total Commission = 1,300.00

    I hope that helps.

    Yes,

    Reply
Tim Kirk - October 31, 2016

Hi Jon,

Great post! It so nearly covered what I was looking for…

How could I make this work if the sales guy was paid on each deal where the percentage changed based on the total for the year?

e.g. in this scenario they are paid 8.5& of each deal until their annual total reaches 200k, 13% of each deal until their total reaches 350k and 15% of each deal for anything over 350k.

This would be simple if it wasn’t likely that the sales guy could be on 175k and score a 100k deal. They should get paid 8.5% of the first 25k and then 13% of the remaining 75k.

I just can’t figure it out…

You would be doing me a huge favour if you could help as that sales guy is me!!

Reply
    Jon Acampora - November 1, 2016

    Hey Tim,

    These types of plans are always difficult to calculate. I have another file in the downloads section titled, “Commission Plan SUMPRODUCT Units Dollars.xlsx” that has an example of how to calculate this with dollars instead of percentage tiers. Hopefully that will help. Thanks!

    Reply
      Kamil Singh - November 16, 2016

      Hi Jon,

      Let say that for each salesperson, there is a cap on commission that they can earn. I can put each salesperson cap in a cell and compare each salesperson earning to that cell by using max to figure out the payout.

      But it is not the best. What formula I can use to make sure that their payout is not higher than their max limit.

      Thanks,

      Reply
BASAVARAJU V - October 15, 2016

Dear sir,

I need formula and how to the calculate quarterly sales incentives for below mentioned slabs

Slabs 80% to 100% – 1%
101 to 124 -2%
Above 126% 3%

Target for achievement – 30,00,000.00
Ac hived target – 40,00,000.00

Thanks

Reply
Michele - October 14, 2016

Hi – I need to create a calculator to show employees earning a percentage of their gross billings, by tier. I am struggling with how to apply these concepts to dollar amounts instead of percentages. As an example, an employee has gross billings of $25,000. They earn 50% on their billings under $10k (so $9,999), 60% of billings from 10,000 – 14,999, and 70% of 15,000 – 25,000. How would you tackle this?

Reply
    Jon Acampora - October 22, 2016

    Hi Michele,

    There is a file in the downloads section above that has an example of how to use this method for dollars.

    Commission Plan SUMPRODUCT Units Dollars.xlsx

    There is also a link to the explanation under the file download link. I hope that helps. Thanks!

    Reply
gayathri - October 3, 2016

Hi,

I need a excel formula to separate management fee amount in actual amount
for Example

Rs.1000/- is actual amount
5% management fee percentage
i want to separate management fee amount in 1000

Why because in 1000 management amount also included.

i need to separate management fee amount from 1000 rupees.

how can we do that help on this pls

Reply
wissam - September 24, 2016

hi,
how can you help by doing an after sales bonus and incentive scheme to be paid monthly for the employees.

how can we use the pivot table

thank you,

Reply
TERRANCE - September 15, 2016

Hi,

I am trying to create a commission tier structure but with a fixed value such as £50,75,100 & 125. would i be able to change the % numbers to normal numbers?

so basically my tiers go from unit sales 0-7, 8-12, 13-17, 18+ and you would £50 for every sale you get up to 7, £75 up to 12, £100 up to 17 and £125 for every unit over 18.

for example if you get 11 sales, you would get 7 * £50 & 4* £75.

Reply
    Jon Acampora - September 27, 2016

    Hi Terrance,

    Great question! There is a file in the downloads section above named “Commission Plan SUMPRODUCT Units Dollars.xlsx” that has a similar solution for this. I don’t have an article that explains it in detail yet. However, the calculation is similar to the formula with percentages. The differential rate is just calculated differently. I hope that helps.

    Reply
sandeep - May 17, 2016

I’m trying to work out commission structures in excel, for example if the fee is between 25k-49.99k, then the commission is 1.25%, if fee is between 50k-99.99k then commission is 1%, if fee is between 1 lacs – 199.99 lacs then commission is 0.75%, if fee is 2 lacs and above commission is 0.5%

Reply
Matt O'Neill - March 31, 2016

Great post and happy to see it’s still alive! Here’s a scenario I haven’t addressed. I’d imagine it’d be a permutation of the Simplified Rate Table but I can’t work it out.

This is for tiered pricing. Let’s say:

Tier, Min, Max, Unit Cost
1, 1, 1000, .25
2, 1001, 5000, .20
3, 5001, 10,000, .10
4, 10,001, 99999999999999, .05

What I’d like is the sum of the area under the curve so to speak. For example someone who buys 7,500 units would pay a total of:
(999 * .25) + (3999 * .20) + (2502 * .10)
249.75 + 799.80 + 250.2
$1,299.75

This as opposed to the ‘simplified’ tier structure where they would pay 7,500 * .10, $750.

Any help working this out would be amazing!

Reply
    Jalal - August 25, 2016

    Hello Matt, I am sorry I have been trying all day to find a similar formula for myself. If you managed to come up with a solution, kindly send it to my email.

    Thank you, and good luck with what you are doing.

    Reply
      Jon Acampora - August 26, 2016

      Hi Jalal,
      Sorry, I missed Matt’s comment. I just added a file to the downloads section above that has a solution for this. It’s named “Commission Plan SUMPRODUCT Units Dollars.xlsx”. It basically uses the same SUMPRODUCT formula, with a simplified version of the differential calculation. I hope that helps.

      Thanks!

      Reply
    Jon Acampora - August 26, 2016

    Hi Matt,

    Sorry, I must have missed your comment. I know this is old and I hope you solved it by now. But just wanted to let you know I added a file in the downloads section above that uses the SUMPRODUCT formula for Units/Dollars in the tiers and payouts.

    The calculation is the same, but the way we calculate the differential rate is simplified because we are not concerned about the tier percentage. Check it out and let me know if you have any questions.

    It is the file named “Commission Plan SUMPRODUCT Units Dollars.xlsx” in the downloads section above.

    Reply
Manuel - March 22, 2016

Hi Jon,

I need help on my spreadsheet. Example, i want to charge $200 dollars From 500 to 1000 items, then $150 from 1001 to 1500 item.

If i type 550 it should multiply it by $200, and if i put 1010 it should multiply it by $150.

I hope you can help me with the formula.

Thank you

Reply
Matthew Burgos - February 18, 2016

If you are interested in using a VLOOKUP based formula instead of sumproduct, you can take a look at the excel file that I have put up on my drop box:

The file can be downloaded in the downloads section above.

The formula I provide is significantly more complicated to use but allows for faster calculation times despite being a longer formula. Sumproduct tends to be a very CPU intesive function within excel, whereas vlookup is much faster especially when you are using these calculations on many thousands of lines of data.

The basics of the formula are as follow:

(((Attainment % – Min Tier Attainment Value) / (Max Tier Attainment Value – Min Tier Attainment Value)) * (Tier Payout Rate – Prior Tier Payout Rate)) + Prior Tier Payout Rate

Using numbers from Jon’s example, the formula would look like this:
(((90% – 80%) / (100% – 80%)) * (100% – 75%)) + 75%

Reduced to
(((10%) / (20%)) * (25%)) + 75%

Further reduced to
(50% * 25%) + 75%

Further reduced to
12.5% + 75%

Finally reduced to
87.5%

In my example you would only need 4 total colums of look up data
Min Max Rate Prior Rate
0% 40% 20% 0
40% 60% 35% 20%
60% 80% 75% 35%
80% 100% 100% 75%
100% 120% 120% 100%

Hope this is useful.

Reply
    Jon Acampora - February 18, 2016

    Thanks Matt! This is awesome! I really appreciate you taking the time to explain the solution. I will make the file downloadable on the site so you don’t have to use your dropbox account, and also link to this comment for the explanation.

    Reply
    Jon Acampora - February 18, 2016

    I added the file to the download section above, and also added an alternative solution using INDEX/MATCH instead of VLOOKUP. That formula will perform less lookups, so it should improve performance if this is an issue in your file.

    Reply
    James - October 30, 2017

    Hi Matthew,
    This is great, thanks for this.
    How would you structure the table so the incentive is build according to attainment in these three tiers only

    70-79% attainment would result 10% of commission
    80-99% attainment, 20% commission
    =100% attainment, 30% of commission

    Thanks!
    James

    Reply
sanjeev kumar - December 11, 2015

Hi John
we have a calculation of scheme as
90% to 100% it 3 salary
( if the person ach 91% then he get 3.1 salary) add one tenth
100 to 110% its 5 months salary
and >110 its 6 months salary
how can i use your table
in excell

Reply
    Jon Acampora - December 12, 2015

    Hi Sanjeev,
    I will have to think about that one. It seems like no two commission plans are the same. It might be easier to use a vlookup formula to determine the base multiplier, then add the remainder percentage to it.

    Here is an article on how to calculate commissions with vlookup.

    Please let me know if you have any questions. Thanks!

    Reply
Jamie - December 9, 2015

Hi Jon

Could you assist me with this on the tier commission structure as I cant seem to work around using your template.

Tier 1 below 70% = 0% commission
Tier 2 70% to 100% = 1% commission
Tier 3 101% to 119.99% = 5% commission
Tier 4 above 120% = 7% commission

Assume sales A was given a target of $10,000 and he achieved $15,000. How can I use excel formula to help me calculate his commission?

Appreciate your help on this.

Thanks
Jamie

Reply
    Charlotte - November 3, 2016

    Hi Jamie

    Did you ever get an answer to this commission structure? I have the same requirements.

    Thanks

    Charlotte

    Reply
Tony - October 19, 2015

Hi,

I follow the logic somewhat, but I’m stuck on how to appropriately calculate using multiplier and effective rate? My range appears to be inflated which is driving my output hi. Can you please help me?

thanks

0-50% 0.16% – 12,480,000.00
> 51-80% 0.38% 12,480,000.01 15,600,000.00
> 81-100% 0.64% 15,600,000.01 18,720,000.00
> 101-120% 0.96% 18,720,000.01 24,960,000.00
> 121%+ 1.28% 24,960,000.01 –

Reply
Judy Gonzales - August 4, 2015

the situation is have a 401(k) plan that allows flat values as well as % and want to convert the flat amount to % of the gross wage. Is this a viable function within excel? The flat dollar can be as low as $1.00 and as high as 100% of pay so $12,000 . I know the “IF” statement for the cap salary but it is the conversion I am trying to figure out. Any assistance would be great!

Second item, is if a 401(k) has integrated with SS wage base ($118,500), point system for several levels based on performance ($575 per point within a level), and a separate % (3%) over wage base for any salary that exceeds can you use the formula’s stated above to compute these values?

Reply
George Goates - July 21, 2015

What if there is no quota it is just a tiered system based on dollars of revenue produced? I would like to put in a dollar amount and solve for a commission:

Tier Begin Tier End Tier Incremental Dollars Incremental Rate
1 – 150,000 150,000 1.40%
2 150,000 400,000 250,000 1.20%
3 400,000 750,000 350,000 1.00%
4 750,000 1,250,000 500,000 1%
5 1,250,000 2,000,000 750,000 1%
6 2,000,000 3,000,000 1,000,000 0%

Can your model be modified to do this?

Reply
    Jon Acampora - July 24, 2015

    Hi George,
    You should be able to replace the percentages in the Attain Tier Min column (D) with the dollar amounts for the tier minimums. Then put the Incremental Rates in the Differential Rate Combined column (F). I’m referring to the ‘Simplified Rate Table’ sheet in the file.

    So 500,000 attainment would calculate a 12,200 commission. Let me know if that works.

    Thanks!

    Reply
Priyanka Subashini - June 26, 2015

how to apply this to my sales commission calculation schedule

Due Date 65-80 81-90 91+
4.00% 3.00% 2.00% 1.00%

Reply
Tiered balance calculation - June 21, 2015

[…] document.write(''); Hi, Instead of writing a long explanation … Better to dive into a working example : Excel Formula to Calculate Commissions with Tiered Rate Structure – Excel Campus […]

Reply
Linda - June 16, 2015

We are planning to hire a sales rep. to sell 4 different product lines. Each product line will pay the rep. a different commission percentage, per sales order/total. But, in ADDITION to the rep’s commission percentage, an additional 5% is to be split and used for Operations and paid out separately.
Please help me write up a calculator for this.

So, the manufacture of each product needs to have his/her costs covered, their minimum, for easy numbers is $1,000, then there is the commission split of 5% (for Operations) and say 10% (for the sales rep).
Can you write this formula so I know how much to pay each entity?

If I’ve confused this, here’s a little more info you may need. There are 4 products/categories, each with a different % rate they will pay the rep. but the 5% for Operations stays consistent at 5%.
I need this as a calculator I can quickly and easily enter in the sale amount AND be sure I pay the manufacture their minimum (costs), then pay out the 2 commissions.
Please help!
Thank you,
Linda
PS – I just found and love your 10 Excel Pro Tips.

Reply
    Jon Acampora - June 18, 2015

    Hi Linda,

    You will probably want to use a VLOOKUP formula to lookup the rate based on the product type. You could create a table that has the product names listed in column 1 and the payout rates listed in column 2. Then on your sales sheet use a VLOOKUP formula in each row to lookup the product name in the rate table and return the rate. You can then multiple this rate by the sales amount.

    Here is an article on how to use the VLOOKUP Function.

    To make it easy, I would calculate the split in a different column, then add them together. I hope that helps.

    Reply
Joshua Smith - June 1, 2015

So I have a question and maybe it is to complicated for a simple formula in excel but I can seem to find anything on it anywhere.

I give 10% commission if my salesman can hit the 50% margin for profit. Sometimes they fall short, sometimes (on repairs and jobs with minimums) it is higher. I need a sliding scale commission where every time the margin exceeds 50% the salesman commission is increased by .02% per 1% increase in profit margin. Also I need the opposite of this if they fall short of the 50% profit margin then their commission is decreased by .02% per 1% decrease under the 50% profit margin.

Please let me know if this is obtainable or if I simply need to continue doing it the old school way.

Thank you

Joshua Smith

Reply
MANOJ KUMAR - May 1, 2015

Hello Sir,
I work at a Courier company.
we charge 5 rupees on every 100 rupees and charges 1 rupees on every 20 rupees,
like we parcel is of rupees 478, so we bifurcate it as,
commission 5*4=20 rupees for 400 hundred rupees.
and 1+1+1+1 = 4 rupees for rest (78) rupees. ( 1 rupee for every parcel of 1 to 20. either it 1 or 19 or 20)
so total commission for 478 is 24 rupees ( 20+1+1+1+1)
could you help me or guide me how to make commission formula to make our work smooth and fast, we’ll be highly grateful to you sir.

Reply
Charles - March 5, 2015

Great post regarding commission..I’m planning for commission pay pkg for my Telesales team and found your inputs very useful..

Reply
andrea sobeski - February 18, 2015

I am trying to a basic tiered commission formula.
from 0 – 4000 no commission paid
4000 – 7000 = 7% commission
above 7000 = 10% commission.

your spreadsheet is great but I think to advanced for what I am trying to do. any help would be great.

thanks andrea

Reply
    Jon Acampora - February 19, 2015

    Hi Andrea,

    You are right, this scenario can be solved with a VLOOKUP formula with TRUE as the last argument to return the closest match. I’m working on a post that will explain this in more detail. I also emailed you a file that contains the formula. Please let me know if you have any questions.

    Thanks!

    Reply
    Jon Acampora - February 19, 2015

    Here is a download link for the file for those that are interested.

    Reply
Maria - January 7, 2015

Excellent post regarding commission tiering!

We work on a flat % for all our consultants here internally – the tiering is external:

0-12 months 12%
13-24 months 8%
24+ months 3%

Our Client pays us a mark-up % on all recruitment fee’s based on the length of time the contractor is on-site. Is there a way to convert your sheets so that our commission reduces over time rather than increases with the billings?

If you can help, I would be so grateful!

Reply
    Jon Acampora - January 8, 2015

    Hi Maria,
    I would be happy to help.

    Are the rates paid monthly, or just based on the tier total? For example, if the contractor is on site for 6 months, does the client pay 6% or 12%?

    If the answer is 6%, how does the 24+ months tier work? If the contractor is on site for 30 months, is the monthly rate = 3% / (30-24) = 0.5%

    Thanks

    Reply
Jon Acampora - January 4, 2015

Bernarda wrote in with a great question asking, “what is the ‘–‘ used for in front of each statement in the SUMPRODUCT formula?”

I thought this would be a good one to share with everyone, so here is the answer.

The “–” is actually a double negative sign that is used to convert the result to a number.

For example, the following statement would return a TRUE value for each cell in the range D5:D9 that is greater than E12, and FALSE for any value that is less.

(E12>’Rate Table’!$D$5:$D$9)

With the sumproduct function we want to turn those TRUE/FALSE values into the number 1 or 0. The double negative “–” is just a way to turn the values into numbers. It is basically multiplying the results by -1 two times to return a positive number.

You could also multiply the argument by 1 to get the same result. So the following two statements would return the same result.

–(E12>’Rate Table’!$D$5:$D$9)
1*(E12>’Rate Table’!$D$5:$D$9)

Please let me know if you have any questions.

Reply
Romizi Zaini - December 16, 2014

Dear Mr Jon,

I sent an email directly with attachment for sample. waiting your soonest reply. maybe my email come to your junk or spam ?

Regards

Reply
Romizi Zaini - December 9, 2014

Hi Mr Jon

What the formula to calculate price nett commission 10% from 500 .

I’m working for travel agent and need to make calculation to get nett rates after commision

Regards
Romizi

Reply
Monica - September 9, 2014

Hello,
I am looking for a spreadsheet that can help me calculate a bonus using a number of parameters. Each will have different weight and should be calculated in percentage. The lower the percentage the higher the feedback.

I am totally new on this and my skills with excel are quite basic, so yes i am struggling.

Appreciate any help with with.

Thank you,

Monica

Reply
Ramachandran - May 5, 2014

Incentive calculation in Excel for Saleswise & No of Customer Wise in 5 Ranges….. Sales <500000, 50001-100000, likewise and No of customer 0,1,2,3,4 likewise. How to calculate if some percentage of Incentive is allocated in each case…..

Reply
Muhammad Saeed - May 2, 2014

Great model. Thanks.

Reply
Sabry - April 22, 2014

Hi Jon,

I have learned too many things from your sharing, and I would like to have a spreadsheet for calculating the sales commission of corrugated carton factory, how much I have to give to the sales team, including the sales Manager, using the Average sales price, collection and the total sales value.

Thanks

Sabry

Reply
    Jon Acampora - April 26, 2014

    Thank you Sabry! I am glad to hear you are learning Excel. I would be happy to help with your spreadsheet. Have you already started creating a file for your commission calculation? If so, you can email it to me with your questions. jon@excelcampus.com

    Reply
Bob Bacon - February 25, 2014

Hi Jon,

This is a good use of the sumproduct Excel function but effective table based commission calculations rely on the rational construction of the table itself. If your readers are searching for a model that helps design the table they could read here: http://bobbacon.net/blog/?p=690

I would also be interested in your feedback on making my Excel formula more efficient.

Cheers!

Bob

Reply
    Jon Acampora - February 25, 2014

    Hi Bob,

    Thanks for sharing! Part of the efficiency of the sumproduct function is that you do not necessarily need a long data table. I believe your data table contains a row for every possible attainment level.

    The sumproduct replaces all the IF statements, eliminating the need for a long data table. When using the sumproduct, your rate table (data table) only needs to contain a row for each rate change (inflection) in the commission plan.

    That is definitely a complex formula you have there. 🙂 I will take a look and see how we could apply the sumproduct technique to shorten it up.

    I also tend to advice against using named ranges because most users don’t use them or understand them. But that is personal preference and will depend on your audience. I do agree that they are helpful, but not as easily transferable.

    I’m working on a large project with CRM based data, and glad you shared your website. Looks like you have some great content that will be helpful.

    Thanks again!

    Reply
Skubi - January 31, 2014

Thanks Jon! email is on its way!!!

Reply
Skubi - January 28, 2014

Thanks a load. I will love to send you a scenario and we work it through together. Great post!

Reply
Mike - January 27, 2014

I have tried to use this formula and it seems well explained however, I’m sure I am the one who doesn’t completely understand this. Could you please help me with the follow structures:
0% – 20% = 0
21% – 60% = 1% bonus for every 1% of quota
61% – 80% = 2% bonus for every 1% of quota
81% – 99% = 1% bonus for every 1% of quota
100% and above is 1% for 1% of quota
quota is $2000 per month

The second one is after we have reached annual quota of $24,000
1% – 50% is 1% for every 1% of quota
51% – 225% of quota is 2% for every 1% of quota
226% and above is 1% for every 1% of quota.

I appreciate any help you can give me. Thanks

Mike

Reply
    Jon Acampora - January 28, 2014

    Hi Mike,

    I would be happy to help. Please feel free to contact me directly and send any files to jon@excelcampus.com.

    Thanks,
    Jon

    Reply
      Ankur Tyagi - August 12, 2015

      Dear Sir,
      I want to calculate the commission on the basis of received amount , for example, if i have received the Rs. 20/- out of Rs.100/- than i would like to pay 20% if the same increase than the commission increased as per the rate.

      Reply
    manoj kumar - March 11, 2015

    sir, i want to know Bill on following unit rate condition
    consumer name unit consummated Bill
    a 150
    b 450
    c 280
    d 300

    condition :-
    0-100U = 3.00 Rs.
    101-200 = 4.50Rs.
    201-300 = 7.00Rs.
    301-400 = 10.00Rs.
    Above 400U=15.00Rs.

    Reply
amir - November 21, 2013

great

Reply

Leave a Reply: