81

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

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

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])

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

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

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.

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%

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

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.

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

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

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

##### Jon Acampora

Michael Fozouni - February 17, 2018

Hi Jon,
Many thanks in advance for such a magnificent site; I’ve learned almost everything about Excel mainly from your site.

Just wanted to give you a heads up that I downloaded your “Tiered Commission Rates using SUMPRODUCT.xls” file and two strange things happened:
1) The 2nd chart disappeared when I pressed “Enable Contents” button, and
2) Was not able to save the file!

I am using Excel 2010 64-bit. Any insight to shed a light on this problem will be greatly appreciated.

Respectfully,
Michael

Gene - February 10, 2018

Haven’t read all the comments but I think you made a typo in your early description of the payout rate fraction. The nominator and denominator should be switched. However the rest of the lengthy example applied the correct fraction.

This has stimulated me to study the SUMPRODUCT function

Thx

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

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.

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%

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.

Natalie - July 30, 2017

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

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.

Best Regards,
Jorge

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

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

Thank you soooo much!

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!

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?

Sanjeep Verma - February 18, 2017

Hi Jon,

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] )

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.

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

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?

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,

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!!

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!

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,

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

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?

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

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

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,

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.

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.

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%

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!

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.

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!

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.

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

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

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.

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.

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

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

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!

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?

Thanks
Jamie

Charlotte - November 3, 2016

Hi Jamie

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

Thanks

Charlotte

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 –

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?

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?

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!

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%

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 […]

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.

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.
Thank you,
Linda
PS – I just found and love your 10 Excel Pro Tips.

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.

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

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.

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

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

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!

Jon Acampora - February 19, 2015

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!

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

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.

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

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

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

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

Muhammad Saeed - May 2, 2014

Great model. Thanks.

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

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

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

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!

Skubi - January 31, 2014

Thanks Jon! email is on its way!!!

Skubi - January 28, 2014

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

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.

Mike

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

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.

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.