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
. 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
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) +
Add in SocialSecurity, Medicare & State tax and I'm always within a penny of
whatever ADP (or whoever) calculates.
"Al H." wrote in message
How do I set up income tax withholding tables and then reference the
amount (vlookup?) in a payroll worksheet I am creating?