# Tax brackets/rate - Excel Function

Discussion in 'Investor Resources & Tools' started by Property WA, 29th Oct, 2007.

1. ### Property WAActive Member

Joined:
31st Jul, 2007
Posts:
44
Location:
Perth, WA
Do any of the excel whizz-bang forumites have an excel function to calculate tax using this years brackets? And that wouldn't mind me cutting and pasting just the function itself.

Doing up a spreedsheet (which is not easy given my lack of knowledge in the area) but need a way to calculate tax on an income rather than sitting there doing it manually.

Did a search but couldn't seem to find what I needed.

2. ### MichaelWhyteWell-Known Member

Joined:
5th Oct, 2005
Posts:
798
Location:
Sydney, NSW
Yep,

Here ya go. The second post on this thread by me has a spreadsheet called IP Calculator (MUH) which is the one you're after I think. You might need to change the bands at the bottom as its a bit out of date, but the formula works.

The 16th post also has a tax calculator which is much easier if you're just after tax applicable to a particular income.

Cheers,
Michael

3. ### Property WAActive Member

Joined:
31st Jul, 2007
Posts:
44
Location:
Perth, WA
Thanks Michael,

Your's is what I'm after as I'm not actually trying to calculate anything specific, just need my spreedsheet to be able to do it through use of a function.

You'll have to forgive my lack of Excel ability..but how do I extract the function from your calculator ? (the only functions I've used to date are things like "SUM(C1:C4)'' so I was looking for something like that on the page).

I'm getting better with this thing but Excel is a whole new world to me.

4. ### MichaelWhyteWell-Known Member

Joined:
5th Oct, 2005
Posts:
798
Location:
Sydney, NSW
Hi Property WA,

You'll just need to copy the table in cells F96 to I100 across to your spreadsheet then copy the formula that references this little table from cell C72 to your spreadsheet too. Modify that formula (in cell C72) to change the references to the table at calls F96 to I100 to wherever that table has been pasted in your spreadsheet. The \$ signs in the formula lock the cell references so you can drag the formulas along rows without losing reference to the table.

Make sense?

Don't bother trying to understand the formula at cell C72 as its a complicated arithmetic lodgic formula using several vlookups to that little table of cells. So long as you get the reference to that little table correct and copy the table across then the vlookups should still work in your formula. Just change the references to those cells in the formula and leave the rest alone. The formula for the "after tax" cell (C72) should be below the cell holding your pre-tax income. i.e. In this spreadsheet cell C72 shows the after tax amount based on the pre-tax amount at cell C71. So paste it below the pre-tax amount and that reference should update automatically.

Clear as mud?

Cheers,
Michael.

5. ### Property WAActive Member

Joined:
31st Jul, 2007
Posts:
44
Location:
Perth, WA
Heya Michael,

Your instructions were great - read it twice and I thought I'd followed everything you'd noted in your post...but I'm getting an '#N/A' on it.

If at some stage today you have a couple of minutes could I perhaps impose and get you to have a look at the attached?

C44 is where I'm trying to put it. (you'll notice a couple of cells such as ' New taxable income' look OK (i.e. they have figures in them) but this is because I've just manually typed in the figure and haven't used a function).

Thanks a heap if you do get some time to have a look over. It's a basic spreedsheet I'm playing with from somersoft.com.au.

P.S I've tried 4 times to upload the attachment but it's not wanting to work so I'll e-message it to you. Hope thats ok.

6. ### Property WAActive Member

Joined:
31st Jul, 2007
Posts:
44
Location:
Perth, WA
Ok - ignore that - I just realised you can't attach anything via the message function. I'll try post it up here again later.

Thanks for your help so far Michael. Really appreciate it.

7. ### MichaelWhyteWell-Known Member

Joined:
5th Oct, 2005
Posts:
798
Location:
Sydney, NSW
No worries. If you can load it up I'll see if I can clean up the references for you.

Cheers,
Michael.

8. ### BandyMember

Joined:
16th May, 2007
Posts:
9
Location:
Brisbane
This is a single cell formula for OLD TAX rates, no need for tables for VLOOKUP. I'll try and find the time to update to this year's rates or have a go yourself Change the cell reference as required to read your income, this one is D18

2007-08 Rates
=IF(D18<6001,0, IF(AND(D18>6000,D18<=30001),(D18-6000)*0.15, IF(AND(D18>30001,D18<75001),3600+(D18-30000)*0.3, IF(AND(D18>=75001,D18<150001),17100+(D18-75000)*0.4, IF(D18>=150001,47100+(D18-150000)*0.45)))))

Old Rates

=IF(D18<6001,0, IF(AND(D18>6000,D18<=21601),(D18-6000)*0.17, IF(AND(D18>21601,D18<58001),2652+(D18-21600)*0.3, IF(AND(D18>=58001,D18<70001),13572+(D18-58000)*0.42, IF(D18>=70001,18612+(D18-70000)*0.47)))))

Last edited by a moderator: 30th Oct, 2007
9. ### Property WAActive Member

Joined:
31st Jul, 2007
Posts:
44
Location:
Perth, WA
FANTASTIC!

UNREAL!!!

Bandy you're an absolute star!!!

That works perfectly. You've made my day.

Thanks to both you and Michael!!

10. ### DaveAWell-Known Member

Joined:
19th Feb, 2007
Posts:
617
Location:
Sydney, NSW
make sure you include the medicare surcharge too though...

also the low incomes rebate if your lucky enough to qualify...

11. ### matthawkeNew Member

Joined:
6th Mar, 2009
Posts:
1
Location:
I realise this is an old thread, but I found this page with a Google and it was helpful. Then I found a single line formula elsewhere which is quite useful and very easy to modify, so I thought I would post it back here.

Using the information here (McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates) and here (Individual income tax rates) I was able to create the following simple formula for the 2008-09 tax rates:

=SUMPRODUCT(--(C5>={6001;34001;80001;180001}), (C5-{6000;34000;80000;180000}), {0.15;0.15;0.10;0.05})

Change C5 to the cell containing the gross income.

Note that the last {} array contains differential tax rates, ie 0.15 for the first tax bracket, 0.15+0.15=0.30 for the second tax bracket, etc.

Essentially the SUMPRODUCT function just calculates the tax for each bracket, as the ATO has already done on their page, and adds them all up.

Cheers,

Matt

P.S. Master class: the double minus at the start of the formula converts the True/False result returned by the first expression into a 1 or a 0, so that it's multiplied out with the rest of the array numbers and differential tax rates correctly.

12. ### sydneyshanNew Member

Joined:
29th Sep, 2011
Posts:
1
Location:
Newcastle, NSW
Great formula!

Thanks, Matt! That formula is fantastic - the simplest and most powerful I've seen for calculating tax brackets. Nice!

Shan

13. ### thinkerNew Member

Joined:
18th Feb, 2012
Posts:
1
Location:
Sydney, NSW
Max & Min

Here's a formula I came up with using MAX & MIN functions :

=0.15*MAX(0,(MIN(B3,37000)-6000))
+0.3*MAX(0,(MIN(B3,80000)-37000))
+0.37*MAX(0,(MIN(B3,180000)-80000))
+0.45*MAX(0,B3-180000)

Where cell B3 has your annual income, and the tax brackets are as follows:
0% 0-6,000
15% 6,001-37,000
30% 37,000-80,000
37% 80,000-180,000
45% over 180,000

Cheers,

Wayne

14. ### RoelVNew Member

Joined:
30th May, 2013
Posts:
1
Location:
NSW
2012-2013 formula

Thank you for the great help here! I used one of the (imho clearer) formula's above and updated it for the 2012-2013 tax brackets as per Individual income tax rates

=IF(B2<=18200,0,
IF(AND(B2>18200,B2<=37000),(B2-18200)*0.19,
IF(AND(B2>37000,B2<=80000),3572+((B2-37000)*0.325),
IF(AND(B2>80000,B2<=180000),17547+((B2-75000)*0.37),
IF(B2>180000,54547+((B2-180000)*0.45))))))

Change B2 to the cell that contains the income.

Enjoy!

15. ### alexgszNew Member

Joined:
19th Mar, 2016
Posts:
1
Location:
Melbourne
I also found this thread on google. Have taken what matthawke posted and updated it to include current rates and the 2% medicare levy.
Posting incase anyone else needs (or I forget how i did it)

=(SUMPRODUCT(--(A1>={18201;37001;80001;180001}), (A1-{18200;37000;80000;180000}), {0.19;0.135;0.045;0.1}))+(A1*0.02)