| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Gross taxation formula
Can someone show me the correct forumla to provide a net mothly figure
e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
for the tax calculation
=SUMPRODUCT(--(A1{0;17500;40000;75000}),(A1-{0;17500;40000;75000}), {0.125;0.085;0.12;0.06}) for the net amount =A1-A2 for an explanation of the sumproduct formula see......... http://www.mcgimpsey.com/excel/variablerate.html -- Greetings from New Zealand "Scoober" wrote in message ... Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Scoober,
For a little more complicated way than has been posted here is the algebra behind what you are asking for: 12.5% - .125x where x is the dollars less than 17500 21% - is the max tax 17500 and below plus the tax on the amount over 17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x 33% - is the max tax 17500 and below plus the max tax for 17501-40000 times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) = -6287.50 + .33x 39% - is max tax of the three previous bands + .39x. So .125(17500) + ..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x =IF(AND($A$1 I2,$A$1<J2),$A$1*$K2, IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3, IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4, IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match")))) A1 = amount entered I J K L 1 Low High Tax Dif Rate 2 0 17500 12.50% 0.125 3 17501 40000 21% -1487.50 4 40001 75000 33% -6287.50 5 75001 10^10 39% -10787.50 Big Num L2 .125 L3 =K2*J2+K3*-J2 L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3) L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4) Regards Harry Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
On Fri, 5 Dec 2008 17:01:00 -0800, Scoober
wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% The easy way is to set up a tax table. Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 Then: A1: Yearly Wage A2: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) A3: =(A1-A2)/12 --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Thanks for your help harry,
Excuse my ignorance but can you tell me which code to post in which box useing my A1 A2 A3 example? Cheers Scott "HaSt2307" wrote: Scoober, For a little more complicated way than has been posted here is the algebra behind what you are asking for: 12.5% - .125x where x is the dollars less than 17500 21% - is the max tax 17500 and below plus the tax on the amount over 17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x 33% - is the max tax 17500 and below plus the max tax for 17501-40000 times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) = -6287.50 + .33x 39% - is max tax of the three previous bands + .39x. So .125(17500) + ..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x =IF(AND($A$1 I2,$A$1<J2),$A$1*$K2, IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3, IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4, IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match")))) A1 = amount entered I J K L 1 Low High Tax Dif Rate 2 0 17500 12.50% 0.125 3 17501 40000 21% -1487.50 4 40001 75000 33% -6287.50 5 75001 10^10 39% -10787.50 Big Num L2 .125 L3 =K2*J2+K3*-J2 L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3) L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4) Regards Harry Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Thanks Ron,
I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott "Ron Rosenfeld" wrote: On Fri, 5 Dec 2008 17:01:00 -0800, Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% The easy way is to set up a tax table. Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 Then: A1: Yearly Wage A2: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) A3: =(A1-A2)/12 --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
On Sat, 6 Dec 2008 21:11:01 -0800, Scoober
wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Scoober
A1 = gross yearly wage A2 = IF(AND($A$1 $I$2,$A$1<$J$2),$A$1*$K$2,IF(AND($A$1 $I$3,$A$1<$J$3),($A$1*$K$3)+$M$3,IF(AND($A$1 $I$4,$A$1<$J$4),($A$1*$K$4)+$M$4,IF(AND($A$1 $I$5,$A$1<$J$5),($A$1*$K$5)+$M$5,"No Match")))) A3 = A1-B1 You will still need the data column I, J, K and L. I uploaded a sample file at http://freefilehosting.net/download/42jj4 Regards Harry Scoober wrote: Thanks for your help harry, Excuse my ignorance but can you tell me which code to post in which box useing my A1 A2 A3 example? Cheers Scott "HaSt2307" wrote: Scoober, For a little more complicated way than has been posted here is the algebra behind what you are asking for: 12.5% - .125x where x is the dollars less than 17500 21% - is the max tax 17500 and below plus the tax on the amount over 17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x 33% - is the max tax 17500 and below plus the max tax for 17501-40000 times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) = -6287.50 + .33x 39% - is max tax of the three previous bands + .39x. So .125(17500) + ..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x =IF(AND($A$1 I2,$A$1<J2),$A$1*$K2, IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3, IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4, IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match")))) A1 = amount entered I J K L 1 Low High Tax Dif Rate 2 0 17500 12.50% 0.125 3 17501 40000 21% -1487.50 4 40001 75000 33% -6287.50 5 75001 10^10 39% -10787.50 Big Num L2 .125 L3 =K2*J2+K3*-J2 L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3) L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4) Regards Harry Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Thanks Harry,
As this formula is only a small part of a much bigger spreadsheet how do I make the I,J,K, and L workings invisable so they work behind the scenes. I have space for two applicants on the spread sheet: Appl 1: Gross figure=k43 Tax paid = N43 and net income = Q43 Appl 2: Gross figure=k44 Tax paid = N44 and net income = Q44 Is there a way i can show you the spreadsheet instead of clumsily trying to explain what i am trying to acheive. I have Skype if that helps.Skype address: Scoober1 Cheers Scott "HaSt2307" wrote: Scoober A1 = gross yearly wage A2 = IF(AND($A$1 $I$2,$A$1<$J$2),$A$1*$K$2,IF(AND($A$1 $I$3,$A$1<$J$3),($A$1*$K$3)+$M$3,IF(AND($A$1 $I$4,$A$1<$J$4),($A$1*$K$4)+$M$4,IF(AND($A$1 $I$5,$A$1<$J$5),($A$1*$K$5)+$M$5,"No Match")))) A3 = A1-B1 You will still need the data column I, J, K and L. I uploaded a sample file at http://freefilehosting.net/download/42jj4 Regards Harry Scoober wrote: Thanks for your help harry, Excuse my ignorance but can you tell me which code to post in which box useing my A1 A2 A3 example? Cheers Scott "HaSt2307" wrote: Scoober, For a little more complicated way than has been posted here is the algebra behind what you are asking for: 12.5% - .125x where x is the dollars less than 17500 21% - is the max tax 17500 and below plus the tax on the amount over 17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x 33% - is the max tax 17500 and below plus the max tax for 17501-40000 times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) = -6287.50 + .33x 39% - is max tax of the three previous bands + .39x. So .125(17500) + ..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x =IF(AND($A$1 I2,$A$1<J2),$A$1*$K2, IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3, IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4, IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match")))) A1 = amount entered I J K L 1 Low High Tax Dif Rate 2 0 17500 12.50% 0.125 3 17501 40000 21% -1487.50 4 40001 75000 33% -6287.50 5 75001 10^10 39% -10787.50 Big Num L2 .125 L3 =K2*J2+K3*-J2 L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3) L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4) Regards Harry Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
I have a huge respect for Harry and Ron. They help a lot of people
understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ "Ron Rosenfeld" wrote in message ... On Sat, 6 Dec 2008 21:11:01 -0800, Scoober wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
"Bill Kuunders" wrote: I have a huge respect for Harry and Ron. They help a lot of people understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ "Ron Rosenfeld" wrote in message ... On Sat, 6 Dec 2008 21:11:01 -0800, Scoober wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
I didn't see any answer............................
in your reply. (:(: Bill K NZ "Scoober" wrote in message ... "Bill Kuunders" wrote: I have a huge respect for Harry and Ron. They help a lot of people understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ "Ron Rosenfeld" wrote in message ... On Sat, 6 Dec 2008 21:11:01 -0800, Scoober wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Hmmmmmmmmmmm........... I don't know what happened there?
I have used the forumula John supplied and compared it to some known bank calculators, as there was a slight difference in results I followed up on a number of options (hence the posts). Since then I have noted that results from the same gross figure entered is different in all the bank calculators I have used. So I am a little more relaxed about the eventual outcome. Once again thank you for all your help. Cheers Scott "Bill Kuunders" wrote: I didn't see any answer............................ in your reply. (:(: Bill K NZ "Scoober" wrote in message ... "Bill Kuunders" wrote: I have a huge respect for Harry and Ron. They help a lot of people understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ "Ron Rosenfeld" wrote in message ... On Sat, 6 Dec 2008 21:11:01 -0800, Scoober wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
You're welcom
good luck. Bill K NZ "Scoober" wrote in message ... Hmmmmmmmmmmm........... I don't know what happened there? I have used the forumula John supplied and compared it to some known bank calculators, as there was a slight difference in results I followed up on a number of options (hence the posts). Since then I have noted that results from the same gross figure entered is different in all the bank calculators I have used. So I am a little more relaxed about the eventual outcome. Once again thank you for all your help. Cheers Scott "Bill Kuunders" wrote: I didn't see any answer............................ in your reply. (:(: Bill K NZ "Scoober" wrote in message ... "Bill Kuunders" wrote: I have a huge respect for Harry and Ron. They help a lot of people understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ "Ron Rosenfeld" wrote in message ... On Sat, 6 Dec 2008 21:11:01 -0800, Scoober wrote: Thanks Ron, I understand what you have discribed to do, but have no understanding of setting up a tax table, sorry? Cheers Scott Sorry, I thought it would be obvious. The table contains the values from which the tax is computed. You "set it up" by entering those values in a range of cells in the manner in which I posted. You could have pasted it directly into your spreadsheet. To be more specific: Set up this table and NAME it TaxTbl (or use the absolute address reference): $ 0 0.00 12.5% $17,500 $2,187.50 21% $40,000 $6,912.50 33% $75,000 $18,462.50 39% The middle column is the cumulative tax paid on the amount in the first column, so can be given by a formula: Assume table is in H1:J4 I1: 0 I2: =J1*(H2-H1)+I1 and fill down to I4 So you would make the following entries: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: 2187.5 J2: 21.0% H3: $40,000 I3: 6912.5 J3: 33.0% H4: $75,000 I4: 18462.5 J4: 39.0% OR, you could use formulas in column I: H1: $0 I1: $0 J1: 12.5% H2: $17,500 I2: =J1*(H2-H1)+I1 J2: 21.0% H3: $40,000 I3: =J2*(H3-H2)+I2 J3: 33.0% H4: $75,000 I4: =J3*(H4-H3)+I3 J4: 39.0% OR, you could just copy/paste the table I posted in my initial response to you. With regard to the formula in A2, you could use either the one I posted previously: =VLOOKUP(A1,TaxTbl,2)+ (A1-VLOOKUP(A1,TaxTbl,1))* VLOOKUP(A1,TaxTbl,3) Or, if you can't figure out how to NAME a range in Excel by using HELP, you can substitute the actual cell references: =VLOOKUP(A1,H1:J4,2)+ (A1-VLOOKUP(A1,H1:J4,1))* VLOOKUP(A1,H1:J4,3) If you put your Tax Table someplace else, you will need to adjust the range references to reflect that new location. --ron |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Scoober,
If you can create a second worksheet in your current workbook, then just move all the I through L to that sheet and adjust the references in the formula in A2 to point to that sheet. Sorry no skype. You can upload a sample file to freefilehosting.net Regards Harry Scoober wrote: Thanks Harry, As this formula is only a small part of a much bigger spreadsheet how do I make the I,J,K, and L workings invisable so they work behind the scenes. I have space for two applicants on the spread sheet: Appl 1: Gross figure=k43 Tax paid = N43 and net income = Q43 Appl 2: Gross figure=k44 Tax paid = N44 and net income = Q44 Is there a way i can show you the spreadsheet instead of clumsily trying to explain what i am trying to acheive. I have Skype if that helps.Skype address: Scoober1 Cheers Scott "HaSt2307" wrote: Scoober A1 = gross yearly wage A2 = IF(AND($A$1 $I$2,$A$1<$J$2),$A$1*$K$2,IF(AND($A$1 $I$3,$A$1<$J$3),($A$1*$K$3)+$M$3,IF(AND($A$1 $I$4,$A$1<$J$4),($A$1*$K$4)+$M$4,IF(AND($A$1 $I$5,$A$1<$J$5),($A$1*$K$5)+$M$5,"No Match")))) A3 = A1-B1 You will still need the data column I, J, K and L. I uploaded a sample file at http://freefilehosting.net/download/42jj4 Regards Harry Scoober wrote: Thanks for your help harry, Excuse my ignorance but can you tell me which code to post in which box useing my A1 A2 A3 example? Cheers Scott "HaSt2307" wrote: Scoober, For a little more complicated way than has been posted here is the algebra behind what you are asking for: 12.5% - .125x where x is the dollars less than 17500 21% - is the max tax 17500 and below plus the tax on the amount over 17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x 33% - is the max tax 17500 and below plus the max tax for 17501-40000 times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) = -6287.50 + .33x 39% - is max tax of the three previous bands + .39x. So .125(17500) + ..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x =IF(AND($A$1 I2,$A$1<J2),$A$1*$K2, IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3, IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4, IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match")))) A1 = amount entered I J K L 1 Low High Tax Dif Rate 2 0 17500 12.50% 0.125 3 17501 40000 21% -1487.50 4 40001 75000 33% -6287.50 5 75001 10^10 39% -10787.50 Big Num L2 .125 L3 =K2*J2+K3*-J2 L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3) L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4) Regards Harry Scoober wrote: Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
On Mon, 8 Dec 2008 07:47:25 +1300, "Bill Kuunders"
wrote: I have a huge respect for Harry and Ron. They help a lot of people understand the use of excel. However the formula which is explained by John? McGimpsey does do it all without any tables. I'm wondering why you haven't tried this. Did it not work? Bill K NZ Interesting, I do not see McGimpsey's contribution. By the way, Bill, using tables, especially for something like US taxes, has a huge advantage in that it can be easily modified. If the tax rates change; or if you want to make different assumptions (e.g. Single/Married/etc), it is a simple matter to either change the table, or set up a new table and refer to that. That's usually much easier that trying to edit a hard-coded formula. --ron |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gross taxation formula
Hi,
I have answered a similar question at the link mentioned below: http://www.merawindows.com/Forums/ta...s/Default.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Scoober" wrote in message ... Can someone show me the correct forumla to provide a net mothly figure e.g if a gross yearly wage figure is entered into A1 how can i show the tax paid in A2 and the resulting nett monthly figure in A3 The formula has to work within the following thresholds: 0-$17,500 taxed at 12.5% $17,501 - $40,000 taxed at 21% $40,001 - $75,000.00 taxed at 33% $75,001 and above taxed at 39% |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| gross margin | Excel Worksheet Functions | |||
| gross profit | Excel Worksheet Functions | |||
| Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) | |||
| Calculate gross pay | Excel Discussion (Misc queries) | |||
| gross profit margin formula | Excel Discussion (Misc queries) | |||
Linear Mode
