Federal Tax Withholding Formula Slightly Off?

M

mike437

New Member
Joined
Nov 6, 2008
Messages
13
I am using the following formula to calculate my federal tax withholding:
Code: 
<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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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.
 
Upvote 0
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.

bBnEYiw.png


oKOQEql.png


qWkGfAI.png
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.[TABLE="width: 693"]
<tbody>[TR]
[TD="colspan: 21"]229,150
[/TD]
[/TR]
[TR]
[TD="colspan: 21"]TABLE 7—ANNUAL Payroll Period
[/TD]
[/TR]
[TR]
[TD="width: 50%, colspan: 11"](a) SINGLE person (including head of household)—
[/TD]
[TD="width: 50%, colspan: 10"](b) MARRIED person—
[/TD]
[/TR]
[TR]
[TD="width: 25%, colspan: 5"]If the amount of wages (after subtracting withholding allowances) is:
[/TD]
[TD="width: 25%, colspan: 6"]The amount of income taxto withhold is:
[/TD]
[TD="width: 25%, colspan: 5"]If the amount of wages (after subtracting withholding allowances) is:
[/TD]
[TD="width: 25%, colspan: 5"]The amount of income taxto withhold is:
[/TD]
[/TR]
[TR]
[TD="width: 25%, colspan: 5"]Not over $2,200...........
[/TD]
[TD="width: 25%, colspan: 6"]$0
[/TD]
[TD="width: 25%, colspan: 5"]Not over $8,300...........
[/TD]
[TD="width: 25%, colspan: 5"]$0
[/TD]
[/TR]
[TR]
[TD="width: 17%, colspan: 3"]Over—
[/TD]
[TD="width: 17%, colspan: 4"]But not over—
[/TD]
[TD="width: 17%, colspan: 3"]of excess over—
[/TD]
[TD="width: 17%, colspan: 4"]Over—
[/TD]
[TD="width: 17%, colspan: 4"]But not over—
[/TD]
[TD="width: 17%, colspan: 3"]of excess over—
[/TD]
[/TR]
[TR]
[TD="width: 10%"]$2,200
[/TD]
[TD="width: 10%, colspan: 3"]—$11,125
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$0.00 plus 10%
[/TD]
[TD="width: 10%, colspan: 2"]—$2,200
[/TD]
[TD="width: 10%"]$8,300
[/TD]
[TD="width: 10%, colspan: 3"]—$26,150
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$0.00 plus 10%
[/TD]
[TD="width: 10%"]—$8,300
[/TD]
[/TR]
[TR]
[TD="width: 10%"]$11,125
[/TD]
[TD="width: 10%, colspan: 3"]—$38,450
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$892.50 plus 15%
[/TD]
[TD="width: 10%, colspan: 2"]—$11,125
[/TD]
[TD="width: 10%"]$26,150
[/TD]
[TD="width: 10%, colspan: 3"]—$80,800
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$1,785.00 plus 15%
[/TD]
[TD="width: 10%"]—$26,150
[/TD]
[/TR]
[TR]
[TD="width: 10%"] $38,450
[/TD]
[TD="width: 10%, colspan: 3"]—$90,050
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$4,991.25 plus 25%
[/TD]
[TD="width: 10%, colspan: 2"]—$38,450
[/TD]
[TD="width: 10%"] $80,800
[/TD]
[TD="width: 10%, colspan: 3"]—$154,700
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$9,982.50 plus 25%
[/TD]
[TD="width: 10%"]—$80,800
[/TD]
[/TR]
[TR]
[TD="width: 10%"]$90,050
[/TD]
[TD="width: 10%, colspan: 3"]—$185,450
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$17,891.25 plus 28%
[/TD]
[TD="width: 10%, colspan: 2"]—$90,050
[/TD]
[TD="width: 10%"]$154,700
[/TD]
[TD="width: 10%, colspan: 3"]—$231,350
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$28,457.50 plus 28%
[/TD]
[TD="width: 10%"]—$154,700
[/TD]
[/TR]
[TR]
[TD="width: 10%"]$185,450
[/TD]
[TD="width: 10%, colspan: 3"]—$400,550
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$44,603.25 plus 33%
[/TD]
[TD="width: 10%, colspan: 2"]—$185,450
[/TD]
[TD="width: 10%"]$231,350
[/TD]
[TD="width: 10%, colspan: 3"]—$406,650
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$49,919.50 plus 33%
[/TD]
[TD="width: 10%"]—$231,350
[/TD]
[/TR]
[TR]
[TD="width: 10%"]$400,550
[/TD]
[TD="width: 10%, colspan: 3"]—$402,200
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$115,586.25 plus 35%
[/TD]
[TD="width: 10%, colspan: 2"]—$400,550
[/TD]
[TD="width: 10%"]$406,650
[/TD]
[TD="width: 10%, colspan: 3"]—$458,300
[/TD]
[TD="width: 10%, colspan: 2"]..
[/TD]
[TD="width: 10%, colspan: 3"]$107,768.50 plus 35%
[/TD]
[TD="width: 10%"]—$406,650
[/TD]
[/TR]
[TR]
[TD="width: 13%, colspan: 2"]$402,200
[/TD]
[TD="width: 13%, colspan: 3"]..............
[/TD]
[TD="width: 13%, colspan: 3"]$116,163.75 plus 39.6%
[/TD]
[TD="width: 13%, colspan: 3"]—$402,200
[/TD]
[TD="width: 13%, colspan: 2"]$458,300
[/TD]
[TD="width: 13%, colspan: 3"]...............
[/TD]
[TD="width: 13%, colspan: 3"]$125,846.00 plus 39.6%
[/TD]
[TD="width: 13%, colspan: 2"]—$458,300
[/TD]
[/TR]
[TR]
[TD="colspan: 21"]TABLE
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
247
mike4777
M
charlesstricklin
  • Question Question
Calculating federal withholding
Replies
5
Views
2K
charlesstricklin
charlesstricklin
G
  • Question Question
Rounding trouble when trying to calculate federal withholding tax for my paycheck
Replies
5
Views
1K
joeu2004
J
D
  • Question Question
Fed. Income Tax
Replies
5
Views
1K
dcribbie
D
G
  • Question Question
Federal Withholding Tax
Replies
1
Views
1K
stanleydgromjr
S

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top