Cohort analysis in HRM: tenure, retention, and cost

Cohort analysis in HRM compares hiring groups at the same tenure, using aggregate retention and cost measures. Keep the original hiring population fixed, wait until each observation window is complete, and reconcile the result to the workforce and payroll summaries. The report answers a planning question about groups rather than predicting who will leave.

Define the population and the observation point

Start with a cohort based on hire month. A US business follows first-time employees in their original employment spell, with no rehires in the report. The reporting boundary is the whole enterprise, so an internal transfer does not create a new hire or end employment retention.

The outcome is remaining continuously employed at the 90-day anniversary of hire. Someone who has departed by that anniversary remains in the original denominator and counts as a departure.

This is distinct from calendar-period workforce movement. A Federal Reserve staff analysis of worker churn records monthly hires and separations separately from the employment count. Following original hires to a tenure anniversary requires its own fixed population.

Agree on cost scope with payroll. Here it means gross payroll earned during each employee's first 90 calendar days, from hire date through hire date plus 89 days, including earnings before an early departure. Employer taxes, benefits, severance, and recruiting costs are outside this particular measure. Label it first-90-day payroll rather than total employment cost.

Obtain an aggregate workforce summary

The workforce owner prepares one row per hire month. It contains original hires, the latest hire date, employees retained at the anniversary, departures by that point, and payroll earned during the defined window.

The example below is complete through May 31, 2026. March is still incomplete because its latest hire reaches the anniversary on June 29. January's latest hire reaches it on May 1, and February's on May 29. A dash means the worksheet cell is empty.

Place these input fields in A1:F4. Dates must be actual spreadsheet dates.

These are illustrative aggregate records. Employees hired earlier in March may already have some usable observations, but this report waits for the whole monthly cohort. That choice keeps each displayed monthly denominator fixed and understandable.

Calculate retention and cost in Excel

Put May 31, 2026, in L1 as the complete-through date. Add G for maturity, H for retention, and I for payroll per original hire. Enter these formulas in G2, H2, and I2, respectively, then fill down:

=IF($L$1>=B2+90,1,0)
=IF(G2=1,D2/C2,"")
=IF(G2=1,F2/C2,"")

90-day retention = Original hires retained at the 90-day anniversary / Original hires in mature cohorts

First-90-day payroll per original hire = Payroll earned in the first 90 days / Original hires in mature cohorts

The pooled formulas use the maturity flag for both numerator and denominator:

=SUMIFS(D2:D4,G2:G4,1)/SUMIFS(C2:C4,G2:G4,1)
=SUMIFS(F2:F4,G2:G4,1)/SUMIFS(C2:C4,G2:G4,1)

Microsoft documents SUMIFS as adding values that meet specified criteria. Here it excludes the unfinished March row consistently.

Retained employees total 34 + 40 = 74, against 40 + 50 = 90 original hires. Retention is 74 / 90 = 82.2%. Payroll is $720,000 + $950,000 = $1,670,000, or $1,670,000 / 90 = $18,555.56 per original hire. Percentages are rounded to one decimal and per-hire costs to cents.

Prove the counts and the payroll

January reconciles as 34 retained + 6 departed = 40 original hires; February as 40 + 10 = 50. Together, 74 retained + 16 departed = 90 mature hires. The full report population is 40 + 50 + 30 = 120 hires, separating into 90 mature and 30 immature hires.

Tie original hires to the scoped workforce register. Tie retained and departed counts to employment-status records at each employee's anniversary. An unresolved status needs investigation; it must not silently become a departure.

Tie the $1,670,000 to payroll detail for those employees and their individual service windows. Reconcile that subtotal to the corresponding ledger accounts with timing and accrual differences explained. It will not equal an entire calendar quarter's payroll, because the report follows service age across different dates.

The same denominator discipline appears in reading a retention table. The customer-focused spreadsheet retention guide supplies the general table method, while the workforce definitions above control this report.

Use group comparisons for workforce planning

February retention is 5.0 percentage points below January, while its payroll per original hire is higher. HR and finance should first check job mix, scheduled hours, and payroll definitions before attributing the difference to a program. Neither aggregate comparison measures individual performance or explains a departure's cause.

Use the report to review staffing assumptions and the payroll needed over an employee group's first service period. Keep distribution limited to the aggregate level needed for that job, combining small groups under the organization's reporting policy. Behavioral cohort analysis covers the different question of grouping customers by prior actions.

Where it goes wrong

• Incomplete tenure. Recent hires cannot be assigned a completed anniversary outcome.
• A changing denominator. Removing leavers inflates retention and distorts payroll per original hire.
• Payment date instead of earning period. A payroll paid later can belong inside the service window.
• A narrower boundary. Treating an internal transfer as an enterprise departure changes the question.
• Cost mistaken for productivity. Payroll per hire is an expense measure; it does not establish output or value.

Covirage is not affiliated with Microsoft.

Review your workforce summary in Covirage

Bring a sample of your own aggregate workforce summary with hire cohorts, maturity dates, status counts, and payroll totals. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the file; creating the account takes about a minute.

See capacity planning for the supporting business planning context.