I am creating a spreadsheet for grossing up income in 2019. I am running into an issue when it comes to social security taxes withheld (column L). As you may or may not know, our gross wages in the U.S. (column J) are taxed at 6.2% (0.062) for the first $132,900 we make in 2019. After that, it is not taxed at all. My problem is I can't figure out how to calculate that on a month-by-month basis and get the formula to stop adding once the cumulative amount in the social security column (column L) has reached its max withholding of -$8,239.80 ($132,900 * 6.2%). If anyone can figure this out, I would be really thankful. Just a note, I am needing the social security column to be a negative amount, and the most negative it can be is the -$8,239.80.
As an example, say from January to June I earned $120,000. Then my cumulative social security withholding in those months would equal $7,440 (120k * 6.2%). Additionally, I make another $20,000 in July. Now, the amount of income I earned in July would bring my cumulative earnings past the threshold of $132,900, so the amount of social security tax withheld in July is just going to be whatever it takes to max out the withhold. In this case it would be the max withholding of $8,239.80 ($132,900 * 6.2%) minus whatever has already been withheld in January through June. At this point, no more social security tax is going to be withheld for the rest of the year.
Okay I really hope the example helped, but please let me know if there are any questions. Thank you.
