Calculate withholding

I have made a simple table to automatically calculate what an employee makes according to the hours he/she worked and the appropriate deductions such as FICA, Medicare and Federal W/H, but I do not know how to go about making the Federal W/H work. I am not sure what the correct method is to do this. I have added the table I am working on.


I can make a chart for their filing status:

employee A single, 0 exemptions
employee B single, 1 exemption
employee C single, 0 + $5
employee D single, 0 + $5

Then there is the tax table from the IRS (Pub 15, page 41). Do I have to list all of this or is there a formula I could use to figure this such as =IF(then use > and < then, col 1,0) or something like that, by the way as you can see I do not have a specific cell reference yet. Or do I use VLOOKUP? and copy the IRS Table? What is the proper way to do this?

Please review what I have done so far and advise me if that is the best way to make those calculations and how I should proceed to do the Federal W/H section. Thanks for your patience with a newbie.

Being english I am not familier with tax or deductions in the states, what exactly is Federal W/H?

And the IRS list?

Federal W/H is the amount an employer witholds from your pay to send to the Internal Revenue for payment of your taxes on income earned. The IRS, Internal Revenue Service, is the entity that is tasked with collecting taxes on everything you do in the world...birth, death, inheritance, etc. The States have their various taxes too.
I think what you have in England is the VAT...not sure how that works or if that becomes all inclusive of ya'lls taxes................too much tax wherever you are...LOL!

VAT is tax added on good your purchase over here.

Whats your specific problem with the WH federal, how cant you get it to work?

Is the tax a percentage of there earnings? Is there different tax bands depending on gross earnings?

yes there are different tax amounts based upon your gross earnings. It is listed in an IRS publication PUB 15...actually it might be easier if you access the IRS.gov site and look for PUB 15 and go to page 41...this is the table I am trying to incorporate in my formula... I need to calculate what the amount of withholding is, according to this table, that coresponds to the appropriate amount of income.
Across the top of the page is the number of withholding allowances and down the left hand column is the gross earning amounts. So for example, if you were to make $380 and the allowance you claim is 1, then the withholding amount would be $36. I am looking for the proper way to automate this in the sample that I attached earlier. I hope this clears it up a bit for you English chaps!...LOL!.

boneob, you shouldn't have to copy the whole table. Here's what you need to know: the $ amount of each exemption and the bracket cut-offs for each status. I didn't really research this, so my numbers are NOT correct, but the attached should give you a head start. I assumed 26 pay periods per year. Feel free to change that in B1 if incorrect. I've made other changes as necessary and highlighted those cells.

OK, so its income tax, fair enough.

So 0 - 10 is withholding allowance, how is that decided?

Not being from the US probs puts me at a disadvantage trying to help you

It's based on the number of people in your house, but you don't technically have to claim all of your kids or even yourself. Some people also claim more than they have because of other issues, like large charity contributions or medical expenses, etc., so they don't get a huge refund back the next year.

Boneob, one note I'd like to make about my spreadsheet is that it will, by its very nature, be slightly incorrect. What I mean is, if you have an employee that works 100 hours one pay period and 60 the next, the sheet calculates as though it was a year of working 100 hours and then a year of working 60 hours. I.e., the past is not accounted for. The only way around this is to have a history of your employees' pay in here, which would require a rewrite of the formula and would still have to assume that later pay periods reflect the current one. Also, the way I have written the formula is the way every payroll program I've seen is calculated. You shouldn't notice too big a difference unless an employee gets a very large bonus. Also, the difference will err toward withholding too much so that the employee gets a refund, rather than too little.

I made an error in the first file. Please disregard it. Here's a corrected version.

Thanks darkyam!! I appreciate the way you made some of the calculations easier and cleaner. As far as the Federal W/H, I see what you are doing, I think (I am a newbie) but as you said it does not give the correct amount.... according to the IRS Pub 15, page 41 table, the deductions should be as follows:
Employee A = $47
Employee B = $24
Employee C = $11 ($6 per the table and the additional $5 requested to be deducted)
Employee D = $12 ($6 per the table and the additional $5 requested to be deducted)

I do however see that I need to include data from the "married persons--weekly amounts should one of the employee's decide to get married and have a different filing status. By the way, this is for a very small cafe that only has the two cooks and two waitresses, so it is not like they will be getting bonuses or making significantly more than they do at this point. I think maybe the formula use by the big companies have too many variables in them and are in a constant state of flux, whereas this is a small, simple business. That by the way is why I am doing this....its for my sister who is even worse than I am on Excel, but is a really good cook!! LOL.

Did you actually adjust the numbers I used? I don't know where the cutoffs are. You will have to dig for that information and update my table accordingly. Also, if people have additional amounts to be taken out, I'd recommend another column for that and then you can just tag on that amount at the end of my formula.

It seems the IRS is not accounting for the standard deduction and one personal exemption (as I was). Thus, the table has to be changed to reflect their real cutoffs. Note that this will give everyone a refund next year, assuming the exemptions are accurate and no other tax info is missing (like other income, etc.)

Darkyam, your table is working off of a percentage where as I am trying to work off of actuals. I am not really sure how to apply your formula to the data I am using. I am attaching the table (Tab 'Single Weekly') to this so that you can see the data ranges. For example if you look at the left hand column and you see a column that has "At least" and then next to it is a column that has "But less than" so I would think that I would have to use the => for the "At least" column and < for the "But less than" column. Now would that be an =INDEX function? or =MATCH function? Across the top of the table are columns 0 thru 10 for the number of allowances. So working off of this table, how would you set it up???

I didn't give up. I have things to do on Sunday.

Second, the table is really just putting the percentages the IRS uses into a table, so that you pay the nearest dollar. Mine, adjusted for the right cutoffs, would actually be more accurate, but should be very close in all instances.

To answer your question, though, you can't really get it to work with what you have because all you have is the table for single withholding. For single people you can use

Please Login or Register  to view this content.

If you were to add tables for the other statuses on other sheets, you could use an Indirect function to do this automatically or just update the function to address that particular sheet manually.