Formula needed to calculate Australian tax on f'cast income

P

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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.

Code: 
=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
 
Sort by date Sort by votes
pedro-egoli,

Try

=IF(income<6000,0,IF(income<21600,(income-6000)*0.17,IF(income<52000,2652+(income-21601)*0.3,IF(income<62500,11772+(income-52001)*0.42,IF(income>62501,16182+(income>62501*0.47)))) ))

It was missing two ) at the end and there was an extra , in 21600.
 
Upvote 0
Thanks for that Kieran,
It worked perfectly.
I see you are an aussie so I hope you can put this to good use.
Regards
Pedro
 
Upvote 0
Hi,

As far as I can see, the formula suggested by kieran gives the wrong result.


Try:

=SUMPRODUCT(--(income>{6000,21600,52000,62500}),income-{6000,21600,52000,62500},{0.17,0.13,0.12,0.05})

instead.
 
Upvote 0
Fairwinds and Kieran,

Yes that formula was incorrect when higher incomes were involved. Have corrected the formula which now reads
=IF(INcome<6000,0,IF(INcome<21600,(INcome-6000)*0.17,IF(INcome<52000,2652+(INcome-21601)*0.3,IF(INcome<62500,11772+(INcome-52001)*0.42,IF(INcome>62501,16182+(INcome-62501)*0.47)))) )

The error was in the final part which now reads
16182+(INcome-62501)*0.47)))) ) whereas the original one had 16182+(INcome>62501*0.47))))) ) (not 100% sure about the number of closing brackets in original , but the main problem was the > rather than - )

Unfortunately got an #VALUE Error message when I put the alternative from Fairwinds in my spreadsheet.

Thanks to you both for your interest and help.

Pedro
 
Upvote 0
pedro-egoli

Sorry but I still do not think your formula is entirely OK. :-?

Try mine again as I think it is simpler.
I see no reason why it should give you an error.
Book2
ABCDE
162501My formulaYour Formula
216182.47FALSE
3
Sheet1
 
Upvote 0
Fairwinds,
This is a real puzzle.

I input this formula into a cell ( which is one of the Named Range for INcome) in my spreadsheet

=SUMPRODUCT(--(INcome>{6000,21600,52000,62500}),INcome-{6000,21600,52000,62500},{0.17,0.13,0.12,0.05})

which I copied from your latest post, and still got the #VALUE error.

I also changed the value in same cell to $62501 with my formula and also got the FALSE answer.
Whereas when I undid the change and reverted to the original amount I got the correct answer ..

If Kieran reads this it would be interesting to see whether he can get both to work or not.
Try this figure for INcome $66,802 and see if you get the same tax as me of $18,204.


It seems that the named range may have something to do with my not being able to get yours to work but I am a raw amateur with this aspect so am not able to see why.

Regards
Pedro
 
Upvote 0
Yes, I get 18203.94

I take it INcome referes to a single cell, right?

Try using a normal cell reference just to check.
 
Upvote 0
Fairwinds,

NAmed refers to cells i8:i44.

When I used your formula and replaced NAmed with a cell reference it worked perfectly.

In my previous post I said
I input this formula into a cell ( which is one of the Named Range for INcome) in my spreadsheet
Click to expand...
which was incorrect. In fact the formula was placed in cell K8 ( ADJACENT to i8 which is the first cell in the NAmed range.

I look forward to your further comments.

Pedro
 
Upvote 0
The formula cannot handle a named range as it tries to evaluate the entire range.

What is the point in using the name as the formula is only to refere to a single cell?

In order to get a single cell out of the named range you need to use e.g.

=SUMPRODUCT(--(INDEX(INcome,1)>{6000,21600,52000,62500}),INDEX(INcome,1)-{6000,21600,52000,62500},{0.17,0.13,0.12,0.05})

But what's the point in that?
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top