Calculating tax based on slabs

[bobitybobbob]

Dear all, Need some guidance calculating taxes based on fixed slabs.
Ive attempted creating a table with the three columns below and tried to work out a logic based on VLOOKUP but am unable to get the formula setup right.
I have salaries at varying levels and I am trying to create a workbook where I enter the monthly amount and get the tax. Note the slabs below are based on an annual total.

Slabs are

1	400,000.00		0%
2	400,001.00	to 800,000.00	1000
3	800,001.00	to 1,200,000.00	2000
4	1,200,001.00	to 2,500,000.00	5% of amount exceeding Rs 1,200,000
5	2,500,001.00	to 4,000,000.00	65,000 + 15% of amount exceeding Rs 2,500,000
6	4,000,001.00	to 8,000,000.00	290,000 +20% of amount exceeding Rs 4,000,000
7	8,000,001.00	to and above	1,090,000 +25% of amount exceeding Rs 8,000,000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

When calculating the conditions are:
If the annual salary is under 400,000 apply zero tax.
If the annual salary is under 800,000 apply 1000 tax.
If the annual salary is under 1,200,000 apply 2000 tax.
If the annual salary is under 2,500,000 apply 2000 on 1,200,000 and 5% on the remaining
If the annual salary s under 4,000,000 apply 65,000 + 15% on remaining above 4,000,000

Many thanks. And firstime poster here so go easy on me

 

[DRSteele]

Re: Help calculating tax based on slabs

Welcome to the forum.

You will need to elaborate. I think what you mean is that these are marginal taxes, not absolute.

A literal reading of what you've written says that if an employee's annual salary is 1,2000,000 then his tax would be zero because the amount exceeding 1.2mm is zero. Do you really mean this instead: the 1.2mm employee would pay tax of 1000 (on the first 800,000) plus 2000 on the next (400,000) and then 5% on the excess over 1.2mm (which happens to be nil) for a total tax of 1000+2000+0 = 3,000?

Help us understand by manually calculating for us what you think these employees should pay in tax: a) 300,000 b) 450,000 c) 875,000 d) 2,000,000 e) 3,400,000 f) 6,000,000 g) 10,000,000

 

[bobitybobbob]

Re: Help calculating tax based on slabs

Sorry for not being clear and thanks for making me do this. Here are some examples
If salary < 800,001, Tax = 1000
If salary < 1,200,001, Tax = 2000
If salary < 2,500,001, Tax = 2000 + 5% of salary minus 1,200,000 e.g. Salary = 2,000,000. First 1,200,000 tax = 2000, next (2,000,000 - 1,200,000) = (800,000 * 0.05) = 40,000. Total Tax = 2000+40,000 = 42,000
If salary < 4,000,001, Tax = 65,000 + (Salary - 2,500,000)*.15% e.g. IF Salary = 3,000,000. Tax = 65,000 + (3,000,000 - 2,500,000)*15% = 140,000

Having written this down I may have worked this out using nested IF statements instead. Just testing out in excel now will post outcome.

 

[joeu2004]

Re: Help calculating tax based on slabs

If salary < 800,001, Tax = 1000
If salary < 1,200,001, Tax = 2000
If salary < 2,500,001, Tax = 2000 + 5% of salary minus 1,200,000 e.g. Salary = 2,000,000. First 1,200,000 tax = 2000, next (2,000,000 - 1,200,000) = (800,000 * 0.05) = 40,000. Total Tax = 2000+40,000 = 42,000
If salary < 4,000,001, Tax = 65,000 + (Salary - 2,500,000)*.15% e.g. IF Salary = 3,000,000. Tax = 65,000 + (3,000,000 - 2,500,000)*15% = 140,000

I believe your original table was correct; not this one. But you omitted an important detail.

First, I did find a non-government website [1] that has a table similar to your revised table. But it does not make sense. Based on the revised table, if the taxable income is 2,500,000 (3rd tier), the tax would be 2000 + (2,500,000 - 1,200,000)*5% = 2000 + 65,000 = 67,000. But if the taxable income is 2,500,001 (4th tier), the tax would be 65,000 + (2,500,000 - 2,500,001)*15% = 65,000 + 0.15 = 65,000.15 -- less(!).

In fact, according to a Pakistan government document [2], the withhold tax "regime" [sic; regimen?] is as your wrote originally, with one additional rule:

``Provided that where the taxable income exceeds Rs. 800,000/- the minimum tax payable shall be Rs.2,000/-``

So for taxable income over 1,200,000 up to 2,500,000, the tax is the minimum of 2000 or 5% over 1,200,000.

IMHO, a better way to write the tax table is:

	A	B	C	D	E
1	over....	up to....	tax....	plus....	over....
2	0	400,000	0	0%	0
3	400,000	800,000	1,000	0%	400,000
4	800,000	1,240,000	2,000	0%	800,000
5	1,240,000	2,500,000	2,000	5%	1,240,000
6	2,500,000	4,000,000	65,000	15%	2,500,000
7	4,000,000	8,000,000	290,000	20%	4,000,000
8	8,000,000		1,090,000	25%	8,000,000

<tbody>
</tbody>

Note that with that change, the tiers are copacetic. The max tax for 800,000 to 1,240,000 is 2,000; for 1,240,000 to 2,500,000: 65,000; for 2,500,000 to 4,000,000: 290,000; and for 4,000,000 to 8,000,000: 1,090,000.

Given a taxable income in column I, a VLOOKUP implementation of the tax in column J might be (showing I2):

=VLOOKUP(I2,$A$2:$E$8,3) + VLOOKUP(I2,$A$2:$E$8,4)*(I2-VLOOKUP(I2,$A$2:$E$8,1))

Note that we do not use or need column E in the table above.

The following is a complete table of the tax breakpoints.

	I	J
1	taxl inc	tax
2	0	0.00
3	400,000	1,000.00
4	400,001	1,000.00
5	800,000	2,000.00
6	800,001	2,000.00
7	1,240,000	2,000.00
8	1,240,001	2,000.05
9	2,500,000	65,000.00
10	2,500,001	65,000.15
11	4,000,000	290,000.00
12	4,000,001	290,000.20
13	8,000,000	1,090,000.00
14	8,000,001	1,090,000.25
15	9,000,000	1,340,000.00

<tbody>
</tbody>

-----
[1] https://www.taxathand.com/article/10774/Pakistan/2018/Individual-tax-law-changes-may-apply-to-expatriates

[2] Go to https://www.fbr.gov.pk/docs/Withholding-Tax-Rates/642, click "Withholding Tax Regime", and go to page 4.

 

[joeu2004]

Re: Help calculating tax based on slabs

Given a taxable income in column I, a VLOOKUP implementation of the tax in column J might be (showing I2):
=VLOOKUP(I2,$A$2:$E$8,3) + VLOOKUP(I2,$A$2:$E$8,4)*(I2-VLOOKUP(I2,$A$2:$E$8,1))

Alternatively, we could "add" column F to the tax table (but remember: column E is superfluous), and calculate the tax in column K as follows (showing K2):

=IF(I2<=$A$5, LOOKUP(I2, $A$2:$A$4, $C$2:$C$4), $C$4 + SUMPRODUCT((I2>$A$5:$A$8)*(I2-$A$5:$A$8), $F$5:$F$8))

The complete tax table:

	A	B	C	D	E	F
1	over....	up to....	min tax....	plus....	over....	diff%
2	0	400,000	0	0%	0
3	400,000	800,000	1,000	0%	400,000
4	800,000	1,240,000	2,000	0%	800,000
5	1,240,000	2,500,000	2,000	5%	1,240,000	5%
6	2,500,000	4,000,000	65,000	15%	2,500,000	10%
7	4,000,000	8,000,000	290,000	20%	4,000,000	5%
8	8,000,000		1,090,000	25%	8,000,000	5%

<tbody>
</tbody>

The formula in F5 is =D5-D4. Copy F5 into F6:F8.

The tax calculation for all tax breakpoints:

	I	J	K
1	taxl inc	VLOOKUP tax	SUMPRODUCT tax
2	0	0.00	0.00
3	400,000	1,000.00	1,000.00
4	400,001	1,000.00	1,000.00
5	800,000	2,000.00	2,000.00
6	800,001	2,000.00	2,000.00
7	1,240,000	2,000.00	2,000.00
8	1,240,001	2,000.05	2,000.05
9	2,500,000	65,000.00	65,000.00
10	2,500,001	65,000.15	65,000.15
11	4,000,000	290,000.00	290,000.00
12	4,000,001	290,000.20	290,000.20
13	8,000,000	1,090,000.00	1,090,000.00
14	8,000,001	1,090,000.25	1,090,000.25
15	9,000,000	1,340,000.00	1,340,000.00

<tbody>
</tbody>

-----

PS.... The aforementioned Pakistan government reference does not say (not that I can see) whether the tax table is for annual taxable incomes per se. I'm not familiar with Pakistan incomes; so I don't know what is "reasonable" to assume. If those are annual amounts, one simple way to use the same table for monthly incomes is: multiply the monthly by 12 (i.e 12*I2), and divide the calculated tax by 12. But I cannot say if that is how monthly withholding should be calculated for Pakistan.

 

[joeu2004]

Re: Help calculating tax based on slabs

``Provided that where the taxable income exceeds Rs. 800,000/- the minimum tax payable shall be Rs.2,000/-``
So for taxable income over 1,200,000 up to 2,500,000, the tax is the minimum of 2000 or 5% over 1,200,000.

Rookie mistake! That should be: the maximum of 2000 or 5% over 1,200,000.

 

[joeu2004]

Re: Help calculating tax based on slabs

(Sorry for the incessant posts)

Errata....

	A	B	C	D	E	F
1	over....	up to....	min tax....	plus....	over....	diff%
2	0	400,000	0	0%	0
3	400,000	800,000	1,000	0%	400,000
4	800,000	1,240,000	2,000	0%	800,000

<tbody>
</tbody>

[....]

	I	J	K
1	taxl inc	VLOOKUP tax	SUMPRODUCT tax
2	0	0.00	0.00
3	400,000	1,000.00	1,000.00
4	400,001	1,000.00	1,000.00
5	800,000	2,000.00	2,000.00

<tbody>
</tbody>

I see I have an off-by-one-row error: the tax in for 400,000 should be 0 in J3:K3; and the tax for 800,000 should be 1000 in J5:K5.

A "quick fix" would be to change A3 to 400,000.01 and A4 to 800,000.01.

But I don't like to do things like that. It assumes that taxable income is rounded at least to the 1/100th in Pakistan. More importantly, it's a "lie" insofar as the calculation is really for "at least" instead of "over" those amounts in just those rows.

(The other rows work because of the "continuous" nature of the tiers.)

Arguably, a more "accurate" implementation is one that I was trying to avoid, namely: leave A3 and A4 as-is, and modify the formulas as follows:

I2: =IF(I2<=$B$2, $C$2,IF(I2<=$B$3, $C$3,IF(I2<=$B$4, $C$4,
     VLOOKUP(I2,$A$5:$E$8,3)+VLOOKUP(I2,$A$5:$E$8,4)*(I2-VLOOKUP(I2,$A$5:$E$8,1)))))

J2: =IF(I2<=$B$2, $C$2,IF(I2<=$B$3, $C$3,IF(I2<=$B$4, $C$4,
    $C$4+SUMPRODUCT((I2>$A$5:$A$8)*(I2-$A$5:$A$8),$F$5:$F$8))))

Copy I2:J2 into I3:J15

 

[bobitybobbob]

Re: Help calculating tax based on slabs

Firstly ... thoroughly impressed! really appreciate your help here. My approach was rather basic and clearly flawed as you pointed out

Based on the revised table, if the taxable income is 2,500,000 (3rd tier), the tax would be 2000 + (2,500,000 - 1,200,000)*5% = 2000 + 65,000 = 67,000. But if the taxable income is 2,500,001 (4th tier), the tax would be 65,000 + (2,500,000 - 2,500,001)*15% = 65,000 + 0.15 = 65,000.15 -- less(!).

I was using a simple nested if

=IF(S10<Q3,0,IF(S10<Q4,(S10)*0+1000,IF(S10<Q5,(S10)*0+2000,IF(S10<Q6,(S10-R4)*0.05+2000,IF(S10<Q7,(S10-R5)*0.15+65000,IF(S10<Q8,(S10-R6)*0.2+290000,(S10-R7)*0.25+1090000))))))

This is exactly the outcome I was after and the results are what I expect to see

(Sorry for the incessant posts)

I2: =IF(I2<=$B$2, $C$2,IF(I2<=$B$3, $C$3,IF(I2<=$B$4, $C$4,
     VLOOKUP(I2,$A$5:$E$8,3)+VLOOKUP(I2,$A$5:$E$8,4)*(I2-VLOOKUP(I2,$A$5:$E$8,1)))))

J2: =IF(I2<=$B$2, $C$2,IF(I2<=$B$3, $C$3,IF(I2<=$B$4, $C$4,
    $C$4+SUMPRODUCT((I2>$A$5:$A$8)*(I2-$A$5:$A$8),$F$5:$F$8))))

Copy I2:J2 into I3:J15

A couple of questions if you dont mind. How do you arrive at the 1,240,000 number. Also, and this is more of a new request the financial year runs from June to June which means there are 6 months left in this year. How do I dynamically get the monthly tax out ... the bands are based on an annual gross (and need to be /12 to get a monthly).

Many thanks and again, very very impressive!

 

[joeu2004]

Re: Help calculating tax based on slabs

Here is yet-another more-compact formula (I prefer to use SUMPRODUCT):

J2: =IF(I2 <= $A$5, LOOKUP(MAX(0, I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    VLOOKUP(I2, $A$5:$E$8, 3) + VLOOKUP(I2, $A$5:$E$8, 4)*(I2 - VLOOKUP(I2, $A$5:$E$8, 1)))

K2: =IF(I2 <= $A$5, LOOKUP(MAX(0, I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    $C$4 + SUMPRODUCT((I2 > $A$5:$A$8)*(I2 - $A$5:$A$8), $F$5:$F$8))

Copy J2:K2 into J3:K15

(Sorry about the typos in the previous postings.)

How do you arrive at the 1,240,000 number.

Recall the "footnote" in the Pakistani reference: ``where the taxable income exceeds Rs. 800,000/- [,] the minimum tax payable shall be Rs.2,000/-``.

Note that (1,240,000 - 1,200,000)*5% = 2,000. So for taxable incomes between 1,200,000 and 1,240,000, the tax is 2,000 instead of (TI - 1,200,000)*5%, because the latter is less than 2,000.

(We determine 1,240,000 by calculating 2000/0.05 = 40,000 "over" 1,200,000 = 1,240,000.)

Since the tax over 800,000 to 1,200,000 is already 2,000, "we" (the Pakistani FBR) might as well simply extend the fixed-tax range to 1,240,000, and structure the next tier as 2,000 plus 5% over 1,240,000. The result is the same.

How do I dynamically get the monthly tax out ... the bands are based on an annual gross (and need to be /12 to get a monthly).

As I alluded to before (and refine below):

J2: =ROUND(IF(12*I2 <= $A$5, LOOKUP(MAX(0, 12*I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    VLOOKUP(12*I2, $A$5:$E$8, 3) + VLOOKUP(12*I2, $A$5:$E$8, 4)*(12*I2 - VLOOKUP(12*I2, $A$5:$E$8, 1))) / 12, 2)

K2: =ROUND(IF(12*I2 <= $A$5, LOOKUP(MAX(0, 12*I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    $C$4 + SUMPRODUCT((12*I2 > $A$5:$A$8)*(12*I2 - $A$5:$A$8), $F$5:$F$8)) / 12, 2)

Of course, it would be better to enter the monthly taxable income into H2, and change I2 to =12*H2.

But I do not know if that calculation agrees with Pakistani FBR regulations.

Sometimes, the tax board has separate tables for different salary payment frequencies (hourly, daily, weekly, biweekly, semi-monthly, monthly, quarterly). The effect is usually the same; but the devil is in the rounding details.

To be safe, you might use ROUNDUP(...,0) instead of ROUND(...,2). You can apply for a refund at the end of the year, if necessary.

On the other hand, if you are the employer, it might be less risky (avoiding any employee complaint) if you round or even round down. Again, that might be controlled by Pakistani FBR regulations.

 

[joeu2004]

Re: Help calculating tax based on slabs

J2: =ROUND(IF(12*I2 <= $A$5, LOOKUP(MAX(0, 12*I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    VLOOKUP(12*I2, $A$5:$E$8, 3) + VLOOKUP(12*I2, $A$5:$E$8, 4)*(12*I2 - VLOOKUP(12*I2, $A$5:$E$8, 1))) / 12, 2)

K2: =ROUND(IF(12*I2 <= $A$5, LOOKUP(MAX(0, 12*I2 - 0.01), $A$2:$A$4, $C$2:$C$4),
    $C$4 + SUMPRODUCT((12*I2 > $A$5:$A$8)*(12*I2 - $A$5:$A$8), $F$5:$F$8)) / 12, 2)

Of course, it would be better to enter the monthly taxable income into H2, and change I2 to =12*H2.

Well, that was "clear as mud". Sorry. What I meant is....

Use 12*I2 in the formulas if I2 contains monthly taxable income instead of the annual taxable income "breakpoints" that I used in previous tables.

Alternatively, continue to use I2 in the formulas, and calculate the annual taxable income in I2 by multiplying the monthly taxable income in H2 by 12.

The monthly taxable income "breakpoints" would be 0.00, 33,333.33, 33,333.42, 66,666.66, 66,666.75, 103,333.33, 103,333.42, 208,333.33, 208,333.42, 333,333.33, 333,333,42, 666,666.66, 666,666.75, 750,000.00.