Tax calculation... IF/AND/OR?

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
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
 

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
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
yeah I know it's a right pain and struggling to believe it's really like this but it came from EY so they should know I hope! what do you mean by the sliding scale, adjust the layout of my spreadsheet somehow?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You have not explained the concept, brackets, or rates for above 600,000.
I do not want to guess!
How many tables are required?
 

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
You have not explained the concept, brackets, or rates for above 600,000.
I do not want to guess!
How many tables are required?
not sure what do you mean by how many tables? I have tried to explain it better below

salary = 600k therefore tax band A
tax = 0% for first 15k, 2.5% for next 15k, 10% for next 15k, 15% for next 15k, 20% for next 140k, 22.5% for 200k and 25% for the amount above 400k which is the remaining 200k

salary = 750k therefore tax band C
tax = 10% for first 45k, 15% for next 15k, 20% for next 140k, 22.5% for next 200k and 25% for the amount above 400k which is the remaining 350k

salary = 5,000,000 therefore tax band F
tax = 22.5% for first 400k and 25% for the amount above 400k which is the remaining 4.6m

Tax calc.xlsx
BCDEFGHIJKLMNOPQR
1Tax Rate:
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 Not applicable - 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 Not applicableNot applicable - 45,000 45,000 60,000 60,000 200,000 200,000 400,000 400,000
7D 800,000 900,000 Not applicableNot applicableNot applicable - 60,000 60,000 200,000 200,000 400,000 400,000
8E 900,000 1,000,000 Not applicableNot applicableNot applicableNot applicable - 200,000 200,000 400,000 400,000
9F 1,000,000 Not applicableNot applicableNot applicableNot applicableNot applicable - 400,000 400,000
10
11Annual SalaryTax paidTax paid should equal:
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 (2)
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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
N.B. The following is a guess that you have three distinct Bands or sets of Bracket and Rates.
You now mention Band A, Band C and Band F.

You can review the following approach.
Edit the bands and rates as appropriate.

UDF Tax or Commission.xlsm
ABCDEFG
1Income$2,000,000.00
2Tax$490,000.00
3
4Band ARateRate
5BracketsRateDifferenceBracketsRateDifference
60.000.0%0.0%0.0020.0%20.0%
715,000.002.5%2.5%200,000.0022.5%2.5%
830,000.0010.0%7.5%400,000.0025.0%2.5%
945,000.0015.0%5.0%1,000,000.00
1060,000.0020.0%5.0%
11200,000.0022.5%2.5%
12400,000.0025.0%2.5%Rate
13BracketsRateDifference
140.0022.5%22.5%
15400,000.0025.0%2.5%
1cc
Cell Formulas
RangeFormula
B2B2=IF(B1>1000000,SUMPRODUCT(--(B1>rB_3),B1-rB_3,rD_3),IF(B1>600000,SUMPRODUCT(--(B1>rB_2),B1-rB_2,rD_2),IF(B1>0,SUMPRODUCT(--(B1>rB_1),B1-rB_1,rD_1),0)))
G14:G15,C6:C12,G6:G8G6=F6-N(F5)
Named Ranges
NameRefers ToCells
'1cc'!rB_1='1cc'!$A$6:$A$12B2
'1cc'!rB_2='1cc'!$E$6:$E$8B2
'1cc'!rB_3='1cc'!$E$14:$E$15B2
'1cc'!rD_1='1cc'!$C$6:$C$12B2
'1cc'!rD_2='1cc'!$G$6:$G$8B2
'1cc'!rD_3='1cc'!$G$14:$G$15B2
'1cc'!rL='1cc'!$A$6:$C$11B2
 

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

found this to explain the same data but displayed on a different table with percentages in y axis and salary bands along the top

I am getting a name error when trying to copy your data, it all looks the same to me :z
Tax calc.xlsx
ABCDEFG
1Income$2,000,000.00
2Tax#NAME?
3
4Band ARateRate
5BracketsRateDifferenceBracketsRateDifference
600.00%0.00%020.00%20.00%
715,000.002.50%2.50%200,000.0022.50%2.50%
830,000.0010.00%7.50%400,000.0025.00%2.50%
945,000.0015.00%5.00%1,000,000.00
1060,000.0020.00%5.00%
11200,000.0022.50%2.50%
12400,000.0025.00%2.50%Rate
13BracketsRateDifference
14022.50%22.50%
15400,000.0025.00%2.50%
16
17B_1=Sheet4!$A$6:$A$12
18B_2=Sheet4!$E$6:$E$8
19B_3=Sheet4!$E$14:$E$15
20D_1=Sheet4!$C$6:$C$12
21D_2=Sheet4!$G$6:$G$8
22D_3=Sheet4!$G$14:$G$15
23L=Sheet4!$A$6:$C$11
Sheet4
Cell Formulas
RangeFormula
B2B2=IF(B1>1000000,SUMPRODUCT(--(B1>rB_3),B1-rB_3,rD_3),IF(B1>600000,SUMPRODUCT(--(B1>rB_2),B1-rB_2,rD_2),IF(B1>0,SUMPRODUCT(--(B1>rB_1),B1-rB_1,rD_1),0)))
G6:G8,G14:G15,C6:C12G6=F6-N(F5)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The Named Ranges were all listed in the post.
You can add the additional bands.
Review and edit the bands and rates as appropriate.
I did not use named ranges with this post.

New.xlsm
ABCDEFG
1Income$600,000.00
2Tax$127,125.00
3
4Band ARateRate
5BracketsRateDifferenceBracketsRateDifference
60.000.0%0.0%0.0020.0%20.0%
715,000.002.5%2.5%200,000.0022.5%2.5%
830,000.0010.0%7.5%400,000.0025.0%2.5%
945,000.0015.0%5.0%
1060,000.0020.0%5.0%
11200,000.0022.5%2.5%
12400,000.0025.0%2.5%Rate
13BracketsRateDifference
140.0022.5%22.5%
15400,000.0025.0%2.5%
16
1
Cell Formulas
RangeFormula
B2B2=IF(B1>1000000,SUMPRODUCT(--(B1>E14:E15),B1-E14:E15,G14:G15),IF(B1>600000,SUMPRODUCT(--(B1>E6:E8),B1-E6:E8,G6:G8),IF(B1>0,SUMPRODUCT(--(B1>A6:A12),B1-A6:A12,C6:C12),0)))
G14:G15,C6:C12,G6:G8G6=F6-N(F5)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,030
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT



There are dubious some limits in those tables. I believe the following does what is intended.

Book1
ABCDEFG
1Income:123,456.00623,456.00723,456.00823,456.00923,456.001,234,567.00
2Tax:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
3
4Check 1:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
5Check 2:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
6
7TAX TABLE
8column for income>=...
90600,000700,000800,000900,0001,000,000
10ratefor amount over...
11(leave this line empty)
120.00%000000
132.50%15,00000000
1410.00%30,00030,0000000
1515.00%45,00045,00045,000000
1620.00%60,00060,00060,00060,00000
1722.50%200,000200,000200,000200,000200,0000
1825.00%400,000400,000400,000400,000400,000400,000
Sheet1
Rich (BB code):
Formulas:
B2: =SUMPRODUCT((B1 > INDEX($B$12:$G$18, 0, MATCH(B1,$B$9:$G$9))) * (B1 - INDEX($B$12:$G$18, 0, MATCH(B1,$B$9:$G$9))), $A$12:$A$18 - $A$11:$A$17)
B4: =SUMPRODUCT((B1>B12:B18) * (B1-B12:B18), $A$12:$A$18 - $A$11:$A$17)
Copy B2 into C2:G2
Copy B4 into C4:G4
B5: =15000*2.5% + 15000*10% + 15000*15% + (B1-B16)*20%
C5: =30000*2.5% + 15000*10% + 15000*15% + 140000*20% + 200000*22.5% + (C1-400000)*25%
D5: =45000*10% + 15000*15% + 140000*20% + 200000*22.5% + (D1-400000)*25%
E5: =60000*15% + 140000*20% + 200000*22.5% + (E1-400000)*25%
F5: =200000*20% + 200000*22.5% + (F1-400000)*25%
G5: =400000*22.5% + (G1-400000)*25%


You only need the tax table in A8:G18, the income in B1, and the formula in B2.

The rest is to demonstrate correctness, notably the formulas in the rows labeled Check1 and Check2.

(Note: The formula in B5 might need to be adjusted for different incomes in that range.)

The INDEX/MATCH expression selects the column of bracket limits to use: B12:B18, C12:C18, etc.

Note: You might want to explicit round to 0 or 2 decimal places, depending on the intended precision of the result.

-----

The dubious limits in the Egyptian EY document are:


1. The columns are label "less than 600,000" and "more than 600,000", for example. What about exactly 600,000?!

I assume that "more than 600,000" means "not less than 600,000". That is, "greater than or equal to 600,000".


2. The bracket limits "from 1 to 15,000" and "from 15,001 to 45,000", for example. What about 15,000.01 to 15,000.99?!

I assume either that amounts of taxable income are rounded to integers, or 15,000.01 to 15,000.99 should be taxed at the rate above 15,000.

In either case, I assume that "from 15,001..." means "for more than (over) 15,000..."
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
With a quick test, Joeu2004's solution gives the same result as my solution.
I completed the list of Tables. A UDF might be the best approach; the if clause is a bit long.

UDF Tax or Commission.xlsm
ABC
1Income$1,234,567.00
2Tax$298,641.75
3
4A>0Rate
5BracketsRateDifference
60.000.0%0.0%
715,000.002.5%2.5%
830,000.0010.0%7.5%
945,000.0015.0%5.0%
1060,000.0020.0%5.0%
11200,000.0022.5%2.5%
12400,000.0025.0%2.5%
13
14B>600,000Rate
15BracketsRateDifference
160.002.5%2.5%
1730,000.0010.0%7.5%
1845,000.0015.0%5.0%
1960,000.0020.0%5.0%
20200,000.0022.5%2.5%
21400,000.0025.0%2.5%
22
23C>700,000Rate
24BracketsRateDifference
250.0010.0%10.0%
2645,000.0015.0%5.0%
2760,000.0020.0%5.0%
28200,000.0022.5%2.5%
29400,000.0025.0%2.5%
30
31D>800,000Rate
32BracketsRateDifference
330.0015.0%15.0%
3460,000.0020.0%5.0%
35200,000.0022.5%2.5%
36400,000.0025.0%2.5%
37
38E>900,000Rate
39BracketsRateDifference
400.0020.0%20.0%
41200,000.0022.5%2.5%
42400,000.0025.0%2.5%
43
44F>1,000,000Rate
45BracketsRateDifference
460.0022.5%22.5%
47400,000.0025.0%2.5%
1c
Cell Formulas
RangeFormula
B2B2=IF(B1>1000000,SUMPRODUCT(--(B1>A46:A47),B1-A46:A47,C46:C47),IF(B1>900000,SUMPRODUCT(--(B1>A40:A42),B1-A40:A42,C40:C42),IF(B1>800000,SUMPRODUCT(--(B1>A33:A36),B1-A33:A36,C33:C36),IF(B1>700000,SUMPRODUCT(--(B1>A25:A29),B1-A25:A29,C25:C29),IF(B1>600000,SUMPRODUCT(--(B1>A16:A21),B1-A16:A21,C16:C21),IF(B1>0,SUMPRODUCT(--(B1>A6:A12),B1-A6:A12,C6:C12),0))))))
C46:C47,C40:C42,C33:C36,C25:C29,C16:C21,C6:C12C6=B6-N(B5)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,030
Office Version
  1. 2010
Platform
  1. Windows
Improvement.... I'm sorry. I started with one design, and as I gravitated to the simpler design, I forgot to include a simplification that eliminates the need for $A$12:$A$18 - $A$11:$A$17 in the SUMPRODUCT formula.

The tax table should be:

egypt tax table depends on income.xlsx
ABCDEFGH
1Income:123,456.00623,456.00723,456.00823,456.00923,456.001,234,567.00
2Tax:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
3
4Check 1:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
5Check 2:16,816.20133,364.00160,614.00187,864.00215,864.00298,641.75
6
7TAX TABLE
8column for income>=...
90600,000700,000800,000900,0001,000,000
10ratediff ratefor amount over...
110.00%0.00%000000
122.50%2.50%15,00000000
1310.00%7.50%30,00030,0000000
1415.00%5.00%45,00045,00045,000000
1520.00%5.00%60,00060,00060,00060,00000
1622.50%2.50%200,000200,000200,000200,000200,0000
1725.00%2.50%400,000400,000400,000400,000400,000400,000
Sheet1 (2)


The change in the tax table is the insertion of column B, which obviates the need for the empty row ("leave this line empty"). The formula in B12 is =A12-A11, which we copy down.

Thus, the tax formula now in C2 (and copied across) is:

=SUMPRODUCT((C1>INDEX($C$11:$H$17,0,MATCH(C1,$C$9:$H$9))) * (C1-INDEX($C$11:$H$17,0,MATCH(C1,$C$9:$H$9))), $B$11:$B$17)

And the formula now in C4 (and copied across) is:

=SUMPRODUCT((C1>C11:C17) * (C1-C11:C17), $B$11:$B$17)
 

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
Thanks both! got them both working. Yes joe the EY page is incomplete but it comes from government (rank 166/190 in ease of doing business in relation to tax so..!)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,186
Messages
5,640,688
Members
417,161
Latest member
Devon150

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