+ Reply to Thread
Results 1 to 13 of 13

Formula to Calculate Employer Soc Security Tax up to Salary Limit

  1. #1
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    What results do you expect and where?

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,943

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

    Administrative Note

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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. #6
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    352

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

    Quote Originally Posted by MNmom View Post
    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
    Last edited by curiouscat408; 10-10-2021 at 08:36 PM.

  7. #7
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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. #8
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    352

    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. #9
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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. #10
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,253

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  12. #12
    Registered User
    Join Date
    10-10-2021
    Location
    minneapolis, Minnesota
    MS-Off Ver
    10
    Posts
    6

    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. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,253

    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))
    If we have provided a satisfactory solution then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
    I hope that you have a blessed day.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to compute backwards from net salary to gross salary in Uganda
    By munaaba in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 10-05-2021, 03:24 AM
  2. 401k Employer Match with a limit
    By GTMoe in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-04-2019, 01:32 AM
  3. Replies: 7
    Last Post: 08-29-2018, 08:28 AM
  4. Replies: 1
    Last Post: 11-22-2017, 05:27 AM
  5. How to calculate Gross Salary from given Net Salary
    By Basab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2015, 01:15 AM
  6. [SOLVED] Need formula for prorating monthly salary based on hire/fire date and annual salary.
    By Excel_Help_Pls in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-06-2015, 12:56 AM
  7. [SOLVED] Salary Bracket returns employees AND EMPLOYER corresponding Contribution for sss
    By ECYOJ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2014, 03:48 AM

Tags for this Thread

Bookmarks

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