Tiered commission formula

D

dip694

New Member
Joined
May 1, 2018
Messages
10
I am trying to create a spreadsheet to calculate the tentative commission for a real estate business. The commission is structured in a tiered way under two situation.
Scenario 1 - Under this scenario the brokerage president is the one that handled the sale. The split would as seen from the screenshot below: Scenario 1: Commission Tiers. This assumes that no other ASD (ie sales guy was involved).

Scenario 2 - Under this scenario, the sale was predominantly handled by the ASD (ie sales guy) while managed by the president. As such the commission tier/structure would be as outlined in Scenario 2.

My question, is what is the best formula to calculate the commission for the president, ASD, and JA (company)?

I very much appreciate all your help.
 

Attachments

  • Screen Shot 2021-08-12 at 14.44.35.png
    Screen Shot 2021-08-12 at 14.44.35.png
    241.5 KB · Views: 23
Sort by date Sort by votes
The post below bases the calculations on the running accumulated amounts.

Rows 9 and 10 show the calculations before I changed the previous calculations to calculating on the cumulative amounts.


This example still uses named information for the calculations as stated in earlier post.
Post #2 stated The named arrays are aB ={0;250000;500000}} and aR ={0.15;0.1;0.15}
I used the same logic for the brackets and the rates used in this post.
You will have to create the named values with the relevant names and data.

If you require more information securing the data and naming the data, please advise.

Commissions 2021.xlsm
ABCDEFG
1GROSS PROFITCumulative by Column DASD Deal?Net to PresidentNet to ASDNet to JA Brokerage
2100,000.00100,000.00Yes10,000.0015,000.0075,000.00
3150,000.00150,000.00No22,500.00 127,500.00
450,000.00150,000.00Yes15,000.0022,500.00112,500.00
5100,000.00250,000.00Yes25,000.0037,500.00187,500.00
6200,000.00350,000.00No62,500.00 287,500.00
7150,000.00500,000.00No100,000.00 400,000.00
8
9250,000.00Yes25,000.0037,500.00187,500.00
10500,000.00No100,000.00 400,000.00
2cc
Cell Formulas
RangeFormula
E2:E7E2=SUMPRODUCT(--($C2>aB),$C2-aB,IF($D2="Yes",rPres,_rPres))
F2:F7F2=IF(D2="Yes",SUMPRODUCT(--($C2>aB),$C2-aB,rASD),"")
G2:G7G2=SUMPRODUCT(--($C2>aB),$C2-aB,IF($D2="Yes",rJA,_rJA))
C2:C7C2=IF(D2="yes",SUMIFS($B$2:B2,$D$2:D2,"=Yes"),SUMIFS($B$2:B2,$D$2:D2,"=No"))
E9:E10E9=SUMPRODUCT(--($B9>aB),$B9-aB,IF($D9="Yes",rPres,_rPres))
F9:F10F9=IF(D9="Yes",SUMPRODUCT(--($B9>aB),$B9-aB,rASD),"")
G9:G10G9=SUMPRODUCT(--($B9>aB),$B9-aB,IF($D9="Yes",rJA,_rJA))
B9:B10B9=SUMIFS(B2:B7,D2:D7,D9)
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Dave Patton said:
This example still uses named information for the calculations as stated in earlier post.
Click to expand...
But absolutely no information on how to set them up
Dave Patton said:
Post #2 stated The named arrays are aB ={0;250000;500000}} and aR ={0.15;0.1;0.15}
Click to expand...
And what about the others?
Please remember that not every one has your level of knowledge, that's why they are asking for help.
 
Upvote 0
The following are just suggestions. How you structure your sheet and how you build the formulas is your choice.
The following illustrates again the formulas with references to the Table or including the bracket detail and rate differential detail in the formula.
Some of the previous examples named the bracket details and the rate differential detail; naming is optional.

rows 13 and 14 with the detail of the brackets and the details of the rate differentials in the formula
rows 18 and 19 with the bracket and rate differentials from the Table.

Commissions 2021.xlsm
ABCDEFGHI
1GROSS PROFITASD Deal?
2100,000.00Yes
3150,000.00No
450,000.00Yes
5100,000.00Yes
6200,000.00No
7150,000.00No
8750,000.00
9
10
11Net to PresidentNet to ASDNet to JA Brokerage
12Results
13250,000.00Yes25,000.0037,500.00187,500.00
14500,000.00No100,000.00 400,000.00
15750,000.00125,000.0037,500.00587,500.00
16
17Example using the Table
18250,000.00Yes25,000.00
19500,000.00No100,000.00
20
21Scenario 1Scenario 2
22 ---- Rates -- -------------- Rates --------
23BracketsPresJAPresASDJA
24Row is blank
25015%85.0%10.0%15.0%75.0%
26250,00025%75.0%12.5%25.0%62.5%
27500,00040%60.0%15.0%40.0%45.0%
28
2ccc
Cell Formulas
RangeFormula
B8B8=SUM(B2:B7)
E13:E14E13=SUMPRODUCT(--($B13>{0;250000;500000}),$B13-{0;250000;500000},IF($D13="Yes",{0.1;0.025;0.025},{0.15;0.1;0.15}))
F13:F14F13=IF(D13="Yes",SUMPRODUCT(--($B13>{0;250000;500000}),$B13-{0;250000;500000},{0.15;0.1;0.15}),"")
G13:G14G13=SUMPRODUCT(--($B13>{0;250000;500000}),$B13-{0;250000;500000},IF($D13="Yes",{0.75;-0.125;-0.175},{0.85;-0.1;-0.15}))
B15,E15:G15E15=SUM(E13:E14)
B18,B13:B14B13=SUMIFS($B$2:$B$7,$D$2:$D$7,D13)
E18:E19E18=IF(D18="yes",SUMPRODUCT(--($B18>$D$25:$D$27),$B18-$D$25:$D$27,$G$25:$G$27-$G$24:$G$26),SUMPRODUCT(--($B18>$D$25:$D$27),$B18-$D$25:$D$27,$E$25:$E$27-$E$24:$E$26))
 
Last edited:
Upvote 0
I appreciate all the help. I certainly am no expert in Excel so this forum is greatly appreciated. That said, I am still struggling in understanding these formula. Perhaps my comments below will help with any confusion. Here is also my updated spreadsheet
  • The percentage for the Net to Presidentis calculated based on the cumulative commission in two scenarios. For example,
    • Example 1 - if there was no ASD involved, but the Cummulative commission was $100,000, the president commission should be Gross Profit x 15% (scenario 1, 1st tier). Now if the Cummulative commission was in equal to or greater than $250,001, but less than $500,000 and no ASD was involved, his commission would be Gross Profit x 25% (scenario 1, 2nd tier). The same would also apply for equal to or greater than $500,000.
    • Example 2 - Now if there was an ASD involved, the percentage for the president, ASD, and JA are reflected in Scenario 2. Unlike Example 1 where everything is based on JA Total Cumulative Commission, this is based on ASD Total Cumulative Commission.
The reason I wanted to clarify that, the example above seemed to mis-calculate a few items. For example, in post #11, row 6, the JA Cumulative Commission should be $600,000. The president's commission should be $200,000 x 40% = $80,000.

I hope this helps and I look forward to you guys feedback.

Commission Split.xlsx
ABCDEFGHIJKLMN
1#GROSS PROFITJA Total Cumulative CommissionASD DealADS Deal Value for Cumulative SumADS Cumulative CommissionNet to PresidentNet to ASDNet to JA Brokerage
21$ 100,000$ 100,000Y$ 100,000$ 100,000$ 15,000$ 15,000SCENARIO 1: Commission Tiers (No ASD)
32$ 150,000$ 250,000N $ -JA Total Cumulative Commission >=PresidentJA
43$ 50,000$ 300,000Y$ 50,000$ 150,000$ -15.0%85.0%
54$ 100,000$ 400,000Y$ 100,000$ 250,000$ 250,00125.0%75.0%
65$ 200,000$ 600,000N $ -$ 500,00040.0%60.0%
76$ 150,000$ 750,000N $ -
87SCENARIO 2: Commission Tiers (w/ASD)
98ASD Total Cumulative Commission >=PresidentASDJA
109$ -10.0%15.0%75.0%
1110$ 250,00112.5%25.0%62.5%
1211$ 500,00015.0%40.0%45.0%
JA Brokerage
Cell Formulas
RangeFormula
E2E2=IF(D2="Y",B2,0)
F2,C2F2=E2
G2G2=SUMPRODUCT(--($C2>aR),$C2-aR,IF($D2="Y",aPP,_aPP))
H2H2=IF(D2="Y",SUMPRODUCT(--($F$2>bR),$F$2-bR,bPA),"")
E3:E7E3=IF(D3="Y",B3,"")
F3F3=IF(E3="",0,F2+E3)
F4:F7F4=IF(E4="",0,SUM($E$2:E4))
M4:M6M4=100%-L4
C3:C7C3=C2+B3
N10:N12N10=100%-L10-M10
Named Ranges
NameRefers ToCells
aPP='JA Brokerage'!$L$4:$L$6G2, M4
aR='JA Brokerage'!$K$4:$K$6G2
bPA='JA Brokerage'!$M$10:$M$12H2, N10
bPP='JA Brokerage'!$L$10:$L$12N10
bR='JA Brokerage'!$K$10:$K$12H2
 
Upvote 0
N.B.
1. We do not know your business or your spreadsheet.
2. I appreciate that you stated that the calculations are on cumulative amounts.
I do not know if you are using different cumulative amounts for each category, etc.
3. I had hoped that you would read and possibly try the suggestions.
3. My suggestions mainly focus on the tiered calculations; you can adapt the suggestions to your numbers.
4. Try using Excel's Evaluate Formula to follow the logic.
5. If some of this works for you and you want to edit your named ranges,
select/highlight $K$11:$K$13 in the formula and press F9 result is the bracket details {0;250001;500000}
select/highlight $L$11:$L$13-$L$10:$L$12 in the formula and press F9 result is the rate differential {0.1;0.025;0.025}
You can name the information that shows for example {0;250001;500000} - Bracket information


With your post, I edited the formulas to work.
- inserted a new row 10 to facilitate recording the rate differential
- referenced the Table for the brackets and rate differential
- do you want the bracket of 250001 or 250000. What does your manual calculation show?

Commissions 2021.xlsm
ABCDEFGHIJKLMN
1#GROSS PROFITJA Total Cumulative CommissionASD DealADS Deal Value for Cumulative SumADS Cumulative CommissionNet to PresidentNet to ASDNet to JA Brokerage
21100,000.00100,000.00Y100,000.00100,000.0010,000.0015,000.0075,000.00SCENARIO 1: Commission Tiers (No ASD)
32150,000.00250,000.00N 0.00JA Total Cumulative Commission >=PresidentJA
4350,000.00300,000.00Y50,000.00150,000.0031,249.9849,999.9028,750.1300.150.85
54100,000.00400,000.00Y100,000.00250,000.0043,749.9874,999.90298,750.132500010.250.75
65200,000.00600,000.00N 0.005000000.40.6
76150,000.00750,000.00N 0.00
87SCENARIO 2: Commission Tiers (w/ASD)
98ASD Total Cumulative Commission >=PresidentASDJA
10Blank rows
11900.10.150.75
12102500010.1250.250.625
13115000000.150.40.45
14
2d
Cell Formulas
RangeFormula
E2E2=IF(D2="Y",B2,0)
F2,C2F2=E2
G2,G4:G5G2=SUMPRODUCT(--($C2>$K$11:$K$13),$C2-$K$11:$K$13,$L$11:$L$13-$L$10:$L$12)
H2,H4:H5H2=SUMPRODUCT(--($C2>$K$11:$K$13),$C2-$K$11:$K$13,$M$11:$M$13-$M$10:$M$12)
I2,I4:I5I2=SUMPRODUCT(--($C2>K11:K13),$C2-$K$11:$K$13,$N$11:$N$13-$N$10:$N$12)
E3:E7E3=IF(D3="Y",B3,"")
F3F3=IF(E3="",0,F2+E3)
F4:F7F4=IF(E4="",0,SUM($E$2:E4))
M4:M6M4=100%-L4
C3:C7C3=C2+B3
N11:N13N11=100%-L11-M11
 
Upvote 0
correction I did not lock one formula

Edit if you want the calculation on different totals

Commissions 2021.xlsm
ABCDEFGHI
1#GROSS PROFITJA Total Cumulative CommissionASD DealADS Deal Value for Cumulative SumADS Cumulative CommissionNet to PresidentNet to ASDNet to JA Brokerage
21100,000.00100,000.00Y100,000.00100,000.0010,000.0015,000.0075,000.00
3
4350,000.0050,000.00Y50,000.00150,000.0015,000.0022,500.00112,500.00
54100,000.00150,000.00Y100,000.00250,000.0025,000.0037,500.00187,500.00
65200,000.00350,000.00N 0.00
2d
Cell Formulas
RangeFormula
C2,F2C2=B2
E2E2=IF(D2="Y",B2,0)
G2,G4:G5G2=SUMPRODUCT(--($F2>$K$11:$K$13),$F2-$K$11:$K$13,$L$11:$L$13-$L$10:$L$12)
H2,H4:H5H2=SUMPRODUCT(--($F2>$K$11:$K$13),$F2-$K$11:$K$13,$M$11:$M$13-$M$10:$M$12)
I2,I4:I5I2=SUMPRODUCT(--($F2>$K$11:$K$13),$F2-$K$11:$K$13,$N$11:$N$13-$N$10:$N$12)
E4:E6E4=IF(D4="Y",B4,"")
F4:F6F4=IF(E4="",0,SUM($E$2:E4))
C4:C6C4=C3+B4
 
Last edited:
Upvote 0
Not sure if the SUMPRODUCT is the correct application. The commission rate is based on the Cumulative Commission (either total for the president, if no ASD, or ASD if one is involved), but the rate/percentage should is multiplied by the gross profit. As mentioned above. Additionally, if there are 3 gross commission of $100k, the cumulative commission would be $300k. As such the first two $100k commission would be at the respective percentage. However, the last deal of $100k, $50k would be at that the previous bracket while $50 would be at the new bracket. I hope this makes sense.
 
Upvote 0
We can get to your goal.

Step 1
We can determine what result you want with one calculation. With Scenario 1 and just considering the President and gross of 1,000,000 what result do you expect? Please just calculate using arithmetic and show your calculation. This will help ensure that we are working toward the same goal.

The picture that you provided showed the brackets and rates as shown below.


Commissions 2021.xlsm
KL
1Scenario 1President
2BracketsRates
3Blank row
40.0015.0%
5250,001.0025.0%
6500,000.0040.0%
3a


Step 2
I will show you a formula solution.

Step 3
I will illustrate some options.
 
Upvote 0
Hi- I have done the arithmetic math into each cell of what I am trying to accomplish.

Commission Split.xlsx
ABCDEFGHIJKLMN
1#GROSS PROFITJA Total Cumulative CommissionASD DealASD Deal Value for Cumulative SumASD Cumulative CommissionNet to PresidentNet to ASDNet to JA Brokerage
21$ 100,000$ 100,000Y$ 100,000$ 100,000$ 10,000$ 15,000$ 75,000SCENARIO 1: Commission Tiers (No ASD)
32$ 125,000$ 225,000N $ -$ 18,750$ -$ 106,250JA Total Cumulative Commission >=PresidentJA
43$ 75,000$ 300,000Y$ 75,000$ 175,000$ 7,500$ 11,250$ 56,250.0$ -15.0%85.0%
54$ 100,000$ 400,000Y$ 100,000$ 275,000$ 12,500$ 25,000$ 62,500.0$ 250,00125.0%75.0%
65$ 200,000$ 600,000N $ -$ 80,000$ -$ 120,000$ 500,00040.0%60.0%
76$ 150,000$ 750,000N $ -$ 60,000$ -$ 90,000
87SCENARIO 2: Commission Tiers (w/ASD)
98ASD Total Cumulative Commission >=PresidentASDJA
109$ -10.0%15.0%75.0%
1110$ 250,00112.5%25.0%62.5%
1211$ 500,00015.0%40.0%45.0%
JA Brokerage (2)
Cell Formulas
RangeFormula
E2E2=IF(D2="Y",B2,0)
F2,C2F2=E2
G2G2=B2*L10
H2H2=B2*M10
E3:E7E3=IF(D3="Y",B3,"")
F3F3=IF(E3="",0,F2+E3)
G3G3=B3*L4
F4:F7F4=IF(E4="",0,SUM($E$2:E4))
G4:G5G4=B4*L10
H4:H5H4=B4*M10
G6G6=B6*L6
G7G7=B7*L6
M4:M6M4=100%-L4
C3:C7C3=C2+B3
I2I2=B2*N10
I3I3=B3*M4
I4:I5I4=B4*N10
I6I6=B6*M6
I7I7=B7*M6
N10:N12N10=100%-L10-M10
Named Ranges
NameRefers ToCells
'JA Brokerage (2)'!aPJA='JA Brokerage (2)'!$M$4:$M$6I3
'JA Brokerage (2)'!aPP='JA Brokerage (2)'!$L$4:$L$6M4, G3
'JA Brokerage (2)'!bPA='JA Brokerage (2)'!$M$10:$M$12N10, H4, H2
'JA Brokerage (2)'!bPJ='JA Brokerage (2)'!$N$10:$N$12I4, I2
'JA Brokerage (2)'!bPP='JA Brokerage (2)'!$L$10:$L$12N10, G4, G2
 
Upvote 0
I can help but you failed to answer the question so we know just what result that you expect.
With Scenario 1 and just considering the President and gross of 1,000,000 what result do you expect?
Please just calculate using arithmetic and show your calculation. This will help ensure that we are working toward the same goal.
The tiered calculation can work on relevant accumulated amounts.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Calculating Tiered Commission for Sales People 😁
Replies
9
Views
950
jdellasala
J
E
  • Question
Calculating Tiered Commission Structure based on Rolling Annual Contribution
Replies
1
Views
930
Dave Patton
D
C
  • Solved
Struggling with a tiered commission structure (monthly & quarterly)
2
Replies
11
Views
2K
Dave Patton
D
M
  • Solved
SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers
2 3 4
Replies
34
Views
3K
mhlester
M
R
  • Question
Real Estate tiered commission calculation with split date
Replies
0
Views
439
rebroker
R

Forum statistics

Threads
1,215,374
Messages
6,124,571
Members
449,173
Latest member
Kon123

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