Formula in a cell to calc FUTA tax

[12by8]

FUTA tax maximum for W-2 employees is $56 based on first $7K of gross wages
(7K*.008). At $7000.01, there's no need to calc FUTA. I have a wage sheet
that tracks Gross Wages per pay period and Total Gross Wages (both same
column), and FUTA tax amount per pay period and Total FUTA tax (both same
column). I've had mild success with IF, MAX functions. Basically, would
like formula to insert a "zero" in a cell if no FUTA tax amount needs to be
calculated. Other issue includes exceeding $7K threshold within one
paycheck; i.e., previous gross wages were $5000, then earn $2100 in following
period - my formula calcs it based on $2100, when it should be capped at
$2000. Any help would be appreciated.

 

[joeu2004]

FUTA tax maximum for W-2 employees is $56 based on first $7K of
gross wages (7K*.008). At $7000.01, there's no need to calc FUTA.
I have a wage sheet that tracks Gross Wages per pay period and
Total Gross Wages (both same column), and FUTA tax amount per
pay period and Total FUTA tax (both same column).

Ostensibly....

For Total FUTA:

=min(56, round(A1*0.8%,2))

where A1 is the cell that contains the cumulative wages subject to
FUTA (Total Gross Wages).

For FUTA Per Period:

=round(0.8%*min(n(A2), max(0, 7000-A1+A2)), 2)

where A2 is the cell that contains the wages earned in the period
subject to FUTA (Gross Wages Per Period). Note that 7000-A1+A2 is a
simplification of 7000-(A1-A2), which might be more intuitive.

Note: N(A2) is required just in case A2 is blank. If that is not a
concern for you, you can replace "N(A2)" with simply A2.

However....

The above formulas can result in an off-by-some-pennies error due to
periodic rounding.

I think the only way to avoid that is to maintain a history of Total
FUTA or FUTA Per Period for each pay period. Can that fit into your
design?

I think it does because you say that you are "tracking" Gross Wages
and Total Gross Wages per period. But the spreadsheet layout is
unclear to me. Can you be more specific?

PS: I'm sure you aware that the FUTA rate is not really 0.8%. And in
fact, the FUTA rate can change over time. It is only 0.8% when the
right conditions hold for your state's unemployment tax rate, as it
applies to you. For example, Calif uses a formula that is specific to
your "experience" as an employer. I presume you have taken that into
account, and you concluded that 0.8% is indeed always the applicate
FUTA rate for you. Alternatively, you might want to replace 0.8% with
a reference to a cell that contains the applicable FUTA rate, and
replace 56 with an appropriate expression (e.g. A3*7000, where A3
contains your FUTA rate).

HTH.

 

[joeu2004]

Improvement....

For FUTA Per Period:
=round(0.8%*min(n(A2), max(0, 7000-A1+A2)), 2)
[....]
The above formulas can result in an off-by-some-pennies
error due to periodic rounding.

I think the following avoids that problem, albeit more complicated:

=min(round(A2*0.8%, 2), max(0, 56-round((A1-A2)*0.8%, 2)))

 

[joeu2004]

Errata....

PS: I'm sure you aware that the FUTA rate is not really 0.8%. And in
fact, the FUTA rate can change over time. It is only 0.8% when the
right conditions hold for your state's unemployment tax rate, as it
applies to you.

Ignore this; it is misleading, if not wrong. Arrgghh! I went through
this nearly a year ago, coming to the same incorrect conclusion
because, I believe, the explanation in Pub 15 is misleading and
inconsistent with Form 940 (or Sched H). Although there are
conditions where 0.8% does not apply, they are exceptional.

 

[12by8]

Greetings joeu2004,

Thankful to see a response to this. I'll implement later today. Will report
back, of course.

Have a fab day,

RDRoy
12by8

 

[12by8]

OK, here we go

Column headings to spreadsheet are: gross wages, fed wthldng, soc.sec.,
medi., state wthldng, total for taxes, net pay. All are totaled at
bottom(eoy); each row represents a pay period. Separate columns calc. futa
and suta per pay period with totals at bottom(eoy).

Implemented formulae with gross wages being $2750. First two pay periods
calc fine at $22 for futa, with total futa of $44. Great! Issues arises in
third period at $2750 when all $22 calcs change to $12 (the actual amount for
period 3 (22+22+12=56). Total FUTA stays at 56, which is right, but issue is
created bcz pay period formula can't keep running total of previous pay
periods g.w. despite A1 reference.

I'll tinker s'more. Your formulae are invaluable and are 99% there. I
shoulda taken a logic class in college. I don't disagree that 940
instructions aren't consistent with P.15 and, yes, the 'ostensible' comment
relates.

Thanks so much.

R.Roy
12by8

Improvement....

For FUTA Per Period:
=round(0.8%*min(n(A2), max(0, 7000-A1+A2)), 2)
[....]
The above formulas can result in an off-by-some-pennies
error due to periodic rounding.

I think the following avoids that problem, albeit more complicated:

=min(round(A2*0.8%, 2), max(0, 56-round((A1-A2)*0.8%, 2)))

 

[joeu2004]

Column headings to spreadsheet are: gross wages, fed wthldng, soc.sec.,
medi., state wthldng, total for taxes, net pay. All are totaled at
bottom(eoy); each row represents a pay period. Separate columns calc.
futa and suta per pay period with totals at bottom(eoy).

Aha! That clarifies and changes things significantly. For the
following, I assume that the first pay period starts in row 2 and that
gross wages is in column A and FUTA is in column H.

Implemented formulae with gross wages being $2750. First two pay periods
calc fine at $22 for futa, with total futa of $44. Great! Issues arises in
third period at $2750 when all $22 calcs change to $12 (the actual amount for
period 3 (22+22+12=56).

Yes, I see the problem. Change the periodic FUTA formula to the
following, starting in H2 and copy down:

=if(A2="", "",
min(round(A2*0.8%, 2), max(0, 56-round((sum($A$2:A2)-A2)*0.8%, 2))))

SUM($A$2:A2)-A2 is a little redundant. I did that so that you could
use just the one formula throughout. Alternatively, the formula in H2
could be:

=if(A2="", "", min(round(A2*0.8%, 2), 56))

and the formula starting in H3 (and copy down) could be:

=if(A3="", "",
min(round(A3*0.8%, 2), max(0, 56-round(sum($A$2:A2)*0.8%, 2))))

I added the test for A2="" so that the periodic FUTA will be blank for
periods in which the gross wages have not yet been filled in.

HTH.

 

[joeu2004]

Improvement....

Alternatively, the formula in H2 could be:

=if(A2="", "", min(round(A2*0.8%, 2), 56))

and the formula starting in H3 (and copy down) could be:

=if(A3="", "",
min(round(A3*0.8%, 2), max(0, 56-round(sum($A$2:A2)*0.8%, 2))))

Silly me! The second formula can simply be:

=IF(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$2:H2))))

MAX(0,...) should not be necessary; 56-SUM($H$2:H2) should suffice. I
tossed in the MAX(0,...) on the off-chance that 56-SUM() becomes
negative because of the vagaries of binary computer arithmetic.

 

[joeu2004]

Improvement #2....

Silly me! The second formula can simply be:
=IF(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$2:H2))))

(Silly me)^2! You could simply put the following formula into H2 and
copy down:

=if(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$1:H1))))

This assumes that H1 is blank or a column heading, and it relies on
the fact that SUM() does not include cells with text.

By the way, my first formula [1] is arguably more reliable because it
is based on cumulative wages. The formulas based on cumulative FUTA
suffer from the defect that if periodic FUTA rounds to zero,
cumulative FUTA might not sum to 56. But that is unlikely since that
means that gross wages are less than $0.62(!). I suspect you do not
even need to report wages in that case ;-).


-----

[1] First formula:

=if(A2="", "",
min(round(A2*0.8%, 2), max(0, 56-round((sum($A$2:A2)-A2)*0.8%,
2))))

 

[12by8]

This ROCKS! I'm not sure I could have deciphered the logic for this. Thanks
a double-bunch squared!

R.Roy
12by8

Improvement #2....

Silly me! The second formula can simply be:
=IF(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$2:H2))))

(Silly me)^2! You could simply put the following formula into H2 and
copy down:

=if(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$1:H1))))

This assumes that H1 is blank or a column heading, and it relies on
the fact that SUM() does not include cells with text.

By the way, my first formula [1] is arguably more reliable because it
is based on cumulative wages. The formulas based on cumulative FUTA
suffer from the defect that if periodic FUTA rounds to zero,
cumulative FUTA might not sum to 56. But that is unlikely since that
means that gross wages are less than $0.62(!). I suspect you do not
even need to report wages in that case ;-).


-----

[1] First formula:

=if(A2="", "",
min(round(A2*0.8%, 2), max(0, 56-round((sum($A$2:A2)-A2)*0.8%,
2))))