Formaul to calculate annual tax rate

[koog]

Hi Excel experts,

I'm trying to come up with a simple formula which will help me calculate the annual tax amount by simply entering annual gross pay in an workbook cell. The 2010 Australian tax personal income tax rates are listed in the table below. A 1.5% medicare levy is also added to your tax on gross income

For e.g. if your gross income for the year is $60,000
Tax on gross income = Tax amount up to $37,000 ($4,650) + tax amount @ higher tax bracket of 30c per $1(60000-37000)*0.30 + Medicare levy 1.5% of gross income.

Therefore
Tax on gross income = $4,650 + $6,899.70 + (60,000*0.015)
$12,449.70




<TABLE style="WIDTH: 538pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=716 border=0><COLGROUP><COL style="WIDTH: 281pt; mso-width-source: userset; mso-width-alt: 13677" width=374><COL style="WIDTH: 257pt; mso-width-source: userset; mso-width-alt: 12507" width=342><TBODY><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 28.5pt; BACKGROUND-COLOR: white" width=374 height=38>Taxable income</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>Tax on this income</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" width=374 height=24>0 – $6,000</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>Nil</TD></TR><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: white" width=374 height=25>$6,001 – $37,000</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>15c for each $1 over $6,000</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" width=374 height=24>$37,001 – $80,000</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>$4,650 plus 30c for each $1 over $37,000</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=374 height=27>$80,001 – $180,000</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>$17,550 plus 37c for each $1 over $80,000</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 281pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=374 height=27>$180,001 and over</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 257pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=342>$54,550 plus 45c for each $1 over $180,000</TD></TR></TBODY></TABLE>

It would be great if you can let me know the best way to calculate annual tax amount using excel.

Cheers

 

[diddi]

you need to go to the ato and download document number NAT1004

make sure it is for the current financial year. its a pdf

http://ato.gov.au/businesses/pathway.asp?mnu=&mfp=001/003&pc=001/003/024/005/006&st=

its schedule 1

 

[koog]

Thanks diddi, I'm trying to learn how to use if and other Excel commands to come up with the formula....I can work out the annual tax amount without using Excel, but if I learn how to use Excel to calculate the answer, it could be applied for similar problems...Regards...Koog

 

[diddi]

did you look at the pdf? it gives all the formulae that one uses to calculate the tax. its not the lookup table

 

[brylemartin12]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Thanks for sharing that formula on how to calculate annual tax rate. Apparently, the fiscal cliff is rapidly nearing as Republicans and Democrats slug it out on Capitol Hill. Democrats say no slashes to entitlement spending, and the GOP is declining to budge on raising taxes. However, a glance at tax rates around the globe shows that Americans have it relatively well, as tax troubles go.