Tax calculation... IF/AND/OR?

BrutalLogiC

Active Member
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

arthurbr

Well-known Member
Last edited by a moderator:

braindiesel

Well-known Member
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
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
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

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
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
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
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

Replies
0
Views
46
Replies
0
Views
647
Replies
2
Views
384

1,141,314
Messages
5,705,689
Members
421,406
Latest member
kluna90

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.

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

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