T
Tom Hagen Jr
I'm trying to create an Excel Workbook to figure out taxes
on paychecks based on the 2003 tax guidelines from the IRS
(www.irs.gov). This is for a client (they don't want to
use Access) and I'm not seeing an easy way to compare
multiple columns to get the data need to do the math
with. If anyone can help me out with the functions I
would be very grateful.
This is the top part of the worksheet 'Federal Tax'. The
sheet gets the user input from a W-2 sheet and a Main
Sheet that is updated by the user. Got most of this part
working but the final line for how much tax to withhold.
For line "6. Tax to be withheld" I need help to figure
out the formula. The tax is based on "Marriage Status"
from 'Main Sheet'!B5 , "Payroll Period" from 'Main Sheet'!
B7, and "5. Amount subject to withholding" from 'Federal
Tax'!E5. I can't figure out how to have excel compare
multiple rows and columns for the rest of the formula.
It goes something like this:
1. If married compare only married column
2. In the married section find the Payroll Period the
match how often check is cut.
3. Compare line 5 Amount subject to withhold to be
between amount in the Over column and Not Over column.
In the example a Married person making $600 on a Biweekly
pay period would have 10% withheld. As the amount falls
between 248 and 710.
1. Total wage payment $600.00
Payroll Period One Withholding Allowance
2. One allowance (Based on Payroll Period) $58.65
Annually 3050
3. Allowances claimed on Form W-4 2
Biweekly 117.31
4. Multiply line 2 by line 3 $117.30
Daily 11.73
5. Amount subject to withholding (subtract line 4 from
line 1) $482.70 Monthly 254.17
6. Tax to be withheld Quarterly
762.5
Semiannually 1525
Semimonthly 127.08
Weekly 58.65
The formula I'm using for "2. One allowance (Based on
Payroll Period)" is =VLOOKUP('Main Sheet'!B7,'Federal Tax'!
E1:F9,2)
Marriage Status Payroll Period Over Not Over
Income tax to withhold % to withhold
Married Annually 6450 18450 10%
Married Annually 18450 52350 1200 15%
Married Annually 52350 111800 6285 27%
Married Annually 111800 179600 22336.5 30%
Married Annually 179600 316850 42676.5 35%
Married Annually 316850 90714 38.60%
Married Biweekly 248 710 10%
Married Biweekly 710 2013 46.2 15%
Married Biweekly 2013 4300 241.65 27%
Married Biweekly 4300 6908 859.14 30%
Married Biweekly 6908 12187 1641.54 35%
Married Biweekly 12187 3489.19 38.60%
Married Daily 24.8 71 10%
Married Daily 71 201.3 4.62 15%
Married Daily 201.3 430 24.17 27%
Married Daily 430 690.8 85.92 30%
Married Daily 690.8 1218.7 164.16 35%
Married Daily 1218.7 348.93 38.60%
Thanks for any help on this.
Tom Hagen Jr.
IT Professional
on paychecks based on the 2003 tax guidelines from the IRS
(www.irs.gov). This is for a client (they don't want to
use Access) and I'm not seeing an easy way to compare
multiple columns to get the data need to do the math
with. If anyone can help me out with the functions I
would be very grateful.
This is the top part of the worksheet 'Federal Tax'. The
sheet gets the user input from a W-2 sheet and a Main
Sheet that is updated by the user. Got most of this part
working but the final line for how much tax to withhold.
For line "6. Tax to be withheld" I need help to figure
out the formula. The tax is based on "Marriage Status"
from 'Main Sheet'!B5 , "Payroll Period" from 'Main Sheet'!
B7, and "5. Amount subject to withholding" from 'Federal
Tax'!E5. I can't figure out how to have excel compare
multiple rows and columns for the rest of the formula.
It goes something like this:
1. If married compare only married column
2. In the married section find the Payroll Period the
match how often check is cut.
3. Compare line 5 Amount subject to withhold to be
between amount in the Over column and Not Over column.
In the example a Married person making $600 on a Biweekly
pay period would have 10% withheld. As the amount falls
between 248 and 710.
1. Total wage payment $600.00
Payroll Period One Withholding Allowance
2. One allowance (Based on Payroll Period) $58.65
Annually 3050
3. Allowances claimed on Form W-4 2
Biweekly 117.31
4. Multiply line 2 by line 3 $117.30
Daily 11.73
5. Amount subject to withholding (subtract line 4 from
line 1) $482.70 Monthly 254.17
6. Tax to be withheld Quarterly
762.5
Semiannually 1525
Semimonthly 127.08
Weekly 58.65
The formula I'm using for "2. One allowance (Based on
Payroll Period)" is =VLOOKUP('Main Sheet'!B7,'Federal Tax'!
E1:F9,2)
Marriage Status Payroll Period Over Not Over
Income tax to withhold % to withhold
Married Annually 6450 18450 10%
Married Annually 18450 52350 1200 15%
Married Annually 52350 111800 6285 27%
Married Annually 111800 179600 22336.5 30%
Married Annually 179600 316850 42676.5 35%
Married Annually 316850 90714 38.60%
Married Biweekly 248 710 10%
Married Biweekly 710 2013 46.2 15%
Married Biweekly 2013 4300 241.65 27%
Married Biweekly 4300 6908 859.14 30%
Married Biweekly 6908 12187 1641.54 35%
Married Biweekly 12187 3489.19 38.60%
Married Daily 24.8 71 10%
Married Daily 71 201.3 4.62 15%
Married Daily 201.3 430 24.17 27%
Married Daily 430 690.8 85.92 30%
Married Daily 690.8 1218.7 164.16 35%
Married Daily 1218.7 348.93 38.60%
Thanks for any help on this.
Tom Hagen Jr.
IT Professional