Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 21st 05, 03:51 PM
Al H.
 
Posts: n/a
Default Income tax withholding tables in Excel?

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   Report Post  
Old February 24th 05, 05:06 AM
George Nicholson
 
Posts: n/a
Default

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
news
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.



  #3   Report Post  
Old April 4th 05, 09:11 AM
Bruce Hawkins
 
Posts: n/a
Default

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
news
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   Report Post  
Old June 6th 19, 12:32 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 1
Default IRS Withholding Tables

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get data (tables) from IE to export to an EXCEL sreadshee WebgirlMD Excel Discussion (Misc queries) 2 January 19th 05 08:41 PM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM
How do I fix column widths in Excel Pivot Tables? skeezix Excel Discussion (Misc queries) 1 December 11th 04 12:43 AM
Vlookup and Indexing in excel CLSCHWIES Excel Worksheet Functions 2 December 4th 04 01:57 AM
most effective way to link ms word tables to excel Mark S Lloyd Excel Discussion (Misc queries) 0 November 26th 04 08:11 PM


All times are GMT +1. The time now is 10:56 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017