is it possible to do this? for income tax withholding

i want to know if this is possible... i think it is i just dont know where to start..

here is my situation:

i want to calculate income tax to be withheld from a certain ammount of dollars, this is for certified payroll forms that need to be turned in for government jobs

the things that affect how much will be taken out is based on:
married or single status (M or S in my excell sheet)
Ammount of dependants
Ammount made for this payroll.

here is an example how the table looks like in the 2005 employers tax guide
------------------------------------
If Married
If wages are at least: 740
But less than: 750

Number of dependants: 0
Income tax withheld: 75

Number of dependants: 1
Income tax withheld: 65

Number of dependants: 2
Income tax withheld: 56

Number of dependants: 3
Income tax withheld: 47

now theres a different table for 750-760, 760-770, etc...
dependants go up to 10... you get the idea.

now if a person is single its a different table
if the Ammount made this payroll: 740-760
Number of dependants: 0
Income tax withheld: 83

etc....


basically what i want to do is make an if then else statement or something... i dont know how i would tackle it though

what im thinking is this:

3 sheets, one for the certified, the second for the married table, the third for the single table

in english this is what the code would look like:

If ($person) = married then go to: sheet 2
else:
go to: $sheet 3
$sheet 2:
If ($gross ammount earned) < $751 then
go to: Row 3
$row 3
if ($dependants) = 0 then ($withholding ammount) = 75
else:
if ($dependants) = 2 then ($withholding ammount) = 65
else:
if ($dependants) = 3 then ($withholding ammount) = 67

$sheet 3
"same thing as above, different numbers"


get the idea?


any help would be greatly appreciated, this would make my certified payroll go by hours faster, its alot of information to put in, but will make 2006 certified payroll be much much quicker.

I have posted an example workbook at http://cjoint.com/?bntPddUPO7

Note that the table of bounds goes left to right, so new entries go before
column B, and the max value is shown.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"lyosha" <lyosha.21l4dy_1137175200.8411@excelforum-nospam.com> wrote in
message news:lyosha.21l4dy_1137175200.8411@excelforum-nospam.com...
>
> i want to know if this is possible... i think it is i just dont know
> where to start..
>
> here is my situation:
>
> i want to calculate income tax to be withheld from a certain ammount of
> dollars, this is for certified payroll forms that need to be turned in
> for government jobs
>
> the things that affect how much will be taken out is based on:
> married or single status (M or S in my excell sheet)
> Ammount of dependants
> Ammount made for this payroll.
>
> here is an example how the table looks like in the 2005 employers tax
> guide
> ------------------------------------
> If Married
> If wages are at least: 740
> But less than: 750
>
> Number of dependants: 0
> Income tax withheld: 75
>
> Number of dependants: 1
> Income tax withheld: 65
>
> Number of dependants: 2
> Income tax withheld: 56
>
> Number of dependants: 3
> Income tax withheld: 47
>
> now theres a different table for 750-760, 760-770, etc...
> dependants go up to 10... you get the idea.
>
> now if a person is single its a different table
> if the Ammount made this payroll: 740-760
> Number of dependants: 0
> Income tax withheld: 83
>
> etc....
>
>
> basically what i want to do is make an if then else statement or
> something... i dont know how i would tackle it though
>
> what im thinking is this:
>
> 3 sheets, one for the certified, the second for the married table, the
> third for the single table
>
> in english this is what the code would look like:
>
> If ($person) = married then go to: sheet 2
> else:
> go to: $sheet 3
> $sheet 2:
> If ($gross ammount earned) < $751 then
> go to: Row 3
> $row 3
> if ($dependants) = 0 then ($withholding ammount) = 75
> else:
> if ($dependants) = 2 then ($withholding ammount) = 65
> else:
> if ($dependants) = 3 then ($withholding ammount) = 67
>
> $sheet 3
> "same thing as above, different numbers"
>
>
> get the idea?
>
>
> any help would be greatly appreciated, this would make my certified
> payroll go by hours faster, its alot of information to put in, but will
> make 2006 certified payroll be much much quicker.
>
>
> --
> lyosha
> ------------------------------------------------------------------------
> lyosha's Profile:
http://www.excelforum.com/member.php...o&userid=30452
> View this thread: http://www.excelforum.com/showthread...hreadid=501162
>

On Fri, 13 Jan 2006 11:56:58 -0600, lyosha
<lyosha.21l4dy_1137175200.8411@excelforum-nospam.com> wrote:

>
>i want to know if this is possible... i think it is i just dont know
>where to start..
>
>here is my situation:
>
>i want to calculate income tax to be withheld from a certain ammount of
>dollars, this is for certified payroll forms that need to be turned in
>for government jobs
>
>the things that affect how much will be taken out is based on:
>married or single status (M or S in my excell sheet)
>Ammount of dependants
>Ammount made for this payroll.
>
>here is an example how the table looks like in the 2005 employers tax
>guide
>------------------------------------
>If Married
>If wages are at least: 740
>But less than: 750
>
>Number of dependants: 0
>Income tax withheld: 75
>
>Number of dependants: 1
>Income tax withheld: 65
>
>Number of dependants: 2
>Income tax withheld: 56
>
>Number of dependants: 3
>Income tax withheld: 47
>
>now theres a different table for 750-760, 760-770, etc...
>dependants go up to 10... you get the idea.
>
>now if a person is single its a different table
>if the Ammount made this payroll: 740-760
>Number of dependants: 0
>Income tax withheld: 83
>
>etc....
>
>
>basically what i want to do is make an if then else statement or
>something... i dont know how i would tackle it though
>
>what im thinking is this:
>
>3 sheets, one for the certified, the second for the married table, the
>third for the single table
>
>in english this is what the code would look like:
>
>If ($person) = married then go to: sheet 2
>else:
>go to: $sheet 3
>$sheet 2:
>If ($gross ammount earned) < $751 then
>go to: Row 3
>$row 3
>if ($dependants) = 0 then ($withholding ammount) = 75
>else:
>if ($dependants) = 2 then ($withholding ammount) = 65
>else:
>if ($dependants) = 3 then ($withholding ammount) = 67
>
>$sheet 3
>"same thing as above, different numbers"
>
>
>get the idea?
>
>
>any help would be greatly appreciated, this would make my certified
>payroll go by hours faster, its alot of information to put in, but will
>make 2006 certified payroll be much much quicker.

I'm no accountant, (nor do I play one on TV), but I do stay at Holiday Inns
from time to time :-))

I would suggest that, instead of getting involved with the tax tables, that you
use the percentage method to determine withholdings.

The variables that determine which table to use are then:

1. Payroll Period (weekly, biweekly, semimonthly, monthly, quarterly,
semi-annually, annually, and daily or miscellaneous). Hopefull you will only
need to deal with one or two of these (plus possibly the miscellaneous or daily
table).

2. Marital Status (Single -- including HOH; or Married).

Using the percentage method, you

1. Multiply one withholding allowance for your payroll period (that's also in a
table) by the number of allowances that the employee claims.
2. Subtract that amount from the employee’s wages.

You then use the appropriate Married or Single table for your pay period.

For example, assume you have a Weekly pay period.

Witholding allowance. . . . . . . . . . . . . . . . . $ 63.46

Gross Pay: $1,000
Withholding allowances claimed: 3
Marital Status: Married

Input to percent table = 1000-(63.46*3)

Take a look at Circular E from the IRS. It would be fairly simple to set up
the appropriate tables in an Excel workbook.






--ron