Home

Formula Stopping Point.

%3CLINGO-SUB%20id%3D%22lingo-sub-759621%22%20slang%3D%22en-US%22%3EFormula%20Stopping%20Point.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759621%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20spreadsheet%20for%20grossing%20up%20income%20in%202019.%20I%20am%20running%20into%20an%20issue%20when%20it%20comes%20to%20social%20security%20taxes%20withheld%20(column%20L).%20As%20you%20may%20or%20may%20not%20know%2C%20our%20gross%20wages%20in%20the%20U.S.%20(column%20J)%20are%20taxed%20at%206.2%25%20(0.062)%20for%20the%20first%20%24132%2C900%20we%20make%20in%202019.%20After%20that%2C%20it%20is%20not%20taxed%20at%20all.%20My%20problem%20is%20I%20can't%20figure%20out%20how%20to%20calculate%20that%20on%20a%20month-by-month%20basis%20and%20get%20the%20formula%20to%20stop%20adding%20once%20the%20cumulative%20amount%20in%20the%20social%20security%20column%20(column%20L)%20has%20reached%20its%20max%20withholding%20of%20-%248%2C239.80%20(%24132%2C900%20*%206.2%25).%20If%20anyone%20can%20figure%20this%20out%2C%20I%20would%20be%20really%20thankful.%20Just%20a%20note%2C%20I%20am%20needing%20the%20social%20security%20column%20to%20be%20a%20negative%20amount%2C%20and%20the%20most%20negative%20it%20can%20be%20is%20the%20-%248%2C239.80.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20example%2C%20say%20from%20January%20to%20June%20I%20earned%20%24120%2C000.%20Then%20my%20cumulative%20social%20security%20withholding%20in%20those%20months%20would%20equal%20%247%2C440%20(120k%20*%206.2%25).%20Additionally%2C%20I%20make%20another%20%2420%2C000%20in%20July.%20Now%2C%20the%20amount%20of%20income%20I%20earned%20in%20July%20would%20bring%20my%20cumulative%20earnings%20past%20the%20threshold%20of%20%24132%2C900%2C%20so%20the%20amount%20of%20social%20security%20tax%20withheld%20in%20July%20is%20just%20going%20to%20be%20whatever%20it%20takes%20to%20max%20out%20the%20withhold.%20In%20this%20case%20it%20would%20be%20the%20max%20withholding%20of%20%248%2C239.80%20(%24132%2C900%20*%206.2%25)%20minus%20whatever%20has%20already%20been%20withheld%20in%20January%20through%20June.%20At%20this%20point%2C%20no%20more%20social%20security%20tax%20is%20going%20to%20be%20withheld%20for%20the%20rest%20of%20the%20year.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%20I%20really%20hope%20the%20example%20helped%2C%20but%20please%20let%20me%20know%20if%20there%20are%20any%20questions.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759621%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759629%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Stopping%20Point.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377972%22%20target%3D%22_blank%22%3E%40bpgower98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3D-(MIN(SUM(%24K%242%3A%24K2)%2C132900)*0.062%2BSUM(%24L%241%3A%24L1))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
bpgower98
Occasional Visitor

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. 

1 Reply

@bpgower98 

That could be

=-(MIN(SUM($K$2:$K2),132900)*0.062+SUM($L$1:$L1))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies