Excel to calculate optimal salary for small businesses

Archived From: Finance
  • Page :
  • 1
  • Text Only
Voting History
rated:
For small business owners (especially professionals), one of the recurring question is how much reasonable salary to take.  Too high a salary means more FICA; too low a salary means you are on IRS radar.  Taking higher salary also helps to put more money in 401k (up to a limit).
The decision to incorporate or not depends also depends on tax savings that may or may not be achieved.
I have seen many threads related to this topic.
Thread (just this week), Thread2 Thread3 etc.

So I have created a public worksheet
401kSalary 

You can view formulas, download the worksheet and edit your copy.  All variables/assumptions are in Column N.  You can play with them to simulate your scenarios.
The worksheet is far from perfect so your comments to correct it or improve it are welcome.

The worksheet has following inputs
Business operating income
Max 401k employee deferral
Max 401k contribution
SS Salary Limit
Employer 401K contribution
State S-Corp income tax
Current avg Fed+State+Local+AMT rate
Future avg Fed+State+Local+AMT rate
Future LT capital gains tax
Rate of return
Inflation rate
Compounding Period (years)
Your ownership % of business
Spouse ownership % of business

Based on these variables, the worksheet will calculate the NPV of your taxable and tax-deferred accounts.

Assumptions / Notes

  1. Husband and wife are the only shareholders/employees.  You can make Spouse Ownership = 0% if you are only the only owner.
  2. You/spouse do not have another job where you pay FICA.  If you do, this worksheet may over-estimate your FICA liability.
  3. Business Operating Income is income BEFORE salaries, FICA, 401k contribution and Business State Tax.
  4. The income tax calculations do not consider tax slabs.  If someone can find a Web api that accepts taxable income and returns total tax, that would be great.
  5. I have included the numbers from IRS v/s Watson case as FYI.  IRS successfully sued a CPA who took very little salary and made him increase it substantially.
  6. The file has formulas to make a column red in case of errors.
         For example, if your total 401k contributions (employer+deferral) exceed the limit, the cell will turn red.
                               If your contributions exceed salary, the cell will turn red.
  7. I have assumed regular 401k (i.e. not Roth 401k).  When it comes to big numbers, most everyone I know is reluctant to gamble on Roth rules remaining unchanged in the future.

Disclaimer: I am not a CPA.  So please use this with the full expectations of it being wrong.
 
Edit: Some edits; added details

Member Summary
Most Recent Posts
Hmmm - If the survey data does not include then it may not be apples-to-apples.
It is rare to find companies that contrib... (more)

ssgcinty (Sep. 21, 2016 @ 8:57a) |

That's again why I use more than 1 source to ferret out that info.

I create my salary based on strict salary. So yes for... (more)

dhodson (Sep. 21, 2016 @ 9:30a) |

Actually, net income exclusive of the owner-employee's salary is probably a better basis than revenue. That is what was ... (more)

btuttle (Sep. 21, 2016 @ 1:17p) |

Link to Worksheet 
Increase your salary in the following order as long as your business income supports it.  These numbers are for 2016

  1. Employer contribution gives the biggest bang for the buck. So always set the Employer contribution at 25%.
  2. Increase salary up to $24,000.  Increase personal deferral to $18,000 accordingly.
  3. Add spouse and increase spouse salary up to $24,000.  Increase spousal deferral to $18,000 according to the increase in spouse salary.
  4. Then increase your own salary up to the SS limit.  You may encounter diminishing NPV due to FICA.
  5. Then increase your salary up to the 401k contribution limit ($140k).  This will most likely result in higher NPV.
  6. Beyond $140k, you will see diminishing NPV.  So increase your salary above $140k only if your business income is high enough to require it.
  7. Keep spouse salary at 24,000.  Yes - the spouse will miss out on the chance to qualify for higher SS benefits.  But at this moment, the benefits are a gamble anyway.
Staff Summary
Thanks for visiting FatWallet.com. Join for free to remove this ad.

The first $24,000 gives you the best bang for the buck.
So at least take a $24,000 salary. Then add spouse up to 24k too if you can.

After that, let's say you want to know the impact of the next $100 increase in salary.
Change the tax bracket (column N10) to your marginal rate.
Change cell N5 from $18,000 to 0
And make your salary $100.

That should give you the impact of the next marginal increase in salary.
ETA: One more change when calculating impact of next $100:, Make cell N8 = 0 if you have reached the SS limit.
ETA: Read the Quick Summary for more details on salary.

Very cool, I love seeing a good numerical analysis. I've been playing this game for many years, and I'm sure I come out well ahead. That said, I am curious what taking lower salary is doing to my eventual Social Security payouts which are based on the 35 years of highest income. Like I said, I'm sure I am coming out ahead, but it would be good to understand the impact. Has anyone seen any sort of tool for calculating this?

First of all, the impact of lower salary is lower at the higher end of salary.  
( I bet you had to read it twice. )
The SS PIA is increased by 90% of the first $826/month of AIME, 32% of the next $4,154/month (up to a total of $4,980/month of earnings), and 15% of the remainder (anything above $4,980/month). 
And on top of that, any salary above the SS limit does not impact your SS benefits.
As long as you take $118,500, you are good.
 

First off I applaud your work in this.

To me the trouble is that many people aren't actually paying themselves a fair and reasonable salary for the position.

What I find people doing is saying if I pay myself 50% of the revenue then I won't trigger an audit and I'll be okay.

Now I have no idea if the IRS uses some % to flag but IF you are audited for any reason then you still need to defend the salary.

What I do is print out the avg and range of salaries for my type of work getting info from places on the web or published surveys and make sure I'm in the higher end of that. I imagine if you were within the range at all you probably could defend it.

Thus while I really do commend you for studying this topic, I think one could too easily get side tracked.

Updated the quick summary to the following
Increase your salary in the following order as long as your business income supports it.  These numbers are for 2016

  1. Employer contribution gives the biggest bang for the buck. So always set the Employer contribution at 25%.
  2. Increase the salary up to $24,000.  Increase personal deferral to $18,000 accordingly.
  3. Add spouse and increase spouse salary up to $24,000.  Increase spousal deferral to $18,000 according to the increase in spouse salary.
  4. Then increase your own salary up to the SS limit.  You may encounter diminishing NPV due to FICA.
  5. Then increase your salary up to the 401k contribution limit ($140k).  If you are in high tax bracket, this will most likely result in higher NPV.
  6. Beyond $140k, you will see diminishing NPV.  So increase your salary above $140k only if your business income is high enough to require it.
  7. Keep spouse salary at 24,000.  Yes - the spouse will miss out on the chance to qualify for higher SS benefits.  But at this moment, the benefits are a gamble anyway.

Edited to add that employer contribution should always be at 25%

dhodson
I agree with you and even I struggle a lot with this.
Let me add another wrinkle.
Say you are a CPA and your s-corp makes a profit of $250k. And you take a salary of $80k.
IRS uses the salary survey and finds the average CPA salary is $125k with your experience.
All is good so far.

But then you grow your CPA firm. Your firm has multiple clients. You work for some clients but you also have other folks working for you. And your firm makes $500k. How will IRS decide the salary now?

As you know I'm not a CPA or a lawyer but if I'm not mistaken the rule is a fair and reasonable salary. The % stuff isn't in any rules. It's just a guess by educated people so what I think would happen is that the % could flag an audit if too low but you have a reasonable chance of success assuming you had good data for the salary. That's why I personally use more than one source. If you just use % then you are just trying to avoid triggering an audit but you really have little defense if you are audited. You can't just say well I thought u wouldn't care bc I used 75% or whatever number. So in the situation you put forth, is that number still a fair and reasonable salary for the work done. I imagine a higher salary would be warranted with that additional business. Again I'm not a tax professional.

Thank you OP for this!

If you found yourself in court defending your position, practically the only thing that would matter to a judge is:  Could you reasonably find another qualified individual to do your job for the salary that you are paying yourself?  Yes you win, no you lose.
Whatever number your spreadsheet spits out, suggest that you evaluate that number with the aforementioned question.

Why is it better to first max employer contributions before employee ones?

tuphat said:   If you found yourself in court defending your position, practically the only thing that would matter to a judge is:  Could you reasonably find another qualified individual to do your job for the salary that you are paying yourself?  Yes you win, no you lose.
Whatever number your spreadsheet spits out, suggest that you evaluate that number with the aforementioned question.


That's pretty much my thoughts as well but I'm no lawyer.

ssgcinty said:   First of all, the impact of lower salary is lower at the higher end of salary.  
( I bet you had to read it twice. )
The SS PIA is increased by 90% of the first $826/month of AIME, 32% of the next $4,154/month (up to a total of $4,980/month of earnings), and 15% of the remainder (anything above $4,980/month). 
And on top of that, any salary above the SS limit does not impact your SS benefits.
As long as you take $118,500, you are good.

Sorry, but can you explain how you come up with $118,500?  Are you suggesting that any salary over $118,500 results in greatly diminished returns when it comes to future SS check amount?

bxd20 said:   Sorry, but can you explain how you come up with $118,500?  Are you suggesting that any salary over $118,500 results in greatly diminished returns when it comes to future SS check amount?
Social security wages max out at $118,500 for 2016. That means that if your wages are greater than $118,500, you only pay social security tax on the first $118,500. It also means that when you calculate your average income for determining benefits, only the first $118,500 of your 2016 wages are used.

Cooz said:   Why is it better to first max employer contributions before employee ones?

Given the profit of the company, let's say your services are worth $15,000.  You have two options.
    Employer pays you $15k as salary.  You put away $15k as deferral.
    OR
    Employer pays you $12k salary and $3k contributions. You put away $12k as deferral.

   In both cases, your income tax savings are same but you save FICA on $3k using the second option.

 

bxd20 said:   
ssgcinty said:   First of all, the impact of lower salary is lower at the higher end of salary.  
( I bet you had to read it twice. )
The SS PIA is increased by 90% of the first $826/month of AIME, 32% of the next $4,154/month (up to a total of $4,980/month of earnings), and 15% of the remainder (anything above $4,980/month). 
And on top of that, any salary above the SS limit does not impact your SS benefits.
As long as you take $118,500, you are good.

Sorry, but can you explain how you come up with $118,500?  Are you suggesting that any salary over $118,500 results in greatly diminished returns when it comes to future SS check amount?

Yes - let me elaborate.
One advantage of taking a salary of $100k instead of $80k is that come retirement time, a higher salary will bump up your PIA and your benefits will be somewhat larger.
But anything about $118.5k does not impact your PIA.  So as far as only Retirement benefits are concerned there is no advantage of going over 118.5k

Don't forget that a salary higher than $118.5k (upto 140k) is not all that bad because it will allow you to put more into 401k.  If you are in a high tax bracket, you may actually come out ahead even after paying 2.9% medicare on the salary after 118.5k.
 

dhodson said:   
tuphat said:   If you found yourself in court defending your position, practically the only thing that would matter to a judge is:  Could you reasonably find another qualified individual to do your job for the salary that you are paying yourself?  Yes you win, no you lose.
Whatever number your spreadsheet spits out, suggest that you evaluate that number with the aforementioned question.

That's pretty much my thoughts as well but I'm no lawyer.

While determining reasonable compensation is still a rather vague concept, you are both in sync with the IRS guidance and the courts. In August 2008 the IRS issued FS-2008-25; "Wage Compensation for S Corporation Officers." Then over the next several years there have been court cases on the issue relative to revenue mostly from professional services. They based the majority of their decision on what is the Fair Market Value of the S-Corp owner's professional services. They matched his salary to comparative salaries.

In those cases, even though not all of the revenue was from the professional services of the S-Corp owner, the courts found that ~= 40%-60% of the revenue were salaries of the owners. The IRS current guidance says the following:The three major sources of revenue are:

  1. Services of shareholder,
  2. Services of non-shareholder employees, or
  3. Capital and equipment.

If the gross receipts and profits come from items 2 and 3, then that should not be associated with the shareholder-employee's personal services and it is reasonable that the shareholder would receive distributions along with compensations.On the other hand, if most of the gross receipts and profits are associated with the shareholder's personal services, then most of the profit distribution should be allocated as compensation.

So as a general rule, most CPAs use 1/2 - 2/3 of revenue from predominantly professional services as compensation. The so called 50% minimum rule. Note: It is not a rule, rather a CPA made up guideline. However, then there is the John Edwards Loophole. Where he used ~= $1.25M salary on ~= $25M revenue (5%). The thought is when you reach the SS max wage base (2016 = $118.5K) as a salary you aren't trying to avoid so much in payroll taxes. So, $140K of salary on $350K revenue might be considered reasonable and $200K on $600K of salary may be reasonable. Note: These are not recommendations, just for entertainment purposes.

The bottom line is that based on IRS guidance and court decisions, tuphat and dhodson have the safest and most "reasonable" approaches to the salary for an S-Corp owner primarily providing professional services.

Btuttle,
Thank you for elaborating on this.
Just so I understand your examples clearly:
You mention 140k salary on 350k revenue or 200k salary on 600k revenue...
Here, the revenue is the total revenue including other CPAs or the revenue attributed just to the owner?

And just to confirm:
The owner's compensation includes salary, Employer 401k contributions and Health insurance premiums. Right?

I'm not sure I get your question

When I get survey data it tells me (typically) what they included. I compare apples to apples. I set my salary on the high end of the range. When I say salary I'm not including those additional which is typically how the data is presented.

Hmmm - If the survey data does not include then it may not be apples-to-apples.
It is rare to find companies that contribute 25% of your salary to your 401k.

So if a typical person in your field gets $90k salary + 10% bonus (total $100k) , are you okay to take a salary of $80k + 20k 401k = $100k?

And by the way, based on your input, I looked for salary surveys.
I found payscale.com that gives typical salary information including perks.

That's again why I use more than 1 source to ferret out that info.

I create my salary based on strict salary. So yes for me I'm comparing apples to apples. I use > 75% last time if memory serves correctly so I'm not trying to be on the low range.

So I don't back down my paid salary for other benefits thus I'm "more conservative" then you appear to want to be.

I'm not looking to get the highest possible benefit just what I think is appropriate. Everyone has to determine what they are comfortable with. My main point is I wouldn't try to stretch it too far. If you can't look another person in the eye and say that's what u think is a fair salary then maybe it isn't.

ssgcinty said:   Btuttle,
Thank you for elaborating on this.
Just so I understand your examples clearly:
You mention 140k salary on 350k revenue or 200k salary on 600k revenue...
Here, the revenue is the total revenue including other CPAs or the revenue attributed just to the owner?

Actually, net income exclusive of the owner-employee's salary is probably a better basis than revenue. That is what was referred to in the court decisions. They focused on the ratio of salary to distributions. Don't get hung up on my example numbers, I was simply trying to point out that higher income individuals might be able to have a reasonable salary less than 50%. With a sole owner providing professional services as the only employee, these are pretty close.

For example, doctors in private practice might have a net income substantially higher than a equivalent W-2 salaried doctor at a multi-doctor practice. So they might have a reasonable salary that is 1/3 - 1/2 of their net income. Someone with a full time job and a part time side eBay business might be able to get away with saying a substantial amount of the net income is from return on capital and sales revenue and not based on their labor. They might be able to claim 1/4 of their net income is salary and 3/4 is properly as a distribution of the business's profits.

As to whether health insurance, HSA contributions, and/or employer retirement plan contributions are considered part of compensation or not for inclusion in "reasonable compensation"? Fringe benefits of a >= 2% S-Corp owner are deductible to the corporation, but then are included in box 1 (but not 3 & 5) as compensation and the self-employed health insurance and HSA contributions are taken as an individual. While on the other hand a >= 2% S-Corp owner's employer retirement plan contribution is treated just like any other W-2 employee as a business expense. This is unlike the self-employed where their employer contribution can not be deducted as a business expense and are deducted on the personal 1040 line 28.

The IRS considers S-Corp owner's health insurance premiums and HSA contributions as W-2 box 1 wages, but considers retirement plan contributions as business expenses. I do not think it is a coincidence that the IRS has addressed both Wage Compensation for S Corporation Officers and Medical Insurance Premiums treated as wage in FS-2008-25 and S Corporation’s Contributions to a 2-Percent Shareholder-Employee’s HSA in Notice 2005-8
 



Disclaimer: By providing links to other sites, FatWallet.com does not guarantee, approve or endorse the information or products available at these sites, nor does a link indicate any association with or endorsement by the linked site to FatWallet.com.

Thanks for visiting FatWallet.com. Join for free to remove this ad.

While FatWallet makes every effort to post correct information, offers are subject to change without notice.
Some exclusions may apply based upon merchant policies.
© 1999-2017