Join our investing community

Tax brackets/rate - Excel Function

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

  1. Property WA

    Property WA Active 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.

    Thanks in advance.
     
  2. MichaelWhyte

    MichaelWhyte Well-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 WA

    Property WA Active 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. MichaelWhyte

    MichaelWhyte Well-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 WA

    Property WA Active 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 WA

    Property WA Active 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. MichaelWhyte

    MichaelWhyte Well-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. Bandy

    Bandy Member

    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 WA

    Property WA Active 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. DaveA

    DaveA Well-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. matthawke

    matthawke New Member

    Joined:
    6th Mar, 2009
    Posts:
    1
    Location:
    Adelaide, SA
    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. sydneyshan

    sydneyshan New 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. thinker

    thinker New 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. RoelV

    RoelV New 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. alexgsz

    alexgsz New 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)