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.


-- 
lyosha
------------------------------------------------------------------------
lyosha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30452
View this thread: http://www.excelforum.com/showthread.php?threadid=501162

0
1/13/2006 5:56:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
331 Views

Similar Articles

[PageSpeed] 11

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?action=getinfo&userid=30452
> View this thread: http://www.excelforum.com/showthread.php?threadid=501162
>


0
bob.phillips1 (6510)
1/13/2006 6:40:55 PM
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
0
ronrosenfeld (3122)
1/13/2006 6:55:17 PM
Reply:

Similar Artilces:

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Incoming mail
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop For some reason, all my incoming mail goes directly to the 'deleted' folder and is marked as 'junk'. This consistently occurs although I have junk mail protection set to 'none' and have no rules established. <br><br>Any guidance is much appreciated! <FONT FACE="Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>On 1/6/10 5:08 PM, in article <a href="59bb0356.-1@webcrossing.JaKIaxP2ac0">59bb0356.-1@webcr...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Sales Tax Problems With RMS 2.0
Alright i just recieved a client call that he is having a problem with sales tax on customers, here are the symptoms. 1. under the customer options --> Billing information, there is a box called (customer tax) and it is grayed out. 2. all customers have been defaulted to tax exempt what am i missing here??? Anyone??? "ILMARcorp" wrote: > Alright i just recieved a client call that he is having a problem with sales > tax on customers, here are the symptoms. > > 1. under the customer options --> Billing information, there is a box called > (customer tax) ...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

Multi incoming e-mail
Hi, I have a problem I have 9 e-mail domains that are incoming mail to an exchange 2003 server, what I want to do is this ! @domain1.com will be the e-mail address I will use But I will accept mail to @domain2.com and @domain3..9.com and when I get e-mail to those domain I want to rename them to @domain1.com and insert them into the exchange store. Is there anyway to do this ? with out having to insert e-mail address for each domain for each user have many user and short live :-) Thanks In Advance Aegir Thordarson ...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Payroll Tax Update Install is Nuts
Why has GP gone to this crazy delivery process for Payroll Tax Updates? Having to give sa control of my server to some undocumented process via the internet scares me. Here it is, our busiest time of the year, and I have to just trust that the process is safe, I can recover from it, my connection won't drop, etc. We don't become DBAs because we just trust everything to work out ok. We become DBAs because we are CONTROL FREAKS! You do know, of course, that you can always download the updates and apply them manually. However, I have found that the connection time is pretty sho...

How do I make a rule to move incoming messages from a specific domain
I'd like to move all my messages fro the @paypal.com an @ebay.com domain to dedicated folders. How would I do that? (regardless of what preceeds the @) Jerry Click on TOOLS>RULES>NEW, Then select start from blank rule, Select when CHECK WHEN MESSAGE ARRIVES, next, then select WITH SPECIFIC WORDS IN THE SENDER'S ADDRESS, then in the lower window, click on "SPECIFIC WORD", then add your filter phrases ("@ebay.com", "@paypal.com", etc) you can keep adding them in here, then next slect what action you want to do (move to folders) and select which ...

Percentage tax calculation
Hi, I need to apply tax rate, in percentage form, not in absolute form. The final goal is to apply a different % tax rate, based on the country of my customer. But apply manually a percentage may be sufficient... Does anyone have a suggestion abt how i can do that ? Thanks, Joe Joe, our certified add-in solution, Quick Quote Plus, can handle that for you. We can even call out to a web-based tax service (Avalara) to pull tax rates down to the US county level. Quick Quote Plus automatically sees the country field from MS CRM and applies the corresponding tax percentage to a quote or orde...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Adding PST Tax onto Invoice Report
Hi there, Can anyone show me or help me start off in modifying the sales invoice report to display both GST and PST taxes for Canadian companies. I know that the report is within report writer but am clueless on how to start this off, but more on where I need to pull the data. Thanks. Hi Ian, This is fairly easy... you'll have to work with calculated fields and use the schdeduled tax table (TX_Detail_MSTR = TX00201). Normaly, those taxes are already taken care within the default SOP Blank Form, but I suspect that you want to show the various tax labels and their respec...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

Is this Possible ?
Hi Have a newsletter document with text boxes that have no outlines but internal gradient colour fills. Is it possible that a macro could remove all colour fills in one hit just leaving the text in place ? many thanks Dim oShape As Shape For Each oShape In ActiveDocument.Shapes If oShape.Type = msoTextBox Then If oShape.Line.Visible = msoFalse Then oShape.Fill.Visible = msoFalse End If End If Next oShape should clear the fill from text boxes that are filled and have no border. -- <>>< ><<> ><&l...

windows live incoming mail server port number
what is the port number for incoming mail server for pop3.live.com 995 with SSL enabled. For an overview of the settings see; http://www.howto-outlook.com/faq/accountsettings.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "scoobasteve" <scoobasteve@discussions.microsoft.com> wrote in message news:78A98DF2-01DD-41D4-96E4-B2DA626D5920@microsoft.com... > what is ...

Price Includes Tax
We have a very simple store that sells 4 sizes of ice cream. Size A is 2.75 Size B is 3.50 Size C is 4.25 Size D is 5.00 These prices include tax which is 7.8%. So what I did was entered item pricing of the following to account for the tax. Size A is 2.55 Size B is 3.25 Size C is 3.94 Size D is 4.64 This works great except for when the customer orders multiple quantities. Because I have rounded up the pricing it eventually rounds up making the total sale value a penny or two higher. For example: If the customer gets 3 Size D's the final price should be $15.00. However, the POS...

Connect Exchjange 2003 to Internet to Get Incoming Mail
Hi All, I have Exchange 2003, I use it internaly in my LAN but I do not have any real IP to publish it, so I can Send from my client - outlook - that is configured with my Exchange Server 2003, but of course I cannot receive from Internet via my Exchange Server. So, I have to configure my mail client using POP3 to collect my Incoming e-mails from outside server that's of my ISP or open Web Interface of this web server to get my incoming mail messages. So, I'd like to configure my Exchange Server to collect these incoming messages and all of my clients only be configured with the ...

Saving copies of e-mails incoming/outgoing in Exchange 2003
Hi, I've just successfully migrated my employers from Windows+Exchange 2000 to Windows+Exchange 2003. What I'm unable to discover is how to save copies of *all* incoming *and* outgoing e-mails in the administrator mailbox. Thanks, I'd be most grateful. Cheers, Alex -- http://www.munted.org.uk If kernel developers were diplomats, we'd all be nuked by now... http://www.msexchange.org/tutorials/Implementing-Exchange-2003-Message-Journaling.html -- John Oliver, Jr. MCSE, MCT, CCNA, Exchange MVP Microsoft Certified Partner "Alex Buell" <alex.buell@munted.org...

Is Batch Marking Possible?
Hello, I am doing a mass mailing from Outlook. I will print 50 address labels per day. Is there a way for me to batch-mark the contacts for which I print a label? Thanks, Talal Itani ...

Tax Invoice automated Tax number
I used Office 97 for a long while. In this Office Software Excell had the best Tax Invoice program or sheet. This sheet had a automated numbering system. Who knows of this and can tell me why in the Office 2003 Software this is not available any more ... this is very disturbing How can I use this Tax numbering system in the new Office 2003 - the numbers should automate themselves ...

Is it possible to make cmd window to flash?
Greetings, I have an existing VC2003 windows console program. Now I want to add a alarm feature to it, the simpliest way is to make the cmd window to flash. Can this be implemented? Thanks, Evan >I have an existing VC2003 windows console program. Now I want to add a alarm >feature to it, the simpliest way is to make the cmd window to flash. Can >this be implemented? Evan, If the console is running in a window, I presume you can use the FlashWindow(Ex) API. In newer OS's you can find the console window handle using the GetConsoleWindow API. For older OS's here's...

Programmatically adding function to copy all incoming mail to public folder (based on sender domain)
Hi all. I would like to be able to (from any client) execute a script (vbs, exe, whatever) that creates a public folder. That's should be pretty easy. The tricky part is that I want the same script to create a rule (?) on the Exchange server that copies all incoming mail from a specific sender domain to that new public folder. Hmm... Let us assume that I have the sufficient credentials for this action hardcoded in the script.. does anyone have a good idea how to do that. I am creating an interface in our ERP software that should be able to see mail correspondance horizontically accross ...

Investment account "other income" transaction in report
When I deposit money (that is income) into an investment account, I select as the activity "Other Income" (there does not seem to be any other choice that makes sense. I enter a catagory for the tranaction. But when I look at a "tranactions by catagory" report, the tranaction does not appear. The account is selected, buut it just looks like tranactions from investment accounts do not get in that report. How do I get it into the report? Normally if an Investment Account can hold cash, you associate it with an Investment Cash Account. The Investment Cash Account is...