Formula to calculate commission bandings

[jab1276]

Hi,
I have seen a couple of threads regarding this, but nothing that specifically does what I need, hoping someone can assist!

I need to work out monthly commission on cumulative banked earnings based on the following percentages:

£0 - £100,000 is 12%
£100,001 - £150,000 is 15%
£150,001 - £250,000 is 18%
£250,000 + is 21%

The thing i'm struggling with, is that if the earnings for the month cross over a threshold into the next banding, part of the commission will be paid on the lower percentage and the other part on the higher percentage - is there a way around this?

For instance - If my total earning for December was £20,000 which took my yearly banked earning to £156,000, then £14,000 would be paid at 15% and £6000 at 18%.

I'm hopeful there is a workaround for this as manually calculating it is causing a headache!

Thanks in advance

 

[dreid1011]

Does this look like what you're after?

Book1
	A	B
1	75000	9000
2	155000	20399.85
3	201000	28679.85
4	255000	38549.67
5	300000	47999.67
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=IF(A1>=250001,SUM((A1-250000)*0.21,99999*0.18,49999*0.15,100000*0.12),IF(AND(A1<=250000,A1>150000),SUM((A1-150000)*0.18,49999*0.15,100000*0.12),IF(AND(A1<=150000,A1>100000),SUM((A1-100000)*0.15,100000*0.12),A1*0.12)))

 

[Dave Patton]

The following formula will complete the calculation as the earning accumulate or calculate on the total.
If you would like to try a Lambda version of the same formula, please advise.

Commission Cumulative Sales b.xlsm
	A	B	C	D	E	F
1
2		Brackets	Rate	Rate Differential
3		0	12%	12%	12,000.00
4		100,000	15%	3%	7,500.00
5		150,000	18%	3%	18,000.00
6		250,000	21%	3%	10,500.00
7
8
9		Nov & prior	Dec	Jan
10	Input amounts: Earnings	136,000.00	20,000.00	144,000.00
11
13	Commission	17,400.00	3,180.00	27,420.00	0.00	0.00
14
15	Total	300,000.00		48,000.00
1z

Cell Formulas
Range	Formula
D3:D6	D3	=C3-N(C2)
E3:E5	E3	=(B4-B3)*C3
E6	E6	=(B15-B6)*C6
B13:F13	B13	=SUM((SUM($B10:B10)>$B$3:$B$6)*(SUM($B$10:B10)-$B$3:$B$6)*($D$3:$D$6))-SUM($A$12:A12)
B15	B15	=SUM(B10:H10)
D15	D15	=SUM((B15>B3:B6)*(B15-B3:B6)*(D3:D6))

 

[Dave Patton]

A Lambda function is one that you build yourself.
- file does not have to be macro enabled
- you can name the function to a name that you prefer
- the function prompts for the necessary input
- you do not have use the bracket and rate table

Use Name Manager
new enter a name that you prefer
value enter the Lamdda information

Commission Cumulative Sales b.xlsm
	A	B	C	D	E	F
8
9		Nov & prior	Dec	Jan
10	Input amounts: Earnings	136,000.00	20,000.00	144,000.00
11
12	Commission	17,400.00	3,180.00	27,420.00	0.00	0.00
13	Commission	17,400.00	3,180.00	27,420.00	0.00	0.00
14
15	Total	300,000.00	48,000.00	48,000.00
1z

Cell Formulas
Range	Formula
B12:F12	B12	=Comm202312(SUM($B$10:B10))-SUM($A$12:A12)
B13:F13	B13	=SUM((SUM($B10:B10)>$B$3:$B$6)*(SUM($B$10:B10)-$B$3:$B$6)*($D$3:$D$6))-SUM($A$12:A12)
B15	B15	=SUM(B10:H10)
C15	C15	=Comm202312(B15)
D15	D15	=SUM((B15>B3:B6)*(B15-B3:B6)*(D3:D6))

Lambda Functions
Name	Formula
Comm202312	=LAMBDA(Cum_Earnings,LET(ce,Cum_Earnings,b,{0;100000;150000;250000},r,{0.12;0.03;0.03;0.03},SUM((ce>b)*(ce-b)*r)))