• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula needed to calculate US payroll taxes that has cap or limits

J

Josiev

New Member
Hello,

I am in need of a formula that I can copy to all rows and columns. I am trying to figure out how to apply the salary cap to calculate payroll taxes. Depending on the employee salary, I can only apply payroll taxes if the limit has not been met. Once the cap is met at a certain point in time, payroll taxes need to be adjusted. Applicable rates must only be applied on the taxable portion of each employee's salary.
Attached is a sample workbook....I have been manually manipulating the formulas to determine when to stop calculating payroll taxes and also to determine the taxable portion, refer to yellow cells with red font. This has been a painful task as I need to use this for 300+ employees. Is there a formula I can copy to all rows across each columns? I have tried using if statement formulas but has not been successful. Any assistance is appreciated.

Josie
 

Attachments

Hi ,

Can you spend some time explaining things in more detail ?

1. As far as I understand, there are 4 types of taxes :

FICA
FICA Medicare
FUTA (After Credit)
SUTA

2. All calculations are only for one calendar year.

3. You have shown tax calculations for three of the 4 types ; FICA Medicare is missing.

4. My suggestion is as follows : we have a limit on the taxable income ; suppose we take FICA where it is 118500 ; we first calculate the total tax on this at the rate of 6.20% ; this works out to 7347.

Now , we can allocate this amount to the individual months.

See the attached workbook and post your comments / clarifications.

Narayan
 

Attachments

Thank you Narayan....your assistance is appreciated.

To answer your questions...
1. You are correct, the taxes you listed are indeed the types we have.
2. Yes, I only need working the calendar year of 2017. I was thinking of using this workbook as a template for coming years by updating the dates
3. Yes, FICA medicare is missing. I omitted from this workbook since there is no salary cap for this type of taxes and I can just add that in later since the formula is not complicated
4. Yes...this is an excellent solution...I can use this same logic for the other taxes and adjust the formulas to point to the correct rates.

Narayan, you truly made my day...this saves me tremendous amount f time. I am truly grateful for your time and effort in helping me solve this problem...Have a great day!

JV
 
You must log in or register to reply here.
Back
Top