Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll tax deductions
I need help in setting up fed and state payroll tax deductions using the
percentage method. I have checked other payroll tax templates and don't find what I am looking for. It would be a look up table that the field would have to look at to see what percentage of tax needs to be withheld, depending on the wages for the period, such as every two week payperiod. Or is this too much to program? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll tax deductions
You mean there are different slabs of tax rates for each level of income?
-- Best Regards, FARAZ A. QURESHI "jgoblish" wrote: I need help in setting up fed and state payroll tax deductions using the percentage method. I have checked other payroll tax templates and don't find what I am looking for. It would be a look up table that the field would have to look at to see what percentage of tax needs to be withheld, depending on the wages for the period, such as every two week payperiod. Or is this too much to program? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll tax deductions
It's very easy to set up. You just need a lookup table. Your table would
have two columns: wages and tax rate. The entry for wages in each row would be the amount where the rate changes, and the tax rate for that amount. Then use Vlookup to get the correct tax rate. After you've check out Vlookup in Help, post back if you need more help. Regards, Fred "jgoblish" wrote in message ... I need help in setting up fed and state payroll tax deductions using the percentage method. I have checked other payroll tax templates and don't find what I am looking for. It would be a look up table that the field would have to look at to see what percentage of tax needs to be withheld, depending on the wages for the period, such as every two week payperiod. Or is this too much to program? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll tax deductions
"jgoblish" wrote:
I need help in setting up fed and state payroll tax deductions using the percentage method. I have checked other payroll tax templates and don't find what I am looking for. It would be a look up table that the field would have to look at to see what percentage of tax needs to be withheld, depending on the wages for the period, such as every two week payperiod. For one method, refer to http://www.mcgimpsey.com/excel/variablerate.html . Alternatively and expanding, consider the following. First, set up a table with the amounts for 1 allowance. For example: Allowance 1 Weekly 67.31 2 Biweekly 134.62 3 Semimonthly 145.83 4 Monthly 291.67 5 Quarterly 875.00 6 Semiannually 1750.00 7 Annually 3500.00 8 Daily, Misc 13.46 Assume that B2 contains the gross wage subject to tax (after pre-tax deductions), and C2 contains the payroll period index (1, 2, etc). Then the amount subject to withholding can be computed in D2: =B2 - vlookup(C2, Allowance, 3, 0) Second, create a withholding table for each payroll period. For example, for federal: Weekly 0 0 0% 51 0 10% 198 14.70 15% 653 82.95 25% 1533 302.95 28% 3202 770.27 33% 6918 1995.89 35% Then the federal withholding can be computed by: =round( (D2 - vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly, Quarterly, Semiannually, Annually, Daily), 1) * vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly, Quarterly, Semiannually, Annually, Daily), 3) + vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly, Quarterly, Semiannually, Annually, Daily), 2), 2) Note that other rounding options may be permitted. Also note that technically, column of the withholding table should be one less (i.e. 50, 197, 652, etc). However, usually marginal rate withholding tables are designed so that it does not matter. (I found one exception in a state withholding table some years ago. But I am quite sure it was a mistake.) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll tax deductions
Errata....
On Mar 2, 11:26*am, wrote: =round( (D2 - vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly, Quarterly, Semiannually, Annually, Daily), 1) There is a missing right parenthesis. It should be: =round( (D2 - vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly, Quarterly, Semiannually, Annually, Daily), 1)) Also note that technically, column of the withholding table should be one less (i.e. 50, 197, 652, etc). I meant to write: "... column 1 of the withholding table ...". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding payroll stubs payroll calculator | New Users to Excel | |||
I need a template to help with sorting/deductions for tax recepts | Setting up and Configuration of Excel | |||
Set up Tax planning sheet by week/monrth- Income/deductions | Excel Worksheet Functions | |||
Planning monthly deductions | Excel Discussion (Misc queries) | |||
How do I setup a formula for payroll deductions in excel | Excel Worksheet Functions |