FUTA/SUTA/FICA Tax Calculations

[SnrAcct]

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.

 

[oldbrewer]

xample: 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).
	7000	< < B8
				col E
01/01/2016	5000			5000	0		row 10
01/02/2016	5000			10000	12
01/03/2016	5000			15000	0
01/04/2016	5000			20000	0
01/05/2016	5000			25000	0
01/06/2016	5000			30000	0
01/07/2016	5000			35000	0
01/08/2016	5000			40000	0
01/09/2016	5000			45000	0
01/10/2016	5000			50000	0
01/11/2016	5000			55000	0
01/12/2016	5000			60000	0
	use a cumultive salary helper column
	formula giving 30
	=IF(E9>$B$8,0,IF(E10>$B$8,(7000-E9)*0.006,E10*0.006))

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[oldbrewer]

xample: 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).
	7000	< < B8		col E
01/01/2016	5000			5000	30		row 10
01/02/2016	5000			10000	12
01/03/2016	5000			15000	0
01/04/2016	5000			20000	0
01/05/2016	5000			25000	0
01/06/2016	5000			30000	0
01/07/2016	5000			35000	0
01/08/2016	5000			40000	0
01/09/2016	5000			45000	0
01/10/2016	5000			50000	0
01/11/2016	5000			55000	0
01/12/2016	5000			60000	0
	use a cumultive salary helper column
	formula giving 30
	=IF(E9>$B$8,0,IF(E10>$B$8,(7000-E9)*0.006,E10*0.006))
											labelling col E caused an error sorry

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

 

[SnrAcct]

Thank you so much.

I am unclear as to what E9 is referencing, and also "formula giving 30". Please could you clarify.

 

[oldbrewer]

E9 is the empty cell above 5000

the formula gives the answer 30
next cell down it gives 12

 

[SnrAcct]

E9 is the empty cell above 5000

the formula gives the answer 30
next cell down it gives 12

______________________________________________________________________________
Thanks again, but unfortunately this formula gives me a cumulative total of the FUTA tax (so Feb shows an amount of $42, which is the $30 from Jan + $12 from Feb.)

I need it just to show each month FUTA on it's own.

Is there anyway I can upload an example of my spreadsheet for you to see what I mean?

 

[Dave Patton]

Excel 2010
	C	D	E
3			FUTA
4	1-Jan-17	5,000.00	30
5	1-Feb-17	5,000.00	12
6	1-Mar-17	5,000.00	0
5d

Cell Formulas
Range	Formula
E4	=MIN(0.006*SUM($D$4:D4),7000*0.006)-N(SUM($E$3:E3))
E5	=MIN(0.006*SUM($D$4:D5),7000*0.006)-N(SUM($E$3:E4))
E6	=MIN(0.006*SUM($D$4:D6),7000*0.006)-N(SUM($E$3:E5))

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	FUCA	7,000.00	0.60%	42.00
2	SUTA	7,000.00	2.70%	189.00
3	FICA	118,500.00	7.65%	9,065.25
4
5			Wage	FUTA	SUTA	FICA
6		1-Jan-17	5,000.00	30.00	135.00	382.50
7		1-Feb-17	5,000.00	12.00	54.00	382.50
8		1-Mar-17	5,000.00	0.00	0.00	382.50
5d

Cell Formulas
Range	Formula
G5	=B3
G6	=MIN($D$3*SUM($D$6:D6),rFICA)-SUM($G$5:G5)
E6	=MIN($D$1*SUM($D$6:D6),rFUCA)-SUM($E$5:E5)
F6	=MIN($D$2*SUM($D$6:D6),rSUTA)-SUM($F$5:F5)

Named Ranges
Name	Refers To	Cells
rFICA	='5d'!$E$3
rFUCA	='5d'!$E$1
rSUTA	='5d'!$E$2

 

[oldbrewer]

I copied my spreadsheet and it gave 30 and 12

you must have entered the formula wrongly, or have data in wrong locations - I was showing you one way to achieve it....

 

[MacroNoobie]

Excel 2010

	B	C	D	E	F	G
1	FUCA	7,000.00	0.60%	42.00
2	SUTA	7,000.00	2.70%	189.00
3	FICA	118,500.00	7.65%	9,065.25
4
5			Wage	FUTA	SUTA	FICA
6		1-Jan-17	5,000.00	30.00	135.00	382.50
7		1-Feb-17	5,000.00	12.00	54.00	382.50
8		1-Mar-17	5,000.00	0.00	0.00	382.50

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

5d​

Worksheet Formulas Cell Formula G5 =B3 E6 =MIN($D$1*SUM($D$6:D6),rFUCA)-SUM($E$5:E5) F6 =MIN($D$2*SUM($D$6:D6),rSUTA)-SUM($F$5:F5) G6 =MIN($D$3*SUM($D$6:D6),rFICA)-SUM($G$5:G5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To rFICA ='5d'!$E$3 rFUCA ='5d'!$E$1 rSUTA ='5d'!$E$2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This worked beautifully! Thank you!