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
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,225
Office Version
  1. 2010
Have you checked the site I indicated?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,030
Office Version
  1. 2010
Platform
  1. Windows
Have you checked the site I indicated?

First, when I click on that link, www.mcgimpsey.com/excel/variablerate1.html , I get a security risk warning from Firefox. The link that I use (from a US location) is www.mcgimpsey.com/excel/variablerate.html (missing "1"). That does not cause a security risk warning in Firefox.

Second, we are all using that idea in our formulas. So, there is no need for the BrutalLogiC to follow that link, unless he wants to understand our formulas better (perhaps).

Finally, the point here is: a single instance of that formula is not sufficient. The Egyptian tax table uses several progressive tax schedules, depending on the total taxable income.

So we have provided methods for selecting the appropriate McGimpsey-like formula, depending on the taxable income.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,890
Office Version
  1. 365
Platform
  1. Windows
The problem with the link was the https:// at the start, which I have corrected.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The same ideas are used but the Tax Tables are made dynamic ie they adjust depending on the Income Amount.

UDF Tax or Commission.xlsm
ABC
1Income$150,000.00
2Tax$22,125.00
3
4[rB][rR] Rate
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
1c
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>rB),B1-A6:A12,rR)
A7A7=IF(B1>600000,0,15000)
A8A8=IF(B1>700000,0,30000)
A9A9=IF(B1>800000,0,45000)
A10A10=IF(B1>900000,0,60000)
A11A11=IF(B1>1000000,0,200000)
C6:C12C6=B6-N(B5)
Named Ranges
NameRefers ToCells
'1c'!rB='1c'!$A$6:$A$12B2
'1c'!rR='1c'!$C$6:$C$12B2


UDF Tax or Commission.xlsm
ABC
1Income$600,000.00
2Tax$127,125.00
1c
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>rB),B1-A6:A12,rR)


UDF Tax or Commission.xlsm
ABC
1Income$2,000,000.00
2Tax$490,000.00
3
1c
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>rB),B1-A6:A12,rR)
 

Dave Patton

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

ADVERTISEMENT

Joe's formula with Index and the regular SumProduct Formula and Manual calculations yield different amounts
at the very top of the brackets 600,000 700,000 800,000 etc.
I adjusted the range C9:G9 by adding 0.01. The amounts with that edit reconcile.

UDF Tax or Commission.xlsm
ABCDEFG
1Income:600,000.00700,000.00800,000.00900,000.001,000,000.002,000,000.00
2Tax:127,125.00152,500.00179,750.00210,000.00235,000.00490,000.00
3
4Check 1:127,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
5Check 2:127,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
6
7TAX TABLE
8column for income>=...
90600,000.01700,000.01800,000.01900,000.001,000,000.01
1cc
Cell Formulas
RangeFormula
B2B2=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)
C2:G2C2=SUMPRODUCT((C1>INDEX($B$12:$G$18,0,MATCH(C1,$B$9:$G$9)))*(C1-INDEX($B$12:$G$18,0,MATCH(C1,$B$9:$G$9))),$A$12:$A$18-$A$11:$A$17)
B4B4=SUMPRODUCT(--(B1>B12:B18),(B1-B12:B18),$A$12:$A$18-$A$11:$A$17)
C4:G4C4=SUMPRODUCT((C1>C12:C18)*(C1-C12:C18),$A$12:$A$18-$A$11:$A$17)
B5B5=15000*0+15000*2.5% + 15000*10% + 15000*15% + 140000*0.2+200000*0.225+(200000-0.01)*0.25
C5C5=30000*2.5% + 15000*10% + 15000*15% + 140000*20% + 200000*22.5% + (C1-400000)*25%
D5D5=45000*10% + 15000*15% + 140000*20% + 200000*22.5% + (D1-400000)*25%
E5E5=60000*15% + 140000*20% + 200000*22.5% + (E1-400000)*25%
F5F5=200000*20% + 200000*22.5% + (F1-400000)*25%
G5G5=400000*22.5% + (G1-400000)*25%


Without adding the amount to C9:G9

UDF Tax or Commission.xlsm
ABCDEFG
1Income:600,000.00700,000.00800,000.00900,000.001,000,000.002,000,000.00
2Tax:127,500.00154,750.00182,000.00210,000.00240,000.00490,000.00
3
4Check 1:127,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
5Check 2:127,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
6
7TAX TABLE
8column for income>=...
90600,000.00700,000.00800,000.00900,000.001,000,000.00
1cc
Cell Formulas
RangeFormula
B2B2=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)
C2:G2C2=SUMPRODUCT((C1>INDEX($B$12:$G$18,0,MATCH(C1,$B$9:$G$9)))*(C1-INDEX($B$12:$G$18,0,MATCH(C1,$B$9:$G$9))),$A$12:$A$18-$A$11:$A$17)
B4B4=SUMPRODUCT(--(B1>B12:B18),(B1-B12:B18),$A$12:$A$18-$A$11:$A$17)
C4:G4C4=SUMPRODUCT((C1>C12:C18)*(C1-C12:C18),$A$12:$A$18-$A$11:$A$17)
B5B5=15000*0+15000*2.5% + 15000*10% + 15000*15% + 140000*0.2+200000*0.225+(200000-0.01)*0.25
C5C5=30000*2.5% + 15000*10% + 15000*15% + 140000*20% + 200000*22.5% + (C1-400000)*25%
D5D5=45000*10% + 15000*15% + 140000*20% + 200000*22.5% + (D1-400000)*25%
E5E5=60000*15% + 140000*20% + 200000*22.5% + (E1-400000)*25%
F5F5=200000*20% + 200000*22.5% + (F1-400000)*25%
G5G5=400000*22.5% + (G1-400000)*25%
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,030
Office Version
  1. 2010
Platform
  1. Windows
Joe's formula with Index and the regular SumProduct Formula and Manual calculations yield different amounts
at the very top of the brackets 600,000 700,000 800,000 etc.


No, they do not! Not for my interpretation of the Egyptian ambiguous tax tables, as I clearly explained in my posting #17.

You simply do not understand my presentation, as I tried to explain to you in PMs that you initiated.

-----

First, as I explained in a PM, the table and formulas in my posting #19 are my preferred choice. But for this discusssion, I will continue to refer to posting #17, as you do.

-----

Second, there is no need for the formula in B2 to be different from the formulas in C2:G2, other than the cell referenced for the total taxable income (a relative reference).

The difference in your B2 is only a matter of style. If that is your preference, it could be applied to C2:G2 as well. Likewise for B4 and C4:G4.

-----

Third, I previously explained my interpretation of the Egyptian tables, to wit: ``I assume that "more than 600,000" means "not less than 600,000". That is, "greater than or equal to 600,000" ``.

Therefore, the income limits that I have in B9:G9 of the Egyptian tables (0, 600000, 700000 etc) are correct for my interpretation, which is clearly labeled in B8 ("column for income>=...").

If you interpret the Egyptian tables differently, that is your prerogative. But do not assert that my formulas are incorrect because they do not work your interpretation.

-----

Finally, as I explained in a PM, unlike the formulas B2:G2 -- which are the intended solution -- the "check" formulas in B4:G4 and B5:G5 are tailored to the income limits that apply to the respective columns of the Egyptian tables.

Ergo, since column C of the Egyptian tables is for incomes less than 700,000 (again, per my interpretation), the largest value allowed in C1 is 699,999.99, not 700,000 as you insist on trying. Likewise for D1:G1, to wit: the limits are 799,999.99, 899,99.99 etc.

If you enter those top values into their respective columns, the results of the formulas in row 2, 4 and 5 agree.

Admittedly, B1 is an exception. Due to a simplification in the "check" formula in B5, the largest value allowed in B1 is 200,000, the top end of the 20% bracket.

So yes, the formula in B5 does not work for 599,999.99, which is less than 600,000 (per my interpretation).

But I implied that in my posting #17, where I wrote: `` The formula in B5 might need to be adjusted for different incomes in that range``.

Moreover, the "check" formula in B4 does agree with the intended solution in B2, even when B1 is 599,999.99.

In any case, those limits for B1:G1 are only for the formuls in B4:G4 and B5:G5. They do not apply to the formulas in B2:G2, because they are each fully-functional and fully generalalized.

The "check" formulas are intended to demonstrate the correctness of the formulas in B2:G2 by presenting a straight-forward implementation that is correct for each respective column in the Egyptian table.

In other words, they are intended to simplify the calculations in order to demonstrate the intent of the INDEX/MATCH expression (to select the columns demonstrated in B4:G4) and the SUMPRODUCT formula overall (the "manual" calcuations demonstrated in B5:G5).
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
My intent was not to argue or imply your formula was incorrect.
If the 0.01 is added to the range C9:G9, the results in Rows 2, 4, and 5 are equal.
If you believe the results should not equal, that is fine.
From your post #19
UDF Tax or Commission.xlsm
ABCDEFGH
1Income:60000070000080000090000010000002000000
2Tax:127500154750182000210000240000490000
3
4Check 1:127125152500179750207000235000490000
5Check 2:127125152500179750207000235000490000
6Difference37522502250300050000
1ccc
Cell Formulas
RangeFormula
C2:H2C2=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)
C4:H4C4=SUMPRODUCT((C1>C11:C17)*(C1-C11:C17),$B$11:$B$17)
C5C5=15000*2.5% + 15000*10% + 15000*15% + 140000*20% + 200000*22.5% + (C1-400000)*25%
D5D5=30000*2.5% + 15000*10% + 15000*15% + 140000*20% + 200000*22.5% + (D1-400000)*25%
E5E5=45000*10% + 15000*15% + 140000*20% + 200000*22.5% + (E1-400000)*25%
F5F5=60000*15% + 140000*20% + 200000*22.5% + (F1-400000)*25%
G5G5=200000*20% + 200000*22.5% + (G1-400000)*25%
H5H5=400000*22.5% + (H1-400000)*25%
C6:H6C6=C2-C5
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,030
Office Version
  1. 2010
Platform
  1. Windows
My intent was not to argue or imply your formula was incorrect. If the 0.01 is added to the range C9:G9, the results in Rows 2, 4, and 5 are equal. If you believe the results should not equal, that is fine. From your post #19


Re: did not ``argue or imply your formula was incorrect``. Of course you did! You say that my rows 2, 4 and 5 are not equal.

They are equal in my design, if you follow my rules, which are consistent with my interpretation of the ambiguous Egyptian tax tables.

You continue to put 600,000, 700,000, etc into C1:H1 (per post #19, not #17). You are wrong to do that in my design, as I stated previously.

If you want to know how my "check" formulas in row 5 calculate the tax for 600,000, you must enter 600,000 into D1, not C1, because that is the column that corresponds to the Egyptian progressive tax schedule for incomes of 600,000 to 699,999.99 in my design according to my interpretation.

I said that for my design -- and only for the simple "check" formulas in row 5 -- the limits in D1:H1 are 699,999.99, 799,999.99 etc, and 200,000 for C1 as an exception that I explained. As demonstrated from my post #19:

egypt tax table depends on income.xlsx
ABCDEFGH
1Income:200,000.00699,999.99799,999.99899,999.99999,999.992,000,000.00
2Tax:32,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
3
4Check 1:32,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
5Check 2:32,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
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 only difference is in C5 when C1 exceeds 200,000, to wit:

egypt tax table depends on income.xlsx
ABCDEFGH
1Income:600,000.00699,999.99799,999.99899,999.99999,999.992,000,000.00
2Tax:127,500.00152,500.00179,750.00207,000.00235,000.00490,000.00
3
4Check 1:127,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
5Check 2:112,125.00152,500.00179,750.00207,000.00235,000.00490,000.00
6
7TAX TABLE
8column for income>=...
90600,000700,000800,000900,0001,000,000
Sheet1 (2)


The difference is due to a purposeful simplification of the "manual" calculation in order to make the calculation for my example more clear, which I have explained a number of times.

(Of course, I can make the formula in C5 work for values up to 599,999.99 in C1. I choose not to.)
 

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