Join 3,372 readers in helping fund MetaFilter (Hide)

Tags:

excel income tax calc help
October 13, 2012 6:00 PM   Subscribe

Excel help: I'm trying to input a dollar value into a cell and have the output be the federal income tax for that amount (just using the brackets). Federal income tax is calculated such that the first x dollars are taxed at rate R, the next y dollars are taxed at rate S, and up through several different rate brackets. I can do this calculation with a simple IF statement using multiple cells by summing the result of 5 separate bracket calculations. I'm not sure if I can cram it into one giant formula. Suggestions?

alternatively, is there any way to ask excel to put value x is cell c, and then to output the result of cell d? eg, if cell c = value in cell x, return value of cell d that would result? (assuming changing cell c affects value of cell d).
posted by leotrotsky to Computers & Internet (4 answers total) 1 user marked this as a favorite
 
Here's one solution: http://www.mcgimpsey.com/excel/variablerate.html
posted by beyond_pink at 6:10 PM on October 13, 2012 [4 favorites]


thanks!
posted by leotrotsky at 8:03 PM on October 13, 2012


You can do it, but you have to include the time period as a factor, since the brackets are based on yearly earnings. The employers guide to wages and withholding (or something like that) has the right brackets and percentages.

You can do a nested if statement, but it gets tricky getting it right in Excel. You can do your method of different calculations for each bracket, but you still have to be careful. It would be best to use if statements there too.

The logic is this (made up numbers):

if INCOME > $350000 then tax is $50000 + 0.38 * (INCOME - $350000)
else if INCOME > $125000 then tax is $12000 + 0.25 * (INCOME - $125000)
else if INCOME > $60000 then tax is $5000 + 0.15 * (INCOME - $60000)
else if INCOME > $12000 then tax is 0.1 * (INCOME - $12000)
else tax is 0

posted by gjc at 8:04 PM on October 13, 2012 [2 favorites]


I have done it, and found that it was a little more instructive to have five (or so) different rows, one for each marginal rate. Each row has an IF statement so that it displays a value only if greater than 0.
posted by yclipse at 1:56 PM on October 14, 2012


« Older I need information on the long...   |  Does any science support alter... Newer »
This thread is closed to new comments.