Tax calculation... IF/AND/OR?

[BrutalLogiC]

Hello board, I'm struggling with a formula to calculate the tax contribution where there are a number of different salary bandings and rates. I've manually typed out in column F what the answer should be but trying to get a formula in yellow cells so I don't have to keep recalculating! Help much appreciated!

Tax calc.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		Annual salary banding			0.0%		2.5%		10.0%		15.0%		20.0%		22.5%		25.0%
3		Above:	Up to and including:		Above:	Up to and including:	Above:	Up to and including:	Above:	Up to and including:	Above:	Up to and including:	Above:	Up to and including:	Above:	Up to and including:	Above:
4	A	-	600,000		-	15,000	15,000	30,000	30,000	45,000	45,000	60,000	60,000	200,000	200,000	400,000	400,000
5	B	600,000	700,000				-	30,000	30,000	45,000	45,000	60,000	60,000	200,000	200,000	400,000	400,000
6	C	700,000	800,000						-	45,000	45,000	60,000	60,000	200,000	200,000	400,000	400,000
7	D	800,000	900,000								-	60,000	60,000	200,000	200,000	400,000	400,000
8	E	900,000	1,000,000										-	200,000	200,000	400,000	400,000
9	F	1,000,000													-	400,000	400,000
10
11		Annual Salary	Tax paid		Tax paid
12		600,000			127,125
13		601,000			127,500
14		650,000			140,000
15		720,000			159,750
16		810,000			184,500
17		950,000			222,500
18		2,000,000			490,000
Sheet1

Cell Formulas
Range	Formula
F12	F12	=(15000*H2)+(15000*J2)+(15000*L2)+(140000*N2)+(200000*P2)+(200000*R2)
F13	F13	=30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+200001*R2
F14	F14	=30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+250000*R2
F15	F15	=45000*J2+15000*L2+140000*N2+200000*P2+320000*R2
F16	F16	=60000*L2+140000*N2+200000*P2+410000*R2
F17	F17	=200000*N2+200000*P2+550000*R2
F18	F18	=400000*P2+1600000*R2

 

[arthurbr]

This technique is described at www.mcgimpsey.com/excel/variablerate1.html

 

[braindiesel]

Just to clarify, if 0-15000 is 0% and 15-30k is 2.5%
If someone earns 16000, they pay 2.5% of 1000 or $25...
If correct the easiest way to solve this is build a lookup table...
$0 earned $0 tax
$15000 $0
30000 $75
45000 $225 (15k at 10% plus previous $75)

Provide me that and I can likely give you a formula to solve this

 

[BrutalLogiC]

Just to clarify, if 0-15000 is 0% and 15-30k is 2.5%
If someone earns 16000, they pay 2.5% of 1000 or $25...
If correct the easiest way to solve this is build a lookup table...
$0 earned $0 tax
$15000 $0
30000 $75
45000 $225 (15k at 10% plus previous $75)

Provide me that and I can likely give you a formula to solve this

yep if they earn 16,000 then it's only 2.5% of 1,000 in tax but if they earn the 1,000,000 then the 2.5% is irrelevant (and so if everything other than the last two percentages if that makes sense?).

 

[BrutalLogiC]

This technique is described at https://www.mcgimpsey.com/excel/variablerate1.html

thanks, will check later as site is blocked at work

 

[Dave Patton]

UDF Tax or Commission.xlsm
	A	B	C	D	E	F
1	Income	$600,000.00		Brackets	Rate	Difference
2	Tax	$127,125.00		0.00	0.0%	0.0%
3		$127,125.00		15,000.00	2.5%	2.5%
4		$127,125.00		30,000.00	10.0%	7.5%
5				45,000.00	15.0%	5.0%
6				60,000.00	20.0%	5.0%
7				200,000.00	22.5%	2.5%
8				400,000.00	25.0%	2.5%
9
1c

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>D2:D8),B1-D2:D8,F2:F8)
B3	B3	=SUMPRODUCT(--(B1>rB),B1-rB,rD)
B4	B4	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F8	F2	=E2-N(E1)

Named Ranges
Name	Refers To	Cells
'1c'!rB	='1c'!$D$2:$D$8	B2:B3
'1c'!rD	='1c'!$F$2:$F$8	B2:B3
'1c'!rL	='1c'!$D$2:$G$7	B2:B3

3 versions of the same Function
B2 uses Table and ranges
B3 uses named ranges
B4 uses named arrays; the table is not needed

You could use arithmetic, VLookups, UDF, etc

 

[Dave Patton]

What are the rates for high rate brackets?
Do you use 2 distinct groups of bands or do we just extend the brackets and rates?

Please clarify what you mean with
" yep if they earn 16,000 then it's only 2.5% of 1,000 in tax but if they earn the 1,000,000 then the 2.5% is irrelevant (and so if everything other than the last two percentages if that makes sense?). "

 

[BrutalLogiC]

What are the rates for high rate brackets?

thanks for your solution above but I don't understand the named ranges part but I'm trying to replicate
if salary was 1,000,000 (row 8) then tax would be 235,000 (200k at 20%, 200k at 22.5% and the remaining 600k at 25%)
if salary was 1,000,001 (row 9) then tax would be 240,000 (400k at 22.5% and remaining 600,001 at 25%)

 

[braindiesel]

thanks for your solution above but I don't understand the named ranges part but I'm trying to replicate
if salary was 1,000,000 (row 8) then tax would be 235,000 (200k at 20%, 200k at 22.5% and the remaining 600k at 25%)
if salary was 1,000,001 (row 9) then tax would be 240,000 (400k at 22.5% and remaining 600,001 at 25%)

This isnt usually how tax works... it is the incremental dollars taxed at the higher rate... this is why I suggested you do a sliding scale list... very east then

 

[BrutalLogiC]

What are the rates for high rate brackets?
Do you use 2 distinct groups of bands or do we just extend the brackets and rates?

Please clarify what you mean with
" yep if they earn 16,000 then it's only 2.5% of 1,000 in tax but if they earn the 1,000,000 then the 2.5% is irrelevant (and so if everything other than the last two percentages if that makes sense?). "

maybe I just explained above in earlier reply but the 7 different percentages (0, 2.5, 10, 15, 20, 22.5 and 25) only apply if you are earning up to 600k..... and specifically between 400k and 600k when each percentage comes into play