# Formula to Calculate Employer Soc Security Tax up to Salary Limit

1. ## Formula to Calculate Employer Soc Security Tax up to Salary Limit

Hi, I am looking for a formula to use in forecasting employer paid Social Security and Medicare taxes by month or quarter
I found a formula post in the forum when I searched FICA but it isn't quite working for me. It is not adding in medicare tax. I copied the formula into cell E19, but it probably needs to be updated to pull the right cells in my example.

ER Soc Security tax is 7.65\$ applied to the first \$142,800 of salary.
Medicare tax is 1.45% of salary, with no salary cap.

2. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

What results do you expect and where?

3. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. 10 is Windows, NOT Office. Thanks.

4. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

I updated the worksheet to include a section that calculates Medicasre tax at 1.45% Fows 23-28 and another section for calculating Social Security Rows 37-42.
I actually could take the total wages x the medicare rate, it would not need to broken out by employee since its a constant percent of wages for the month.
I added a section where the social Security formula would go but don't know how to write it. if cumulative ytd salary is less than \$142,800 (salary limit) then take current monthly wages x soc security rate of 6.2%.
But if during the month, an employees ytd wages go above \$142,500, then I'd only want to multiple salary for the month up \$142,500 x the social security rate.

If there is a formula that can combine both fica and medicare, that would be fine. Otherwise it ban be broken out.

Thanks for looking at this.

5. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

Hi

Please manually add the results you expect, tell us which cell references are the results and the calculation you've made - again referencing which cells are used by the calculation.

It's always easier for us if we can see Exactly what results you need.

6. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

Originally Posted by MNmom
If there is a formula that can combine both fica and medicare, that would be fine.
Do you really mean ``combine Soc Sec and Medicare``?

FICA = Soc Sec plus Medicare

``Taxes under the Federal Insurance Contributions Act (FICA) are composed of the old-age, survivors, and disability insurance taxes, also known as social security taxes, and the hospital insurance tax, also known as Medicare taxes.``
Source: https://www.irs.gov/taxtopics/tc751

As you noted, Soc Sec is limited to 6.2% of the year's cap, which is indeed \$142,800 for 2021, not \$142,500 (sic) as you mistype sometimes.

In contrast, Medicare is 1.45% without a cap.

-----
BTW, this is a new one for me. It's been many years since I dealt with FICA.

``Employers are responsible for withholding the 0.9% Additional Medicare Tax on an individual's wages paid in excess of \$200,000 in a calendar year, without regard to filing status. An employer is required to begin withholding Additional Medicare Tax in the pay period in which it pays wages in excess of \$200,000 to an employee and continue to withhold it each pay period until the end of the calendar year.``
Source: https://www.irs.gov/taxtopics/tc751

7. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

replying to last post you are correct.
I should have said add together Social Security and Medicare (FICA), and mistyped the annual salary limit.

8. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

@MNmom, see my "BTW", which I might have edited (sigh) after you saw my previous posting.

9. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

I am trying again.

I entered into rows 37 a manual example of what should happen. Goal is to stop calculating Soc Sec tax when the cumulative salary hits the limit of \$142,800.
Cells should show zero for remainder of the year.

An alternative would be to combine the medicare tax and the social security tax into one formula which would equal the FICA amount as the person above noted.

10. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

thank you so much!

I checked and the Medicare tax for over \$200,000 is paid by the employee so I will not need to include it in the company budget. One less thing to worry about

11. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

I feel that the formulas could be:
For Soc. Security: =IF(E10="","",IF(E10<\$I\$27,E2*\$H\$27,IF(D10<\$I\$27,(\$I\$27-D10)*\$H\$27,0)))
For Medicare: =IF(E10="","",IF(E10<\$I\$28,E2*\$H\$28,IF(D10<\$I\$28,(\$I\$28-D10)*\$H\$28,0)))
For FICA: =SUM(E17:E18)
Let us know if you have any questions.

12. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

JeteMc

thank you for taking time to look at this, it looks like its working for the first person. Let me try the formula on a few others.
(I needed to adjust the SS rate to 6.2% and remove the salary limit on Medicare - I did that by adding \$1M as a cap since everyone is lower than that - that just changes the numbers, not the formula)

13. ## Re: Formula to Calculate Employer Soc Security Tax up to Salary Limit

You're Welcome and thank you for the feedback.
I will point out that the overall FICA amount for a year could be calculated in one formula, as modeled in cells D22:D27, using: =SUM(IF(D2>I\$27,I\$27*H\$27,D2*H\$27),IF(D2>I\$28,I\$28*H\$28,D2*H\$28))
I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1