Compute Tax?

  • Thread starter Thread starter FeDude
  • Start date Start date
F

FeDude

This must have been done before, but for the life of me I cannot figure out
how (for a cell that contains taxable income), to compute federal income
taxes from a set of rules without VBA. Has anyone done this before.

Income Is Over But Not Over This Amount Plus This % Of the Excess Over
0 14,000 0 0.1 0
14,000 56,800 1,400 0.15 14,000
56,800 114,650 7,820 0.25 56,800
114,650 174,700 22,283 0.28 114,650
174,700 311,950 39,097 0.33 174,700
 
Hi FeDue,

Your table doesn't show up too clearly for me so I may be wrong in what your
exact needs are (it might be clearer if you put commas or some other
delimter between the headings). Hopefully this will help though.

There are lots of different ways of doing this, creating a User Defined
Function is prob a good way of doing it. A simpler tho way would be to use
an IF for each section/stage of tax and then hide those columns and simply
show the sum of them all.

e.g. Income upto 14k

=if(a1>0,if(a1<=14000,sum(a1*TAXRATE1),sum(14000*TAXRATE1)),0)

This will determine if there is any income, if the income is less than or
equal to 14k then that sum is multiplied by the tax rate, otherwise if the
amount is greater than 14k then the full 14k is taxed at that rate.
(TAXRATE1 should be whatever the tax rate for that bracket is. I would
suggest that you keep all of the amounts e.g. 14k and the different tax
rates elsewhere on the spreadsheet and refer to them in the formula so that
should they change it is less work to update them all).

The next stage of the formula will be:

=if(a1>14000,if(a1<=56800,sum(sum(a1-14000)*TAXRATE2),sum(sum(56800-14000)*T
AXRATE2)),0)

If the income is greater than 14k and less than 56800 then subtract 14k from
it and multiply the excess by the tax rate. If the income is greater than
56800 then subtract 14k from 56800 and multiply the difference by the tax
rate. Once you do this for each section then hide the columns and do a sum
across them.

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk
 
posted in misc & new users. I answered in misc and my ans showed up in
newusers and here?
 
Hi,

you can scale it down to 3 columns, make a table as this

0 0.0 0.1
14,000 1,400 0.15
56,800 7,820 0.25
114,650 22,283 0.28
174,700 39,097 0.33

call the table tax or something, put the earned amount in A1
then use a formula like

=VLOOKUP(A1,Tax,2)+(A1-VLOOKUP(A1,Tax,1))*VLOOKUP(A1,Tax,3)

(to name the table, select the dimensions of the raw data, click in the
name box above the row and column header and type the name there
or do insert>name>define and type it there)
 
Hi Don, that's because it was actually crossposted to the following;-

microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.funct
ions

so replying to it in any of those groups should see that reply posted to all. It's only when they
multipost by posting it in each of the groups separately that you don't then see what others have
already responded to the note in other groups. Hence the advice line in Harlan's signature:-
Never multipost (though crossposting is usually OK).
Click to expand...
 
By the way, thanks again for pointing that out that you can now use the delete button to kill off
posts within the group itself, without having to go through the search rigmarole to do so. I've
been delete happy on all those blasted patches, paypals, make moneys etc ever since. Soooooooo
much easier than before :-)
 
Thanks for the thanks and the explanation.

Ken Wright said:
By the way, thanks again for pointing that out that you can now use the delete button to kill off
posts within the group itself, without having to go through the search rigmarole to do so. I've
been delete happy on all those blasted patches, paypals, make moneys etc ever since. Soooooooo
much easier than before :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --




microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public
..excel.worksheet.funct all. It's only when
they then see what others
have
Click to expand...
 
Thanks for some of the tips. I could not seem to find what I was looking
for via the google search.

I'm starting to think that using standard formulas looks pretty involved and
probably unmaintailable in the long run..

I wanted to try my hand at VB so I took this opportunity to get my feet wet,
thinking a custom function was the way to go. However, I added this
function to my worksheet, but I can't seem to call it from a cell in the
worksheet. I'm guessing I've done something wrong in either the function or
where I put it in the worksheet, but I don't know.

Any help here would be appreciated.


================================
Public Function federalTax(income As Integer) As Integer

' 2003 Tax Table
'Income But Pay This Plus Of the
'Is Over Not Over Amount This % Excess Over
' 0 14,000 0 10% 0
' 14,000 56,800 1,400 15% 14,000
' 56,800 114,650 7,820 25% 56,800
'114,650 174,700 22,283 28% 114,650
'174,700 311,950 39,097 33% 174,700


Select Case (income)
Case 0 To 13999 '10%
federalTax = 0 + income * 0.1
Case 14000 To 56799 '15%
federalTax = 1400 + ((income - 14000) * 0.15)
Case 56800 To 114649 '25%
federalTax = 7820 + ((income - 56800) * 0.25)
Case 114650 To 174699 '28%
federalTax = 22283 + ((income - 114650) * 0.28)
Case 174700 To 311949 '33%
federalTax = 39097 + ((income - 174700) * 0.33)
Case Else '0 or more than 312000
federalTax = 0
End Select

End Function
==============================
 
WOW!

This is very easy and understandable....

This is a lot easier than I thought. I was thinking that a custom function
was the only way to go.

Thanks Peo!
 
You can do it in one step:

=ROUND(SUMPRODUCT(--(A1>thresh),(A1-thresh),mrates),0)

where you have your table on Sheet2 and thresh = Sheet2!A2:A6,
mrates = Sheet2!B2:B6:

A B
1 Threshold Change in Rate
2 0 0.10
2 14000 0.05
3 56800 0.1
4 114650 0.03
5 174700 0.05

Note that column B is the *change* in marginal tax rate, e.g.,
14000-56850 is taxed at 0.10 + 0.05.

see http://www.mcgimpsey.com/excel/taxvariablerate.html for more details.
 
It would be better if you only posted in ONE group.

FeDude said:
Thanks for some of the tips. I could not seem to find what I was looking
for via the google search.

I'm starting to think that using standard formulas looks pretty involved and
probably unmaintailable in the long run..

I wanted to try my hand at VB so I took this opportunity to get my feet wet,
thinking a custom function was the way to go. However, I added this
function to my worksheet, but I can't seem to call it from a cell in the
worksheet. I'm guessing I've done something wrong in either the function or
where I put it in the worksheet, but I don't know.

Any help here would be appreciated.


================================
Public Function federalTax(income As Integer) As Integer

' 2003 Tax Table
'Income But Pay This Plus Of the
'Is Over Not Over Amount This % Excess Over
' 0 14,000 0 10% 0
' 14,000 56,800 1,400 15% 14,000
' 56,800 114,650 7,820 25% 56,800
'114,650 174,700 22,283 28% 114,650
'174,700 311,950 39,097 33% 174,700


Select Case (income)
Case 0 To 13999 '10%
federalTax = 0 + income * 0.1
Case 14000 To 56799 '15%
federalTax = 1400 + ((income - 14000) * 0.15)
Case 56800 To 114649 '25%
federalTax = 7820 + ((income - 56800) * 0.25)
Case 114650 To 174699 '28%
federalTax = 22283 + ((income - 114650) * 0.28)
Case 174700 To 311949 '33%
federalTax = 39097 + ((income - 174700) * 0.33)
Case Else '0 or more than 312000
federalTax = 0
End Select

End Function
==============================

Don Guillett said:
Thanks for the thanks and the explanation.

the
delete button to kill off etc
ever since. Soooooooo
Click to expand...
microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public posted
to
all. It's only when don't
then see what others
Click to expand...
line
in
Harlan's signature:-
Click to expand...
up
in federal
income
Click to expand...
Click to expand...
 
Very interesting. I had no clue what this function would ever be used for.

I follow most of your description from your web page, except one portion:

"When a boolean value is used in a math function, XL converts TRUEs to 1 and
FALSEs to 0. The double unary-minus (-) operators are the fastest way to do
the coercion."

Why is it necessary to do a double unary-minus? I thought XL would convert
binary results to 1/0 anyway? maybe I don't know exactly what "--" does?
 
FeDude said:
This must have been done before, but for the life of me I cannot figure out
how (for a cell that contains taxable income), to compute federal income
taxes from a set of rules without VBA. Has anyone done this before.

Income Is Over But Not Over This Amount Plus This % Of the Excess Over
0 14,000 0 0.1 0
14,000 56,800 1,400 0.15 14,000
56,800 114,650 7,820 0.25 56,800
114,650 174,700 22,283 0.28 114,650
174,700 311,950 39,097 0.33 174,700
Click to expand...

There are several way to do this. For instance:

=(X407-VLOOKUP(X407,Married2003,1,TRUE))*VLOOKUP(X407,Married2003,4,TRUE)+VLOOKUP(X407,Married2003,3,TRUE)

where X407 is taxable income, Married2003 is the IRS table for MFJ and
see Excel Help VLOOKUP for what the formulas mean. You need a table
for each tax status (MFJ, JFS, S, HH) and year and select which cell
to read depending on status and year. Another way is:

=MAX(0.1*I$506,0.15*I$506-350,0.25*I$506-3190,0.28*I$506-5254,0.33*I$506-12429,0.35*I$506-18668)

where I$506 is taxable income. Again you need a sepearate formula for
each tax status and year. Ed
 
The explicit coercion by the double unary minus is necessary only
because SUMPRODUCT() expects numeric arrays, so a boolean array
throws an error.
 
FeDude said:
This must have been done before, but for the life of me I cannot figure out
how (for a cell that contains taxable income), to compute federal income
taxes from a set of rules without VBA. Has anyone done this before.

Income Is Over But Not Over This Amount Plus This % Of the Excess Over
0 14,000 0 0.1 0
14,000 56,800 1,400 0.15 14,000
56,800 114,650 7,820 0.25 56,800
114,650 174,700 22,283 0.28 114,650
174,700 311,950 39,097 0.33 174,700
Click to expand...

I posted this a couple of hours ago, but it must have gotten lost in
all the other comments: Where B1 is your taxable income.

=MAX(0.1*B1,0.15*B1-700,0.25*B1-6380,0.28*B1-9819.5,0.33*B1-18554.5,0.35*B1-24793.5)

You can also use:

=(B1-VLOOKUP(B1,Married2003,1))*VLOOKUP(B1,Married2003,3)+VLOOKUP(B1,Married2003,2)

where B is taxable income, 1 is your first column of brackets, 2 is
the tax on the prior bracket and 2 is the tax rate, Married2003 is
your chart ed
 
This formula assumes the income cell to tax is D21 because I copied it from
my own excel sheet but it should work all the same.

=IF(AND(D21>0,D21<=14000),(D21*0.1),IF(AND(D21>14000.01,D21<56800),((D21-140
0)*0.15)+1400,IF(AND(D21>56800.01,D21<114650),((D21-7820)*0.25)+147.1,IF(AND
(D21>114650.01,D21<174700),((D21-22283)*0.28)+518.89,IF(AND(D21>174700.01,D2
1<311950),((D21-39097)*0.33)+1433.89,)))))
 
Back
Top