Calculate Federal payroll taxes

[bhansen70]

Does anyone know of a way to calculate federal tax withholding that should be taken out of your paychecks? What i have is the most current tax tables from the IRS and then some general information such as pay frequency and filing status. Then based on those entered parameters i need to have the federal taxes calculated.

Here is what my spreadsheet looks like.

Excel Workbook
	B	C	D
2	General Information
3	Gross Pay	$65,520.00	Annually
4	Pay Period	Bi-Weekly
5	Filing Status	Married
6	# of Allowances	0
7	Additional Fed Withholding	$0.00
8
SalaryPaycheck

Cells C4 and C5 are pull down cells that include the different options as listed here.

C4 - Daily, Weekly, Bi-Weekly, Monthly, Semi-Monthly
C5 - Single or Married

Then i have these tax tables created as per IRS

Excel Workbook
	B	C	D	E	F	G	H
3	TABLE 1- WEEKLY Payroll Period
4
5	Weekly Single				Weekly Married
6	Amount	Base	Percent		Amount	Base	Percent
7	$138	$0.00	10%		$303	$0.00	10%
8	$200	$6.20	15%		$470	$16.70	15%
9	$696	$80.60	25%		$1,455	$164.45	25%
10	$1,279	$226.35	28%		$2,272	$368.70	28%
11	$3,338	$802.87	33%		$4,165	$898.74	33%
12	$7,212	$2,081.29	35%		$7,321	$1,940.22	35%
13
14	TABLE 2- BIWEEKLY Payroll Period
15
16	Bi-Weekly Single				Bi-Weekly Married
17	Amount	Base	Percent		Amount	Base	Percent
18	$276	$0.00	10%		$606	$0.00	10%
19	$400	$12.40	15%		$940	$33.40	15%
20	$1,392	$161.20	25%		$2,910	$328.90	25%
21	$2,559	$452.95	28%		$4,543	$737.15	28%
22	$6,677	$1,605.99	33%		$8,331	$1,797.79	33%
23	$14,423	$4,162.17	35%		$14,642	$3,880.42	35%
24
25	TABLE 3- SEMIMONTHLY Payroll Period
26
27	Semi-Monthly Single				Semi-Monthly Married
28	Amount	Base	Percent		Amount	Base	Percent
29	$299	$0.00	10%		$656	$0.00	10%
30	$433	$13.40	15%		$1,019	$36.30	15%
31	$1,508	$174.65	25%		$3,152	$356.25	25%
32	$2,772	$490.65	28%		$4,922	$798.75	28%
33	$7,233	$1,739.73	33%		$9,025	$1,947.59	33%
34	$15,625	$4,509.09	35%		$15,863	$4,204.13	35%
35
36	TABLE 4- MONTHLY Payroll Period
37
38	Monthly Single				Monthly Married
39	Amount	Base	Percent		Amount	Base	Percent
40	$598	$0.00	10%		$1,313	$0.00	10%
41	$867	$26.90	15%		$2,038	$72.50	15%
42	$3,017	$349.40	25%		$6,304	$712.40	25%
43	$5,544	$981.15	28%		$9,844	$1,597.40	28%
44	$14,467	$3,479.59	33%		$18,050	$3,895.08	33%
45	$31,250	$9,017.98	35%		$31,725	$8,407.83	35%
2009TaxTable

So in this example if an employee was being paid $65,520 per year which would be $2,520.00 Bi-Weekly. The federal tax would be $232.60 if the employee was Married with 0 allowances being paid Bi-Weekly. I would like to have this calculated with an excel formula using the tax tables in this example.

Any help would be highly appreciated.

 

[bhansen70]

Excel Workbook
	B	C	D
3	Gross Pay	$2,520.00
4	Pre-Tax Deduction	$0.00
5	Taxable Wages	$2,520.00
6	Federal Tax	$270.40
7	Social Security	$156.24
8	Medicare	$36.54
9	Post-Tax Deduction	$0.00
10	Total Taxes	$463.18
11	Net Pay	$2,056.82
HourlyPaycheck

This is what i use now but need to add the ability for filing status and pay frequency as i have listed in my first post.


Any help would be highly appreciated.

 

[bhansen70]

Is this just impossible to accomplish using Excel?

 

[charlesstricklin]

I would be interested to know if there's a solution to this, as I have the same question.