Cumulative amounts at different threshold rates

[controlz]

Hi,
There are many ways to calculate what I need to do but I can't seem to finish any route that I've attempted. I'm trying to calculate excise tax rates for gallons of alcohol transferred. The first 30K gallons cost 1.00/gallon, the next 100K (30,001-130,000) cost 0.90/gallon, and anything above costs 0.54/gallon. Can someone help me with my formula. I was going to do the calc per tranche but also do one calc with different rates. Can i get a brainiac to assist? Please help!

 

[KRice]

Give this a try. Your inputs are in the blue cells. The cumulative gallons are determined, and the total excise taxes due on the cumulative gallons are determined from a single formula. That formula relies on using the difference in rates as one moves from one tier to the next higher, so band rates of 1.00, 0.90, and 0.54 have band rate differences of 1.00, -0.10, and -0.36. You'll see arrays using those differences in the formula. As a check, take November as an example, where the total gallons transferred by then are distributed among the three rate bands, and each band's taxes are determined. The answer is given in the green cell, which matches with the green cell in O4.

UKtax20200428.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				Start at 0	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Sum
2		Gallons Transferred Total (monthly)			15028	11962	19503	9329	10000	10000	20000	20000	20000	20000	20000	20000
3		Cumulative gallons		0	15028	26990	46493	55822	65822	75822	95822	115822	135822	155822	175822	195822
4		Cumulative Excise Taxes		0	15028	26990	44843.7	53239.8	62239.8	71239.8	89239.8	107239.8	123143.9	133943.9	144743.9	155543.9
5		Monthly Excise Taxes			15028	11962	17853.7	8396.1	9000	9000	18000	18000	15904.08	10800	10800	10800	155543.9
6
7
8	Low Threshold		Rate	Rate Diff	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	0	First 30000	1.00	1.00											30000		30000
10	30000	Next 100000	0.90	-0.10											100000		90000
11	130000	everything above	0.54	-0.36											45822		24743.88
12																	144743.9
13
Sheet1

Cell Formulas
Range	Formula
E3:P3	E3	=D3+E2
E4:P4	E4	=SUMPRODUCT(--(E3>={0;30000;130000}),E3-{0;30000;130000},{1;-0.1;-0.36})
E5:P5	E5	=E4-D4
Q5	Q5	=SUM(E5:P5)
Q9:Q11	Q9	=O9*C9
Q12	Q12	=SUM(Q9:Q11)

 

[Dave Patton]

The formula is for the Cumulative amount.
With calculations such as certain taxes, commissions, etc., the rates are tiered relating to specified brackets. Alternative methods of calculation include Boolean logic, if functions, VLookup functions, or SumProduct formulas. Certain calculations such as the Vlookup approach require interim calculations. The SumProduct solution is probably the most concise.
N.B.
1. It is not necessary to use Named Ranges. The heading for the brackets includes [rB] which is the named range for E4:E7
2. If you do not want to have the table on your sheet
a) put the table on another sheet or
b) build the table and convert the ranges to values or
c) build the arrays directly or
d) name the arrays in Name Manager

See Between 2 numbers

T202005a.xlsm
	B	C	D	E	F	G
1				T202005a	1b
2	Amount	Tax
3	150,000.00	130,800.00		Bracket [rB]	Rate	Rate_Delta [rR]
4		130,800.00		0	100%	100%
5				30,000	90%	-10%
6				130,000	54%	-36%
1bb

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(B3>rB),B3-rB,rR)
C4	C4	=SUMPRODUCT(--(B3>E4:E6),B3-E4:E6,G4:G6)
G4:G6	G4	=F4-N(F3)

Named Ranges
Name	Refers To	Cells
'1bb'!rB	='1bb'!$E$4:$E$6	C3:C4
'1bb'!rL	='1bb'!$E$4:$H$6	C3:C4
'1bb'!rR	='1bb'!$G$4:$G$6	C3:C4

 

[KRice]

I'll add one more option (starting at row 8) if you'd prefer to see the breakdown by month and rate tier. As Dave mentions, the approach is based on cumulative gallons through the month in question. This takes the cumulative gallons and distributes them among the rate tiers and then applies the appropriate rate to that tier for the Cumulative Excise Taxes (row 13). Monthly excise taxes are then determined based on the difference between consecutive monthly cumulative results. In this example, I adjusted the October quantity to give 150000 cumulative gallons for comparison with Dave's post (see red cells). You'll see that all three options produce equivalent results.

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Start at 0	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Gallons Transferred Total (monthly)			15028	11962	19503	9329	10000	10000	20000	20000	20000	14178	20000	20000
3	Cumulative gallons		0	15028	26990	46493	55822	65822	75822	95822	115822	135822	150000	170000	190000
4	Cumulative Excise Taxes		0	15028.00	26990.00	44843.70	53239.80	62239.80	71239.80	89239.80	107239.80	123143.88	130800.00	141600.00	152400.00
5	Monthly Excise Taxes			15028.00	11962.00	17853.70	8396.10	9000.00	9000.00	18000.00	18000.00	15904.08	7656.12	10800.00	10800.00
6
7
8	Low Threshold (gallons)	Rate	Rate Diff	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	0	1.00	1.00	15028	26990	30000	30000	30000	30000	30000	30000	30000	30000	30000	30000
10	30000	0.90	-0.10	0	0	16493	25822	35822	45822	65822	85822	100000	100000	100000	100000
11	130000	0.54	-0.36	0	0	0	0	0	0	0	0	5822	20000	40000	60000
12	99999999
13	Cumulative Excise Taxes		0.00	15028.00	26990.00	44843.70	53239.80	62239.80	71239.80	89239.80	107239.80	123143.88	130800.00	141600.00	152400.00
14	Monthly Excise Taxes			15028.00	11962.00	17853.70	8396.10	9000.00	9000.00	18000.00	18000.00	15904.08	7656.12	10800.00	10800.00
controlz

Cell Formulas
Range	Formula
E3:P3	E3	=D3+E2
E4:P4	E4	=SUMPRODUCT(--(E3>={0;30000;130000}),E3-{0;30000;130000},{1;-0.1;-0.36})
E14:P14,E5:P5	E5	=E4-D4
E9:P9	E9	=MIN(E$3,$B10-$B9)
E10:P11	E10	=MIN(E$3-SUM(E$9:E9),$B11-$B10)
E13:P13	E13	=SUMPRODUCT($C$9:$C$11,E9:E11)

 

[controlz]

Thank you! Such elegant and concise, clever options. You both made my weekend!!!

 

[KRice]

You're welcome. We're happy to help.