Excel 2007 : if statement

[whitepaw]

Can someone help me with the following please. I want to calculate the net income per annum for any income amount based on the following tax rates. $0 to $19,999 is taxed at 18%, $20,000 to $34,999 is tax at 27%, $35,000 to $59,000 is taxed at 35% and $60,000+ is tax at 40%.

[Paul]

Hello Whitepaw,

You don't need an IF formula here; a LOOKUP formula should do the trick though. Assuming net income per annum is calculated as:

GROSS INCOME * (1 - TAXRATE) then try:

=A1*(1-LOOKUP(A1,{0,20000,35000,60000},{0.18,0.27,0.35,0.4}))

Where A1 holds the gross income. Hope that helps!

[whitepaw]

Hi, thanks for this answer - it is close but not quite correct. The income is taxed progressively, thus someone earning $70,000 is taxed 18% on the first $20,000, 27% on the next $15,000, 35% on the next $25,000 & 40% on the last $10,000.

Finally I would like to also be able to diveide the annual net figure by 12 to show monthly net. Hope this is not too difficult. Cheers

[DonkeyOte]

You can use a SUMPRODUCT

=SUMPRODUCT(--(A1>{0,20000,35000,60000}),A1-{0,20000,35000,60000},{0.18,0.09,0.08,0.05})

Where A1 = Income value

Note the Tax % bands are incrementally increased (ie rate 2 is 9% higher than rate 1)

[whitepaw]

Thank you Donkeyote, your answer is great but give a negative result. I have tried to amend to get a positive result but can't seem to. My formula is as follows:

=SUMPRODUCT(-(A1>{0,38000,60000}),A1-{0,38000,60000},{0.805,-0.135,-0.06}/12)

This is a little different from my original info however correct for my own purposes and also gives the monthly net income figure after tax deducted at correct rates here in NZ. Cheers for your help so far. Whitepaw

[whitepaw]

Please ignore my last post - I have figured it out. I adjusted the tax rates from positive to negative and vice versa. Thanks again for your help - this forum is great as I have been trying to deal with this issue for years.