Thank you for your question today.
In 2014, the cap will be at 117K
there is no percentage amount of salary that computes the cap. It is 7.65% for SS & Medicare combined up to the earnings limit.
BUT THEN....there is an additional Medicare tax that must be withheld equal to 0.9% after you hit 200K earnings for the calendar year.
Plus, the Medicare amount of 1.45% does NOT have a cap at all.
In your Excel spreadsheet, you'll need a column for taxable SS wages in addition Taxable Wages
Then you can compute the tax amount in another column for SS tax (6.2%). You can use an If/Then type statement in the calculation such that "If YTD wages in the Taxable SS column is less than 117001, then SS tax = 62% of that column, otherwise the amount is 0"
You would have another column for "additional Medicare tax", similarly, although the column would calculate as "If the YTD SS wages are >200.000 then the Additional Medicare tax is 0.9% of the amount over $200K"
Do you have an example worksheet?
No, but you'll need to have several intermediary columns, as noted above.
For each pay period, you have a current earnings and a YTD amount. The tricky calculations are when you hot the YTD trigger amounts.
The calculation in Excel looks something like this, although I don't have enough time right now to do the full TRUE calculation.
Assume cell A1 has the current period check and B1 is the YTD after that check.
Formula would look like =if(A2>117000,0,(A1*0.062))
This says "If the YTD amount is Greater than 117K, then the SS amount = 0. If this is false, then the SS amount for this check is 6.2% times A1"
To do this fully accurately, where the TRUE statement is 0, you would need an IF statement in case the current period check carries the YTD amount OVER the 117K threshold, but PART of the current period check is taxed.