Formula Help

[Tia]

I need help with a formula for the following:

FUTA is calculated on YTD income up to $7000.00. Once an employee's income
is greater than $7000.00, they owe $0 FUTA. My formula works until an
employee makes over $7000.00. I think the problem is that they owe FUTA on
all income up to $7000.00 so if for example I have the following employee:
YTD Payroll Payroll for Period FUTA
01/01/08 6450.00 6450.00 51.60 = (6450.00)
0.008
01/15/08 12000.00 5550.00 4.40 =
(7000-6450)0.008
02/01/08 18000.00 6000.00 0.00 (0) 0.008


I've pasted the formula that I've been trying to use.
G4 is the YTD Payroll Total

 

[bpeltzer]

The amount on which you'll have paid FUTA after this check is min(7000,YTD);
the amount prior to this check is min(7000,YTD-current). So the FUTA for
this period is =.008*(min(7000,g4)-min(7000,g4-f4))

 

[joeu2004]

FUTA is calculated on YTD income up to $7000.00.  Once an
employee's income is greater than $7000.00, they owe $0 FUTA.

Minor note: FUTA is paid by the employer, not the employee; so "they"
do not owe anything. I'm sure what you mean is: the FUTA attributed
to the employee's wages.

so if for example I have the following employee:
               YTD Payroll      Payroll for Period         FUTA
01/01/08       6450.00             6450.00               51.60 = (6450.00)
0.008
01/15/08      12000.00            5550.00                4.40 =  
(7000-6450)0.008
02/01/08      18000.00            6000.00                0.00  (0) 0.008

To avoid rounding errors, I think FUTA should always be computed based
on the YTD wages (subject to FUTA), then subtract the FUTA previously
reported. So assuming the first YTD Payroll figure is in B2, the
corresponding FUTA formula in D2 should be:

=round(min(7000,B2)*0.8%,2) - sum($D$11)

As you drag this down column D, you will see that $D$11 changes to $D
$12, then $D$13 appropriately.