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(A1B8,A1<=C8),((A1-E8)*F8),IF(AND(A1B9,A1<C9),((A1-E9)*F9),IF(AND(A1B10,A1<C10),((A1-E10)*F10),IF(AND(A1B11,A1<C11),((A1-E11)*F11),IF(AND(A1B12,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

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'.

" 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(A1B8,A1<=C8),((A1-E8)*F8),IF(AND(A1B9,A1<C9),((A1-E9)*F9),IF(AND(A1B10,A1<C10),((A1-E10)*F10),IF(AND(A1B11,A1<C11),((A1-E11)*F11),IF(AND(A1B12,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

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.

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!


wrote:
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.

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 ass-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).