Tax Calculations with multiple states

[ExcelSur]

Hello Gurus
I am trying calculate state payroll taxes based on 3 states with different tax rates and wage thresholds. As an example for California state unemployment taxes are calculated on the first $7000.00 of employee wages.

I have attached a sample worksheet with desired output.

Really appreciate your help.

Thanks

 

[bosco_yip]

In F4, array (Ctrl+Shift+Enter) formula copied down :

=PRODUCT(VLOOKUP(B4,$H$3:$J$5,{2,3},0))

 

[Peter Bartholomew]

The same formula but implemented using Tables and MS365:
= PRODUCT( XLOOKUP([@State], Reference[State], Reference[[Tax Rate]:[Threshold Wages]] ) )
The formula calculates the result for a single employee and the table propagates the formula down.

Alternatively, if the objective were to return the entire set of results as a spilt range, two lookups would allow the multiplication to be performed without the use PRODUCT, which like other aggregations, is not really designed for array calculation:
= XLOOKUP( Employee[State], Reference[State], Reference[Tax Rate] ) *
XLOOKUP( Employee[State], Reference[State], Reference[Threshold Wages] )

 

[Peter Bartholomew]

Other options include the LET function

= LET(
  taxRate,   XLOOKUP(Employee[State], Reference[State], Reference[Tax Rate]),
  threshold, XLOOKUP(Employee[State], Reference[State], Reference[Threshold Wages]),
  stateTax,  taxRate * threshold
  stateTax )

which provides a level of self-documentation but at the expense of a somewhat verbose syntax.

Power query also offers possibilities though is overkill when all that has been requested is a single product of a rate and amount.

let
    Source = Employee,
    #"Merged Queries" = Table.NestedJoin(Source, {"State"}, Reference, {"State"}, "Reference", JoinKind.LeftOuter),
    #"Expanded Reference" = Table.ExpandTableColumn(#"Merged Queries", "Reference", {"Tax Rate", "Threshold Wages"}, {"Tax Rate", "Threshold Wages"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Reference", "State Tax", each [Tax Rate] * [Threshold Wages], Currency.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Multiplication",{"XLOOKUP", "Tax Rate", "Threshold Wages"})
in
    #"Removed Columns"

 

[GraH - Guido]

Hi ExcelSur,

I doubt your given result is correct when I follow your explanation on thresholds. In the result given you are not using the thresholds. So why have them at all?

So I would add a column in the Tax table "State Tax" =[@[Tax Rate]]*[@[Threshold Wages]]
Then [E4] = MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , VLOOKUP ( [@State] , Rates , 4 , 0 ) ), where CSE is never needed.

or reusing Bosco's wit in [F4] =MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , PRODUCT ( VLOOKUP ( B4 , $J$3:$L$5 , {2,3} , 0 ) ) )
CSE might no longer be needed if you are on a 365 subscription.

 

[ExcelSur]

Hi ExcelSur,

I doubt your given result is correct when I follow your explanation on thresholds. In the result given you are not using the thresholds. So why have them at all?

So I would add a column in the Tax table "State Tax" =[@[Tax Rate]]*[@[Threshold Wages]]
Then [E4] = MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , VLOOKUP ( [@State] , Rates , 4 , 0 ) ), where CSE is never needed.

or reusing Bosco's wit in [F4] =MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , PRODUCT ( VLOOKUP ( B4 , $J$3:$L$5 , {2,3} , 0 ) ) )
CSE might no longer be needed if you are on a 365 subscription.

View attachment 70710

Thanks GraH,
I didnt notice the Employee 12 with $14,000 and you are correct. I am trying to calculate estimated state payroll. Usually the estimated wages are much higher than $14,000. Thanks for providing the solution.

 

[bosco_yip]

Hi ExcelSur,

I doubt your given result is correct when I follow your explanation on thresholds. In the result given you are not using the thresholds. So why have them at all?

So I would add a column in the Tax table "State Tax" =[@[Tax Rate]]*[@[Threshold Wages]]
Then [E4] = MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , VLOOKUP ( [@State] , Rates , 4 , 0 ) ), where CSE is never needed.

or reusing Bosco's wit in [F4] =MIN ( VLOOKUP ( [@State] , Rates , 2 , 0 ) * [@Wages] , PRODUCT ( VLOOKUP ( B4 , $J$3:$L$5 , {2,3} , 0 ) ) )
CSE might no longer be needed if you are on a 365 subscription.

View attachment 70710

Good catch, another option without helper column

In F4, copied down :

=VLOOKUP(B4,$H$3:$J$5,2,0)*MIN(D4,VLOOKUP(B4,$H$3:$J$5,3,0))

Regards

 

[GraH - Guido]

I like that solution.

 

[ExcelSur]

Good catch, another option without helper column

In F4, copied down :

=VLOOKUP(B4,$H$3:$J$5,2,0)*MIN(D4,VLOOKUP(B4,$H$3:$J$5,3,0))

Regards

Really appreciate your help with the solution

 

[Peter Bartholomew]

The adjustment to the most unfamiliar of the solutions I presented

= LET(
  taxRate,   XLOOKUP(Employee[State], Reference[State], Reference[Tax Rate]),
  threshold, XLOOKUP(Employee[State], Reference[State], Reference[Threshold Wages]),
  taxable,   IF( Employee[Wages] < threshold, Employee[Wages], threshold ),
  stateTax,  taxRate * taxable,
  stateTax )

 

[ExcelSur]

The adjustment to the most unfamiliar of the solutions I presented

= LET(
  taxRate,   XLOOKUP(Employee[State], Reference[State], Reference[Tax Rate]),
  threshold, XLOOKUP(Employee[State], Reference[State], Reference[Threshold Wages]),
  taxable,   IF( Employee[Wages] < threshold, Employee[Wages], threshold ),
  stateTax,  taxRate * taxable,
  stateTax )

Thanks Peter. Really appreciate your help