Home | Log in | Join Now! | Blog | Contact    Subscribe to the InvestEd Forum feed (new threads) via RSS
InvestEd :: Wealth Education for Australian Investors

Notices

Tax brackets/rate - Excel Function

 
LinkBack Thread Tools
Old 29-10-2007, 10:57 AM   #1
Member
 
Posts: 44
Join Date: Jul 2007
Location: Perth, WA
Tax brackets/rate - Excel Function

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.
Property WA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 11:06 AM   #2
Member
 
MichaelWhyte's Avatar
 
Posts: 798
Join Date: Oct 2005
Location: Sydney, NSW
Quote:
Originally Posted by Property WA View Post
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.
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
__________________
Goal: Financial independence by 2015
Plan: Focussing on Resi property development in the short term to build in equity and improve cash flow.
Status: Development site procured and DA approval achieved. Intend to commence construction mid-2009.
MichaelWhyte is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 11:17 AM   #3
Member
 
Posts: 44
Join Date: Jul 2007
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.
Property WA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 11:31 AM   #4
Member
 
MichaelWhyte's Avatar
 
Posts: 798
Join Date: Oct 2005
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.
__________________
Goal: Financial independence by 2015
Plan: Focussing on Resi property development in the short term to build in equity and improve cash flow.
Status: Development site procured and DA approval achieved. Intend to commence construction mid-2009.
MichaelWhyte is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 12:09 PM   #5
Member
 
Posts: 44
Join Date: Jul 2007
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.
Property WA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 12:15 PM   #6
Member
 
Posts: 44
Join Date: Jul 2007
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.
Property WA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 29-10-2007, 12:50 PM   #7
Member
 
MichaelWhyte's Avatar
 
Posts: 798
Join Date: Oct 2005
Location: Sydney, NSW
Quote:
Originally Posted by Property WA View Post
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.
No worries. If you can load it up I'll see if I can clean up the references for you.

Cheers,
Michael.
__________________
Goal: Financial independence by 2015
Plan: Focussing on Resi property development in the short term to build in equity and improve cash flow.
Status: Development site procured and DA approval achieved. Intend to commence construction mid-2009.
MichaelWhyte is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 30-10-2007, 07:25 AM   #8
Member
 
Posts: 9
Join Date: May 2007
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 Sim; 30-10-2007 at 08:20 AM. Reason: 2007-08 tax rates
Bandy is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 30-10-2007, 09:25 AM   #9
Member
 
Posts: 44
Join Date: Jul 2007
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!!
Property WA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Old 30-10-2007, 09:40 AM   #10
Member
 
Posts: 617
Join Date: Feb 2007
Location: Sydney, NSW
make sure you include the medicare surcharge too though...

also the low incomes rebate if your lucky enough to qualify...
DaveA is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Taxes Tropo Off Topic 1 28-02-2009 08:13 AM
Hong kong Tax evisional Accounting, Tax and Legal 0 22-10-2007 12:17 PM
SA Land tax loophole gone NickM Accounting, Tax and Legal 2 11-07-2007 02:11 PM
Business profits Sim Accounting, Tax and Legal 7 08-11-2005 10:18 PM


All times are GMT +10. The time now is 11:33 AM.

Copyright © 2006 Investor Education Pty Ltd (ACN 114 677 226)
Site by Hampel Group