Sliding incremental bonus calculation

[burgie10]

Hi there,

I haven't used this forum for a while but I'm desperately in need of some help!
I've been asked to put together a bonus calculator for a new bonus scheme at work. The scheme works like this;

Assuming John hits his target i.e. D6 is greater than 0 then;
- He gets 10% (D11) of the variance between 0%-5% (B11-C11)
- He then gets 15% (D12) of the variance between 6%-10%
- He then gets 20% (D13) of the variance between 11%-15%
- He then gets 30% (D14) of the variance between 16%-20%
- He then gets 40% of the variance above 21%

I have attached a mini sheet and am after a way to calculate with formulas the yellow shaded cells. I'd like to be able to change the criteria (% bonus paid & the ranges) but with the calculation still working!

I hope this makes sense... any help much appreciated!

 

[Dave Patton]

What is the expected result?

N.B. You can post an extract of your sheet with the forum's tool XL2BB. We will then not have to type your example and
guess at the layout. You will be able to paste out posts to a clean sheet.

 

[Dave Patton]

You can copy the following to a clean sheet. Click on the icon below the f(x) in the heading and then move to
your sheet and paste.

Please confirm that the result is the same as your calculation.
If the calculations agree, I will post alternative configurations of the formula.

Commission2020.xlsm
	A	B	C	D	E	F
1
2
3					0%	10%
4					5%	15%
5					10%	20%
6					15%	30%
7					20%	40%
8
9
10
11	John's target	850,000
12	Achieved	1,100,000
13		250,000
14	Percentage	29.41%
15	Bonus	18,786.76
5aa

Cell Formulas
Range	Formula
B13	B13	=B12-B11
B14	B14	=B13/B11
B15	B15	=SUMPRODUCT(--(B14>E3:E7),B14-E3:E7,F3:F7-F2:F6)*B13

 

[burgie10]

Hi there, thank you so much for your response.

Using the example you kindly copied I have calculated the commission manually and get to a different number. I'll try to explain in words!
- The commission earned on the first 5% (i.e. 5% of 850,000 = $42,500) should be 10% of $42,500 = $4,250
- The commission earned between 6%-10% = 15% of $42,500 = $6,375
- The commission earned between 11%-15% = 20% of $42,500 = $8,500
- The commission earned between 16%-20% = 30% of $42,500 = $12,750
- All outperformance above 20% pays 40% i.e. 40% of $80,000 ($250k-$170k*) = $32,000

*$170k is 20% of the original target i.e. $850k. So 40% needs to be paid on the additional outperformance i.e. $250k-$170k

So in total, my calculation comes to $63,875

Does that help? I have attached an image to show my manual calculations.

Thank you so much

 

[Dave Patton]

SumProduct 2020.xlsm
	A	B	C	D	E	F
1
2
3					0%	10%
4					5%	15%
5					10%	20%
6					15%	30%
7					20%	40%
8
9
10
11	John's target	850,000.00
12	Achieved	1,100,000.00
13		250,000.00
14	Percentage	29.41%
15	Bonus	63,875.00
7c

Cell Formulas
Range	Formula
B13	B13	=B12-B11
B14	B14	=B13/B11
B15	B15	=SUMPRODUCT(--(B13>E3:E7*B11),B13-E3:E7*B11,F3:F7-F2:F6)

 

[Dave Patton]

The following shows some alternatives and more detail.
Several of the alternatives do not require the table.
Hopefully some of the ideas are helpful.
The values for Brackets and Rate Differentials used in D15 are the same as in B15; highlight the ranges like B3:B7 and press F9.
The named arrays in E15 are the arrays shown in D15. Use Excel's Name Manager and name the Bracket and Rate Differentials as follows:
aB={0;0.05;0.1;0.15;0.2}*Target
aR= {0.1;0.05;0.05;0.1;0.1}

Commission2020.xlsm
	A	B	C	D	E
1
2				Arithmetic
3	0%	0	10%	4,250.00
4	5%	42,500.00	15%	6,375.00
5	10%	85,000.00	20%	8,500.00
6	15%	127,500.00	30%	12,750.00
7	20%	170,000.00	40%	32,000.00
8				63,875.00
9
10
11	John's target	850,000
12	Achieved	1,100,000
13		250,000		-- No Table --
14	Percentage	29.4%		Values	Named arrays
15	Bonus	63,875.00	63,875.00	63,875.00	63,875.00
16
5a

Cell Formulas
Range	Formula
B4:B7	B4	=A4*Target
D3:D7	D3	=MAX(0,(MIN($B$13,B4)-B3))*C3
D8	D8	=SUM(D3:D7)
B13	B13	=B12-B11
B14	B14	=B13/B11
B15	B15	=SUMPRODUCT(--(B13>B3:B7),B13-B3:B7,C3:C7-C2:C6)
C15	C15	=SUMPRODUCT(--(B13>A3:A7*Target),B13-A3:A7*Target,C3:C7-C2:C6)
D15	D15	=SUMPRODUCT(--(B13>{0;42500;85000;127500;170000}),B13-{0;42500;85000;127500;170000},{0.1;0.05;0.05;0.1;0.1})
E15	E15	=SUMPRODUCT(--(B13>aB),B13-aB,aR)

Named Ranges
Name	Refers To	Cells
Target	='5a'!$B$11	E15, C15, B13:B14, B4:B7

 

[Dave Patton]

Commission2020.xlsm
	A	B	C	D	E
11	John's target	1,000,000
12	Achieved	1,100,000
13		100,000		-- No Table --
14				Values	Named arrays
15	Bonus			12,500.00	12,500.00
16
5a

Cell Formulas
Range	Formula
B13	B13	=B12-B11
D15	D15	=SUMPRODUCT(--(B13>{0;0.05;0.1;0.15;0.2}*Target),B13-{0;0.05;0.1;0.15;0.2}*Target,{0.1;0.05;0.05;0.1;0.1})
E15	E15	=SUMPRODUCT(--(B13>aB),B13-aB,aR)

Named Ranges
Name	Refers To	Cells
Target	='5a'!$B$11	D15:E15, B13

 

[j4y4sha]

Hi, sorry to jump on this post, but this is exactly the kind of template I am looking for. Any chance someone can forward me a copy of this please. Thank you

 

[Dave Patton]

Welcome to the forum.

Please provide an explanation of your challenge.
Ensure that you provide the Brackets, Rates, and expected result for your calculation.
I will show relevant formulas for Excel 365.


Please see

j4y4sha 's thread.​

This thread is from 2021.

 

[Joe4]

j4y4sha.
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

I am going to lock this thread now to ensure that their question is only active in one spot.