Excel 2007 : Nested "IF" statements to calculate marginal tax rates

[Martin Snerl]

I am trying use “IF” statements 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; the formula

For a single taxpayer, the marginal tax rates and the associated income amounts are:
10% up to $8,350
(e.g., income tax for income of $5,000 is $500)

15% up to $33,950
(e.g., income tax for income of $10,000 is $8,350*10% + $1,650*15% = $747.50)

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(D36>8350,D36<=D31),SUM(D36/10,D36/6.666))

[DonkeyOte]

Perhaps

Please Login or Register  to view this content.

EDIT: incidentally your sample for 10k is incorrect I think - should be 1082.50

[sweep]

Hi,

How about this

=D36*INDEX({0.1,0.15,0.25,0.28,0.33,0.35},MATCH(D36,{8350,33950,171550,82250,372950},1))

[rwgrietveld]

Dave (Sweep),

My results are different than yours. Please review. You need at least a zero (0) value for the match(,,1) o you'll end up with #NA for values < 8350.

I kept a table

=SUM(IF(A1:A6<A8,($A$8-A1:A6)*B1:B6))

Where
A8 is the Amount
A1:A6 are the amounts 0, 8350,33950, 82250, 171550, 372950
B1:B6 are the amounts 0.1, 0.05, 0.1, 0.03, 0.05, 0.02

SCE required as it is an array function

[DonkeyOte]

Ricardo, for the sake of end-user robustness I'd probably combine the two approaches that is to say persist with SUMPRODUCT (no CSE entry) but use a Table for ease of modification
(ie so as to avoid having to modify inline array)

Converting my earlier SUMPRODUCT utilising your ranges:

Please Login or Register  to view this content.

That being said - if I were to keep a table I would probably hold the actual % in one Column and store the (calculated) incremental % difference in another for sake of transparency...

[Martin Snerl]

Thank you "DonkyeOte" and Dave for your suggestions. I have copied them into my excel file and will be reviewing them.
Martin

[DonkeyOte]

Just for clarity...

If the calculation is tiered then as inferred by Ricardo, Dave's INDEX solution won't work and if not tiered neither my SUMPRODUCT nor Ricardo's SUM Array would work.