I'm intrested in doing this on an excel sheet as well, but my numbers are different, and I'm not grasping where they plug in.
I'm single, and get biweekly pay. It says:
Not over 233 = $0 withheld
233 - 401 = 10% of excess over 233
401 - 1387 = 16.80 plus 15% of excess over 401
1387 - 2604 = 164.70 plus 25% of excess over 1387
I don't need to go higher than that at the moment.
Any ideas of how that would be set up in the prior table?
Much appreciated!
-- Nikki
Ron Rosenfeld wrote:
Re: Calculate Personal Federal Tax - weekly
08-Nov-08
The only advantage of the formulas, vs just the values, is that next year, when
the tax tables change, editing the table will be simpler.
I suspect your TaxTable NAME'd range had only a worksheet scope. I think in
the pre-2007 versions of Excel, when NAMEing the range, you need to include the
SheetName in order to get workbook scope.
Glad to help. And I'm glad you got it worked out.
--ron
Previous Posts In This Thread:
Calculate Personal Federal Tax - weekly
I've set up a spreadsheet to calculate my regular personal paycheck taxes
which vary week to week. My problem... the variables in calculating the
"Federal WH".
D4 F4 H4 J4 L4 N4 P4
R4 T4
GROSS Federal State County FICA Other Other2 TOTAL NET
$1188.00 $ $40.39 $11.88 $90.88 $35.64 $80.77 $116.53 $121.07
For the "Federal" I used their standard formulas (below):
NOT over $154 $0.00
Over $154 but NOT over $453 ADD 10% to excess over $154
Over $453 but NOT over $1,988 ADD 15% to excess over $453 + $29.90
Over $1,388 but NOT over $2,651 ADD 25% to excess over $1,388 + $170.15
Over $2,651 but NOT over $3,994 ADD 28% to excess over $2,651 + $485.90
Over $3,994 but NOT over $7,021 ADD 33% to excess over $3,994 + $861.94
Over $7,021 ADD 35% to excess over $7,021 +
$1,860.85
Below is what I started and have tried in vain to make work.
=IF(AND(D4>154,D4<453),(D4-154)*10%,0),IF(AND(D4>453.1,D4<1388),(D4-453)*15%+29.9,0)
Please help!
TWM
Re: Calculate Personal Federal Tax - weekly
Set up a table as below, NAME it TaxTbl, then, with your gross in A1, use this
formula:
=VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3)
Amt Base PerCent
$0 $- 0%
$154 $- 10%
$453 $29.90 15%
$1,388 $170.15 25%
$2,651 $485.90 28%
$3,994 $861.94 33%
$7,021 $1,860.85 35%
--ron
RE: Calculate Personal Federal Tax - weekly
Hi,
Here is a rather elegent solutions:
Set up a tax table, here in the range A9
14:
A B C D
9 153.01 0 10%
10 453.01 29.9 15% 112.047
11 1388.01 170.15 25% -47.005
12 2651.01 485.9 28% -406.2856
13 3994.01 861.94 33% -922.0266
14 7021.01 1860.85 35% -2037.357
In cell D9 enter the formula
=(D$4-A10-0.01)*C10
And copy it down.
Here is the formula necessary to calculate the federal tax:
=SUMPRODUCT(VLOOKUP(D4,A9
14,{2,4}))
If you name the tax table T then the formula becomes
=SUMPRODUCT(VLOOKUP(D4,T,{2,4}))
If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire
:
Re: Calculate Personal Federal Tax - weekly
On Sat, 1 Nov 2008 13:53:02 -0700, ShaneDevenshire
In trying your solution, either as you've written it or as displayed, I don't
get the same answers as precise application of the IRS table would provide.
I suspect the formula you wrote for D9 is really in D10, and that D9
14 should
be filled with the formula. If that is the case:
Doing that, your results are "close enough" for the purpose of WH, but will
occasionally be off by a penny or a dime.
Your formula shows a tax of $0.10 on $154, where it should be zero.
However, if in addition to making the change in the column D formula locations
I suggested, you also change your break points in Col A to agree with those in
the IRS table, (e.g. 154 instead of 153.01 and so forth, then your answers
agree with the table to two decimal places.
--ron
Re: Calculate Personal Federal Tax - weekly
Ron's VLOOKUP approach is the most malleable and arguably easiest to
set up because it follows directly from the tax tables.
However, if you are looking for a one-line approach, there are several
ways to implement it without nested IFs. One approach (using your
table; I did not vet it):
=3DMAX(0, (D4-154)*10%, (D4-453)*15%+29.9,
(D4-1388)*25%+170.15, (D4-2651)*28%+485.9,
(D4-3994)*33%+861.94,(D4-7021)*35%+1860.85)
The terms can be simplified algebraically (e.g, D4*15%-38.05). But it
is not as clear how to change the formula later.
NOTES:
1. If you want to calculate your "paycheck tax", I assume you mean you
want to calculate your weekly withholding. Typically, that is not the
same as your tax liability. (Although you can try to make it close.
That's another story.)
The withholding computation is more complicated because it is not
based on the "gross" wages per se. For the computation above, D4
should be: gross wages minus pre-tax deductions minus withholding
allowance. Only you can know your pre-tax deductions, if any. The
weekly withholding allowance for 2008 is 67.31 times the number of
allowances you claim on a W-4, which can be zero.
Since your other wage withholdings (e.g. FICA and state and local
taxes) are usually not based on the federal taxable wages, you
probably want to use something other than D4 for one or the other.
2. A quick spot-check confirms that the marginal numbers you use above
are indeed from the withholding tables -- weekly married,
specifically.
Re: Calculate Personal Federal Tax - weekly
One additional point which joeu has alluded to.
In calculating your withholding using this method, which is called, by the IRS,
the "percentage" method, you must first subtract your allowances from your
gross pay. For a weekly paycheck, that amount is $67.31 per deduction. So if
your gross pay is $1,188 and you have told your employer (on your W4) that you
have two deductions, the value entered into the formula would be $1,053.38 and
your WH would be $119.96.
But your employer can also choose to use other methods to compute withholding.
So $1,188 with two deductions might result in a WH of $120 from the
wage-bracket method. And there are several other IRS acceptable methods which
may result in even different amounts (see IRS Pubs 15 and 15a).
As a matter of fact, your employer can use any method he wants to calculate
your fed WH tax, so long as the difference between that method, and the IRS
approved method, does not differ by a specified amount.
--ron
All I can say is WOW... Thank you all so much for your help!
All I can say is WOW... Thank you all so much for your help!
And Ron for his attention to the smallest details, and dry humor.
AGAIN, Thank you ALL!
Re: Calculate Personal Federal Tax - weekly
You're welcome.
By the way, do your calculations agree with that of your employer?
--ron
Re: Calculate Personal Federal Tax - weekly
Everything works GREAT. Thanks Ron!
Tom
:
Shane and/or Ron,The calculation works well. Thanks again.
Shane and/or Ron,
The calculation works well. Thanks again.
One more thing though.
I keep WEEKLY time spreadsheets. These track my hours, payrate, and finally
my Gross pay. These WEEKLY spreadsheets are saved individually by date, and
time is kept from Sunday thru Saturday in each.
Another spreadsheet which is linked to ALL of the WEEKLY time sheets is
called 'Time2008' which merely summaries everything by the week for the
entire year.
Yet another linked spreadsheet called 'Taxes2008' acutally does all of the
tax calculations. My taxes and deductions are calculated as each WEEKLY
timesheet is fillout. GROSS pay is in column D, row 4 through row 56 (one
for each week... see below). My current problem (or maybe I wasn't fully
understanding what the two of you explained) is how do I make it calculate
the Federal WH automatically for EACH WEEK and retain it for each in
'Taxes2008' (see below)?
1 B D F H J L N
P R T
2 Week GROSS Fed State County FICA I.U.O.E Other Total NET
3 3.40% 1.00% 7.65% 3.00%
4 1/5/08 $1,188.00 140.15 $40.39 $11.88 $90.88 $35.64 $0 $318.94 $869.06
5 1/12/08 $1,446.70 184.82 $49.19 $14.47 $110.67 $43.40 $0 $402.55 $1,044.15
6 1/19/08
7
8
I did create YOUR tax sheet and called it 'TaxTable' Works great, but you
have to manually change the number in 'TaxTable' "D4"
In short... my WEEKLY GROSS is in D4 for week 1, D5 for week 2, D6 for week
3 and so on. I would like the FEDERAL WH to calculate automately for each
week and display in F4 for week 1, F5 for week 2, F6 for week 3 and so on.
Please!
Whew... long winded I am, but I wanted to explain as best I could!
With respect;
Tom
:
No, my calculations do NOT agree with my employer.
No, my calculations do NOT agree with my employer. STATE WH is a couple of
dollars low, and FEDERAL WH appears to average $70 to $90 high. I checked a
few online calculators, and Shane's and your spreadsheet work out to within
...10 cents of the online calculators. Much closer than my employer. That's
why I wanted to create this spreadsheet. I believe something is amiss with
my employer.
Thanks again;
Tom
Re: Calculate Personal Federal Tax - weekly
You merely put the formula in the Fed cell on your sheet Time2008!F4
The formula would look something like:
=VLOOKUP(D4,TaxTbl,2)+(D4-VLOOKUP(D4,TaxTbl,1))*VLOOKUP(D4,TaxTbl,3)
As you "fill down", the D4 will adjust to the proper row.
You need to make sure that your NAME'd TaxTbl is a Workbook level name.
--ron
Ron, I copied and pasted your formula, and I get a #NAME?
Ron, I copied and pasted your formula, and I get a #NAME? error
The tax table I created is called TaxTable and I changed the name in your
formula. This is the TaxTable I created which starts at column A row 9 to
column D row 14.
154.00 0.00 10%
453.00 29.90 15% =(D$4-A10-0.01)*C10
1388.00 170.15 25% =(D$4-A11-0.01)*C11
2651.00 485.90 28% =(D$4-A12-0.01)*C12
3994.00 861.94 33% =(D$4-A13-0.01)*C13
7021.00 1860.85 35% =(D$4-A14-0.01)*C14
Sorry, I don't know what the formulas in D10
14 (D9 is blank)
Tom
:
Re: Calculate Personal Federal Tax - weekly
That's very odd. Maybe he is not counting some deductions you claimed on your
W4?
--ron
Re: Calculate Personal Federal Tax - weekly
My formula was designed to work with the Tax Table I provided. The Tax Table I
provided has only three columns. And you are missing a row in what you posted:
Amt Base PerCent
$0 0 0%
$154 0 10%
$453 $29.90 15%
$1,388 $170.15 25%
$2,651 $485.90 28%
$3,994 $861.94 33%
$7,021 $1,860.85 35%
If your first row of data is in A2, then
B2: 0
B3: =B2+C2*(A3-A2)
and fill down to B8.
If you are getting a NAME error, and you changed the formula I provided
properly, then your TaxTable name does not have the Workbook Scope, as I wrote
it should have. You can either NAME it properly, or use the other reference I
mentioned:
=SheetName!$A$1:$C$8 in place of TaxTable
Substitute the correct name where I have SheetName above. You may have to add
single quotes around the name if there is a space in the correct name.
Of course, you could have some other typo on the formula; that would also give
you a NAME error.
--ron
Ron,I did not have the formula(s) for B3 to B8.
Ron,
I did not have the formula(s) for B3 to B8. I've applied them as per your
direction.
Still had the #NAME? problem. So I again made changes as you suggested.
=VLOOKUP(D6,TaxTable!$A$2:$C$8,2)+(D6-VLOOKUP(D6,TaxTable!$A$2:$C$8,1))*VLOOKUP(D6,TaxTable!$A$2:$C$8,3)
Viola! I now have a working spreadsheet!
Thanks for being patient!
Tom
:
Re: Calculate Personal Federal Tax - weekly
The only advantage of the formulas, vs just the values, is that next year, when
the tax tables change, editing the table will be simpler.
I suspect your TaxTable NAME'd range had only a worksheet scope. I think in
the pre-2007 versions of Excel, when NAMEing the range, you need to include the
SheetName in order to get workbook scope.
Glad to help. And I'm glad you got it worked out.
--ron
Numbers don't match
I'm intrested in doing this on an excel sheet as well, but my numbers are different, and I'm not grasping where they plug in.
I'm single, and get biweekly pay. It says:
Not over 233 = $0 withheld
233 - 401 = 10% of excess over 233
401 - 1387 = 16.80 plus 15% of excess over 401
1387 - 2604 = 164.70 plus 25% of excess over 1387
I don't need to go higher than that at the moment.
Any ideas of how that would be set up in the prior table?
Much appreciated!
-- Nikki
Submitted via EggHeadCafe - Software Developer Portal of Choice
Win a free Professional license of CodeSmith Generator 5.2!
http://www.eggheadcafe.com/tutorial...sional-license-of-codesmith-generator-52.aspx