Tiered Calculation Formula

[jayjay123]

Hi there,

Would someone be able to help me build a formula that will calculate the total $ value according to the appropriate tier.

The first extract 1 is a tier structure table. Between certain values, a set pricing will be applicable. e.g. values between $0 - $100,000 will be Tier 1 at the rate of $0.66 cents.
In extract 2, we have the monthly total in column A, and a cumulative YTD in column B. Column 3 - categorises these in the appropriate tier.


Can you please help me design an excel formula to calculate column D?
Extract 2:
- Tier 2 - Calculation should be $165,000.
- First $100,000 @ $0.66 = $66,000
- Remaining $65,000 @ $0.63 = $40,950
Total should be: $106,950

Extract 1:

Tier	Start	End	$
1	$0.00	$100,000.00	0.66
2	$100,000.01	$250,000.00	0.63
3	$250,000.01	$500,000.00	0.55
4	$500,000.01	$999,999.00	0.50
Extract 2:
Month total	Cumulative YTD	Value	Calculation
$40,000.00	$40,000.00	Tier 1
$125,000.00	$165,000.00	Tier 2
$275,000.00	$400,000.00	Tier 3
$540,000.00	$815,000.00	Tier 4

Thanks very much for your help

 

[Dave Patton]

C2 I named the array of Bracket data aB {0;100000;250000;500000} and
I named the array of Rate Differentials aR {0.66;-0.03;-0.08;-0.05
With the named arrays, we do not need the table of Brackets / Tiers.

Commissions 2021.xlsm
	A	B	C	D
1	Total sales	165,000.00
2	Commission	106,950.00	106,950.00
3	Commission Arithmetic	106,950.00
4
5	The Table below is not required.
6	Brackets	Rates
7
8	0	66%	66,000.00	66,000.00
9	100,000	63%	40,950.00	106,950.00
10	250,000	55%		106,950.00
11	500,000	50%		106,950.00
12		1E+308
1c

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,{0.66;-0.03;-0.08;-0.05})
B2	B2	=SUMPRODUCT(--(B1>A8:A11),B1-A8:A11,B8:B11-B7:B10)
B3	B3	=MAX(D8:D11)
C8:C9	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8:D11	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+D7
B12	B12	=BigNum

 

[Dave Patton]

Commissions 2021.xlsm
	A	B	C	D
1	Total sales	815,000.00
2	Commission	455,500.00	455,500.00
3	Commission Arithmetic	455,500.00	455,500.00
4
5	The Table below is not required.
6	Brackets	Rates
7
8	0	66%	66,000.00	66,000.00
9	100,000	63%	94,500.00	160,500.00
10	250,000	55%	137,500.00	298,000.00
11	500,000	50%	157,500.00	455,500.00
12		1E+308
1c

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>A8:A11),B1-A8:A11,B8:B11-B7:B10)
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,{0.66;-0.03;-0.08;-0.05})
B3	B3	=MAX(D8:D11)
C3	C3	=SUM(C8:C11)
C8:C11	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8:D11	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+D7
B12	B12	=BigNum

 

[jayjay123]

Thank you very much Dave.
I would like to keep the existing table & format as this template is used by other members of my team.
Is it possible to still make a formula?

 

[kieran]

Hi, I had a similar problem trying to calculate progressive tax scales.
It turns out the Vlookup function can do this.

If your table [tax_table} is :

lower bound	Upper bound	marginal rate	Base
0	18,200	0.000	$0
18,201	45,000	0.190	$0
45,001	120,000	0.325	$5,092
120,001	180,000	0.370	$29,467
180,001	999,999,999	0.450	$51,667

The total tax payable is based on the value of the cell B4 =VLOOKUP($B$4,Tax_Table,4,TRUE)+($B$4-VLOOKUP($B$4,Tax_Table,1,TRUE)+1)*VLOOKUP($B$4,Tax_Table,3,TRUE)
NOte - I have an added column here that you do not show above - called base - this is the value of all the rows above and is used to avoid calcualting each row each ever time.

With only small changes, will solve the problem.

 

[jayjay123]

Hi Kieran and Dave.

Thank you so much for your help.
I'm not very good with excel, so I'm still having a bit of trouble trying to get the right calculation even though I tried the above.

I've attached a screenshot below which might be able to help explain what I'm looking for.
I am trying to find a formula to calculate G11 onwards.
For example: Feb21 - Accumulated Total $969,070 will be made up of two pricing structures (Tier 1 + 2).

Kindly assist!

 

[Dave Patton]

What results do you calculate with your example?

I named the array of Bracket data aB {0;500000;1000000;1500000;2000000} and
I named the array of Rate Differentials aR {0.66;-0.04;-0.04;-0.04;-0.04}
Use Name Manager and then try the formula.

Commissions 2021.xlsm
	A	B	C	D	E	F
10	Month				Total
11	Jan-21				344,070	227,086.20
12	Feb-21				969,070	620,823.40
13	Mar-21				1,900,000	1,146,000.00
14
1d

Cell Formulas
Range	Formula
F11:F13	F11	=SUMPRODUCT(--(E11>aB),E11-aB,aR)