I am having difficulty in getting a complete formula to calculate payroll taxes. I have a spreadsheet with 12 tabs (each tab is a different month). The issue I am having is in the months where the wage base is reached (so need taxes to be calculated only on the difference in the wage and the base rate).
Example: FUTA Wage base rate $7000, tax 0.60% Jan wage $5000, Feb wage $5000, March wage $5000 etc...
So Jan FUTA tax is $30 (0.60% of $5000)
Feb FUTA tax is $12 (0.60% of $2000 (since $7000 max wage base rate is reached)
Mar FUTA tax is $0 (0.60% of $0, since $7000 wage base reached).
Same thing for SUTA @ 2.7% Wage base $7000, and FICA @ 7.65% Wage base $118,500.
I have been playing around with variations of IF and MIN functions, but am stumped. Any help would be appreciated.
Example: FUTA Wage base rate $7000, tax 0.60% Jan wage $5000, Feb wage $5000, March wage $5000 etc...
So Jan FUTA tax is $30 (0.60% of $5000)
Feb FUTA tax is $12 (0.60% of $2000 (since $7000 max wage base rate is reached)
Mar FUTA tax is $0 (0.60% of $0, since $7000 wage base reached).
Same thing for SUTA @ 2.7% Wage base $7000, and FICA @ 7.65% Wage base $118,500.
I have been playing around with variations of IF and MIN functions, but am stumped. Any help would be appreciated.
