Tiered Bonus & Commission in Excel

J

jenni554

New Member
Joined
May 23, 2017
Messages
3
Hi,
I need to calculate the bonus that will be applied in addition to the normal 3% commission. The tiers are:

35000-50000 3.5% (bonus is only on amount within 35-50k)
50000-75000 5.0%
75000-100000 10.0%
100000-125000 15.0%

I need to be able to say for example: 65000 commission would be a bonus of 1275.00. Total payout: 66275. What would be the best way to set up a formula for this?
 
Sort by date Sort by votes
Dave Patton said:
Please show what you have tried.

Suggestions
- if formula
- logic formula
- above suggestion

If the person sells 500 units what will he be paid?
Click to expand...

If the person sells 500 units and $200 per they would be paid $100,000

All I have is a simple lookup that tells them the rate at which they are being paid out

=VLOOKUP(C10,$C$4:$D$6,2,TRUE)

[TABLE="width: 319"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sales Tiers[/TD]
[TD]Tier Minimum[/TD]
[TD]Payout Rate[/TD]
[/TR]
[TR]
[TD]0-30[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]31-59[/TD]
[TD]31[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]$60[/TD]
[TD]60[/TD]
[TD]$200
[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to figure out a way that as they go along week by week and add in their numbers that it would figure out commissions based on the tiers and spit out a number... less than 30 they get no commission 31 plus $125 each and so on.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You could use a table similar to the one above:

BCDEFGHIJK
TierRateDifferentialWeekSalesSales to dateBonusBonus to date

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]1750[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]2600[/TD]
[TD="align: right"]4350[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]4200[/TD]
[TD="align: right"]8550[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8750[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]414[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]82800[/TD]
[TD="align: right"]91550[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=IF(H2<>"",SUM($H$2:$H2),"")[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=IFERROR(K2-K1,K2)[/TD]
[/TR]
[TR]
[TH]K2[/TH]
[TD="align: left"]=IF(H2<>"",SUMPRODUCT(--(SUM($H$2:$H2)>$B$2:$B$4),(SUM($H$2:$H2)-$B$2:$B$4),$D$2:$D$4),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the tiers and rates in B1:D4. Then you can create the table in G1:K14. Put the formulas in I2:K2, then drag down to row 14. Now as they enter their sales in column H, the totals in I:K will update automatically.

Note that this calculates a bonus of $91,550 for 500 sales. This is because the first 30 sales earned $0, and the next 30 earned $125 each. If you really do want a bonus of $100,000, then I can adjust the formulas.
 
Last edited:
Upvote 0
Your information is not clear.
What is the amount for 72 units

What is the amount for 500 units.


Excel 2010
ABCD
1Month 1Month 2Month 3
2Units270428
3Cumulative272500
4Payment total06,15091,750
506,15085,600
6
706,15091,750
8Prior06,15085,600
9
7a
Cell Formulas
RangeFormula
B3=SUM($B$2:B2)
B4=(B3>30)*B3*125+(B3>60)*(B3-30)*(200-125)
B5=B4-SUM($A$4:A4)
B7=IF(B3>30,B3*125,0)+(IF(B3>60,(B3-60)*(200-125),0))
B8=B7-SUM($A$7:A7)
C3=SUM($B$2:C2)
C4=(C3>30)*((C3-30)*125)+(C3>60)*(C3-60)*(200-125)
C5=C4-SUM($A$4:B4)
C7=IF(C3>30,(C3-30)*125,0)+(IF(C3>60,(C3-60)*(200-125),0))
C8=C7-SUM($A$7:B7)
D3=SUM($B$2:D2)
D4=(D3>30)*((D3-30)*125)+(D3>60)*(D3-60)*(200-125)
D5=D4-SUM($A$4:C4)
D7=IF(D3>30,(D3-30)*125,0)+(IF(D3>60,(D3-60)*(200-125),0))
D8=D7-SUM($A$7:C7)



Excel 2010
ABCDEF
1TierRateDiffCumulativeBonus
2000300.00
330125125726,150.00
4602007550091,750.00
5
7aa
Cell Formulas
RangeFormula
F2=SUMPRODUCT(--(E2>$A$2:$A$4),(E2-$A$2:$A$4),$C$2:$C$4)
F3=SUMPRODUCT(--(E3>$A$2:$A$4),(E3-$A$2:$A$4),$C$2:$C$4)
F4=SUMPRODUCT(--(E4>$A$2:$A$4),(E4-$A$2:$A$4),$C$2:$C$4)
 
Last edited:
Upvote 0
72 units would equal $10,500

 500 units would equal $95,500 because the first 60 are at the $125 rate.

Dave Patton said:
Your information is not clear.
What is the amount for 72 units

What is the amount for 500 units

Excel 2010
ABCD
Month 1Month 2Month 3
Units
Cumulative
Payment total
Prior

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]428[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]6,150[/TD]
[TD="align: right"]91,750[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6,150[/TD]
[TD="align: right"]85,600[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6,150[/TD]
[TD="align: right"]91,750[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]6,150[/TD]
[TD="align: right"]85,600[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
7a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=SUM($B$2:B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=SUM($B$2:C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=SUM($B$2:D2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=(B3>30)*B3*125+(B3>60)*(B3-30)*(200-125)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=(C3>30)*((C3-30)*125)+(C3>60)*(C3-60)*(200-125)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=(D3>30)*((D3-30)*125)+(D3>60)*(D3-60)*(200-125)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=B4-SUM($A$4:A4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=C4-SUM($A$4:B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=D4-SUM($A$4:C4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=IF(B3>30,B3*125,0)+(IF(B3>60,(B3-60)*(200-125),0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=IF(C3>30,(C3-30)*125,0)+(IF(C3>60,(C3-60)*(200-125),0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=IF(D3>30,(D3-30)*125,0)+(IF(D3>60,(D3-60)*(200-125),0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=B7-SUM($A$7:A7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=C7-SUM($A$7:B7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=D7-SUM($A$7:C7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2010
ABCDEF
TierRateDiffBonus

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Cumulative[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6,150.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]91,750.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
7aa​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMPRODUCT(--(E2>$A$2:$A$4),(E2-$A$2:$A$4),$C$2:$C$4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMPRODUCT(--(E3>$A$2:$A$4),(E3-$A$2:$A$4),$C$2:$C$4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=SUMPRODUCT(--(E4>$A$2:$A$4),(E4-$A$2:$A$4),$C$2:$C$4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
any of the three solutions can be edited to provide the correct answer.

You originally stated
"
...less than 30 they get no commission 31 plus $125 each and so on.



You have have a new post. Someone can provide an answer(s) as the question evolves!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
56
babyjwhale
B
D
  • Solved
Sliding Scale Commission Table
Replies
6
Views
2K
etaf
etaf
I
  • Solved
Commission Calculator based on total Amount of Sales
2
Replies
11
Views
1K
Dave Patton
D
M
  • Solved
SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers
2 3 4
Replies
34
Views
4K
mhlester
M
B
  • Question Question
Tiered bonus structure
Replies
8
Views
1K
bsutherland
B

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top