Track 90-Day New Hire Retention % in Excel
to Improve Hiring Performance

How to Calculate 90-Day New Hire Retention % in Excel

1. Organize the data you need

• Start with a dataset that includes at least two key columns:
  • Hire Date
  • Termination Date
• Other columns like pay rate, lead source, or role can stay in the file, but they are not required for this KPI.
• Each row represents one employee, so each row counts as one hire.

2. Create a hiring cohort column

• Add a new column that groups each employee into the month-end hiring cohort.
• Use the EOMONTH function on the Hire Date column.
• This assigns every hire to the end of their hire month, making it easy to group January hires together, February hires together, and so on.
• Format the results as dates so the cohort month is easy to read.

3. Create a 90-day retained status column

• Add another column to determine whether each employee was retained for 90 days.
• Use nested IF logic, or the IFS function if you prefer.
• The logic in the video works like this:
  • If the Termination Date is blank, mark the employee as Yes
  • If there is a Termination Date and the difference between Termination Date and Hire Date is 90 days or more, mark Yes
  • If the Termination Date is less than 90 days from the Hire Date, mark No
• This gives you a simple retained flag for every employee.

4. Build a unique list of cohort months

• In your summary table, create a list of all hiring cohorts.
• Use the UNIQUE function on the cohort column you created with EOMONTH.
• Format the list as dates so each cohort month displays correctly.
• These cohort months will become the rows in your KPI table.

5. Count total hires by cohort

• Add a Hires column next to the cohort list.
• Use the COUNTIF function to count how many employees belong to each cohort month.
• This gives you the total number of hires made in each hiring cohort.
• Fill the formula down for every month in the summary table.

6. Count retained hires by cohort

• Add a Retained column.
• Use the COUNTIFS function because you need more than one condition:
  • The retained status must equal Yes
  • The cohort month must match the month in your summary table
• Fill the formula down to calculate retained hires for each cohort.
• This shows how many employees in each hiring month made it past 90 days.

7. Calculate the retention percentage

• Add a Percent column.
• Divide the retained count by the total hires count for each month.
• Format the results as percentages.
• This produces the monthly 90-day retention rate by hiring cohort.

8. Add a target line

• Add a Target column to the summary table.
• Enter your chosen benchmark, such as 80% or 85%, in the first row.
• In the rows below, reference the cell above so the same target repeats all the way down.
• This makes the target dynamic, so if you change the first target cell, the entire chart updates automatically.

9. Create the chart

• Highlight the cohort month column, the hires column, the retention percentage column, and the target column.
• Hold Ctrl if needed to select non-adjacent columns.
• Go to Insert and choose Recommended Charts.
• Open All Charts and select Combo to build the chart manually.

10. Set up the combo chart correctly

• Keep Hires as the column series.
• Change both the Retention % series and the Target series to lines.
• Place both lines on the Secondary Axis so percentages are not plotted on the same scale as headcount.
• This creates the same chart style shown in the video:
  • Bars for hires
  • A line for retention percentage
  • A line for the target benchmark

11. Format the chart

• Update the chart title to match the KPI name.
• Adjust the secondary axis number formatting so percentages display cleanly.
• Change the target line color to black so it stands out clearly.
• Reduce the line thickness if needed so it does not dominate the chart.
• Clean up the visual formatting until the chart is easy to read.

12. Use the chart to monitor performance

• Because the target column references the cell above, you can change the benchmark at any time and the chart updates automatically.
• In the final chart:
  • Values below the target line indicate stronger performance
  • Values above the target line indicate weaker performance relative to the goal
• This gives you a quick monthly view of where early retention is meeting expectations and where it is falling short.