Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Federal Tax Calculation

=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 a
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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 860
Default Federal Tax Calculation

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

HTH
Martin


"ash3154" wrote in message
...
=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 a
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Federal Tax Calculation

On Feb 7, 9:09 pm, ash3154 wrote:
=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 -----

On Feb 7, 9:09*pm, ash3154 wrote:
=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 a
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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Federal Tax Calculation

PS....

On Feb 7, 10:07*pm, I wrote:
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.
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 524
Default Federal Tax Calculation

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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Federal tax withholding calculations - using IF statements..? clintjjohnson Excel Worksheet Functions 13 April 3rd 23 04:16 PM
how to sum, if column name is F02 and row name is Federal Seema Excel Worksheet Functions 2 January 3rd 08 07:55 PM
Federal and Private Student Loan Consolidation Programs edfed Excel Worksheet Functions 0 May 26th 07 07:00 AM
federal tax tables Bob Excel Worksheet Functions 3 December 7th 05 01:01 AM
Formula to calculate federal income tax mikeburg Excel Discussion (Misc queries) 1 August 26th 05 05:30 PM


All times are GMT +1. The time now is 06:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"