Which Tax Tables to use?

Hi,

I am new to excel and I took my whole Sunday searching high and low
for a formula or template (yea I guess I am lazy, but don't want to
spend my whole Sunday doing something heh, especially reinventing the
wheel) to calculate Federal withholding to organize my personal budget
and figure what-if scenarios. I finally wrote/copied code that I found
bits and pieces of. I learned a lot about tax tables and such. However,
it has come to my attention that there are at least (2) official
sources for Federal Tax tables. The trouble is, both are different and
I am not sure which one to use.

The first source is "Publication 15", aka "Circular E, Employer's Tax
Guide".
http://www.irs.gov/pub/irs-pdf/p15.pdf

The second is the "Wage Bracket Method Table for Computing Income Tax
Withholding From Gross Wages"
http://www.irs.gov/publications/p15a/21453t29.html

I made a replica (just the first part done so far that pertains to me)
of the Wage Bracket Method Table on a worksheet and am referencing the
formula's there. I will likely change the actual positions of the cell
references later, as I want it to look as close to the actual table as
possible. I figure that when I share this workbook I am creating, I
would like to have as reference things that look familiar and also
inform users how things are being calculated. I was going to learn how
to use an array, but I wasn't sure how to do that since I have a column
with the word subtract (per the Wage Table document). I will likely
make a work-around later.

So, here is my formula:
=IF(AND(A1>B8,A1<=C8),((A1-E8)*F8),IF(AND(A1>B9,A1<C9),((A1-E9)*F9),IF(AND(A1>B10,A1<C10),((A1-E10)*F10),IF(AND(A1>B11,A1<C11),((A1-E11)*F11),IF(AND(A1>B12,A1<C12),((A1-E12)*F12),)))))


I hope it makes since.. and also, here is an image on fileshack of
what my table looks like, that should help.

http://img100.imageshack.us/my.php?i...axtable8iu.png

I used the cell references in the hope that next year I can just use
the table again, with it updated. I plan to distribute a workbook that
allows you to enter in your pertanant information, such as dependants,
married, etc and it update/change the formula's required to calculate
the actual withholding. In the workbook will be a worksheet to
calculate monthly budget, all unlocked so that a user can alter,
protect as they wish.

Thank you for your help in advance!

-Alden

junk.jason@gmail.com wrote:
> it has come to my attention that there are at least (2) official
> sources for Federal Tax tables. The trouble is, both are different and
> I am not sure which one to use.
>
> The first source is "Publication 15", aka "Circular E, Employer's Tax
> Guide". http://www.irs.gov/pub/irs-pdf/p15.pdf
>
> The second is the "Wage Bracket Method Table for Computing Income Tax
> Withholding From Gross Wages"
> http://www.irs.gov/publications/p15a/21453t29.html

As the URL path name suggests, the second table comes from IRS Pub
15-A. As that Pub and Pub 15 explain, there are a variety of methods
that employers can use to compute withholding, depending on the payroll
system (if any) that they use. Consequently, your ability to match
exactly the amount of tax that an employer might withhold depends on
knowing exactly the method of calculation used by that employer. That
is usually unnecessary. If you compare all of the methods, hopefully
the difference is no more than $50 per pay period.

For planning purposes, I simply use the Percentage Method in IRS Pub
15. I think it is the easiest to set up in Excel. For example, the
taxable income for a semimonthly salary is:

=GrossIncome - Allowances*137.50

The income tax for Married to withhold semimonthly is:

=10%*max(0,TaxableInc-333) + 5%*max(0,TaxableInc-954) + 10%*max(0,
TaxableInc-2835)
+ 3%*max(0, TaxableInc-5288) + 5%*max(0,TaxableInc-8144) + 3%*max(0,
TaxableInc-14315)

For planning purposes, don't forget to account for Soc Sec and Medicare
withholding (FICA).

Also, if a significant portion of compensation is in bonuses, be sure
to study the Supplement Wages section of Pub 15 and decide how to apply
the employer's choices.

Finally, if you work in a state that has income taxes and disability
insurance tax (e.g. California), be sure to study the information in
similar "pubs" for that state.

It sounds to me like joeu2004 knows a whole lot more about this than I do,
but I agree that you want to be looking at Publication 15, and that the
percentage method is easier to set up in Excel. Also, it is independent of
information on the W4.

I started working with someone elsewhere in setting up an Excel workbook to
help do payroll - ended up abandoning the project because of my ignorance of
all the very complex rules for it all. But I did make progress in
calculating the withholding part of it using the percentage method. You can
see the work I did do in this workbook:
http://www.jlathamsite.com/uploads/A...ingPayroll.xls
perhaps it'll give you some ideas. Then again, may confuse you all to heck
- I know it did me before it was open. It gets nasty because there are so
many conditions to check for and different tables to use. It would get a lot
cleaner if you are just trying to do it all for one person with known set of
'conditions'.

"junk.jason@gmail.com" wrote:

> Hi,
>
> I am new to excel and I took my whole Sunday searching high and low
> for a formula or template (yea I guess I am lazy, but don't want to
> spend my whole Sunday doing something heh, especially reinventing the
> wheel) to calculate Federal withholding to organize my personal budget
> and figure what-if scenarios. I finally wrote/copied code that I found
> bits and pieces of. I learned a lot about tax tables and such. However,
> it has come to my attention that there are at least (2) official
> sources for Federal Tax tables. The trouble is, both are different and
> I am not sure which one to use.
>
> The first source is "Publication 15", aka "Circular E, Employer's Tax
> Guide".
> http://www.irs.gov/pub/irs-pdf/p15.pdf
>
> The second is the "Wage Bracket Method Table for Computing Income Tax
> Withholding From Gross Wages"
> http://www.irs.gov/publications/p15a/21453t29.html
>
> I made a replica (just the first part done so far that pertains to me)
> of the Wage Bracket Method Table on a worksheet and am referencing the
> formula's there. I will likely change the actual positions of the cell
> references later, as I want it to look as close to the actual table as
> possible. I figure that when I share this workbook I am creating, I
> would like to have as reference things that look familiar and also
> inform users how things are being calculated. I was going to learn how
> to use an array, but I wasn't sure how to do that since I have a column
> with the word subtract (per the Wage Table document). I will likely
> make a work-around later.
>
> So, here is my formula:
> =IF(AND(A1>B8,A1<=C8),((A1-E8)*F8),IF(AND(A1>B9,A1<C9),((A1-E9)*F9),IF(AND(A1>B10,A1<C10),((A1-E10)*F10),IF(AND(A1>B11,A1<C11),((A1-E11)*F11),IF(AND(A1>B12,A1<C12),((A1-E12)*F12),)))))
>
>
> I hope it makes since.. and also, here is an image on fileshack of
> what my table looks like, that should help.
>
> http://img100.imageshack.us/my.php?i...axtable8iu.png
>
> I used the cell references in the hope that next year I can just use
> the table again, with it updated. I plan to distribute a workbook that
> allows you to enter in your pertanant information, such as dependants,
> married, etc and it update/change the formula's required to calculate
> the actual withholding. In the workbook will be a worksheet to
> calculate monthly budget, all unlocked so that a user can alter,
> protect as they wish.
>
> Thank you for your help in advance!
>
> -Alden
>
>

JLatham wrote:
> I agree [...] that the percentage method is easier to set up in Excel.
> Also, it is independent of information on the W4.

I am not sure what you intended to say, but the number of allowances
comes directly from the W4 that you file with your employer. (And the
DE4 for California.) In that sense, the withholding computation
(however you choose to do it) is __not__ independent of the W4.

PS: In my original response, I neglected to include a W4 option that
many employers support, namely the ability to withhold an additional
fix amount per pay period in addition to the amount determined by the
withholding computation. Yet another reason why income tax withholding
is "dependent" on the W4.

Thank you both for your responses. The thing is, I used the tax table
and my formula, and with a $368.20 "test", it came within 2 cents. I
think I can live with that. I started with the excel template "Personal
Monthly Budget" from office.microsoft.com . It has an actual and a
projected monthly income. I plan to take that worksheet (heavily
modified) and eventually be able on it to put in (or on another sheet)
my pay rate and hours, my overtime/bonus pay, subtract allowances and
insureance, etc.and have the worksheets automatically figure out my
gross wages.

I want it to figure out my federal, state, and social security
withholding, tax out my tihes, everything. All that bascially
completly varible with a person's pertenant information giving you a
complete overview. I know it will be a BIG project, especially for
someone who is relatively new to Excel and whose only experiance with
programming anything is Counter Strike Scripts, lol. I guess I will
have to take into account different emplorer methods of withholding,
but most should be very similar and I could always leave it open to
adjustment by the individual using the workbook.


I just got in from work after being at Walmart for two hours with bad
shoes and $160 in groceries. So I am none the less tired and will check
out your sample workbook in the morning. I can't wait.


joeu2004@hotmail.com wrote:
> JLatham wrote:
> > I agree [...] that the percentage method is easier to set up in Excel.
> > Also, it is independent of information on the W4.
>
> I am not sure what you intended to say, but the number of allowances
> comes directly from the W4 that you file with your employer. (And the
> DE4 for California.) In that sense, the withholding computation
> (however you choose to do it) is __not__ independent of the W4.
>
> PS: In my original response, I neglected to include a W4 option that
> many employers support, namely the ability to withhold an additional
> fix amount per pay period in addition to the amount determined by the
> withholding computation. Yet another reason why income tax withholding
> is "dependent" on the W4.

what are you looking for?



"unknown" escreveu:

>

> I believe the following should work just as well for Single Weekly.
> Assuming that A1 is Gross Wages and B1 is Allowances:
>
> =max(0, 10%*(A1 - B1*63.46 - 51), 15%*(A1 - B1*63.46 - 98), 25%*(A1 -
> B1*63.46 - 306.80), 28%*(A1 - B1*63.46 - 424.89), 33%*(A1 - B1*63.46 -
> 817.03), 35%*(A1 - B1*63.46 - 1142.23))
>
> Of course, you can save yourself some typing by replacing A1 - B1*63.46
> with C1 (taxable income) computed as:
>
> =A1 - B1*63.46
>
> You might need to convince yourself that the single max(...) formula
> above works for all combinations of allowances, given that the second
> reference shows different gross-wage cut-offs and offsets for each tax
> bracket. For example, for 15%, the gross wage range is $192.01 to $620
> for zero allowances, but $255.47 to $683.46 for one allowance. But
> also note that the offsets are -98 and -161.46 respectively. If we
> call the zero-allowance gross wage limits the "taxable income" (T),
> then algebraically the one-allowance case for the 15% bracket becomes:
>
> Gross - 161.46
> = (T + 63.46) - (98 + 63.46)
> = T + 63.46 - 98 - 63.46
> = T - 98
>
> As you can see, the additional factor (Allowances*63.46) for the
> cut-offs and offset simply cancels out.
>



Yes, that made it simple to understand. It has been awhile since I
have done Algebra. I need to obviously brush up before I go back to
school this spring.

I figured out my GA state taxes by hand and I could come within a few
pennies if I used the federal taxable wages, and I was over if I used
just my gross pay.

For example on one check I had $ 388.80, with 370.30 taxable
federally.

So I worked out:

370.30 - 44.25 = 326.05
326.05 - 135 = 191.05
191.05 * 6% = 11.463 + 4.42 = 15.883

from this publication.

http://www.etax.dor.ga.gov/taxguide/...xGuide2005.pdf

I am single with no (zero) allowances.

I have been working on trying to go about making a formula for this
work but I guess I will have to keep at it. I need to find a few hours
without getting interrupted I guess.

If you however, can figure a formula without taking to much of your
time, it would greatly be appreciated. I know if you made one it would
undoubtedly be far simpler than my own.

Should I get one working I will get back to you.

Jason.Alden.Benoit@gmail.com wrote:
> I figured out my GA state taxes by hand
> [....]
> from this publication.
> http://www.etax.dor.ga.gov/taxguide/...xGuide2005.pdf
>
> I am single with no (zero) allowances.

And inferentially, it appears that you are paid weekly.

> I figured out my GA state taxes by hand and I could come within a few
> pennies if I used the federal taxable wages, and I was over if I used
> just my gross pay.
> For example on one check I had $ 388.80, with 370.30 taxable
> federally.

Let me clarify and correct the terminology in my first response in this
thread.

What I called "gross income" should be called "taxable gross pay (or
compensation)".
I am guessing that $370.30 is your federal "taxable gross" pay. That
is your gross pay less qualified federal pretax deductions, e.g.
employee contributions to a 401(k) or other employer pension plan and
to pretax accounts such as medical and commuter benefits. In theory,
your state's "taxable gross" pay may or may not be the same as the
federal taxable gross pay. That is the state's choice to make.
Apparently for GA, it is the same, based on your observation and based
on page 18 of the cited GA document ("Are contributions to qualified
.... plans taxable?"). I believe that is usually the case.

What I call "taxable income" is also called "wages subject to
withholding". Compare the definition of "taxable income" on page 8
with the example on page 40 of the cited GA document.

> If you however, can figure a formula without taking to much of your
> time, it would greatly be appreciated.

Not a problem. However, the GA instructions are poor and unclear on a
few points. The following is my interpretation. Use at your own risk.

I assume that you are looking at pages 41-43 of the cited GA document.
The GA Percentage Method is similar to the Federal Percentage Method.
You could use the formulation in my first response as a paradigm. By
the way, if you do not understand that formulation and you want an
explanation of the limits and the percentages in the formula, let me
know.

Looking at Form G-4, GA's equivalent to the Federal W-4, you declare
personal allowances (0-1 for single; 0-2 for others), dependent
allowances and additional allowances. I lump the latter two together
as "other allowances".

Thus, the gaTaxableIncome for Single Weekly can be computed by:

=gaTaxableGross - 44.25 - PersonalAllowances*51.92 -
OtherAllowances*57.50

Ostensibly, the gaWithholding might be computed by:

=1%*gaTaxableIncome + 1%*max(0,gaTaxableIncome-14.50) +
1%*max(0,gaTaxableIncome-43.50) + 1%*max(0,gaTaxableIncome-72) +
1%*max(0,gaTaxableIncome-101) + 1%*max(0,gaTaxableIncome-135)

I put that in a general form that works for all marginal tax tables.
But you might notice that 1% is a common factor in GA's case. So the
above can be simplified as follows:

=1%*(gaTaxableIncome + max(0,gaTaxableIncome-14.50) +
max(0,gaTaxableIncome-43.50) + max(0,gaTaxableIncome-72) +
max(0,gaTaxableIncome-101) + max(0,gaTaxableIncome-135))

However, that computes a tax of $15.90, whereas you correctly manually
compute $15.88. The answer lies in some anomalies of the GA table.
Consider the top taxable income of the 5% bracket, $135. The
withholding amount is (135-101)*5% + 2.74 = 1.70 + 2.74 = $4.44. That
should also be the amount to add in the 6% for taxable incomes over
$135. But GA adds $4.42 instead. There are other similar anomalies in
the Single Weekly table.

The following formula computes exactly the same withholding amount as
the Single Weekly table in the cited GA document.

=max(0, 1%*gaTaxableIncome, 2%*(gaTaxableIncome-14.50)+0.14,
3%*(gaTaxableIncome-43.50)+0.72, 4%*(gaTaxableIncome-72)+1.59,
5%*(gaTaxableIncome-101)+2.74, 6%*(gaTaxableIncome-135)+4.42)

Alternatively, combining constants:

=max(0, 1%*gaTaxableIncome, 2%*gaTaxableIncome-0.15,
3%*gaTaxableIncome-0.585, 4%*gaTaxableIncome-1.29,
5%*gaTaxableIncome-2.31, 6%*gaTaxableIncome-3.68)

It had never occurred to me that marginal rate tables might have such
anomalies. I might switch to this latter paradigm myself. (I have
been using my other formula for a very long time!)

On the other hand, you said that your figure ($15.88) differed by "a
few pennies" from your employer's figure. If the employer computed
$15.90, perhaps he is using a formula similar to my first one.

I am going to a company picnic in a few and when I get back I will look
over this more. Looks like good stuff, thank you.

However something seems to be wrong. I worked out I believe $15.88 for
gaTaxable income. My check states $15.91. Using your formula however I
get $18.558. (18.56) Perhaps I am doing something wrong? I just renamed
the cell I was using for federal gross income to gaTaxableincome.

I am glad that the publication wasn't clear to you either. I was
hoping it wasn't just me. Also, I apoligize for giving insufficient
information, good thing you are intuitive!


joeu2004@hotmail.com wrote:
> Jason.Alden.Benoit@gmail.com wrote:
> > I figured out my GA state taxes by hand
> > [....]
> > from this publication.
> > http://www.etax.dor.ga.gov/taxguide/...xGuide2005.pdf
> >
> > I am single with no (zero) allowances.
>
> And inferentially, it appears that you are paid weekly.
>
> > I figured out my GA state taxes by hand and I could come within a few
> > pennies if I used the federal taxable wages, and I was over if I used
> > just my gross pay.
> > For example on one check I had $ 388.80, with 370.30 taxable
> > federally.
>
> Let me clarify and correct the terminology in my first response in this
> thread.
>
> What I called "gross income" should be called "taxable gross pay (or
> compensation)".
> I am guessing that $370.30 is your federal "taxable gross" pay. That
> is your gross pay less qualified federal pretax deductions, e.g.
> employee contributions to a 401(k) or other employer pension plan and
> to pretax accounts such as medical and commuter benefits. In theory,
> your state's "taxable gross" pay may or may not be the same as the
> federal taxable gross pay. That is the state's choice to make.
> Apparently for GA, it is the same, based on your observation and based
> on page 18 of the cited GA document ("Are contributions to qualified
> ... plans taxable?"). I believe that is usually the case.
>
> What I call "taxable income" is also called "wages subject to
> withholding". Compare the definition of "taxable income" on page 8
> with the example on page 40 of the cited GA document.
>
> > If you however, can figure a formula without taking to much of your
> > time, it would greatly be appreciated.
>
> Not a problem. However, the GA instructions are poor and unclear on a
> few points. The following is my interpretation. Use at your own risk.
>
> I assume that you are looking at pages 41-43 of the cited GA document.
> The GA Percentage Method is similar to the Federal Percentage Method.
> You could use the formulation in my first response as a paradigm. By
> the way, if you do not understand that formulation and you want an
> explanation of the limits and the percentages in the formula, let me
> know.
>
> Looking at Form G-4, GA's equivalent to the Federal W-4, you declare
> personal allowances (0-1 for single; 0-2 for others), dependent
> allowances and additional allowances. I lump the latter two together
> as "other allowances".
>
> Thus, the gaTaxableIncome for Single Weekly can be computed by:
>
> =gaTaxableGross - 44.25 - PersonalAllowances*51.92 -
> OtherAllowances*57.50
>
> Ostensibly, the gaWithholding might be computed by:
>
> =1%*gaTaxableIncome + 1%*max(0,gaTaxableIncome-14.50) +
> 1%*max(0,gaTaxableIncome-43.50) + 1%*max(0,gaTaxableIncome-72) +
> 1%*max(0,gaTaxableIncome-101) + 1%*max(0,gaTaxableIncome-135)
>
> I put that in a general form that works for all marginal tax tables.
> But you might notice that 1% is a common factor in GA's case. So the
> above can be simplified as follows:
>
> =1%*(gaTaxableIncome + max(0,gaTaxableIncome-14.50) +
> max(0,gaTaxableIncome-43.50) + max(0,gaTaxableIncome-72) +
> max(0,gaTaxableIncome-101) + max(0,gaTaxableIncome-135))
>
> However, that computes a tax of $15.90, whereas you correctly manually
> compute $15.88. The answer lies in some anomalies of the GA table.
> Consider the top taxable income of the 5% bracket, $135. The
> withholding amount is (135-101)*5% + 2.74 = 1.70 + 2.74 = $4.44. That
> should also be the amount to add in the 6% for taxable incomes over
> $135. But GA adds $4.42 instead. There are other similar anomalies in
> the Single Weekly table.
>
> The following formula computes exactly the same withholding amount as
> the Single Weekly table in the cited GA document.
>
> =max(0, 1%*gaTaxableIncome, 2%*(gaTaxableIncome-14.50)+0.14,
> 3%*(gaTaxableIncome-43.50)+0.72, 4%*(gaTaxableIncome-72)+1.59,
> 5%*(gaTaxableIncome-101)+2.74, 6%*(gaTaxableIncome-135)+4.42)
>
> Alternatively, combining constants:
>
> =max(0, 1%*gaTaxableIncome, 2%*gaTaxableIncome-0.15,
> 3%*gaTaxableIncome-0.585, 4%*gaTaxableIncome-1.29,
> 5%*gaTaxableIncome-2.31, 6%*gaTaxableIncome-3.68)
>
> It had never occurred to me that marginal rate tables might have such
> anomalies. I might switch to this latter paradigm myself. (I have
> been using my other formula for a very long time!)
>
> On the other hand, you said that your figure ($15.88) differed by "a
> few pennies" from your employer's figure. If the employer computed
> $15.90, perhaps he is using a formula similar to my first one.

Jason.Alden.Benoit@gmail.com wrote:
> However something seems to be wrong. I worked out I believe $15.88 for
> gaTaxable income. My check states $15.91. Using your formula however I
> get $18.558. (18.56) Perhaps I am doing something wrong? I just renamed
> the cell I was using for federal gross income to gaTaxableincome.

That was your mistake. Your federal gross income is 370.30. The
gaTaxableIncome is 326.05 = 370.30 - 44.25, subtracting the standard
deduction for single weekly.

> I am glad that the publication wasn't clear to you either. I was
> hoping it wasn't just me.

I only quibble with a few points. I already mentioned one: the fact
that the amount added in one bracket is not always the same as the
amount computed for the top end of the previous bracket, even if we
allow for rounding differences. Some other quibbles (page numbers
refer to the GA document that you cited previously):

1. I am not sure I understand the intent or significance of Step 5 on
page 40, to wit: "If zero exemption is claimed, subtract the standard
deduction only". What does "zero exemption" mean? (Rhetorical.)
There is exemption from withholding; but in that case, there is no need
to subtract anything since there is no withholdoing to compute. There
is zero allowances ("exemption" is a common misnomer); but in that
case, Step 5 is just a simplification of Steps 1-3. My interpretation
is the latter.

2. Both Step 3 and Table E on page 40 refer to (only) "dependent
allowances", which GA Form G-4 (withholding certificate) distinguishes
from "additional allowances". Are we to ***-u-me that you exclude
"additional allowances" from the computation? (Rhetorical.) That
would defeat the purpose of declaring additional allowances. My
interpretation is that "dependent allowances" includes "additional
allowances" for this purpose. That interpretation is bolstered by the
footnote to the Example on page 40, to wit (emphasis added): "Skip
Step 2(b) if employee does not claim children or __additional__
allowances", where Step 2b is "Less Dependent Allowance".

3. In Tables F-H on pages 41-43, the low end of each bracket is labeled
"At Least" instead of "Over". If they really mean "At Least", that
would be ambiguous with the label of the high end of the previous
bracket, namely "But Not Over". For example, if taxable income for
Single Weekly is $135, should the withholding be $4.44 = 2.74 +
5%*(135-101) or $4.42 = 4.42 + 6%*(135-135)? (Rhetorical.) For most
"percentage method" tables that I've seen, it is a moot point because
there is no difference. But for GA, there is a difference, albeit
insignificant. In any case, my interpretation is that the low-end
title should be treated as "Over", just like similar federal tables.

> Also, I apoligize for giving insufficient
> information, good thing you are intuitive!

I do not recall commenting on "insufficient" information. On the
contrary, I think your information was more than sufficient. Normally
I avoid providing personal financial details in public postings. If I
must show a complete numerical example, I will choose "hypothetical"
numbers (labeled as such).