Benjamin Franklin once said, “in this world, nothing can be said to be certain, except death and taxes“.
There is nothing else as certain as these two, (except Failure), yet there are many who are not prepared for them.
The question is, given below Tax calculation instructions, How do you loop through the tax bracket for each staff?
Using NESTED IF to Calculate Tax
This is my least recommended method as it is the slowest, most-prone to error and there are better alternatives to Nested IF.
Step 1: Calculate tax Per bracket
Step 2: Key In NESTED IF
=IF(
C3>$F$6, ((C3-$F$6)*$H$6)+$I$5+$I$4,
IF(C3>$F$5, ((C3-$F$5)*$H$5)+$I$4,
IF(C3=$G$4,$I$4,0
)
)
)How the Function Works:
⭐ C3>$F$6, ((C3-$F$6)*$H$6)+$I$5+$I$4, Checks if the taxable amount is greater than the largest amount in tax brackets.
If TRUE, it calculates the tax for the 3rd bracket ((C3-$F$6)*$H$6) and then adds the 2 previous brackets’ tax $I$5+$I$4
⭐ IF(C3>$F$5, ((C3-$F$5)*$H$5)+$I$4, Checks if the taxable amount is greater than the second largest amount in tax brackets.
If TRUE, it calculates the tax for the 2nd bracket ((C3-$F$5)*$H$5) and then adds the 1 previous bracket’s tax $I$4
⭐ IF(C3=$G$4,$I$4,0 Finally, if the taxable amount is equal to the initial tax bracket amount, then return tax for the 1st bracket else zero
Using IFS to Calculate Tax
=IFS(
C3>$F$6, ((C3-$F$6) * $H$6) + $I$5 + $I$4,
C3>$F$5, ((C3-$F$5) * $H$5) + $I$4,
C3=$G$4, $I$4,
TRUE, 0
)How the Function Works:
Works the same as the nested IF function except you do not have to repeat the IF function.
Points to Note on IFS Function:
- It evaluates up to 127 different conditions.
- It Returns the Value for the 1st Logical test that evaluates into TRUE
- Where there is no corresponding value_if_true, the function displays the message “You’ve entered too few arguments for this function”.
- If the logical test does not result in TRUE/FALSE, the function returns #VALUE! error.
- If all logical tests result in FALSE, the function returns #N/A error.
To overcome this always end your function with TRUE, then value to return if all else is false. i.e., =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…TRUE, 0)
- It is found in Excel 2019 greater versions.
Using SWITCH Function to Calculate Tax
=SWITCH(TRUE,
C3>$F$6, ((C3-$F$6)*$H$6) + $I$5 + $I$4,
C3>$F$5, ((C3-$F$5)*$H$5) + $I$4,
C3=$G$4, $I$4,
TRUE, 0
)How the Function Works:
Works the same as the nested IFS function except SWITCH evaluates an expression against a list of values
Points to Note on SWITCH Function:
- It Returns the Result of the 1st Match between Value & Expression.
- The expression can be a Value or True/False or a Function.
- Matching values and corresponding results are entered in pairs.
- Can handle up to 126 pairs.
- Include the last Default value if no match is found.
- Only performs an exact match, so you cannot use (>, <). To overcome this limitation use TRUE as an expression and use a formula that evaluates TRUE.
- Found from Excel 2019 greater versions.
Using a Nested XLOOKUP Function to Calculate Tax
=XLOOKUP(TRUE,
C3>$F$6, ((C3-$F$6)*$H$6)+$I$5+$I$4,
XLOOKUP(TRUE,
C3>$F$5, ((C3-$F$5)*$H$5)+$I$4,
XLOOKUP(TRUE,
C3=$G$4,$I$4,0
)
)
)How the Function Works
The only trick here is you have to use the “If_Not_Found” argument of XLOOKUP to Nest another XLOOKUP function
Like the previous functions, it will return the first Lookup value that evaluates to TRUE.
The rest of the calculations are the same as the Nested IF function
Using a SUMPRODUCT Function to Calculate Tax
This method differs from the previous ones since it utilizes the marginal tax rate (tax rate you pay on an additional dollar of income) concept.
Step 1: Adjust the brackets and calculate the margin tax percentage
=IF(
C3<MIN($G$5:$G$7),0,
SUMPRODUCT(
$I$5:$I$7,
C3-$F$5:$F$7,
(C3>$F$5:$F$7)+0
)
)How it works:
C3<MIN($G$5:$G$7),0 Checks if the salary is below the minimum taxable amount, return zero
$I$5:$I$7 returns the margin rates
C3-$F$5:$F$7 returns the margin bracket amounts {103800;79800;71466.67}
(C3>$F$5:$F$7)+0 ensures that tax will be calculated for only brackets where salary is greater than the minimum bracket amount. +0 to convert TRUE/FALSE to 1/0
SUMPRODUCT({0.1;0.15;0.05},{103800;79800;71466.67},{1;1;1}) adds up the product of these 3 arrays
Using a LAMBDA Function to Calculate Tax
=LAMBDA(salo, tax_array,
LET(
minAmt, MIN( FILTER(tax_array,{0,1,0,0}) ),
marginRate, FILTER(tax_array,{0,0,0,4}),
marginAmt, salo-FILTER(tax_array,{1,0,0,0}),
withinBracket, salo>FILTER(tax_array,{1,0,0,0}),
IF(salo<minAmt,0,SUM(withinBracket*marginAmt*marginRate))
)
)With LAMBDA you can create a function for your users instead of tasking them to remember the above functions.
This LAMBDA uses the same concept of margin rates and margin amounts as the SUMPRODUCT & IF function above
⭐ minAmt, MIN( FILTER(tax_array,{0,1,0,0}) ) returns the minimum taxable salary
⭐ marginRate, FILTER(tax_array,{0,0,0,4}) returns the column with margin tax rates
⭐ marginAmt, salo-FILTER(tax_array,{1,0,0,0}), returns the additional amount per tax bracket
⭐ withinBracket, salo>FILTER(tax_array,{1,0,0,0}) ensures no negative tax is calculated
How to Add a Lambda Function
Using a POWER QUERY to Calculate Tax
=let
maxAmt=32333.33,
minAmt=24000,
minT=2400,
midT=2083.33,
tax=
if [Taxable Amt]>maxAmt then
([Taxable Amt]-maxAmt)*0.3+midT+minT
else
if [Taxable Amt]>minAmt then
([Taxable Amt]-minAmt)*0.25+minT
else
if [Taxable Amt]=minAmt then minT else 0
in
taxIf you do not want to hard-code the amounts in your M code, use below
let
index=List.Count(
let valz = [Taxable] in List.Select(rates[From],each _<valz)),
margin = List.Sum(List.FirstN(rates[Margins],index)),
excess=[Taxable]-rates[From]{index},
rts=rates[Rate]{index},
min=List.Min(rates[To]),
Tax=if [Taxable]<min then 0 else List.Sum({excess*rts, margin})
in
TaxHow to code works:
Step 1: Before Loading the tax rates table to Power Query, add an extra row and calculate the tax margins
Learn More
Step by step approach, easy to follow