How to Calculate Income Tax In Pakistan

On Linkedin, after sharing my article on 7 Ways to Calculate Income tax, I got a question on how to calculate income tax in Pakistan.

Below are their tax brackets

USING SUMPRODUCT

Step 1: Summarise the above brackets to a table like below

Step 2: Use the below formula

=IF(
C3>MAX($F$4:$F$10),(C3-MAX($F$4:$F$10))*MAX($H$4:$H$10)+MAX($I$4:$I$10),
SUMPRODUCT((C3>$F$4:$F$10)*(C3<=$G$4:$G$10)*(C3-$F$4:$F$10)*$H$4:$H$10)+
SUMPRODUCT((C3>$F$4:$F$10)*(C3<=$G$4:$G$10)*$I$4:$I$10)
)

How the formula works:

(C3>$F$4:$F$10)*(C3<=$G$4:$G$10) Checks if the salary is within the lower limit and upper limit of the tax bracket. It returns an array of 1/0 {0;0;1;0;0;0;0}

(C3-$F$4:$F$10) Returns the amount exceeding the Lower limit of the tax bracket

(C3>$F$4:$F$10)*(C3<=$G$4:$G$10)*(C3-$F$4:$F$10) will ensure you pick the correct amount exceeding the lower limit

Now you just need to multiply the above array with the tax rates to get tax charged (C3>$F$4:$F$10)*(C3<=$G$4:$G$10)*(C3-$F$4:$F$10)*$H$4:$H$10

Finally, we need to add the additional tax associated with the tax bracket (C3>$F$4:$F$10)*(C3<=$G$4:$G$10)*$I$4:$I$10

Since the Upper-tax bracket does not have an upper limit, the above calculations will not apply thus, you first need to check if salary falls in the upper tax bracket C3>MAX($F$4:$F$10)

For salaries falling in the upper tax bracket, calculate the amount exceeding the lower limit (C3-MAX($F$4:$F$10)) then lookup the max tax rate MAX($H$4:$H$10) and the maximum additional amount MAX($I$4:$I$10)

USING LAMBDA FUNCTION

CALC_TAX = LAMBDA(salo, tax_brac,
    LET(
        maxAmt, MAX(FILTER(tax_brac, {1, 0, 0, 0})),
        maxAdd, MAX(FILTER(tax_brac, {0, 0, 0, 1})),
        maxRate, MAX(FILTER(tax_brac, {0, 0, 1, 0})),
        rate, FILTER(tax_brac, {0, 0, 1, 0}),
        add, FILTER(tax_brac, {0, 0, 0, 1}),
        from, FILTER(tax_brac, {1, 0, 0, 0}),
        to, FILTER(tax_brac, {0, 1, 0, 0}),
        inBrackets, (salo > from) * (salo <= to),
        IF(
            salo > maxAmt,
            (salo - maxAmt) * maxRate + maxAdd,
            SUMPRODUCT(inBrackets * (salo - from) * rate) +
                SUMPRODUCT(inBrackets * add)
        )
    )
);

The LAMBDA function works the same as the SUMPRODUCT function above.

An alternative LAMBDA is using the INDEX function as shown below

TAX_CALC=LAMBDA(sal, bracs,
  LET(row,  SUM((FILTER(bracs,{1,0,0,0})<sal)*1),
      rate, INDEX(bracs,row,3),
      add,  INDEX(bracs,row,4),
      excess, sal-INDEX(bracs,row,1),
      excess*rate+add
      )
)

The key to understanding the above function is knowing how to retrieve the row number

After getting the row number, use INDEX to look up the bracket rate, additions, and calculating excesses.

USING POWER QUERY

let
index=List.Count(
let values= [Salary] in List.Select(rates[From],each _<values))-1,
rts= rates[Rate]{index},
excess =[Salary]-rates[From]{index},
adds=rates[Additions]{index},
Tax=excess*rts+adds
in 
Tax

How it works:

let values= [Salary] in List.Select(rates[From],each _<values)) Looks up the values from the “From” Column in the rates table that are less than each salary amount and returns a list

List.Count( let values= [Salary] in List.Select(rates[From],each _<values))-1 returns a count of above list of values. Since Power Query counts from 0, remember to deduct 1

Now we have an index value that we can use to look up the rates and additional tax in the rates table

rts= rates[Rate]{index} returns the rate in the tax bracket where the salary falls into

rates[From]{index} returns the threshold value in the tax bracket where the salary falls. To get the excess, deduct this amount from the salary [Salary]-rates[From]{index}

Tax=excess*rts+adds Tax then will be excess above threshold multiply by tax bracket rate plus additional tax