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


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

Step 2: Use the below formula


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)


CALC_TAX = LAMBDA(salo, tax_brac,
        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),
            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),

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.


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

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


Related Article

7 Ways to Calculate PAYE Tax In Excel


Print Friendly, PDF & Email

Do you want to excel in Excel?


Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.