SUMPRODUCT - Tiered Commissions - Two Categories of Commissions

[mhlester]

Hi, everyone.

I am requesting help with a SUMPRODUCT formula for the following tiered commission situation.

The commission schedule changes at two levels, 90,000 and 150,000, when the Cumulative Company share of Gross Commissions reaches each tier. There is one schedule for Category 1 projects and another schedule for category 2 projects.

I've calculated it manually to show how it should work (I think), but I have not been able to figure out the SUMPRODUCT formula.

Gross Commission	Company Share of Commission Manually Calculated	Cumulative	Formula?	Category
65,000.00	26,000.00	26,000.00	SUMPRODUCT?	2	.4*65000
60,000.00	18,000.00	44,000.00	SUMPRODUCT?	1	.3*60000
75,000.00	30,000.00	74,000.00	SUMPRODUCT?	2	.4*75000
85,000.00	22,533.33	96,533.33	SUMPRODUCT?	1	16000/0.3=53333.33*.3 + 32666.67*.2
80,000.00	24,000.00	120,533.33	SUMPRODUCT?	2	.3*80000
100,000.00	29,822.22	150,355.56	SUMPRODUCT?	2	29466.67/.3=98222.23*.3 + 1777.77*.2

Crossover points Row 5 and Row 7

Tiered Commission Schedule - Company Share

When Company Cumulative Share of Gross Commissions Meets each Tier

Tier Start	Tier End	Category 1	Category 2
0	90000	30%	40%
90000	150000	20%	30%
150000		20%	20%

Thank you,

Michael

[#2]

Can you explain the logic in words that leads to a calculation like

 16000/0.3=53333.33*.3 + 32666.67*.2 

Where do all these numbers come from?? How do they relate to the data in that row?

Gross Commission Company Share of Commission Manually Calculated Cumulative
      85,000.00               22,533.33           96,533.33

[#3]

@Ingeborg Hawighorst 

Hi,

Thank you for your reply.

Yes.

When I see that the next commission will bring Cumulative Company commissions above the next tier, in this case, $90,000, and because I do not have a formula to help me calculate the commission, I must do it manually.

In this case, 16000/0.3=53333.33*.3 + 32666.67*.2 ...

I see from the chart that Cumulative Company Commissions have already reached $74,000, so another $16,000 will bring Cumulative Company Commissions to the next tier, $90,000. I can calculate how much of the current commission of $85,000 is used up at the below $90,000 (1st) tier by dividing $16,000 by the percentage for the 1st tier, 30%, which equals $53,333,33. Therefore, the remaining commission to be split between the company and the salesperson at the 2nd tier is $31,666.67 ($32,666.67 was a miscalc). The 2nd tier rate is 20%, and 20% of 31,666.67 equals $6,333.33. Adding together the 1st tier Company share of $16,000 to the 2nd tier Company share of $6,333.33 gives a total Company share of $22,333.33 (not 22,533.33 in the original post... my miscalc).

I hope this is the logic you are looking for.

The tiers will only crossover twice per year, once at $90,000 and once at $150,000. The fact there are two categories in addition to two tiers really trips me up. I've tried using "if" statements and it works up to the first tier, but when it gets to the second tier the if statements get too complex for me.

Thanks,

Michael