Gross taxation formula

S

Scoober

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%
 
H

HaSt2307

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
 
R

Ron Rosenfeld

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%
Click to expand...

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
 
S

Scoober

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
 
S

Scoober

Thanks Ron,

I understand what you have discribed to do, but have no understanding of
setting up a tax table, sorry?

Cheers Scott
 
R

Ron Rosenfeld

Thanks Ron,

I understand what you have discribed to do, but have no understanding of
setting up a tax table, sorry?

Cheers Scott
Click to expand...

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
Click to expand...

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
 
H

HaSt2307

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"))))
Click to expand...
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
 
S

Scoober

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
 
B

Bill Kuunders

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
 
S

Scoober

Bill Kuunders said:
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
Click to expand...
 
S

Scoober

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
 
B

Bill Kuunders

You're welcom
good luck.
Bill K
NZ
Scoober said:
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
Click to expand...
 
H

HaSt2307

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with MAX / MIN formula (I think) 13
Help with a formula 2
Which Tax Tables to use? 13
A formula for sliding scale percentage?????? 4
formula using date format 1

Top