Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rfhorn
 
Posts: n/a
Default Need formula for budgeting payroll taxes.

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?
  #2   Report Post  
Gary Brown
 
Posts: n/a
Default

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?

  #3   Report Post  
rfhorn
 
Posts: n/a
Default

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?

  #4   Report Post  
N Harkawat
 
Posts: n/a
Default

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" wrote in message
...
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?



  #5   Report Post  
rfhorn
 
Posts: n/a
Default

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" wrote in message
...
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?






  #6   Report Post  
N Harkawat
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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?






  #7   Report Post  
rfhorn
 
Posts: n/a
Default

I still do not understand the formula. Following is how I input the fromula
into cell c2;
=if((column(3)-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((column(3)-3))*$b2/12)))*0.0008

I get the response back that there is an error in the formula.
I am new to excel and have not worked with many formulas. I still do not
understand what you mean in the formula when you freference column( )-2 and
column( ) -3.


\"N Harkawat" wrote:

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" wrote in message
...
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" wrote in message
...
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?






  #8   Report Post  
N Harkawat
 
Posts: n/a
Default

Simply copy and paste the formula on cell C2 as I had originally offered as
below.
=IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008

DO NOT CHANGE ANY PART OF IT....



"rfhorn" wrote in message
...
I still do not understand the formula. Following is how I input the fromula
into cell c2;
=if((column(3)-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((column(3)-3))*$b2/12)))*0.0008

I get the response back that there is an error in the formula.
I am new to excel and have not worked with many formulas. I still do not
understand what you mean in the formula when you freference column( )-2
and
column( ) -3.


\"N Harkawat" wrote:

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" wrote in message
...
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" wrote in message
...
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?








  #9   Report Post  
rfhorn
 
Posts: n/a
Default

Thanks that did the job. I appreciate your patience with me. However, what
would I have to do to the formula if the person did not start until say month
5. Would it just require me to only start the formula in the month that the
person started? (i.e. An employee that will be making 60,000 per year does
not start until May 15th.) I know I would put the employee name in Col A, in
Col B $60,000, Col C,D,E,F I would leave blank. I am not sure what to put in
Col G. If the employee started in the beginning of the month I do not think I
would have a problem, But if the start date does not coincide with the
beginning of the month I have no idea how to enter a formula. Any
suggestions? Thanks.

"N Harkawat" wrote:

Simply copy and paste the formula on cell C2 as I had originally offered as
below.
=IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008

DO NOT CHANGE ANY PART OF IT....



"rfhorn" wrote in message
...
I still do not understand the formula. Following is how I input the fromula
into cell c2;
=if((column(3)-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((column(3)-3))*$b2/12)))*0.0008

I get the response back that there is an error in the formula.
I am new to excel and have not worked with many formulas. I still do not
understand what you mean in the formula when you freference column( )-2
and
column( ) -3.


\"N Harkawat" wrote:

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" wrote in message
...
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" wrote in message
...
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?









  #10   Report Post  
N Harkawat
 
Posts: n/a
Default

My suggestion is that you should consider buying a payroll tax software to
manage these requirements With excel formula it is way too complicated...

"rfhorn" wrote in message
...
Thanks that did the job. I appreciate your patience with me. However, what
would I have to do to the formula if the person did not start until say
month
5. Would it just require me to only start the formula in the month that
the
person started? (i.e. An employee that will be making 60,000 per year does
not start until May 15th.) I know I would put the employee name in Col A,
in
Col B $60,000, Col C,D,E,F I would leave blank. I am not sure what to put
in
Col G. If the employee started in the beginning of the month I do not
think I
would have a problem, But if the start date does not coincide with the
beginning of the month I have no idea how to enter a formula. Any
suggestions? Thanks.

"N Harkawat" wrote:

Simply copy and paste the formula on cell C2 as I had originally offered
as
below.
=IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008

DO NOT CHANGE ANY PART OF IT....



"rfhorn" wrote in message
...
I still do not understand the formula. Following is how I input the
fromula
into cell c2;
=if((column(3)-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((column(3)-3))*$b2/12)))*0.0008

I get the response back that there is an error in the formula.
I am new to excel and have not worked with many formulas. I still do
not
understand what you mean in the formula when you freference column( )-2
and
column( ) -3.


\"N Harkawat" wrote:

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" wrote in message
...
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" wrote in message
...
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?













  #11   Report Post  
rfhorn
 
Posts: n/a
Default

Thanks. I do appreciate the help you gave me.

"N Harkawat" wrote:

My suggestion is that you should consider buying a payroll tax software to
manage these requirements With excel formula it is way too complicated...

"rfhorn" wrote in message
...
Thanks that did the job. I appreciate your patience with me. However, what
would I have to do to the formula if the person did not start until say
month
5. Would it just require me to only start the formula in the month that
the
person started? (i.e. An employee that will be making 60,000 per year does
not start until May 15th.) I know I would put the employee name in Col A,
in
Col B $60,000, Col C,D,E,F I would leave blank. I am not sure what to put
in
Col G. If the employee started in the beginning of the month I do not
think I
would have a problem, But if the start date does not coincide with the
beginning of the month I have no idea how to enter a formula. Any
suggestions? Thanks.

"N Harkawat" wrote:

Simply copy and paste the formula on cell C2 as I had originally offered
as
below.
=IF((COLUMN()-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((COLUMN()-3))*$B2/12)))*0.008

DO NOT CHANGE ANY PART OF IT....



"rfhorn" wrote in message
...
I still do not understand the formula. Following is how I input the
fromula
into cell c2;
=if((column(3)-2)*$B2/12<7000,$B2/12,MAX(0,7000-(((column(3)-3))*$b2/12)))*0.0008

I get the response back that there is an error in the formula.
I am new to excel and have not worked with many formulas. I still do
not
understand what you mean in the formula when you freference column( )-2
and
column( ) -3.


\"N Harkawat" wrote:

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" wrote in message
...
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" wrote in message
...
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?












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF & VLOOKUP FORMULA taxmom Excel Worksheet Functions 3 March 2nd 05 04:35 PM
How do I setup a formula for payroll deductions in excel Skeeter Excel Worksheet Functions 1 February 6th 05 10:51 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"