Federal Tax Withholding Formula Slightly Off?

[mike437]

I am using the following formula to calculate my federal tax withholding:

<code>=IF(C10<1479,((C10-SUM(B13:B17)-428)*.15)+34.3,((C10-SUM(B13:B17)-1479)*.25)+191.95)</code></pre>

C10 is my gross pay and B13:B17 are all of my pre-tax deductions.

All of the other formulas I have are on the money -- SS, Medicare, Retirement -- but my Federal withholding is pennies too low. The formula is displaying 227.77 when both my paycheck and PaycheckCity show 227.83. What would cause this slight variation?

 

[Andrew Poulsom]

It may help to see what's in C10 and B13:B17. Maybe the tax on your payslip is calculated on a cumulative basis. That's certainly the case in the UK.

 

[mike437]

C10 is just the sum of my gross pay -- in this case, $1771.40. B13:B17 are all pre-tax withholdings. B13 is 3% of my gross pay, which is dynamic. B14 through B17 are all static values. I don't believe it's calculated based on a year-to-date value for two reasons: the value is only $0.06 off from what it should be, and when I used the PaycheckCity calculator, I didn't input any year-to-date calculations -- just what was on this paycheck. See the three attached photos. The first is PaycheckCity's results, the second is my check, and the third is my spreadsheet.

 

[Andrew Poulsom]

You have the constant 191.95 in your formula.

428*8% is 34.24 plus 1051*15% is 157.65, so it should be 191.89.

 

[mike437]

Those numbers are static because of the IRS Publication 15 calculation, seen here

 

[Andrew Poulsom]

Then I don't know, sorry. Your formula works if net is $3,463.

 

[MikeWx]

Many automated payroll systems annualize your per pay period taxable wages, calculate the annual tax on the annualized wages, then divide the tax by the factor used to annualize the wages. In your case, $1,622.29 * 26 pay periods is $42,179.54 From the annual table below from Pub 15, your annual tax is $5,923.64. Dividing the annual tax by 26 pay periods results in a pay period tax amount of $227.83.

229,150
TABLE 7—ANNUAL Payroll Period
(a) SINGLE person (including head of household)—	(b) MARRIED person—
If the amount of wages (after subtracting withholding allowances) is:	The amount of income taxto withhold is:	If the amount of wages (after subtracting withholding allowances) is:	The amount of income taxto withhold is:
Not over $2,200...........	$0	Not over $8,300...........	$0
Over—	But not over—	of excess over—	Over—	But not over—	of excess over—
$2,200	—$11,125	..	$0.00 plus 10%	—$2,200	$8,300	—$26,150	..	$0.00 plus 10%	—$8,300
$11,125	—$38,450	..	$892.50 plus 15%	—$11,125	$26,150	—$80,800	..	$1,785.00 plus 15%	—$26,150
$38,450	—$90,050	..	$4,991.25 plus 25%	—$38,450	$80,800	—$154,700	..	$9,982.50 plus 25%	—$80,800
$90,050	—$185,450	..	$17,891.25 plus 28%	—$90,050	$154,700	—$231,350	..	$28,457.50 plus 28%	—$154,700
$185,450	—$400,550	..	$44,603.25 plus 33%	—$185,450	$231,350	—$406,650	..	$49,919.50 plus 33%	—$231,350
$400,550	—$402,200	..	$115,586.25 plus 35%	—$400,550	$406,650	—$458,300	..	$107,768.50 plus 35%	—$406,650
$402,200	..............	$116,163.75 plus 39.6%	—$402,200	$458,300	...............	$125,846.00 plus 39.6%	—$458,300
TABLE

<tbody>
</tbody>

 

[Andrew Poulsom]

That's what I alluded to in Post #2. Thanks for solving.

 

[MikeWx]

You're welcome. Glad to help.