conditional formula for tax witholdings

[fyrfytr265]

Hello, I am a novice at writing formulas for excell, but did get this to
work for me to a point. Here goes. I want to write a formula that will
calculate how much federal tax to withold based on a given monetary
amount.
Here is how the tax code says to figure it......If the amount of
wages is not over $248, then the amount of tax to withhold is 0. If the
amount of wages is over $248, but less than $710 then the amount of tax
to withold is 10% of the excess over $248. If the amount of wages is
over $710, but less than $2013 then the amount of tax to withhold is
$46.20 plus 15% of the amount in excess of $710.
Now, here is what I wrote.
=IF(AND(A1>248,A1<=710),.1*(A1-248))
This formula works fine, so long as the wages are not over 710, my
question is how do I use another formula with different wage amounts in
the same cell, and have excell pick which formula it needs to use based
on the wage amount in cell A1.

I hope someone can give me some guidance on this, and hopefully my
message is not too confusing. Thank You!

 

[Arvi Laanemets]

Hi

=IF(AND(A1>248,0.1*(IF(A1>710,710,A1)-248),0)+IF(A1>710,0.15*(IF(A1>2013,201
3,A1)-710),0))

 

[fyrfytr265]

I tried the formula and it gave me the message that there were too many
argument. Any thoughts?

 

[J.E. McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/taxvariablerate.html

 

[Arvi Laanemets]

Hi

There was a bracket too much at end - sorry but I was in hurry and couldn't
test the formula.
=IF(A1>248,0.1*(IF(A1>710,710,A1)-248),0)+IF(A1>710,0.15*(IF(A1>2013,2013,A1
)-710),0)

 

[fyrfytr265]

Thanks for the help. I figured it out on my own but thought I would post
my solution to see if anyone knows of a better way to write it.

Some of the figures are different, I was using outdated tax figures,
but the concept is the same.

In the formula, F27 represents gross wages. If gross is less than or
equal to 308 then tax is 0. If gross is between 308 and 858 then tax is
10% of the amount of wages over 308. If gross is between 858 and 2490
then tax is $55 plus 15% of amount of wages over 858.

=IF(F27<=308,0,IF(AND(F27>308,F27<=858),0.1*(F27-308),IF(OR(F27>858,F27<=2490),55+0.15*(F27-858),(0))))

If anyone knows of another way please feel post it!

Again, Thanks.

 

[Ken Russell]

I'm no expert, but it seems to me that writing a formula that incorporates
the tax scales is counter productive. Each time the scales or thresholds
change you have to meticulously edit the formula.

Why not set the tax data up in a table and use the LOOKUP function. Future
changes can be made to the table without editing the formula.

--
Ken Russell


|
| Thanks for the help. I figured it out on my own but thought I would post
| my solution to see if anyone knows of a better way to write it.
|
| Some of the figures are different, I was using outdated tax figures,
| but the concept is the same.
|
| In the formula, F27 represents gross wages. If gross is less than or
| equal to 308 then tax is 0. If gross is between 308 and 858 then tax is
| 10% of the amount of wages over 308. If gross is between 858 and 2490
| then tax is $55 plus 15% of amount of wages over 858.
|
|
=IF(F27<=308,0,IF(AND(F27>308,F27<=858),0.1*(F27-308),IF(OR(F27>858,F27<=249
0),55+0.15*(F27-858),(0))))
|
| If anyone knows of another way please feel post it!
|
| Again, Thanks.
|
|
| ------------------------------------------------
|
|
|

 

[fyrfytr265]

I weighed the options and decided the formula was the best way to go
since I only have to change 8 numbers when the tax scale changes. If I
used a table then there would be hundreds if not thousands of numbers
to change when the tax rate changes. Also, I can use the same formula
for both married and single employees by changing the previously
mentioned 8 numbers, whereas using tables, I would have to create two
different tables with hundreds or thousands of numbers.

 

[Ken Russell]

I'm afraid I didn't make myself clear. The table you set up would contain
exactly the data you gave us in your post, not the whole tax schedule.

The LOOKUP formula simply accesses this very short table and does its
calculations based on the thresholds in the table.

Ken Russell

|
| I weighed the options and decided the formula was the best way to go
| since I only have to change 8 numbers when the tax scale changes. If I
| used a table then there would be hundreds if not thousands of numbers
| to change when the tax rate changes. Also, I can use the same formula
| for both married and single employees by changing the previously
| mentioned 8 numbers, whereas using tables, I would have to create two
| different tables with hundreds or thousands of numbers.
|
|
| ------------------------------------------------
|
|
|

 

[Arvi Laanemets]

Hi

As this tax scale table will be edited only occassionally, you can define it
with series of named ranges instead - as bonus you formulas will be nicer to
look at too.
An example with my formula here (I added parts for 0-tax to formula, to get
calculating fully formalized in my example, and also edited it a bit).
The OP has determined 3 tax ranges:
The part of gross between >0 and <=308 is taxed with 0%
The part of gross between >308 and <=858 is taxed with 10%
The part of gross between >858 and <=2490 is taxed with 15%
The part of gross >2490 isn't given, let's be 0%

Defined named ranges will be p.e.:
Lim1=308
Lim2=858
Lim3=2490
Tax1=0
Tax2=0.1
Tax3=0.15
Tax4=0

The general formula will be
=Tax1*IF(A1>Lim1,Lim1,A1)+Tax2*IF(A1>Lim1,IF(A1>Lim2,Lim2,A1)-Lim1,0)+Tax3*I
F(A1>Lim2,IF(A1>Lim3,Lim3;A1)-Lim2,0)+Tax4*IF(A1>Lim3,A1-Lim3,0)

It's very easy to expand, when additional ranges are added to tax scale -
add named ranges and according number of parts to formula, and edit the last
part of formula.
As about different scales for married and single employees - I'm confused.
Maybe singles have some additional tax, which can be calculated separately?

For OP, we can drop parts of formula with Tax=0
=Tax2*IF(A1>Lim1,IF(A1>Lim2,Lim2,A1)-Lim1,0)+Tax3*IF(A1>Lim2,IF(A1>Lim3,Lim3
;A1)-Lim2,0)

 

[J.E. McGimpsey]

A little simpler to use this technique:

Say the table in J1:K4:

J K
1 Band Rate
2 308 10%
3 858 5%
4 2490 -15%

where the values in K2:K4 are the marginal rates, or the differences
in rates between bands (e.g., (10%-0%), (15%-10%), (0% - 15%))

Name J2:J4 "band", K2:K4 "mrate".

Then the tax is calculated as:

=SUMPRODUCT(--(A1>band),(A1-band),mrate)

To expand, simply expand the named ranges, no change to formulae are
necessary.

 

[Arvi Laanemets]

Nice ))

Have a couple of such like formulas seen in some Excel NG before too, but
for using them myself I don't understand them enough (I mean this '--'
part). I.e. I understand what it does, but not why.

 

[J.E. McGimpsey]

The double unary minus coerces an array of Boolean TRUE/FALSE values
to numeric 1/0 values (you need 2 in this case since the first one
returns -1/0).

For a more comprehensive explanation of using the SUMPRODUCT()
formula for calculating variable rates, see

http://www.mcgimpsey.com/excel/taxvariablerate.html

 

[ryanb.]

JE

Is it not possible to use"*" in place of the "--" and "," ? The SUMPRODUCT
formulas I use look like this:

=SUMPRODUCT((QUERY!$A$2:$A$49644=$B14)*(QUERY!$F$2:$F$49644=F$3)*(QUERY!$G$2
:$G$49644=ALMONTH)*(QUERY!$H$2:$H$49644))

Just curious if it is an alternative way of doing it, or if SUMPRODUCT is
capable of doing something else as well.

TIA,

ryanb.

 

[J.E. McGimpsey]

Certainly you can use the multiplication operator. In both cases,
boolean arrays need to be coerced into numeric arrays, which can be
done using the unary minus or done implicitly during the
multiplication. Figures posted in these groups, however, and in my
own tests show a small speed advantage when the comma form is used.

This should not be surprising, since using * does the array
multiplication first, then hands the result to SUMPRODUCT(), so that
SUMPRODUCT is only summing the array, whereas with the comma form,
the array multiplication happens within SUMPRODUCT(), and so would
presumably be optimized.

That doesn't guarantee that the comma form will be faster, of
course, but it would be very surprising if it were slower than using
the * operator.