Australian Tax Formula

I will start by saying that i have used the search option on the forums and spent many hours on google attempting to get the answer so i apologies if it is already on the forums.

I am need someone to correct this formula for me.

I am aiming to make an automatic tax calculation for clients to use when they are giving us an income and expenses page for the 2011 Australian Tax brackets.

This is my formula i have made but it is only showing as 'FALSE'.

=IF(B27<6001,0,0)=IF(AND(B27<37001,B27>6000),(B27-6000)*0.15,0)=IF(AND(B27<80001,B27>37000),((B27-37000)*0.3)+4650,0)=IF(AND(B27<180001,B27>80000),((B27-80000)*0.37)+17550,0)=IF(AND(B27>180000),((B27-180000)*0.45)+54550,0)

The tax bracket is as follows

Taxable income Tax on income (2011 - 2012) Tax rate
$0 – $6,000 Nil 0%
$6,001 – $37,000 15c for each $1 over $6,000 15%
$37,001 – $80,000 $4,650 plus 30c for each $1 over $37,000 30%
$80,001 – $180,000 $17,550 plus 37c for each $1 over $80,000 38%
Over $180,000 $54,550 plus 45c for each $1 over $180,000 45%

Thanks for your assistance in advance.

Hi tiafferty,

Thank for your help. i tried your suggestion and replaced all the '=' (exempt the first one) with ',' and now instead of 'FAILED' it reads #VALUE.

If you know why this is?

Thanks again

Perhaps this formula works better?

=IF(B27<=6000,0,IF(AND(B27>=6001,B27<=37000),(B27-6000)*0.15,IF(AND(B27>=37001,B27<=80000),(4650+(B27-37000)*0.3),IF(AND(B27>=80001,B27<=180000),(17550+(B27-80000)*0.38),IF(B27>180000,(54550+(B27-180000)*0.45))))))

Just one question. In the tax bracket you wrote 37 c for each dollar above 80001 but you also said 38% tax? As I never knew a tax collector to take less tax than possible I've used 38 % in the formula.

Alf

Perfect!!!

Thanks Alf, that formula worked, the tax rate was actually 37% (i must have mistyped it) but i changed the calculation for that tax bracket accordingly.

Thanks Again
Phil

Hi philjthommo

Glad to be of help. If this takes care of your problem could you please mark your post "Solved".

DonkeyOte: A most impressive solution! Very elegant and “compact”!

Alf

It just gets better and better!

Alf

Whoops, sorry. Posted here because I figured the formula would be similar, and there'd be no need to explain the issue again in detail.