Income Tax Calculation in Excel with IF - Pakistan

As per the Income tax ordinance passed by Government of Pakistan, following slabs and income tax rates will be applicable for salaried persons for the year 2017-2018:

1. Where the taxable salary income does not exceed Rs 400,000, the rate of income tax is 0%.

2. Where the taxable income exceeds Rs 400,000 but does not exceed Rs 500,000, the rate of income tax is 2% of the amount exceeding Rs 400,000.

3. Where the taxable income exceeds Rs 500,000 but does not exceed Rs 750,000, the rate of income tax is Rs. 2,000 + 5%of the amount exceeding Rs 500,000.

4. Where the taxable income exceeds Rs 750,000 but does not exceed Rs 1,400,000,the rate of income tax is Rs 14,500 + 10% of the amount exceeding Rs 750,000.

5. Where the taxable income exceeds Rs 1,400,000 but does not exceed Rs 1,500,000, the rate of income tax is Rs 79,500 + 12.5% of the amount exceeding Rs 1,400,000.

6. Where the taxable income exceeds Rs 1,500,000 but does not exceed Rs 1,800,000, the rate of income tax is Rs 92,000 + 15% of the amount exceeding Rs 1,500,000.

7. Where the taxable income exceeds Rs 1,800,000 but does not exceed Rs 2,500,000, rate of tax is Rs 137,000 + 17.5% of the amount exceeding Rs 1,800,000.

8. Where the taxable income exceeds Rs 2,500,000 but does not exceed Rs 3,000,000, the rate of income tax is Rs 259,500 + 20% of the amount exceeding Rs 2,500,000.

9. Where the taxable income exceeds Rs 3,000,000 but does not exceed Rs 3,500,000, the rate of income tax is Rs 359,500 + 22.5% of the amount exceeding Rs 3,000,000.

10. Where the taxable income exceeds Rs 3,500,000 but does not exceed Rs 4,000,000, the rate of income tax is Rs 472,000 + 25% of the amount exceeding Rs 3,500,000.

11. Where the taxable income exceeds Rs 4,000,000 but does not exceed Rs 7,000,000, the rate of income tax is Rs 597,000 + 27.5% of the amount exceeding Rs 4,000,000.

12. Where the taxable income exceeds Rs 7,000,000, rate of tax is Rs 1,422,000 + 30% of the amount exceeding Rs 7,000,000

Solution

Assume that our annual income is given in cell A2, we shall apply the following formula to include all 12 tax brackets. (There should be no [break or Enter key pressed] in between the the formula components).

[Download the Excel file from here]

=IF(A2<=400000,0,

IF(A2<=500000,(A2-400000)*0.02,

IF(A2<=750000,(A2-500000)*0.05+2000,

IF(A2<=1400000,(A2-750000)*0.1+14500,

IF(A2<=1500000,(A2-1400000)*0.125+79500,

IF(A2<=1800000,(A2-1500000)*0.15+92000,

IF(A2<=2500000,(A2-1800000)*0.175+137000,

IF(A2<=3000000,(A2-2500000)*0.20+259500,

IF(A2<=3500000,(A2-3000000)*0.225+359500,

IF(A2<=4000000,(A2-3500000)*0.25+472000,

IF(A2<=7000000,(A2-4000000)*0.275+597000,

(A2-7000000)*0.3+1422000)))))))))))