Re: Income tax withholding tables in Excel?


Would it be possible to get a copy of your worksheet, my Excel skills are


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
> 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(TaxableIncome,TableName,2))+vlookup(TaxableIncome,TableName,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." <AlH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:D46171FF-AC79-491F-9F74-31B688B48FC5@xxxxxxxxxxxxxxxx
> > 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.

Relevant Pages

  • RE: Computing totals for tax and non-tax items
    ... Good dary Bruce, ... >Then add a cell to calculate the tax based on ... >> a worksheet being used as an order form for products to ... >> referenced by VLOOKUP in the order worksheet. ...
  • Re: Formulas
    ... datasheet view of an Access table and an Excel worksheet into thinking that ... In an worksheet values can be addressed by ... I should point out that this is an unrealistic example as, in an invoice, tax ...
  • Re: Schedule D LT Cap. Gains (Tax Worksheet) Form 1040, line 13 question
    ... Schedule D worksheet to get the loweer reate for LTCG and Q Divs. ... is used to calculate income tax, and it does that by calculating ...
  • Re: 1099-DIV - What do I put on 1040-Line 9a and 1040-Line 9b -
    ... $89.00 bucks - so I'm sure I'm better off (at least in terms of my ... "If your taxable income is less than $100,00, you must ... do not use the Tax Table or Tax Computation Worksheek to ... "Qualified Dividends and Capital Gains Tax Worksheet" "If you do not ...