Federal Tax Calculation

[ash3154]

=MAX(0, 10%*(CD7 - $M$17*134.62 - 102), (15%*(1445 - (1*134.62) - 396))+29.4,
(25%*(CD7 - ($M$17*134.62) - 1306))+165.9, (28%*(CD7 - $M$17*134.62 -
3066))+605.9, (33%*(CD7 - $M$17*134.62 -6404))+1540.54, (35%*(CD7 -
$M$17*134.62 - 13833))+3992)

Gross amt =1440 (CD7)
One withholding allowance =134.62 filing single ($M$17)
Limits are:
Less than 102 --- $0.00 tax
102 to 396 --- 10% * (gross-withholding-102)
396 to 1306 --- 15% * (gross-withholding-396)+29.40
1306 to 3066 --- 25% on (gross-withholding-1306)+165.90
3066 to 6404 --- 28% on (gross-withholding-3066)+605.90
6404 to 13833 --- 33% on (gross-withholding-6404)+1540.54
greater than 13833 --- 35% * (gross-withholding-13833)+3992


For some reason this formula picks up 25% portion.

Based on the IRS publication it should be at the 15%? What am I doing wrong

 

[MartinW]

Maybe try a different approach. Take a look here.
http://www.mcgimpsey.com/excel/variablerate.html

HTH
Martin

 

[joeu2004]

=MAX(0, 10%*(CD7 - $M$17*134.62 - 102), (15%*(1445 - (1*134.62) - 396))+29.4,
(25%*(CD7 - ($M$17*134.62) - 1306))+165.9, (28%*(CD7 - $M$17*134.62 -
3066))+605.9, (33%*(CD7 - $M$17*134.62 -6404))+1540.54, (35%*(CD7 -
$M$17*134.62 - 13833))+3992)
[....]
For some reason this formula picks up 25% portion.
Based on the IRS publication it should be at the 15%?
What am I doing wrong

First, you are talking about federal withholding for biweekly
payment. The term "federal tax" usually refers to the 1040 tax rate
schedule.

Second, you have some obvious typos and other anomalies, no doubt left
over from your development efforts. In the 15% term, "1445" should be
CD7 (which you say is 1440), and "1*" should be "$M$17*".

Looks to me that the 1445 typo is the root cause of the problem.

Note that 1440 - 134.62 is 1305.38, very near the top of the 15%
bracket. Your corrected formula computes withholding of 165.81, which
is very close to the highest withholding for the 15% bracket
(165.90). With your typo (1445 instead of 1440), the computed
withholding is 166.56. I presume that is what you mean "pick[ing] up
[a] 25% portion".

HTH.


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

 

[joeu2004]

PS....

Looks to me that the 1445 typo is the root cause of the problem.

I might also note that you should round the final amount at least to
the penny. Other rounding options are permitted, per IRS Pub 15, as
you may know.

Don't rely on Excel's formatting to display the rounded amount. The
underlying value still has higher precision. That might throw off
dependent calculations, if any.

 

[Stan Brown]

Thu, 7 Feb 2008 21:09:00 -0800 from ash3154 <ash3154
@discussions.microsoft.com>:

=MAX(0, 10%*(CD7 - $M$17*134.62 - 102), (15%*(1445 - (1*134.62) - 396))+29.4,
(25%*(CD7 - ($M$17*134.62) - 1306))+165.9, (28%*(CD7 - $M$17*134.62 -
3066))+605.9, (33%*(CD7 - $M$17*134.62 -6404))+1540.54, (35%*(CD7 -
$M$17*134.62 - 13833))+3992)

For some reason this formula picks up 25% portion.

Based on the IRS publication it should be at the 15%? What am I doing wrong

Your formula is, frankly, too complicated for me. But I would never
think to implement these sorts of calculations in any way but by a
VLOOKUP table. The great benefit of doing it that way is that the
table (which changes yearly) is separated from the formula (which
should never change). Define the table as a name and use the name in
the formula, and then you never change the formula even if the table
grows by a row.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?