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