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
|