On 7 May 2020, Law no. 26 of 2020 was issued by the Egyptian Government to introduce new progressive tax rates for individual income tax purposes that will apply as from 1 July 2020 with respect to income related to salaries.
www.ey.com
There are dubious some limits in those tables. I believe the following does what is intended.
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G |
|---|
| 1 | Income: | 123,456.00 | 623,456.00 | 723,456.00 | 823,456.00 | 923,456.00 | 1,234,567.00 |
|---|
| 2 | Tax: | 16,816.20 | 133,364.00 | 160,614.00 | 187,864.00 | 215,864.00 | 298,641.75 |
|---|
| 3 | | | | | | | |
|---|
| 4 | Check 1: | 16,816.20 | 133,364.00 | 160,614.00 | 187,864.00 | 215,864.00 | 298,641.75 |
|---|
| 5 | Check 2: | 16,816.20 | 133,364.00 | 160,614.00 | 187,864.00 | 215,864.00 | 298,641.75 |
|---|
| 6 | | | | | | | |
|---|
| 7 | TAX TABLE | | | | | | |
|---|
| 8 | | column for income>=... | | | | | |
|---|
| 9 | | 0 | 600,000 | 700,000 | 800,000 | 900,000 | 1,000,000 |
|---|
| 10 | rate | for amount over... | | | | | |
|---|
| 11 | | (leave this line empty) |
|---|
| 12 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0 |
|---|
| 13 | 2.50% | 15,000 | 0 | 0 | 0 | 0 | 0 |
|---|
| 14 | 10.00% | 30,000 | 30,000 | 0 | 0 | 0 | 0 |
|---|
| 15 | 15.00% | 45,000 | 45,000 | 45,000 | 0 | 0 | 0 |
|---|
| 16 | 20.00% | 60,000 | 60,000 | 60,000 | 60,000 | 0 | 0 |
|---|
| 17 | 22.50% | 200,000 | 200,000 | 200,000 | 200,000 | 200,000 | 0 |
|---|
| 18 | 25.00% | 400,000 | 400,000 | 400,000 | 400,000 | 400,000 | 400,000 |
|---|
|
|---|
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..."
