Tax calculation... IF/AND/OR?

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQR
2Annual salary banding0.0%2.5%10.0%15.0%20.0%22.5%25.0%
3Above: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:
4A - 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
5B 600,000 700,000 - 30,000 30,000 45,000 45,000 60,000 60,000 200,000 200,000 400,000 400,000
6C 700,000 800,000 - 45,000 45,000 60,000 60,000 200,000 200,000 400,000 400,000
7D 800,000 900,000 - 60,000 60,000 200,000 200,000 400,000 400,000
8E 900,000 1,000,000 - 200,000 200,000 400,000 400,000
9F 1,000,000 - 400,000 400,000
10
11Annual SalaryTax paidTax 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
RangeFormula
F12F12=(15000*H2)+(15000*J2)+(15000*L2)+(140000*N2)+(200000*P2)+(200000*R2)
F13F13=30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+200001*R2
F14F14=30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+250000*R2
F15F15=45000*J2+15000*L2+140000*N2+200000*P2+320000*R2
F16F16=60000*L2+140000*N2+200000*P2+410000*R2
F17F17=200000*N2+200000*P2+550000*R2
F18F18=400000*P2+1600000*R2
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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?).
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,820
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
UDF Tax or Commission.xlsm
ABCDEF
1Income$600,000.00BracketsRateDifference
2Tax$127,125.000.000.0%0.0%
3$127,125.0015,000.002.5%2.5%
4$127,125.0030,000.0010.0%7.5%
545,000.0015.0%5.0%
660,000.0020.0%5.0%
7200,000.0022.5%2.5%
8400,000.0025.0%2.5%
9
1c
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>D2:D8),B1-D2:D8,F2:F8)
B3B3=SUMPRODUCT(--(B1>rB),B1-rB,rD)
B4B4=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F8F2=E2-N(E1)
Named Ranges
NameRefers ToCells
'1c'!rB='1c'!$D$2:$D$8B2:B3
'1c'!rD='1c'!$F$2:$F$8B2:B3
'1c'!rL='1c'!$D$2:$G$7B2: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

Well-known Member
Joined
Feb 15, 2002
Messages
4,820
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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?). "
 
Last edited:

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,203
Messages
5,674,392
Members
419,506
Latest member
mpazr001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top