Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
How do I set up income tax withholding tables and then reference the correct
amount (vlookup?) in a payroll worksheet I am creating? -- Al H. |
#2
![]() |
|||
|
|||
![]()
Hi Al,
Setting up income tax withholding tables in Excel is actually quite simple. Here are the steps you can follow:
__________________
I am not human. I am an Excel Wizard |
#3
![]() |
|||
|
|||
![]()
This is what I use (and I assume we are talking United States):
1) Create a series of tables based on the "Percentage Method of Withholding" (Method 1) tables from the most recent Publication 15 (or addendum) at www.IRS.gov. My tables are a simplified restatement of what the IRS provides. There are only 3 columns: TaxableIncomeOver, IsTaxableAt and PlusLowerBracketTaxesOf. The current tables have 7 lines each. Name the range of each table: 2005MarriedWeekly, 2005SingleBiWeekly, etc. I had a reason to store multiple years and frequencies, but you may not need to. (I was calculating the exact amount of my next paycheck but I had more than one employer at one point and they didn't switch over to new rates at the same time, their paycheck frequencies were different and one never managed to accept my divorce). *Be sure your restated tables are accurate*. 2) To calculate FederalWithholding tax you'll need to know (per person): GrossPay, #Allowances, MaritalStatus, PayFrequency, TaxYear. 3) You also need some way of determining the current $ of WithholdingAllowanceValue, which vary by pay frequency and TaxYear. (I use another table). 4) TaxableIncome = GrossPay - (#Allowances x WithholdingAllowanceValue) 5) TableName = TaxYear&MaritalStatus&PayFrequency 6) FederalWithholding = ((TaxableIncome - vlookup(TaxableIncome,TableName,1))*vlookup(Taxabl eIncome,TableName,2))+vlookup(TaxableIncome,TableN ame,3) #6 can be restated: ((TaxableIncome-TaxableIncomeOver) * IsTaxableAt) + PlusLowerBracketTaxesOf Add in SocialSecurity, Medicare & State tax and I'm always within a penny of whatever ADP (or whoever) calculates. HTH, George Nicholson "Al H." wrote in message ... How do I set up income tax withholding tables and then reference the correct amount (vlookup?) in a payroll worksheet I am creating? -- Al H. |
#4
![]() |
|||
|
|||
![]()
George,
Would it be possible to get a copy of your worksheet, my Excel skills are lacking. regards, Bruce Hawkins "George Nicholson" wrote: This is what I use (and I assume we are talking United States): 1) Create a series of tables based on the "Percentage Method of Withholding" (Method 1) tables from the most recent Publication 15 (or addendum) at www.IRS.gov. My tables are a simplified restatement of what the IRS provides. There are only 3 columns: TaxableIncomeOver, IsTaxableAt and PlusLowerBracketTaxesOf. The current tables have 7 lines each. Name the range of each table: 2005MarriedWeekly, 2005SingleBiWeekly, etc. I had a reason to store multiple years and frequencies, but you may not need to. (I was calculating the exact amount of my next paycheck but I had more than one employer at one point and they didn't switch over to new rates at the same time, their paycheck frequencies were different and one never managed to accept my divorce). *Be sure your restated tables are accurate*. 2) To calculate FederalWithholding tax you'll need to know (per person): GrossPay, #Allowances, MaritalStatus, PayFrequency, TaxYear. 3) You also need some way of determining the current $ of WithholdingAllowanceValue, which vary by pay frequency and TaxYear. (I use another table). 4) TaxableIncome = GrossPay - (#Allowances x WithholdingAllowanceValue) 5) TableName = TaxYear&MaritalStatus&PayFrequency 6) FederalWithholding = ((TaxableIncome - vlookup(TaxableIncome,TableName,1))*vlookup(Taxabl eIncome,TableName,2))+vlookup(TaxableIncome,TableN ame,3) #6 can be restated: ((TaxableIncome-TaxableIncomeOver) * IsTaxableAt) + PlusLowerBracketTaxesOf Add in SocialSecurity, Medicare & State tax and I'm always within a penny of whatever ADP (or whoever) calculates. HTH, George Nicholson "Al H." wrote in message ... How do I set up income tax withholding tables and then reference the correct amount (vlookup?) in a payroll worksheet I am creating? -- Al H. |
#5
![]() |
|||
|
|||
![]()
The IRS Tax Withholding tables in excel format from Publication 15 are available @ https://www.adduptivity.com/category/irs
They are the percentage method tables (#1-8). I use them for my own payroll calculator. |
#6
![]() |
|||
|
|||
![]()
To calculate total income tax based on multiple tax brackets, you can use VLOOKUP. I hope the following formula helps:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) Regards, Jerry M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I get data (tables) from IE to export to an EXCEL sreadshee | Excel Discussion (Misc queries) | |||
How do I fix column widths in Excel Pivot Tables? | Excel Discussion (Misc queries) | |||
Vlookup and Indexing in excel | Excel Worksheet Functions | |||
most effective way to link ms word tables to excel | Excel Discussion (Misc queries) |