Tax calculation... IF/AND/OR?

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
238
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
552
Office Version
365, 2019, 2010
Platform
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

Board Regular
Joined
Feb 26, 2006
Messages
238
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,097
Office Version
2010
Platform
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,097
Office Version
2010
Platform
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?). "
 
Last edited:

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
238
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
552
Office Version
365, 2019, 2010
Platform
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

Board Regular
Joined
Feb 26, 2006
Messages
238
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top