Results 1 to 4 of 4

Formula for calculating Federal Withholding in payroll

This is a discussion on Formula for calculating Federal Withholding in payroll within the Excel Questions forums, part of the Question Forums category; I need a formula that will automatically calculate the Federal withholding on an employee's check based on gross wages. Here's ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default

    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 ]

  2. #2
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default

    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

  3. #3
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default

    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

  4. #4
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com