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.
What results do you expect and where?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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.
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.
Do you really mean ``combine Soc Sec and Medicare``?Originally Posted by MNmom
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.
replying to last post you are correct.
I should have said add together Social Security and Medicare (FICA), and mistyped the annual salary limit.
@MNmom, see my "BTW", which I might have edited (sigh) after you saw my previous posting.
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.
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
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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)
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks