Need formula for budgeting payroll taxes.

rfhorn · 04-04-2005, 10:06 AM

I am trying to budget employer payroll tax expense by employee by month. I
have tried an if function and it does not work by month to month bases only
in total. Example of the data is as follows: I have an employee that makes
$72,000 per year or $6,000 per month. The FUTA taxes are .8% on the first
$7,000 of pay or $56. The first month the expense is $$48.00 ($6,000 * .08%).
the next month the expense is $8 ($1,000 *.08%). All subsequent months the
expense is zero since the maximum was reached by month two. The same type
calcualtion holds true for the SUI tax expense except the maximum wages is
$23,00 and the tax rate is 2.88%. It will take almost four months befroe the
maximum SUI tax expnse is reached. I thought I could set up an if function
that would calculate the monthly tax expnse but was unsuccessful. Any ideas
of what formula I should be using?

Gary Brown · 04-04-2005, 01:06 PM

Strongly recommend you talk to a Tax Accountant. You also have to factor in
SUI credits or you will be overpaying the government.

Per Publication 15...
http://www.irs.gov/publications/p15/ar02.html#d0e3106
Computing FUTA tax. For 2004 and 2005, the FUTA tax rate is 6.2%. The tax
applies to the first $7,000 that you pay to each employee as wages during the
year. The $7,000 is the federal wage base. Your state wage base may be
different. Generally, you can take a credit against your FUTA tax for amounts
that you paid into state unemployment funds. This credit cannot be more than
5.4% of taxable wages. If you are entitled to the maximum 5.4% credit, the
FUTA tax rate after the credit is 0.8%.

Good Luck,
Gary Brown

"rfhorn" wrote:

> I am trying to budget employer payroll tax expense by employee by month. I
> have tried an if function and it does not work by month to month bases only
> in total. Example of the data is as follows: I have an employee that makes
> $72,000 per year or $6,000 per month. The FUTA taxes are .8% on the first
> $7,000 of pay or $56. The first month the expense is $$48.00 ($6,000 * .08%).
> the next month the expense is $8 ($1,000 *.08%). All subsequent months the
> expense is zero since the maximum was reached by month two. The same type
> calcualtion holds true for the SUI tax expense except the maximum wages is
> $23,00 and the tax rate is 2.88%. It will take almost four months befroe the
> maximum SUI tax expnse is reached. I thought I could set up an if function
> that would calculate the monthly tax expnse but was unsuccessful. Any ideas
> of what formula I should be using?

rfhorn · 04-04-2005, 02:06 PM

Thanks for the response. I am aware of the tax laws covering the paying of
FUTA taxes. The example I gave takes into consideration the employer tax
credit a company receives against FUTA tax for contributions paid into state
unemployment funds.

"Gary Brown" wrote:

> Strongly recommend you talk to a Tax Accountant. You also have to factor in
> SUI credits or you will be overpaying the government.
>
> Per Publication 15...
> http://www.irs.gov/publications/p15/ar02.html#d0e3106
> Computing FUTA tax. For 2004 and 2005, the FUTA tax rate is 6.2%. The tax
> applies to the first $7,000 that you pay to each employee as wages during the
> year. The $7,000 is the federal wage base. Your state wage base may be
> different. Generally, you can take a credit against your FUTA tax for amounts
> that you paid into state unemployment funds. This credit cannot be more than
> 5.4% of taxable wages. If you are entitled to the maximum 5.4% credit, the
> FUTA tax rate after the credit is 0.8%.
>
> Good Luck,
> Gary Brown
>
> "rfhorn" wrote:
>
> > I am trying to budget employer payroll tax expense by employee by month. I
> > have tried an if function and it does not work by month to month bases only
> > in total. Example of the data is as follows: I have an employee that makes
> > $72,000 per year or $6,000 per month. The FUTA taxes are .8% on the first
> > $7,000 of pay or $56. The first month the expense is $$48.00 ($6,000 * .08%).
> > the next month the expense is $8 ($1,000 *.08%). All subsequent months the
> > expense is zero since the maximum was reached by month two. The same type
> > calcualtion holds true for the SUI tax expense except the maximum wages is
> > $23,00 and the tax rate is 2.88%. It will take almost four months befroe the
> > maximum SUI tax expnse is reached. I thought I could set up an if function
> > that would calculate the monthly tax expnse but was unsuccessful. Any ideas
> > of what formula I should be using?

N Harkawat · 04-04-2005, 03:06 PM

Say on your spreadsheet Col A has Name of the employees and column B has
Annual Salary on column C copy this formula on cell C2
=IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008

and copy it across column N (such that Col C = Jan and Col N = Dec)

For the SUI tax Expenses simply change the 7000 to 23000 and 0.008 to 0.0288
in the above formula

This will give the result per employee siuch that the nmax payout for FUTA
= 56 if wages > 7000 per year.

"rfhorn" <rfhorn@discussions.microsoft.com> wrote in message
news:373CE874-A621-491E-9F6C-38A0EBF36236@microsoft.com...
> Thanks for the response. I am aware of the tax laws covering the paying of
> FUTA taxes. The example I gave takes into consideration the employer tax
> credit a company receives against FUTA tax for contributions paid into
> state
> unemployment funds.
>
> "Gary Brown" wrote:
>
>> Strongly recommend you talk to a Tax Accountant. You also have to factor
>> in
>> SUI credits or you will be overpaying the government.
>>
>> Per Publication 15...
>> http://www.irs.gov/publications/p15/ar02.html#d0e3106
>> Computing FUTA tax. For 2004 and 2005, the FUTA tax rate is 6.2%. The
>> tax
>> applies to the first $7,000 that you pay to each employee as wages during
>> the
>> year. The $7,000 is the federal wage base. Your state wage base may be
>> different. Generally, you can take a credit against your FUTA tax for
>> amounts
>> that you paid into state unemployment funds. This credit cannot be more
>> than
>> 5.4% of taxable wages. If you are entitled to the maximum 5.4% credit,
>> the
>> FUTA tax rate after the credit is 0.8%.
>>
>> Good Luck,
>> Gary Brown
>>
>> "rfhorn" wrote:
>>
>> > I am trying to budget employer payroll tax expense by employee by
>> > month. I
>> > have tried an if function and it does not work by month to month bases
>> > only
>> > in total. Example of the data is as follows: I have an employee that
>> > makes
>> > $72,000 per year or $6,000 per month. The FUTA taxes are .8% on the
>> > first
>> > $7,000 of pay or $56. The first month the expense is $$48.00 ($6,000 *
>> > .08%).
>> > the next month the expense is $8 ($1,000 *.08%). All subsequent months
>> > the
>> > expense is zero since the maximum was reached by month two. The same
>> > type
>> > calcualtion holds true for the SUI tax expense except the maximum wages
>> > is
>> > $23,00 and the tax rate is 2.88%. It will take almost four months
>> > befroe the
>> > maximum SUI tax expnse is reached. I thought I could set up an if
>> > function
>> > that would calculate the monthly tax expnse but was unsuccessful. Any
>> > ideas
>> > of what formula I should be using?

rfhorn · 04-05-2005, 09:06 AM

Thanks for the reponse. I am afraid I do not understand the formula. what
odes the column() mean and why are you sutracting 2 form it and later 3?

"N Harkawat" wrote:

> Say on your spreadsheet Col A has Name of the employees and column B has
> Annual Salary on column C copy this formula on cell C2
> =IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008
>
> and copy it across column N (such that Col C = Jan and Col N = Dec)
>
> For the SUI tax Expenses simply change the 7000 to 23000 and 0.008 to 0.0288
> in the above formula
>
> This will give the result per employee siuch that the nmax payout for FUTA
> = 56 if wages > 7000 per year.
>
>
>
>
> "rfhorn" <rfhorn@discussions.microsoft.com> wrote in message
> news:373CE874-A621-491E-9F6C-38A0EBF36236@microsoft.com...
> > Thanks for the response. I am aware of the tax laws covering the paying of
> > FUTA taxes. The example I gave takes into consideration the employer tax
> > credit a company receives against FUTA tax for contributions paid into
> > state
> > unemployment funds.
> >
> > "Gary Brown" wrote:
> >
> >> Strongly recommend you talk to a Tax Accountant. You also have to factor
> >> in
> >> SUI credits or you will be overpaying the government.
> >>
> >> Per Publication 15...
> >> http://www.irs.gov/publications/p15/ar02.html#d0e3106
> >> Computing FUTA tax. For 2004 and 2005, the FUTA tax rate is 6.2%. The
> >> tax
> >> applies to the first $7,000 that you pay to each employee as wages during
> >> the
> >> year. The $7,000 is the federal wage base. Your state wage base may be
> >> different. Generally, you can take a credit against your FUTA tax for
> >> amounts
> >> that you paid into state unemployment funds. This credit cannot be more
> >> than
> >> 5.4% of taxable wages. If you are entitled to the maximum 5.4% credit,
> >> the
> >> FUTA tax rate after the credit is 0.8%.
> >>
> >> Good Luck,
> >> Gary Brown
> >>
> >> "rfhorn" wrote:
> >>
> >> > I am trying to budget employer payroll tax expense by employee by
> >> > month. I
> >> > have tried an if function and it does not work by month to month bases
> >> > only
> >> > in total. Example of the data is as follows: I have an employee that
> >> > makes
> >> > $72,000 per year or $6,000 per month. The FUTA taxes are .8% on the
> >> > first
> >> > $7,000 of pay or $56. The first month the expense is $$48.00 ($6,000 *
> >> > .08%).
> >> > the next month the expense is $8 ($1,000 *.08%). All subsequent months
> >> > the
> >> > expense is zero since the maximum was reached by month two. The same
> >> > type
> >> > calcualtion holds true for the SUI tax expense except the maximum wages
> >> > is
> >> > $23,00 and the tax rate is 2.88%. It will take almost four months
> >> > befroe the
> >> > maximum SUI tax expnse is reached. I thought I could set up an if
> >> > function
> >> > that would calculate the monthly tax expnse but was unsuccessful. Any
> >> > ideas
> >> > of what formula I should be using?
>
>
>

N Harkawat · 04-05-2005, 09:06 AM

Column() is just a substitute for using increments of 1,2,3
Since January is on Column C ie column 3 but really is month one I subtract
it by 2 to get 1 ( 3-2=1)
The reason to subtract 3 is to determine the YTD payments made on FUTA and
its all columns upto last month so current column minus 3
Hope that helps

"rfhorn" <rfhorn@discussions.microsoft.com> wrote in message
news:29040CEC-1A12-47CD-A261-87A20AC82A70@microsoft.com...
> Thanks for the reponse. I am afraid I do not understand the formula. what
> odes the column() mean and why are you sutracting 2 form it and later 3?
>
> "N Harkawat" wrote:
>
>> Say on your spreadsheet Col A has Name of the employees and column B has
>> Annual Salary on column C copy this formula on cell C2
>> =IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008
>>
>> and copy it across column N (such that Col C = Jan and Col N = Dec)
>>
>> For the SUI tax Expenses simply change the 7000 to 23000 and 0.008 to
>> 0.0288
>> in the above formula
>>
>> This will give the result per employee siuch that the nmax payout for
>> FUTA
>> = 56 if wages > 7000 per year.
>>
>>
>>
>>
>> "rfhorn" <rfhorn@discussions.microsoft.com> wrote in message
>> news:373CE874-A621-491E-9F6C-38A0EBF36236@microsoft.com...
>> > Thanks for the response. I am aware of the tax laws covering the paying
>> > of
>> > FUTA taxes. The example I gave takes into consideration the employer
>> > tax
>> > credit a company receives against FUTA tax for contributions paid into
>> > state
>> > unemployment funds.
>> >
>> > "Gary Brown" wrote:
>> >
>> >> Strongly recommend you talk to a Tax Accountant. You also have to
>> >> factor
>> >> in
>> >> SUI credits or you will be overpaying the government.
>> >>
>> >> Per Publication 15...
>> >> http://www.irs.gov/publications/p15/ar02.html#d0e3106
>> >> Computing FUTA tax. For 2004 and 2005, the FUTA tax rate is 6.2%.
>> >> The
>> >> tax
>> >> applies to the first $7,000 that you pay to each employee as wages
>> >> during
>> >> the
>> >> year. The $7,000 is the federal wage base. Your state wage base may be
>> >> different. Generally, you can take a credit against your FUTA tax for
>> >> amounts
>> >> that you paid into state unemployment funds. This credit cannot be
>> >> more
>> >> than
>> >> 5.4% of taxable wages. If you are entitled to the maximum 5.4% credit,
>> >> the
>> >> FUTA tax rate after the credit is 0.8%.
>> >>
>> >> Good Luck,
>> >> Gary Brown
>> >>
>> >> "rfhorn" wrote:
>> >>
>> >> > I am trying to budget employer payroll tax expense by employee by
>> >> > month. I
>> >> > have tried an if function and it does not work by month to month
>> >> > bases
>> >> > only
>> >> > in total. Example of the data is as follows: I have an employee that
>> >> > makes
>> >> > $72,000 per year or $6,000 per month. The FUTA taxes are .8% on the
>> >> > first
>> >> > $7,000 of pay or $56. The first month the expense is $$48.00 ($6,000
>> >> > *
>> >> > .08%).
>> >> > the next month the expense is $8 ($1,000 *.08%). All subsequent
>> >> > months
>> >> > the
>> >> > expense is zero since the maximum was reached by month two. The same
>> >> > type
>> >> > calcualtion holds true for the SUI tax expense except the maximum
>> >> > wages
>> >> > is
>> >> > $23,00 and the tax rate is 2.88%. It will take almost four months
>> >> > befroe the
>> >> > maximum SUI tax expnse is reached. I thought I could set up an if
>> >> > function
>> >> > that would calculate the monthly tax expnse but was unsuccessful.
>> >> > Any
>> >> > ideas
>> >> > of what formula I should be using?
>>
>>
>>