# Solved: Excel Payroll Tax Formula



## mtf (May 17, 2003)

I need formula to calculate FUTA tax (.008 of gross salary on first $7,000) -- thus, when it gets to $56.00 no more tax is deducted. Start date is January, assume 1,255.00 bimonthly payment. Thanks for your help.


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

I've uploaded a sample file with two options. Option #1 at the top uses the data on the left. As you can see it just sum's along the way giving you a YTD total, and an extra column showing the total tax up to $7000 (or whatever is set in the cell). Option #2 is an all-in-one step where you only need to enter the total in one cell. Not sure how your data is setup, but you can use either/or.

HTH


----------



## mtf (May 17, 2003)

Thanks for your reply. Unfortunately it doesnt work. The maximum tax payable is $56, so Excel has to stop calculating the tax when amount earned reaches $7,000; I entered a formula "manually" to get the result for this (see file) but would like a formula that it's automatic, no matter how many employees or amount of salary.


----------



## Zack Barresse (Jul 25, 2004)

I don't understand it doesn't work. It works the way you asked. What exactly should the desired results return? The formula you entered does not calculate tax based on YTD gross salary, but the difference between your max (7000) minus the YTD, which is inaccurate. I also don't understand "no matter how many employees or amount of salary". It sounds like you're trying to apply this to another file. Can you share that with us, or describe the data structure and expected results?


----------



## mtf (May 17, 2003)

I have to go now but will be back later tonite and will try to make myself understood. In the meantime, thanks a lot!


----------



## mtf (May 17, 2003)

I think I got it now. The desired result is to come up with the SUI and FUTA tax of employees based on tax rate (5.4% and .8%) of gross salary up to 9K and 7K, respectively, so that amount of tax paid by employee doesnt exceed $486 for SUI and $56 for FUTA. Please see worksheet. Would highly appreciate your comments.

Thank you for your time and advice.


----------



## Zack Barresse (Jul 25, 2004)

You don't really need the tables on the right, as you can do it with a single formula (one for each tax)...

SUI

```
=IF(SUM($C$7:C8)<sui,$C8*suitax,IF(sui-SUM($C$7:C7)<0,0,(sui-SUM($C$7:C7))*suitax))
```
FUTA

```
=IF(SUM($C$7:C8)<$N$6,$C8*$G$7,IF($N$6-SUM($C$7:C7)<0,0,($N$6-SUM($C$7:C7))*$G$7))
```
Of course, you don't have to have the named ranges unless it makes you feel better. So _sui_ would be _$K$6_, _suitax_ would be _$F$7_, etc. Entirely up to you whichever you prefer.


----------



## mtf (May 17, 2003)

I agree, it's better just one formula. Couldn't figure it out! I copied your formulas and will not use the table. Thanks a lot for your help! Now I know where to go when Excel gives me a hard time. All best, Margarita


----------



## Zack Barresse (Jul 25, 2004)

Take care Margarita.

You can mark your thread as Solved by clicking the Mark Solved button.


----------

