Self-employment compensation calculation

[BG5150]

I used to have a handy-dandy spreadsheet that helped calcuate self-employment comp given net sched c or k-1 income and the rest of the ER cotnributions.

Does anyone have one handy for 2015 they would like to share?

[Tom Poje]

possibly this will work, no instructions provided, aside from entering data in the gold colored boxes, possibly some items you wouldn't even use.

ideal salary current.xls

[BG5150]

Thanks, Tom!

[Peter Gulia]

For an extra-useful software, consider Gary Lesser's QP-SEP Illustrator:

http://www.garylesser.com/

[Mike Preston]

Tom, cells K14-K23 use non-partner comp in excess of G4 (Taxable Wagebase) as the trigger. Are you sure the trigger shouldn't be G5 (Integration level)?

Haven't reviewed it all, but it is a neatly put together worksheet. Thanks.

So as to accommodate every NHCE in their own group I would move L13 through L25 to column M and then put "override" in cell L13, then change the formulas in column M to be, for example: IF(L14>0,L14,J14+k14)

[Tom Poje]

Mike-looking at it, I would say you are correct.

The original sheet only had a single cell to enter total contribution to NHCEs. I modified it to input comps on NHCEs and have that field populated. Then I had a plan that was integrated at 100% of the TWBso I added a column for that. so it wouldn't surprise me if I missed something. Of course, I have always compared the sheet to actual results running a formula on other software, and never had a problem, though I suppose it could occur. even at 80% TWB NHCEs generally won't get an integrated piece.

I've never thought of using the sheet for the modern day everyone in their own group possibility. good suggestion.

the brief history of the sheet is that I had written up notes for the calculation.

someone had gotten a hold of those notes and then sent me the spreadsheet (many moons ago - late 90's probably) to make sure the logic worked. I was impressed (and still am!). just about everything from column H on was my later additions.

sort of like the sheet for calculating the indexed limits, someone saw my notes on how those are calculated every year and sent me a sheet on that, which of course I have tweaked and modified over the years.

Guess I've been lucky to have written some notes that were clear enough to make sense, and then have someone send me the notes back in an excel format.

[Mike Preston]

Well, it has no provision for an owner to have outside W-2 and it doesn't cater to the possibility that a particular NHCE's contribution would apply to a given owner in a proportion other than the "Partnership Ratio".

But those things certainly don't come up all the time.

I don't particularly like the Step 6 or Step 7 approach used...I'd use programming to hide the inapplicable formulas.

I've annotated my version with the issues I'd update if I had the time and will do so... if I ever have the time (which may be never).

Thanks for posting it.

[Mike Preston]

I should also point out that it uses the "old" method of determining the 1/2 FICA deduction (using 1/2 the Total Self-employment taxes) rather than the "new" method (using 59.6% of the FICA tax plus 50% of the Medicare tax) so all calculations for years after 2010 will be off by a couple of dollars. If that matters to you, you'd have to do some serious surgery to the spreadsheet.

[Mike Preston]

Tom, look at cell J28. The formula is =I14+I15+I16+d55. I think it should be =SUM(I14:I23,MIN($g$3,$d$55))

C52 s/b =MIN($g$3,G51)

J32 s/b = L24+L25

Then I found lots of errors associated with the fact that the worksheet doesn't attempt to implement the 401a17 limit when applying the formula contribution amount so I just gave up and reprogrammed the whole worksheet. Or at least I started to. Give me a week or so to clean it up.

[Tom Poje]

for 2 years (2011 and 2012) there was a special calc for FICA. even the form SE shows the 59.6% you are referring to on line 13, and I even added that to the spreadsheet for those years.

my understanding things returned back to normal and even the schedule SE for 2014 indicates things are back to the way they were - at least I don't see ' 59.6%" on line 13 anymore., so I removed that from the spreadsheet.

2012 form SE.pdf

form 2014 schedule SE.pdf

[mbozek]

Just what is this new calculation of the one half fica tax deduction? Pub 560 P 22 describes the fica deduction as the amount reported on line 27 of the 1040. There was a different calculation in 2010 for self employed owners who claimed the self employed health insurance deduction on line 29 but that was a one year event.

[Tom Poje]

for the record, There was a 2 year window in which the rates were changed. it only changed things a few dollars.

as noted, it was TRA 2010

http://www.irs.gov/Businesses/Small-Businesses-&-Self-Employed/Self-Employment-Tax-Social-Security-and-Medicare-Taxes

Self-Employment Tax Rate

The 2010 Tax Relief Act reduced the self-employment tax by 2% for self-employment income earned in calendar year 2011. The self-employment tax rate for self-employment income earned in calendar year 2011 was 13.3% (10.4% for Social Security and 2.9% for Medicare). The Temporary Payroll Tax Cut Continuation Act of 2011 extended the self-employment tax reduction of 2% for calendar year 2012 so the rates for 2011 remained in effect for 2012. For self-employment income earned in 2013 and 2014, the self-employment tax rate is 15.3%. The rate consists of two parts: 12.4% for social security (old-age, survivors, and disability insurance) and 2.9% for Medicare (hospital insurance).

[Mike Preston]

On my master sheet I had made that change (back to the way it was for years after 2012), but I forgot about that when making my comment regarding surgery. Thanks.

[Mike Preston]

OK, a week is up. Attached is the spreadsheet that Tom posted, with a gazillion changes made by me. I think it is kind of spiffy.

Note: it doesn't do everything and since I have only been stress testing it for a couple of days, it wouldn't surprise me if there are bugs. So, if you use it and find any bugs, please report them to me.

Thanks

[Mike Preston]

Let's try again.ideal salary from Tom Poje updated by Mike Preston v1.00.xls

[Tom Poje]

Without looking at it, but knowing your skills, I'll simply say "you're a better man than I am Gunga Din"

[Mike Preston]

Well, if you get the chance to look at it, I'd greatly appreciate some feedback (from you or anybody): useful? overkill? somewhere in between?

Thanks

[Tom Poje]

in the past, I only have one or two plans that need such a calculation

I've used the spreadsheet sort of as a prelim for running something on the valuation software we use (and the numbers have always been what I expected). don't think I have any plans left to run this year, but next time I need a 'quick and easy' check I'll look.

sorry.

[Mike Preston]

np

[Bill Presson]

Mike/Tom,

Just saw this thread and haven't opened the spreadsheet yet. What are the odds it would work for a 140 partner LLC?

WCP

[Mike Preston]

It would "work", but it would be unwieldy. It is geared to one worksheet per owner. 140 worksheets is a pain. How many non-owners? Are the non-owner contribution amounts pre-determined or does the spreadsheet have to calculate the non-owner contribution amounts? How is the expense of the non-owner contributions shared? In the same ratio as the profit interest for all non-owner contributions? Or are there certain non-owners who are charged against a particular owner at a rate which is different from the profit interest? The worksheet has no capability to adjust for unreimbursed partnership expenses. Or to apply Section 179 expenses in a manner other than, in the aggregate, reducing the Gross Profit (which is the same thing as charging them to each owner in the same ratio of the profit interest).

[Tom Poje]

as Mike said it was designed for a simple 1 person case (though I guess arguably you could use the sheet 140 times (one for each partner)

It was originally intended to simply calc the FICA but was expanded to handle a fairly simple small plan with a few people, which are the only cases I ever had to deal with in my days!

Attached is one I have never used, don't know where I pulled it from. dated in 2005. I even have a number of versions, all with the same date and time, one called original, one says revised, another says better, etc. so I really have no idea.

This one is coded up with 6 partners and 10 employees.

again, I have never used it, never tested it or anything

earned income revised.xls

[Bill Presson]

Thanks guys. They're using Relius to do the calculation and I believe it was accurate. Just thought it might be nice for a double check outside the system.

[Tom Poje]

unless something broke over the years I would expect Relius to work

way back in the early 90's when I did support for Pentabs someone once asked if you could do a similar type calc on an age weighted plan with around 100 partners. I was 'stupid' enough to say 'not as designed, but give me a little and I'll figure something out', and I actually did figure it out- coded the plan as a first year target benefit and it actually worked. (Target benefits had an ideal salary cal, but no such thing existed for age weighted plans)

[Mike Preston]

Tom, when I got it, it already had a cell that was used to determine the share of gross profits applicable to the one owner/partner that was being calculated, so somebody at some point thought it was for more than the simple case of a single owner.

[Tom Poje]

Bill-

My greatest admiration for someone who double checks data from time to time.

To many times do we simply 'assume' the software is doing things correctly (of course sometimes it is user coding issues)

[BG5150]

Tom, can you (or someone) update this for 2016?  There's so much going on that I don't want to miss something.

 

Thanks in advance.

 

 

 

[Tom Poje]

I believe the only items you ever need to update are the first few items

contribution limit

compensation limit

taxable wage base

2016 is the same as 2015

so unless there is some other change my brain gears aren't thinking of, nothing has changed

[BG5150]

Got it.  Thanks.  I did just that.

 

(At first I was totally confused and the latest seemed to look nothing like the first one.  But it was just the instructions page!)