Formula for calculating Federal Withholding in payroll

laclewis

New Member
Joined
Jan 13, 2003
Messages
8
I need a formula that will automatically calculate the Federal withholding on an employee's check based on gross wages. Here's my info: Wage Bracket Percentage Method Table for Computing Income Tax Withholding From Gross Wages Employer's Tax Guide Pub. 15A. This Table shows for i.e. if an employee has less than 40 hours and he will make over $0 but not over $472.30, then subtract $241.30 from gross wages, multiply the results by 10%. This is what you would withhold on Federal. But that employee has 40 hrs the next week here is what you're working with: Gross wages are $472.30 but not over $1,124.30 subtract 318.30 from gross wages and multiply results by 15%. What formula can I put in that will calculate federal withholdings based on gross wages no matter how many hours the employee has. Here is what I tried but didn't work =IF((gross wages>=472.30),0,(gross wages-318.30)*15%,(gross wages-241.30)*10%)Please Help
This message was edited by laclewis on 2003-01-14 17:44
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
i think your formula will work if you modify it as follows:

=IF(A1>=472.3,(A1-318.3)*0.15,(A1-241.3)*0.1)

where A1 contains your gross wage number. alternatively you might try this formula instead:

=IF(AND(A1>0,A1<=472.29),(A1-241.3)*0.1,0)+IF(AND(A1>472.3,A1<=1124.29),(A1-318.3)*0.15)

also, i wasn't seeing these numbers that you quoted in your post in the new 2003 pub. 15A so I would just double-check your numbers. and you may want to consider using cell references instead of constants in your formula or formulas as these numbers used in the IRS's withholding calculation tables will change often in the future

hth
kevin
 
Upvote 0
Thank you so much for your advice. The first formula worked. You're an angel, I spent 2 days on this. I was looking at the weekly payroll Married with 2 allowances column. I'm self taught when it comes to spreadsheets so I'm still learning. You referred to cell references, wouldn't that mean that I would have to input all of the data from the IRS wage bracket table? Would I just open a new book to do that?
Laura
 
Upvote 0
hi Laura, glad i could help - let me show you what i mean by cell references through an example:

in the first formula
=IF(A1>=472.3,(A1-318.3)*0.15,(A1-241.3)*0.1)

instead of having the numbers 472.3, 318.3, .15, 241.3, and .1 in the formula, replace these with references to cells in your spreadsheet that contain these numbers for example, you could place the following data into your spreadsheet:

cell B1 - 472.3
cell B2 - 318.3
cell B3 - .15
cell B4 - 241.3
cell B5 - .1

if these cells already contain data, place the data in other cells. then modify the formula as follows:

=IF(A1>=B1,(A1-B2)*B3,(A1-B4)*B5)

using the above cells in place of the actual numbers in your formula, or whatever cells you placed your data into if not in B1:B5

now when the formula information in the IRS publication changes, which it undoubtedly will, all you have to do is change the data in your cells B1:B5, and the formula will not have to be edited.

hth
kevin
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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