Real Cost of an Employee Spreadsheet Calculator
What's an employee REALLY cost? How do you compare a subcontractor’s rate to the hourly rate you pay an employee? What does providing health insurance really add to the bottom line, considering payroll taxes are reduced by pretax deductions? How much does an employee add to your worker’s compensation insurance? You can use this calculator to find out.
What can you do with the employee cost calculator spreadsheet?
- Show an employee the value of their compensation package.
- Use when negotiating a pay increase.
- Compare total employee costs (not just the hourly rate) to subcontractor rates.
- Calculate the minimum billing rate for an employee’s time.
- Budget employee costs more accurately.
- ‘Test-drive’ various scenarios before committing to a new hire or changing your employee benefits package.
The 2013 calculator can be used in 2016; you do NOT need to upgrade to a newer version. All you need to do is update the wage bases in the required benefits section. For 2016, the social security wage base is $118,500. If you are in Massachusetts, the state unemployment (SUTA) wage base went up $15,000 on January 1st, 2015 (previously it was $14,000).
Why not use the free online version?
The Excel calculator includes several more options than the online version. If you don’t need them, stick with the free online version.
- Include bonuses and taxable benefits in addition to wages.
- Include retirement and other pre-tax benefits in addition to health and dental insurance.
- Include up to 4 additional state or county-specific taxes, and/or rename any tax line to use it for pensions or other employer contributions (for example, for government employees)
- Include indirect or overhead expenses.
- Calculate an employee’s fringe benefits percentage, overhead percentage and wage multiplier, as well as the total cost to the company and real hourly rate.
- Save filled-out copies with information about each employee for your records.
Your purchase entitles you to use this calculator yourself, not distribute copies to others. If someone else wants a copy, please direct them to this website. If you want to provide this calculator to your own clients as an affiliate, contact me.
Available in .xls format, compatible with Excel for Mac (97-2004) and Windows (97-2003) or higher, or in .xlsx format for Mac (Excel 2008 or higher) or Windows (Excel 2007 or higher), the calculator is designed to be easy to use. Minimum required information and optional values are color-coded and the rest of the sheet is locked to allow easy tabbing through the form and prevent over-writing the calculations. It includes help text and data validation to reduce errors, and a setting to hide cell borders, color coding and extra text before printing. Note that this is NOT an editable template; it is a locked calculator; if necessary you can customize your output by linking another spreadsheet to the calculator.
How to purchase and download the employee cost calculator:
Reminder: The 2013 calculator can be used in 2015; you do NOT need to upgrade to a newer version. All you need to do is update the wage bases in the required benefits section. For 2015, the social security wage base is $118,500. If you are in Massachusetts, the state unemployment (SUTA) wage base went up $15,000 on January 1st, 2015 (previously it was $14,000).
- Select your version (.xls or .xlsx) and click the Paypal "Buy Now" button.
- You will be taken to the Paypal site, where you can pay with a credit card or Paypal account. If your credit card or Paypal account has a Massachusetts address, sales tax will be added to the purchase price. Before completing the purchase at Paypal, please make sure the email address Paypal uses is current or you will not receive your download link.
- Once you complete your purchase at Paypal and your payment clears, a download link will be sent to the email address you used at Paypal. If you pay with a credit card or existing funds in a Paypal account, this should happen within a few minutes to 1/2 hour, depending on how busy the Paypal site is (it should arrive at about the same time as the receipt Paypal emails you). If you pay with an echeck, it may take several days for the funds to clear.
- Please wait for the email with the download link; do NOT use the "Buy Now" button a second time or you will be charged again. If you think the download link should have arrived, check your spam or junkmail box for the email. If you can’t find it, please whitelist jolanders.com in your email software, and use the contact form to let me know you’ve done that and need the email re-sent. Please include the PayPal transaction number and spreadsheet version you purchased in the message.
- Click on the download link in the email. This will bring you back to this site and automatically download the file to the default download location set in your browser preferences. There is also a download link on the page; before you use it, please check where your browser downloads files because you should only need it if there’s a problem loading the page. Clicking on it multiple times will NOT change where the file is being saved; it will just create multiple copies of the file. Note that the download link expires 24 hours after it was created.
- Go to the downloaded file and unzip it (most users will just need to double-click the file). It should now be ready to use.
Refunds/Returns: No refunds or returns. If you are unable to download the file or you receive a corrupted file, I will happily send you a replacement. If you don’t like the calculator or discover an error, please provide feedback using the contact form on this site. If you suggest something I think would be useful for the majority of users, I may incorporate it into a new version and send you an updated copy. However, I will not refund your original purchase price.
DISCLAIMER: Jo Landers does not guarantee the accuracy of this calculator. I hope you find it useful; however your actual employee costs may be different from those generated by the calculator.
Spreadsheet Calculator Requirements:
- Any Mac or Windows version of Excel (97 or later) capable of opening .xls or .xlsx files.
- Ability to unzip a .zip archive (if your computer won’t do this, there are free programs that will).
- Does not use macros or require VB support (good news for Mac Office 2008 users!).
The calculator includes help and uses data validation to reduce errors. All help text, color coding and box borders can be hidden before printing. Input cells are color-coded to make data entry easier, and all cells except those intended for data entry are locked to allow easy tabbing through the calculator and prevent a user "breaking" it (for example, by typing over a formula). The password to unlock cell protection is not provided; however, if you need to customize the output, you can.
If the employee is on salary, you can use the built-in salary to hourly converter to get an hourly rate.
- Enter the employee's hourly rate and average hours per week.
- Enter the number of hours per week your business considers full-time.
Annual Wages & Taxable Benefits
The calculator will compute working and non-working hours, show percentages and costs for each, and add any additional taxable compensation or benefits to wages.
- Enter information about paid time off, including paid breaks, vacation, holidays, sick time, etc.
- Enter information about paid non-working time, including paid training, travel or on-call time.
- Enter taxable compensation the employee receives in addition to wages, such as bonuses, use of company car, or excess childcare reimbursements.
Optional Benefits in Addition to Wages
The calculator will show the company and employee cost for each benefit, and calculate taxable wages after pre-tax deductions for payroll taxes.
- Enter the monthly cost and company contribution to health and dental insurance.
- Enter the monthly payment, if any, you receive from the Massachusetts Insurance Partnership (or if your state has a similar program) for the employee (IP payments reduce an employee’s pre-tax deductions, increasing your payroll tax expense).
- Enter the dollar amount or percent of wages the employee contributes to a retirement plan.
- Enter the dollar amount, percent of wages or matching percentage the company contributes to a retirement plan.
- Enter other nontaxable benefits, such as nontaxable childcare or tuition reimbursements, commuter passes, or de miniumus fringe benefits which do not need to be included as pay.
The calculator will compute and show the company cost for each required benefit.
- The calculator includes lines to calculate Social Security, Medicare, FUTA, State Unemployment Insurance and Worker's Compensation Insurance, and up to four additional taxes or required benefits
- You can rename any tax line (for example, if you pay into a pension fund instead of FICA), and change the contribution rate and wage base as needed.
- Social Security, Medicare and FUTA are calculated using the employee's wages after pre-tax deductions. The spreadsheet includes one extra line for calculations based on wages after pre-tax deductions.
- There are four lines which calculate based on total wages, up to the wage cap entered. These can be used for state unemployment insurance, state workforce training fund, etc. (if you don't know your State UI rate or wage base, contact your state unemployment agency).
- Worker's compensation is calculated using the employee's total wages and rate you enter (if you don't know the rate for this employee, check your policy or ask your insurance agent). You can override the wages used for calculating worker's compensation insurance if the employee is subject to a minimum or maximum wage amount regardless of actual wages (for example, a company officer) by entering the amount to use for calculations. If you are in a state that calculates WC based on hours worked instead of wages paid, override the base wage and enter the hours instead, or multiple the WC rate by the employee&rsqo; hourly rate to convert it to the equivelant rate for their wages.
The calculator allows you to enter up to three line items for indirect expenses, such as the cost of office space, tools or equipment. This is used when calculating the employee's total real cost.
The summary section provides the following information:
- Annual wages for work - the cost for all working time, excluding paid time off or paid non-working hours.
- Optional Benefits - total and cost for paid time off, health and dental insurance, retirement, and other benefits.
- Required Benefits - total and cost for each required benefit or payroll tax
- Other Expenses - total and cost for paid non-working time, other included in wages (such as bonuses), and indirect expenses.
- Total annual cost for the employee, with all expenses included. Use for budgeting and planning.
- Fringe benefits cost and percentage, and hourly rate after fringe benefits have been included. Use for showing an employee what the total value of their compensation is.
- Other expenses cost and percentage.
- A wage multiplier and real hourly cost after all expenses are included. Useful for calculating a minimum rate (before profit) if billing the employee's time to others.
State-specific versions of the employee cost calculator:
If your state has specific requirements not covered by the calculator, please let me know. I’ve tried to create a calculator that can be used in every state, but I may not be aware of a change in your state.
The calculator is fine as-is for most people. However, if you need to customize your output, you can, by linking another spreadsheet to the calculator while leaving the original intact. Use your spreadsheet to display just the results you want from the employee cost calculator and add any additional calculations you need. You can also customize the format, add a logo, or do anything else you want, and, if necessary, create your own summary section, based on any new information or calculations you’ve added, while leaving the original intact.
The password for the employee cost calculator is NOT provided, for several reasons:
- When (not if) someone unlocks the calculator and types over a formula by accident, they will “break” it. This was a major customer service issue (people expected a replacement) until I started offering it only as a locked calculator, and not as an editable template.
- It is fairly complex, with most calculations or data validation dependant on entries in multiple cells, or other, “chained” calculations. Trying to customize it - without knowing how modifying an existing formula or inserting extra rows or columns will affect the rest of the calculations - makes errors likely. If my copyright is intact, a modified calculator that isn’t accurate is detrimental to my reputation, while an accurate version I don’t have available for sale becomes a customer service issue.
- Finally, when it was unlocked, people removed my copyright and attempted to sell or distribute the calculator as if they created it themselves.