Net Earned Income Calcl. with 2 employer sources

[Guest bernie lomax]

Does anyone have a spreadsheet that can figure net earned income when you have 401(k), safe harbor match and an integrated profit sharing calculation. This is a parternship with 6 partners and 10 employees.

[Mike Preston]

Yes. (g)

[Earl]

Does anyone have a spreadsheet that will allocate the same (except cross tested - not integrated) as above but contains a rank & file employee that became a Partner mid-year (W-2 and K-1).

Would it accomodate different rates of ownership and profit split?

thanks...

[Mike Preston]

Bernie,

Try the following.

I make absolutely no warranties of any kind, however.

Instructions:

Input stuff in blue cells.

Start with the plan stuff at the top.

Cell E4 is for overriding the integration percentage if you aren't using a standard one.

Cell C2 is Y if SH match is 100% up to 4% of pay; otherwise if it is anything other than Y it is 100% up to 3% plus 50% over 3% and up to 5%.

Then put in everybody's deferrals in row 16

Then put in actual earned income inn row 12

Then put in "share of employee cost" in row 13

Then put in something for compensation in row 9

Then see if the stuff in row 9 matches the stuff in row 30

If not, change the stuff in row 9 until it matches row 30.

You can hit cntl-shift-I if you have enabled macros and it will fiddle with 9 until it matches row 30.

Again.... no warranties.

[Macro wasn't updating the 6th Partner column, so I changed it.]

Net_Earned_Income_Determination.xls

[Mike Preston]

OK, Earl. Try the attached. If you think the prior spreadsheet came without any warranties, that goes for this one to the nth power! (where N=> infinity)

See the prior message for how some of the information is expected to be input.

C7 now asks for overall profits, and the amount put in there is allocated to Partners IN ADDITION to the individual earned income put into row 12.

Row 13 now asks for the share of profits to divide up the overall profits identified in Cell C7. This allows for a share of profits to be different from the share of expenses.

There is only one "share of expenses" row, which is Row 16. If an employee becomes a partner during the year, the portion of the ER contribution attributable to the period of time when that individual was an employee is shared among all the partners (including the partner who was previously an employee). If you don't like the way the breakdown works, change the percentage on the "share of expenses" row until you like what you get.

Line 22 now contemplates that any W-2 from the firm should be entered here. It is only used on this line for partners, though, to determine the FICA offset. It is NOT tied to the "employee" column (see below) because I've seen situations where accountants issue W-2's for partners that were never "rank and file" employees during the year. Flexibility makes things complicated, doesn't it? In normal circumstances you would just enter here the amount of W-2 from the column that represents the employee. Or, if the partner was never an employee during the year, normally this would be zero.

Line 23 asks for W-2 earned outside the firm to help determine the FICA offset, also. Some judgment is used in this break down. If you don't like it, change it! Note that this is just a shortcut anyway, because the other "modifiers" that you find on Schedule SE aren't included here. In normal circumstances, without farm income and the like, it should get you close.

Line 26 asks for the COLUMN indicator of the COLUMN that corresponds to the employee that matches the partner. Huh? If an employee becomes a partner during the year, enter that employee both as a partner and as an employee in separate columns. Then indicate in the partner's column, in line 26, the letter that corresponds to the employee's column.

I have removed the cell protection on this workbook because there are just too many things that somebody might want to override.

There are a ton of things not "checked" (for purposes of maximums, like overall deferral limitations, overall 415 limitations, etc.). The compensation limitation of $200,000 (as input in Cell C5) *IS* checked, however, and limited as to the SUM of the EE and Partner columns, if Line 26 has a letter in it.

Again, no warranties, and check absolutely every result.... twice... to see whether it really works.

Net_Earned_Income_Determination_version_with_Partner_W2.xls

[Guest bernie lomax]

Thanks so much. It works great. The only problem I found was in the 1/2 FICA adjustment. In the second half of the formula, you need to remove the $ in front of E15. It should be e$15*.9235*.029. If not, everyone uses the first partners comp.

The spreadsheet is great.

Thanks again!

[Earl]

awesome.

Now I get to check what I came up with pushing a lumbering valuation program around through various permutations and plugging various results into a "master valuation".

The changed the ownership on 11/1. Don't you think they could have waited 2 months?

[Mike Preston]

Actually, it should be E15 in the first place rather than E$15 so it will change as you indicate, but there is no real reason to "freeze" the row either because it isn't copied to any other location. I have re-uploaded to the original message a modified version making that change.

So, if anybody downloaded it before the date/time of this [edited] message, you need to either make the change as indicated or re-download.

Thanks for the feedback.

[Mike Preston]

I have made the same change to the spreadsheet uploaded pursuant to Earl's request, as well.

So, if anybody downloaded it before the date/time of this [edited] message, you need to either make the change as indicated or re-download.

[Mike Preston]

Well, Earl. How close did it come?

[Earl]

that's for this weekend...

[Earl]

Well, my information is on the sheet.

The issues are that the plan is cross tested, not integrated and its a 3% NESH, not match.

I put questions and comments at the bottom.

I have the rates that will pass, but don't know how to develop the comp numbers. What to do with the EE cost for the EE that became a Ptr. I will put my best try on a second reply.

I think they are going to chrage the EE cost for the pre-PTR comp to the Partnership and then have the Ptr make it up next year. Which I don't really get but I guess anything can be negotiated.

I thought this was an interesting scenario but virtually unsolvable (for me.) I will recommend partner changes at year end for the future.

preston2.xls

[Earl]

best try

2003_Partner_Income_Generic.pdf

[Earl]

can't figure how to add more than one file.

2003_Total_Allocation_Generic.pdf

[Gary Lesser]

Unless the nonowner contributions amounts are known, one-half of the SE tax is unknown. Therefore, the "1/2 of the SE tax deduction" must be determined on the fly.

You may find the attached software program, QP-SEP Illustrator, helpfull. The 2006 version is attached.

GSL Galactic Consulting Software Installation

(For all Microsoft Windows®-based operating systems, including “Vista”)

Install to hard drive. Run (open or execute) GSL-QPSEP to install QP-SEP (Cor-Sar and Keo-Sar) programs. Accept all defaults. Program will install two icons on your desktop. Click on the desktop icon to launch desired program. Your screen may go blank for a few seconds while program loads (this is normal). Once Title screen loads, press any key to exit Title screen and enter input screen. Press [Alt]+Q at any time thereafter to Exit program.

Designed to be used for: Designing retirement plans and allocating contributions under SEPs, SARSEPs, profit sharing, and money purchase pension plans for corporations, partnerships, and self-employed individuals.

Key features: Automatically calculates net earned income, self-employment tax, integration spreads, actual deferral percentage (ADP), top-heaviness, and limitations on contributions and their deductibility. Ineligible owner and guaranteed payment partner situations can also be handled. Other factors, such as outside W-2 income and self-employment gains and losses, are also taken into account (if entered). Top-heaviness is based on document type entered, e.g., model or prototype. Client illustrations can be printed, saved, and recalled.

Starting the program: To operate program or to view the instructions after installation, click the Start button, select Programs, open (click on) the GSL-QPSEP and click (open) the icon for the program you wish to run (COR-SAR for corporations, or KEO-SAR for unincorporated entities) or to view or print the instructions and other information charts. The program can also be started directly by clicking the yellow and red "COR-SAR" or "KEO-SAR" icon on your desktop.

For support call me at 317-254-0385.

________________________________________

GSL_QPSEP.ZIP