✦Template Printable Ideas Insights

Calculate Cost per Hire Formula in Excel

By

Calculating the "cost per hire" (CPH) is a critical metric for HR departments and recruitment agencies to assess the efficiency and effectiveness of their hiring processes. Excel, with its powerful calculation and data management features, is the go-to tool for crunching these numbers. However, to optimize your CPH formula in Excel, you'll need to understand the components and best practices.

Before we dive into the formula, let's ensure we're on the same page with the cost components. These typically include: job advertising, recruitment software, headhunter fees, internal administrative costs, and onboarding expenses.

Calculating Direct Costs

The direct costs are the expenses that you incur specifically for a particular hire. These are typically variable costs that scale with the number of hires.

Download Cost Per Hire Calculator Excel Template - ExcelDataPro
Download Cost Per Hire Calculator Excel Template - ExcelDataPro
The Secret to Streamlining Your HR Process with 10 Free Templates! | Process Street | Compliance Operations Platform
The Secret to Streamlining Your HR Process with 10 Free Templates! | Process Street | Compliance Operations Platform
15+ Excel Formulas Every HR Pro Should Know
15+ Excel Formulas Every HR Pro Should Know

Job advertising and recruitment software are two of the most significant direct costs. platform such as LinkedIn or Indeed, the cost can vary greatly depending on the package you choose.

Job Advertising Costs

To calculate the direct cost of job advertising, you'll need to know the cost per job posting and the number of times you've advertised the role.

an invoice form with the words job cost sheet
an invoice form with the words job cost sheet
Job Cost Calculator Excel | Small Business Profit Tracker (Digital Download)
Job Cost Calculator Excel | Small Business Profit Tracker (Digital Download)
SUS - Excel Formula Cheat Sheet For Finance Professionals 🏦 #excelformulas #ExcelFunctions #finance #accounting | Facebook
SUS - Excel Formula Cheat Sheet For Finance Professionals 🏦 #excelformulas #ExcelFunctions #finance #accounting | Facebook

For example, if you've advertised a role on LinkedIn at a cost of $400 per post and you've done so 3 times, your job advertising cost would be: 400 * 3 = 1200

Recruitment Software Costs

Recruitment software like Workable or Lever can streamline your hiring process but comes at a cost. These tools usually charge a subscription fee that varies based on the number of users or jobs.

For instance, if you're using Lever and paying $950 per month for their Professional plan, your software cost for 3 months would be: 950 * 3 = 2850

Excel Formula Cheat Sheet Printable - Excel Functions Guide PDF - Excel Reference Sheet Digital Download
Excel Formula Cheat Sheet Printable - Excel Functions Guide PDF - Excel Reference Sheet Digital Download
Top 25 Basic Excel Formulas Every Beginner Must Know
Top 25 Basic Excel Formulas Every Beginner Must Know
the top 2 excel formulas are in this document, and it's also available for
the top 2 excel formulas are in this document, and it's also available for
Essential Excel Formulas for Finance Experts 🏦
Essential Excel Formulas for Finance Experts 🏦
the basic guide to excelif formulas for beginners and advanced students in english
the basic guide to excelif formulas for beginners and advanced students in english
7 Excel Formulas Every Finance Professional Should Master πŸ’ΌπŸ“Š
7 Excel Formulas Every Finance Professional Should Master πŸ’ΌπŸ“Š

Calculating Indirect Costs

Indirect costs are overhead expenses that support the hiring process but aren't tied to a specific hire. These often include the time spent by your hiring team and any associated administrative costs.

For instance, if your HR team spends 8 hours on admin tasks per hire and you pay your HR team $50 per hour, your indirect cost per hire would be: 50 * 8 = 400

Onboarding costs can also be considered indirect costs. These might include training materials, new equipment, or temporary salary advances.

Exel Formulas
Exel Formulas
Contractor Job Costing Spreadsheet | Installation Pricing Calculator | Labour, Travel & Profit Estimator
Contractor Job Costing Spreadsheet | Installation Pricing Calculator | Labour, Travel & Profit Estimator
Format of Cost Sheet - HONOURS / REGULAR - Free PDF Download 2026
Format of Cost Sheet - HONOURS / REGULAR - Free PDF Download 2026

Internal Hiring Team Costs

Your internal hiring team's time is a valuable resource. To calculate their cost, you'll need to know their hourly rate and the time they spend on each hire.

For example, if your hiring manager charges back their time at a rate of $100 per hour and spends 4 hours on each hire, their cost contribution would be: 100 * 4 = 400

Onboarding Costs

Onboarding costs can vary greatly depending on the role and the company. Some roles might require expensive equipment or extensive training, while others might not.

Let's say you're onboarding a new software developer who needs a new laptop, a design software license, and 2 weeks of training. The total onboarding cost could be something like: 2000 (laptop) + 500 (software) + 200 (training) = 2700

Putting It All Together

Now that you've calculated your direct and indirect costs, you can formula for cost per hire in Excel. The formula is simply the total cost divided by the number of hires.

For example, if your total cost for a particular role was $10,000 and you made 5 hires, your cost per hire would be: =10000/5 = 2000

Regularly reviewing and updating your CPH formula can help you make data-driven decisions about your hiring process. It can guide you to optimize your recruitment strategies, identify cost-saving opportunities, and ensure you're getting the best return on investment for your hiring efforts.

So, go ahead, optimize your Excel CPH formula, and happy hiring!