SHOPPING CART0 item(s)$0.00 AUD + GST

2012/13 Excel Income Tax Calculator

04 June, 2012

If you're interested in how to calculate tax tiers in Excel, how to model the impact of changes in tax rates and brackets - or heck, if you just want to know how the tax tier changes affect your pay packet - take a look at the latest Excel Income Tax Calculator from Plum Solutions. 

This calculator has always been our number one most downloaded item on the site and I'm not sure if  it's because you're all so interested in financial modelling tax tier calculations, or you just want to know how much of your salary goes in tax!  Calculating an amount based on tiering tables is a pretty complex formula, and is commonly used in lots of different financial models, especially pricing models.

I've made some improvements to the tax calculator this year to previous versions.  Without getting into the politics of carbon tax etc and the complexities of the flood and medicare levies, basically the Australian Government has carefully adjusted the bracket to benefit those earning between $20k and $80k per annum.  So this was an obvious opportunity to show how a data table in Excel can easily show how sensitive the result of a formula (in this case, tax payable) can be to changes in input numbers (taxable income).  And of course sensitivity analysis is never complete without a chart!  If you want to know more about how to build a data table and use it in a financial model, this is covered in the Advanced Financial Modelling course which is regularly run as a workshop or an online course

Download the 2012/13 Excel Income Tax Calculator

This calculator contains some hidden columns so if you want to break it open and take it apart to see how it all works, please get in touch for the password.

Comments

 

This is great - it's always a pain trying to refactor previous Excel spreadsheets for the new tax brackets and allowances. Thanks for putting it all together.

Can you please give me some more information on your Advanced Financial Modelling course? I finished my CFA program a while ago, but I still feel there is a lot to learn with multi-variable financial modelling, especially with new startups where you don't necessarily have the previous profit & loss docs or balance sheets.

I'd rather learn the proper way to do these things in Excel like you've managed here, than get a pre-made macro that does the job but doesn't teach me anything.

 

Submitted by Emma Tameside (not verified) on Sat, 04/08/2012 - 21:06.

Hi Emma,

Sure, the Advanced Financial Modelling course teaches you how to create financial models like this one.  It run as a face to face workshop, or it also available online if you can't make it to one of our locations.  Hope to see you on a course soon!

Danielle.

Submitted by danielle on Sat, 04/08/2012 - 21:22.

Hi Danielle,

Why your tax calculations are different to other online tax calculators? It's like $1k difference. The savings between the FY2012 and FY2013 is the same but the end result is different. Which one suppose to be correct?

Submitted by lee.martin on Thu, 30/08/2012 - 16:35.

Hi Lee, Our calculator assumes you put in the after super amount. It also does not take into account any medicare levy, flood levy, tax deductions or other allowances which other calculators, such as the one from the ATO does. This model has been deliberately kept simple to demonstrate the use of a tiering tax table, and data tables for the purpose of sensitivity analysis (for comparing the impact of changing tax brackets) in financial modelling in Excel. So in answer to your question, if you need an exact amount the other online calculators will be more accurate.

Submitted by danielle on Sat, 01/09/2012 - 19:43.

Hi Lee. I think there's a small error in your spreadsheet. Your lookup table calculates the total tax from the preceding bracket and keeps a running total as the brackets increase. That makes sense. But your calculation of the amount to be taxed is a little bit wrong. For instance, you say the taxable amount in the $37,001 to $80,000 bracket is ($80,000 - 37,001), which is $42,999. This is then multiplied by the tax rate for that bracket which is 32.5%. I think you should be multiplying the tax rate by $43,000, not $42,999. Think about a tax bracket of 11 to 15 dollars. The difference is 4, but there are actually 5 dollars being taxed - dollars 11, 12, 13, 14 and 15. In the next bracket 16 to 20, dollars 16, 17, 18, 19 and 20 will be taxed. Adding them together, we tax 10 dollars in the two brackets. Anyway, now I know how a Vlookup can be used to look up the tax table. You saved me hours of work, and now I can put the lookup tables into my own spreadsheets. Regards James from Canberra

Submitted by Anonymous on Fri, 05/10/2012 - 17:08.

Thanks James!

Submitted by danielle on Mon, 15/10/2012 - 11:23.

Thanks for making me aware about the Financial Modelling course. Your stuff will be so helpful for me because I'm also going to join this course soon.

Submitted by Anonymous on Sat, 11/05/2013 - 18:38.

Thanks for letting us know about 2012/13 Excel Income Tax Calculator and whatever information you put here seems to me really sound wonderful and I'm very much willing to know more about it. Thanks Tax attorney

Submitted by Anonymous on Tue, 13/08/2013 - 14:05.

For an update for the 2014/15 tax year, including the new debt levy then see the 2015 Deficit Levy Tax Calculator: http://www.plumsolutions.com.au/articles/deficit-levy-tax-calculator

Submitted by Anonymous on Fri, 04/07/2014 - 14:27.

Post new comment

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.