Useful HR Formulas

Use metrics to monitor the health of your workforce. Some of the most important and most difficult figures to calculate are those that involve human capital. Here's a list of Excel formulas to give you the boost you need!

Annualized Turnover

=(Ending headcount – Starting headcount) / Average Headcount ) * (12 / # of months remaining in the year)

Length of Service
a. Column A – Name
b. Column B – Date of Hire
c. Column C – Date of Termination
d. Length of Service = (C2 – B2)/365
e. Immediately Select Format -> Number -> 1 Decimal Place

Headcount Tally Shortcut
When faced with a long list of employee names to count, simply sort them by department, use the Autofilter function to filter them by type. The display in the bottom right corner should show a count of the highlighted cells. If not, right click on that area in the bottom right of the screen and change the calculation method to “Count”.

Human Capital Cost
Average pay per regular full-time employee. To have a more comprehensive idea of the cost of doing business, it would be best to consider the cost of contingent labor. Take the cost of consultant fees and total expenses and divide this by the number of employees and contingents in your workforce. The formula is:
=(Pay + Benefits + Contingent Labor Cost) / Full-Time Equivalents

Human Capital ROI
Measures the return on expenses of pay and benefits. The formula is:
=((Gross Revenue) – (Non-Employee Related Expenses)) /
(Total Compensation for FTE and contingent workers with benefits)