Tax brackets/rate - Excel Function

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

Join Australia's most dynamic and respected property investment community
  1. Property WA

    Property WA Active Member

    Joined:
    1st Jul, 2015
    Posts:
    36
    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. MichaelW

    MichaelW Well-Known Member

    Joined:
    25th Jun, 2015
    Posts:
    839
    Location:
    Brisbane
    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:
    1st Jul, 2015
    Posts:
    36
    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. MichaelW

    MichaelW Well-Known Member

    Joined:
    25th Jun, 2015
    Posts:
    839
    Location:
    Brisbane
    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:
    1st Jul, 2015
    Posts:
    36
    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:
    1st Jul, 2015
    Posts:
    36
    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. MichaelW

    MichaelW Well-Known Member

    Joined:
    25th Jun, 2015
    Posts:
    839
    Location:
    Brisbane
    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:
    1st Jul, 2015
    Posts:
    6
    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:
    1st Jul, 2015
    Posts:
    36
    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:
    1st Jul, 2015
    Posts:
    580
    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:
    1st Jul, 2015
    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:
    1st Jul, 2015
    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:
    1st Jul, 2015
    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:
    1st Jul, 2015
    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. Jaro

    Jaro New Member

    Joined:
    19th Mar, 2016
    Posts:
    1
    Location:
    Melb
    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)
     

Build Passive Income WITHOUT Dropping $15K On Buyers Agents Each Time! Helping People Achieve PASSIVE INCOME Using Our Unique Data-Driven System, So You Can Confidently Buy Top 5% Growth & Cashflow Property, Anywhere In Australia