Home 
Search 
Today's Posts 
#21




Help with tax formula for marginal tax rates
PS....
I wrote: "מיכאל (מיקי) אבידן" <mickya*at*tapuz.co.il wrote previously: http://img85.imageshack.us/img85/6707/nonameqh.png Which now looks suspiciously like one of the solutions that I presented in this thread two days ago, complete with relying on a row with 0%. Actually, your table is different in design: it is limited to incomes of less than $10 million, whereas mine is not. Not a big limitation, admittedly; but an unnecessary one. There is nothing wrong with Bob's approach [...] it is indeed the most efficient formulation, I believe. I meant the most efficient formulation presented so far in this thread. Some years ago, I was posting a formula using MAX. I suspect it is more efficient, although I am not taking the time to test the theory. I still use the MAX formula sometimes for quickanddirty implementations. But I have adopted a tabledriven design for responses because I believe it is less errorprone and easier to maintain (change from year to year). It is certainly easier to remember ;).  original message  "Joe User" <joeu2004 wrote in message ... I've been watching this part of the discussion, and it reminded me of dialog between Dumb and Dumber (before Bob jumped in). I held my tongue because everyone is entitled to post their opinions, no matter how misguided they might. "מיכאל (מיקי) אבידן" <mickya*at*tapuz.co.il wrote: Well..., for a salary of $ 50,000: My formula returns: $ 8,687.50 and yours: $ 8,132.50 You are correct: $8687.50 is the right answer. But you don't need to throw out the baby with the bath water. You might have simply noted that Bob had written 8950 and 39500 instead of 8350 and 33950. "מיכאל (מיקי) אבידן" <mickya*at*tapuz.co.il wrote previously: By using your suggestion in a different approach all mistakes and "extra work" that can be eliminated. http://img85.imageshack.us/img85/6707/nonameqh.png Which now looks suspiciously like one of the solutions that I presented in this thread two days ago, complete with relying on a row with 0%. Bob wrote: I was responding to your you where you showed a formula using INDIRECT, which is awful You (Micky) fail to acknowledge the point that Bob is trying to educate you about, namely: INDIRECT is a volatile function, which carries a huge cost in terms of worksheet preformance. Your first solution at http://img691.imageshack.us/img691/622/nonamecz.png was indeed unduly cumbersome and complicated. Micky wrote previously: with your permission I have ONE important comment. It is not a good idea to type all the various "values of steps" WITHIN the formula. There is nothing wrong with Bob's approach. For my money, it is errorprone, and it contains "simplifications" that deserved explanation; but it is indeed the most efficient formulation, I believe. I can see why it might have been a complete mystery to you, given that you did not even know how US marginal tax rate tables work just 2 days ago. I, for one, am getting sick and tired of your misguided sermonizing. It reflects badly on you more than on the people you try to criticize. But if you are going to berate people publicly, you should be a mensh and admit your own mistakes publicly and upfront. And do follow your own advice, misguided as it might have been in the original context, namely: ``I would dare to say that 3040% of the replies are Superfluous. I think it will be a good idea that every supporter will adopt the "habit" of pressing [F5] BEFORE(!) replying.`` [From: "מיכאל (מיקי) אבידן" <mickya*at*tapuz.co.il, Newsgroups: microsoft.public.excel.misc, Sent: Thursday, December 31, 2009 10:35 AM, Subject: excel decimal rounding down all numbers.]  original message  "מיכאל (מיקי) אבידן" <mickya*at*tapuz.co.il wrote in message ... Well..., for a salary of $ 50,000: My formula returns: $ 8,687.50 and yours: $ 8,132.50 Would you be so kind to calculate the Income Tax step by step...? Micky "Bob Phillips" wrote: Micky, I agree that if the formula is used on many people a table would be the way to go, on a single item, debatable. However I just offered a solution, and it worked. I was responding to your you where you showed a formula using INDIRECT, which is awful, and should be avoided at all costs IMO. My solution worked, no INDIRECT, in one cell, not cumbersome ... as I said. Typos? I tested it and it seemed fine to me.  HTH Bob Phillips "????? (????) ?????" <mickya*at*tapuz.co.il wrote in message ... Bob, If I may  this was one of the "brainstorming" thread I participated in during the last couple of weeks. Your formula is, as you stated, "Not large, not cumbersome, and all in one cell"  however, with your permission I have ONE important comment. It is not a good idea to type all the various "values of steps" WITHIN the formula. If you'll take a close look  it seems that you have had more than one TYPO in what you presented. In addition  whenever one needs to change one, or more, values  he would have to edit the formula and copy it along the Salaries. By using your suggestion in a different approach all mistakes and "extra work" that can be eliminated. http://img85.imageshack.us/img85/6707/nonameqh.png Thank you, Micky "Bob Phillips" wrote: Which is exactly what my formula gives, not large, not cumbersome, and all in one cell.  HTH Bob Phillips "rzink" wrote in message ... MZ, I interpreted your question a bit dirrently than Stefi. Coming from an accounting background, I understand your question to be, "what is the income tax based on the taxable income entered into D36?" First of all, you are correct in your title asking for the "marginal" income tax, but this is a bit more complex than simply multiplying your taxable income by the tax bracket. For example: If a person has $50K of taxable income, the tax rate on the first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840), and the rate on the ramaining $16,051 is 25% ($4,013). If you add up the individual tax calculation, the total tax is $8,688 or an overall tax rate of 17.4%. This can be done with a series of calculations set up as a table, but you will end up with a very large and cumbersome formula if you try to put it all in one cell. rzink "MZ" wrote: I am trying to make a single formula that will calculate a person's taxable income assuming the six different marginal tax rates that range from 10% to 35%, as income increases For a single taxpayer, the marginal tax rates and the associated income amounts a 10% up to $8,350 15% up to $33,950 25% up to $82,250 28% up to $171,550 33% up to $372,950 35% over $372,950 I am using "IF" statements to test for each marginal tax rate. I began the formula as follows, but it returns a value error. Cell D36 is the taxpayer's income =IF(D36<=8350,D36/10),IF(AND(D368350,D36<=D31),SUM(D36/10,D36/6.666)) Also, is the only way to multiply by a percentage to make a fraction with the denominator the value of the percentage  e.g. 6.666 is 15%? Thank you  MZ . . 
#22




Help with tax formula for marginal tax rates
I'm wondering what an active dispute is in process while the OP didn't
respond at all!  Regards! Stefi „MZ” ezt *rta: I am trying to make a single formula that will calculate a person's taxable income assuming the six different marginal tax rates that range from 10% to 35%, as income increases For a single taxpayer, the marginal tax rates and the associated income amounts a 10% up to $8,350 15% up to $33,950 25% up to $82,250 28% up to $171,550 33% up to $372,950 35% over $372,950 I am using "IF" statements to test for each marginal tax rate. I began the formula as follows, but it returns a value error. Cell D36 is the taxpayer's income =IF(D36<=8350,D36/10),IF(AND(D368350,D36<=D31),SUM(D36/10,D36/6.666)) Also, is the only way to multiply by a percentage to make a fraction with the denominator the value of the percentage  e.g. 6.666 is 15%? Thank you  MZ 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Marginal Abatement Cost  Creating a column chart  Charts and Charting in Excel  
I need a formula to calculate rates based on current age  Excel Worksheet Functions  
"How do enter formula for multiple labour rates when calculatin p.  Excel Discussion (Misc queries)  
Income Tax Payable from table with escalating marginal percentage rates.  Excel Worksheet Functions  
Excel formula for monthly interest rates  Excel Worksheet Functions 