Functions for weekly net pay, single, 1 witholding, no state tax?

[TheSooz]

I know that my witholding allowance is: $291.67
Income tax withholding is: $302.95 plus 28%
SS Tax percent w/held: 6.2%
Medicare Tax w/held: 1.45%.

I do not pay State Taxes, and have no additional witholdings. I've built my
spreadsheet to calculate my pay-per-period, and can get to the Gross Pay
total, but am not Excel friendly enough to figure out how to use functions to
calculate the above withholdings in order to determine my take-home pay.
I've used the online paycheck calculators, which are accurate, but they don't
tell me what formulas/functions were used. Help please!

 

[joeu2004]

I know that my witholding allowance is: $291.67
Income tax withholding is: $302.95 plus 28%
SS Tax percent w/held: 6.2%
Medicare Tax w/held: 1.45%.
[....]
I've built my spreadsheet to calculate my pay-per-period, and
can get to the Gross Pay total, but am not Excel friendly enough
to figure out how to use functions to calculate the above withholdings

Okay, so let's keep it simple. The following is not robust, but it
might suit your purposes.

First, you need one other value: the wage bracket offset. Apparently
your filing status is single, and your weekly pay is $1533 to 3202.
So the wage bracket offset is $1533.

Also, you have an inconsistency: $291.67 is the exemption for 1
allowance for a __monthly__ paycheckl. But as noted, your wage
bracket information suggests that you are paid __weekly__. So your
exemption must be a multiple of $67.31, based on the number of
allowances (0, 1, 2, etc).

Suppose that A1 is the gross wages subject to withholding and FICA,
and A2 is the number of allowances (0, 1, 2, etc). Then:

Income Tax: =round((A1 - A2*31.67 - 1533)*28% + 302.95, 2)
Soc Sec Tax: =round(A1*6.2%, 2)
Medicare Tax: =round(A1*1.45%, 2)

Caveats:

1. Soc Sec tax is limited to 6.2% of $102,000 for the year (2008). So
the Soc Sec for the current paycheck should be computed as follows,
where A3 contains the sum of previous gross pay:

=round(min(A1, max(0, 102000 - A3))*6.2%, 2)

2. Under some circumstances, the amount of gross wages subject to FICA
(Soc Sec and Medicare Tax) differs from the amount of gross wages
subject to income tax withholding. Look at your paycheck to see if
that is the case for you.


3. Employers are permitted to round income tax withholding
differently. See IRS Pub 15 for details.


----- original posting -----

 

[joeu2004]

Errata....

Income Tax:     =round((A1 - A2*31.67 - 1533)*28% + 302.95, 2)

That assumes that your gross wage less exemption -- that is, A1 -
A2*31.67 -- is between $1533 and $3202. To play it safer, you could
write:

=if(A1 - A2*31.76 < 1533, NA(), round((A1 - A2*31.67 - 1533)*28% +
302.95, 2))

If you get the value #N/A, you might want to post back for further
assistance.

Of course, that still assumes that your gross wage (less exemption) is
less than $3202. But that was part of your original assumptions,
because you said your marginal rate is 28%.