|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
May 25th, 2010, 01:52 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Income Tax
Hi All,
Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober 
|
|
#2
May 25th, 2010, 04:52 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
Thanks Demi,
This formula appears to tax the entire income at the rate the income fits into instead of taxing the income on the tiers as it passes through them. e.g. on the 80,000.00 income the formula would have to work out the following The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% + the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%. as i see it the formula you sent to me taxes the whole 80,000 at 38% which is not accurate. Do you have a formula that completes the above example? -- Thanks in advance. Scoober "demi" wrote: =IF(E3014000,0.125,IF(E3048000,0.21,IF(E3070000 ,0.33,0.38)))*E30 "Scoober" дÈëÓʼþ ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober .
|
|
#3
May 25th, 2010, 07:44 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
=IF(E30=14000,E30*0.125,IF(E30=48000,(E30-14000)*0.21+1750,IF(E30=70000,(E30-48000)*0.33+8890,(E30-70000)*0.38+16150)))
Vijay "Scoober" wrote: Thanks Demi, This formula appears to tax the entire income at the rate the income fits into instead of taxing the income on the tiers as it passes through them. e.g. on the 80,000.00 income the formula would have to work out the following The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% + the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%. as i see it the formula you sent to me taxes the whole 80,000 at 38% which is not accurate. Do you have a formula that completes the above example? -- Thanks in advance. Scoober "demi" wrote: =IF(E3014000,0.125,IF(E3048000,0.21,IF(E3070000 ,0.33,0.38)))*E30 "Scoober" дÈëÓʼþ ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober .
|
|
#4
May 25th, 2010, 08:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
Hi Vijay,
When i tried your formula i got the message #Value! I cannot work out why - do you have any thoughts? -- Thanks in advance. Scoober "Vijay" wrote: =IF(E30=14000,E30*0.125,IF(E30=48000,(E30-14000)*0.21+1750,IF(E30=70000,(E30-48000)*0.33+8890,(E30-70000)*0.38+16150))) Vijay "Scoober" wrote: Thanks Demi, This formula appears to tax the entire income at the rate the income fits into instead of taxing the income on the tiers as it passes through them. e.g. on the 80,000.00 income the formula would have to work out the following The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% + the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%. as i see it the formula you sent to me taxes the whole 80,000 at 38% which is not accurate. Do you have a formula that completes the above example? -- Thanks in advance. Scoober "demi" wrote: =IF(E3014000,0.125,IF(E3048000,0.21,IF(E3070000 ,0.33,0.38)))*E30 "Scoober" дÈëÓʼþ ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober .
|
|
#5
May 25th, 2010, 08:52 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
The content of your E30 must be text, not a number. Check with =ISTEXT(E30)
and =ISNUMBER(E30). -- David Biddulph "Scoober" wrote in message ... Hi Vijay, When i tried your formula i got the message #Value! I cannot work out why - do you have any thoughts? -- Thanks in advance. Scoober "Vijay" wrote: =IF(E30=14000,E30*0.125,IF(E30=48000,(E30-14000)*0.21+1750,IF(E30=70000,(E30-48000)*0.33+8890,(E30-70000)*0.38+16150))) Vijay "Scoober" wrote: Thanks Demi, This formula appears to tax the entire income at the rate the income fits into instead of taxing the income on the tiers as it passes through them. e.g. on the 80,000.00 income the formula would have to work out the following The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% + the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%. as i see it the formula you sent to me taxes the whole 80,000 at 38% which is not accurate. Do you have a formula that completes the above example? -- Thanks in advance. Scoober "demi" wrote: =IF(E3014000,0.125,IF(E3048000,0.21,IF(E3070000 ,0.33,0.38)))*E30 "Scoober" дʼ ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober .
|
|
#6
May 25th, 2010, 10:13 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
Hi,
You may refer to this article - http://ashishmathur.com/articles.aspx. Scroll down to B 1 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Scoober" wrote in message ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober
|
|
#7
May 25th, 2010, 11:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
On Mon, 24 May 2010 17:52:01 -0700, Scoober
wrote: Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! Easiest, in my opinion, to both understand and maintain, is to use an lookup table. Set up a table someplace on your worksheet with the tax tiers, amount of tax on each tier, and the marginal amount. I used the range J2:L5 The table will look like: Tier Amount Rate $ 0.00 $ 0.00 12.5% $14,000.00 $ 1,750.00 21.0% $48,000.00 $ 8,890.00 33.0% $70,000.00 $16,150.00 38.0% or, showing the formula in the second column: Tier Amount Rate 0 0 0.125 14000 =K2+(J3-J2)*L2 0.21 48000 =K3+(J4-J3)*L3 0.33 70000 =K4+(J5-J4)*L4 0.38 I then defined the Name: TaxTbl to refer to this range (J2:L5) With your income in A1, the tax is given by the formula: =VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3) As you can see, you can easily modify and/or extend the table if/when tax rates change. --ron
|
|
#8
May 25th, 2010, 09:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
Hi Ron,
I am a beginner so not so bright.  I have c&P'd $ 0.00 $ 0.00 12.5% $14,000.00 $ 1,750.00 21.0% $48,000.00 $ 8,890.00 33.0% $70,000.00 $16,150.00 38.0% to sheet 3 and Defined it TaxTbl I have then pasted =VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3) In J30. I get the answer #N/A What step have i missed? Scoober "Ron Rosenfeld" wrote: On Mon, 24 May 2010 17:52:01 -0700, Scoober wrote: Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! Easiest, in my opinion, to both understand and maintain, is to use an lookup table. Set up a table someplace on your worksheet with the tax tiers, amount of tax on each tier, and the marginal amount. I used the range J2:L5 The table will look like: Tier Amount Rate $ 0.00 $ 0.00 12.5% $14,000.00 $ 1,750.00 21.0% $48,000.00 $ 8,890.00 33.0% $70,000.00 $16,150.00 38.0% or, showing the formula in the second column: Tier Amount Rate 0 0 0.125 14000 =K2+(J3-J2)*L2 0.21 48000 =K3+(J4-J3)*L3 0.33 70000 =K4+(J5-J4)*L4 0.38 I then defined the Name: TaxTbl to refer to this range (J2:L5) With your income in A1, the tax is given by the formula: =VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3) As you can see, you can easily modify and/or extend the table if/when tax rates change. --ron .
|
|
#9
May 25th, 2010, 09:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
My guess is that your problem is the same as I suggested was the problem
with one of your other formulae. I think you've got text instead of numbers. As I said earlier, check this with the ISTEXT and ISNUMBER functions. Come back to us when you've done that. -- David Biddulph "Scoober" wrote in message ... Hi Ron, I am a beginner so not so bright. I have c&P'd $ 0.00 $ 0.00 12.5% $14,000.00 $ 1,750.00 21.0% $48,000.00 $ 8,890.00 33.0% $70,000.00 $16,150.00 38.0% to sheet 3 and Defined it TaxTbl I have then pasted =VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3) In J30. I get the answer #N/A What step have i missed? Scoober "Ron Rosenfeld" wrote: On Mon, 24 May 2010 17:52:01 -0700, Scoober wrote: Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! Easiest, in my opinion, to both understand and maintain, is to use an lookup table. Set up a table someplace on your worksheet with the tax tiers, amount of tax on each tier, and the marginal amount. I used the range J2:L5 The table will look like: Tier Amount Rate $ 0.00 $ 0.00 12.5% $14,000.00 $ 1,750.00 21.0% $48,000.00 $ 8,890.00 33.0% $70,000.00 $16,150.00 38.0% or, showing the formula in the second column: Tier Amount Rate 0 0 0.125 14000 =K2+(J3-J2)*L2 0.21 48000 =K3+(J4-J3)*L3 0.33 70000 =K4+(J5-J4)*L4 0.38 I then defined the Name: TaxTbl to refer to this range (J2:L5) With your income in A1, the tax is given by the formula: =VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3) As you can see, you can easily modify and/or extend the table if/when tax rates change. --ron .
|
|
#10
May 25th, 2010, 09:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Income Tax
Hi David,
Thank you for your help. As i am working out tax rates i am using numbers. When you say =ISTEXT(E30) and =ISNUMBER(E30) Do i have to add this to Vijays formula - if so how would it look? -- Thanks in advance. Scoober "David Biddulph" wrote: The content of your E30 must be text, not a number. Check with =ISTEXT(E30) and =ISNUMBER(E30). -- David Biddulph "Scoober" wrote in message ... Hi Vijay, When i tried your formula i got the message #Value! I cannot work out why - do you have any thoughts? -- Thanks in advance. Scoober "Vijay" wrote: =IF(E30=14000,E30*0.125,IF(E30=48000,(E30-14000)*0.21+1750,IF(E30=70000,(E30-48000)*0.33+8890,(E30-70000)*0.38+16150))) Vijay "Scoober" wrote: Thanks Demi, This formula appears to tax the entire income at the rate the income fits into instead of taxing the income on the tiers as it passes through them. e.g. on the 80,000.00 income the formula would have to work out the following The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% + the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%. as i see it the formula you sent to me taxes the whole 80,000 at 38% which is not accurate. Do you have a formula that completes the above example? -- Thanks in advance. Scoober "demi" wrote: =IF(E3014000,0.125,IF(E3048000,0.21,IF(E3070000 ,0.33,0.38)))*E30 "Scoober" дÈëÓʼþ ... Hi All, Can someone come up with a formula on how to work out how much tax is paid on a person's income. Tax rates are as follows $0 - $14,000 = 12.5% $14,000 - $48,000 = 21% $48,000 - $70,000 = 33% $70,000+ = 38% E30 = Income J30 = Target Cell I have no idea how to attack this so can even offer a formula that does not work!! -- Thanks in advance. Scoober . .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
