Formula needed to calculate Australian tax on f'cast income

[pedro-egoli]

Would be grateful for a formula to assist me calculate australian tax on forecast incomes.

Tax rates in Australia are
$1 to $6000 Nil
$6001 to $21600 17cents for each $1 over $6000
$21601 to $52000 $2652 + 30 cents for each $1 over $21,600
$52001 to $62500 $11772 +42 cents for each $1 over $52000
$62501 and over $16182 + 47cents for each $1 over $62500

My attempt produced the following and I mention that I have a Named Range (Income) in cell B1 which a friend did for me and left me to my own devices to do the formula.

=IF(Income<6000,0,IF(Income<21,600,(income-6000)*0.17,IF(Income<52000,2652+(income-21601)*0.3,IF(INCOME<62500,11772+(income-52001)*.42,IF(INCOME>62501,16182+(INCOME>62501*.47))))

When I try this I get a message "Your formula is mising a parenthesis--)or(.check the formula and then add the parenthesis in the appropriate place."
For guidance the tax on $30000 is $5172 and for $50000 is $11172.
Any assistance will be greatly appreciated.

Pedro

 

[pedro-egoli]

Fairwinds,

The reason I needed a formula to calculate tax was because the spreadsheet I am using deals with what we know in Australia as a Self Managed Allocated Pension.
Drawings from this Allocated Pension change from year to year ( depend on an age factor), and also depend on earnings of fund , and end of previous year balancel
Accordingly the tax will also change as the drawings change.
These drawings are then subject to a deductible amount and the net figure is INcome (cells i8:i44) used to calculate the tax.
In my spreadsheet I have forecast an earning rate and combined this with the relative drawings .
INcome figure also attracts a tax offset and so the INcome figures in i8:i44 are used in two columns to give me an overall Net Income figure, which is what I was chasing.
The formula I am using works and later on will try the one you suggested.

One question is what would be the benefit of your suggested formula over the one I am presently using.
And thanks again for your ready assistance.

Pedro

 

[fairwinds]

I did not question why you needed the formula, just why you could not refere to the cells directly. :wink:

The main advantage with my formula is that it gives the correct result
(but I guess you changed yours to do that to)

Else it is just shorter, more visible and easier to change/update.


You should use what you feel comfortable with.

 

[pedro-egoli]

Fairwinds,
Thanks for that. I have tried your amended formula and it works .

Would appreciate it if you could now look at this thread , as I am after another formula (or 2) to make the worksheet user friendly regardless of one's age.
http://www.mrexcel.com/board2/viewtopic.php?t=146882&highlight=

I tried to download Colo's HTML maker without success (message I needed an "Object?) to display the sheet, so will need someone to try and follow what I am trying to do.

Perhaps when you read it you might pose any questions you need to clarify matters.

Thanks again
Pedro