Taxation Formula
Posted by Steve T on January 04, 2002 7:03 PM
Does anyone know the excel VLOOKUP FORMULA to enter into my worksheet for 'sliding scale taxation' to return the correct income tax amount.
So when the gross amount is entered, the formula looks up the tax table
ie. A1:B6 (Tax Table)
ROW A being -Income $
ROW B being -Tax Percent %(according to the ATO)
The formula needs to work out how much income is by refering to (for arguments sake C1) and then finding the correct amount in the table and then workout the appropriate tax(not to hard using VLOOKUP) , but cause its a sliding scale it then needs to subtract the next threshold and work the tax out on that amount and add that tax amount tothe preceeding amount and so on ...a little confusing but maybe by the grace of god someone can help me
Posted by Tom Dickinson on January 04, 2002 9:59 PM
An alternate to what you asked for
If this is anything like my state tas, you may want to just write a formula that has more than 1 IF/THEN statements. As an example, lets say the tax was:
5% of the first 10K, 6% of the next 10K, and 7% of the rest. The following would work:
=if(A1>10000,500,A1*0.05) + if(A1>10000,if(A1>20000,600,(A1-10000)*.06),0) + if(A1>20000,(A1-20000)*.07,0)
Posted by Carl B on January 04, 2002 10:15 PM
I maybe going in the wrong direction here. Would a nested If statement work?
=IF(C1 < 2650, B1, IF(C1 < 28700, B2, IF(C1 < 62200, B3, IF(C1 < 138400, B4, IF(C1 < 299000, B5, B6)))))
In Addition you could substitute the B cell references with the actual Taxation formula.
Posted by Steve T on January 04, 2002 10:33 PM
Re: An alternate to what you asked for
Thanks heaps guys both formulas work, I was on the vlookup trail purely cause ive seen done that before, thanks again
Posted by Dan on January 04, 2002 10:35 PM
I think I know where your going, but it's usually easier when you can add examples when you post your question...
Let's say you have in column A:
0
25000
35000
45000
55000
65000
And in B:
0%
15%
23%
28%
33%
36%
So that if you make less than 25000, you are taxed 0%. If you make 27,000 then 2,000 is taxed at 15% and 25000 is at 0%. And so on...Is that what you mean? If so, try this alternative:
In cell c1 type this formula:
=IF($D$1-A1>0,$D$1-A1-SUM(C2:$C$6),0)
Copy this formula down from C1 to C5.
in cell C6 use the shortened formula:
=IF($D$1-A6>0,$D$1-A6,0)
In Cell D1, type in whatever income $ amount you are looking at.
In Cell E1, use this formula to calculate tax amount:
=SUMPRODUCT(B1:B6,C1:C6)
You can hide column C if you want to.
I'm sure there's more than one way to solve this, including using some sort of vlookup function, but I'm betting either way you are going to need a separate column to break down the income level in to those different tax brackets. Otherwise a VBA macro would also do the trick nicely. I'm heading off to bed now. If you still need help you can email me.
HTH
Posted by Dan on January 04, 2002 10:38 PM
An advantage to my solution is that your tax tables are not hard coded in to your formula, so that if you ever need to change your income level and taxation levels, you just do so in A1:B6 and you shouldn't have to worry about rewriting your formulas.
Posted by Steve T on January 05, 2002 1:42 PM
Thanks Dan this seems the best way so far... i tried using the if statements but it went on for ages.
