Employee attrition by tenure cohort: annualised rates that add up

The HR head's question is about when people leave

"Our attrition is 30%" is not actionable. The CHRO, or the delivery head of a services business, needs to know whether people leave in the first months (an onboarding or hiring-fit problem), around the one-to-three-year mark (a growth and compensation problem), or after long tenure (a leadership or role problem). Each has a different owner and fix. So the report should answer: in which tenure band is our attrition concentrated, and is it voluntary?

Data you will get from the HRMS

Before any rate: remove intra-group transfers from exits, decide whether contract staff and interns are in scope, and confirm whether a rehire's tenure restarts.

The exposure method

For each calendar month in the financial year:

• An employee is at risk in a month if they joined before the month ended and had not exited before it began.
• Their tenure band is measured at the start of that month (for a joiner in that month it is 0).
• Each at-risk employee contributes one person-month to their band.
• A leaver is counted in the band they were in during their exit month.

Then, per band:

• Annualised attrition % = leavers ÷ person-months × 12 × 100

Across all bands: person-months ÷ 12 is the average headcount, and the company rate is leavers ÷ average headcount.

Run it on a synthetic business unit

Twenty-four employees, FY 2025-26 (April 2025 to March 2026), synthetic.

-- Synthetic employee master for one business unit
CREATE TABLE employees (
  emp_id TEXT, join_date TEXT, exit_date TEXT, exit_type TEXT
);
INSERT INTO employees VALUES
 ('E01','2019-06-10',NULL,NULL),
 ('E02','2020-02-03',NULL,NULL),
 ('E03','2021-08-16','2025-11-30','voluntary'),
 ('E04','2022-01-10',NULL,NULL),
 ('E05','2022-07-04',NULL,NULL),
 ('E06','2023-03-13','2025-06-30','voluntary'),
 ('E07','2023-09-18',NULL,NULL),
 ('E08','2024-01-08','2025-08-14','voluntary'),
 ('E09','2024-04-15',NULL,NULL),
 ('E10','2024-06-03','2026-01-31','involuntary'),
 ('E11','2024-11-11',NULL,NULL),
 ('E12','2025-01-06','2025-07-31','voluntary'),
 ('E13','2025-02-17',NULL,NULL),
 ('E14','2025-04-07','2025-06-20','voluntary'),
 ('E15','2025-04-07','2025-09-12','voluntary'),
 ('E16','2025-04-21',NULL,NULL),
 ('E17','2025-05-12','2025-12-05','voluntary'),
 ('E18','2025-07-01',NULL,NULL),
 ('E19','2025-08-18','2025-10-31','voluntary'),
 ('E20','2025-09-01',NULL,NULL),
 ('E21','2025-10-06','2026-03-13','voluntary'),
 ('E22','2025-11-03',NULL,NULL),
 ('E23','2026-01-05',NULL,NULL),
 ('E24','2026-02-02',NULL,NULL);

-- Month starts of FY 2025-26 (April 2025 to March 2026)
WITH RECURSIVE months(m) AS (
  SELECT '2025-04-01'
  UNION ALL SELECT date(m, '+1 month') FROM months WHERE m < '2026-03-01'
),
band_of AS (
  SELECT e.emp_id, e.exit_type, m.m,
         CASE WHEN e.exit_date >= m.m AND e.exit_date < date(m.m, '+1 month')
              THEN 1 ELSE 0 END AS left_this_month,
         (julianday(m.m) - julianday(e.join_date)) / 30.44 AS tenure_months
  FROM employees e JOIN months m
    ON e.join_date < date(m.m, '+1 month')
   AND (e.exit_date IS NULL OR e.exit_date >= m.m)
),
banded AS (
  SELECT *, CASE WHEN tenure_months < 6 THEN '1: 0-6 months'
                 WHEN tenure_months < 12 THEN '2: 6-12 months'
                 WHEN tenure_months < 36 THEN '3: 1-3 years'
                 ELSE '4: 3+ years' END AS tenure_band
  FROM band_of
)
SELECT tenure_band,
       COUNT(*) AS person_months,
       SUM(left_this_month) AS leavers,
       SUM(CASE WHEN left_this_month = 1 AND exit_type = 'voluntary' THEN 1 ELSE 0 END) AS voluntary,
       ROUND(100.0 * 12 * SUM(left_this_month) / COUNT(*), 1) AS annualised_pct
FROM banded
GROUP BY tenure_band
ORDER BY tenure_band;

The bands reconcile: 190 person-months is an average headcount of 15.8, and 10 leavers on that is an annualised rate of about 63% for the unit. Small units like this one swing a lot on single exits, which is exactly why the next section matters.

Compare it with the shortcut

The common spreadsheet approach counts leavers by their tenure on exit and divides by the 31 March headcount in each band:

-- The shortcut: leavers by tenure at exit / 31-March headcount by tenure
WITH band AS (
  SELECT emp_id, exit_date,
         (julianday(COALESCE(exit_date, '2026-03-31')) - julianday(join_date)) / 30.44 AS t
  FROM employees
  WHERE join_date <= '2026-03-31'
    AND (exit_date IS NULL OR exit_date >= '2025-04-01')
),
b AS (
  SELECT *, CASE WHEN t < 6 THEN '1: 0-6 months' WHEN t < 12 THEN '2: 6-12 months'
                 WHEN t < 36 THEN '3: 1-3 years' ELSE '4: 3+ years' END AS tenure_band
  FROM band
)
SELECT tenure_band,
       SUM(exit_date IS NOT NULL) AS leavers,
       SUM(exit_date IS NULL) AS headcount_31_mar,
       ROUND(100.0 * SUM(exit_date IS NOT NULL) / SUM(exit_date IS NULL), 1) AS naive_pct
FROM b GROUP BY tenure_band ORDER BY tenure_band;

It reports 133.3% attrition for the 0–6 month band: four leavers against three people in the band on 31 March. The three survivors are simply the most recent joiners; everyone who joined earlier in the year and stayed has aged into the next band, so the denominator is tiny. The exposure method uses 66 person-months instead and gives 90.9%. Still high, but now a real rate.

(The two methods also band some leavers differently: E12 was under six months tenure when July began but over six at exit. Pick one convention and state it.)

A joiner-cohort view for the hiring team

Rates by band answer "where"; a cohort view answers "how quickly". Take everyone who joined in a quarter and report the share still employed at 3, 6 and 12 months. In this data, four people joined in April and May 2025 (E14 to E17); two had left within six months of joining. With cohorts this small, show the counts, not just the percentage.

Where tenure attrition numbers go wrong

• Transfers counted as exits. Moving from one group entity to another often creates an exit and a new join in the HRMS. Remove them or attrition doubles-counts internal mobility.
• Resignation date versus last working day. Under a 90-day notice period, a resignation in March becomes an exit in June. Reports using exit date lag the resignation trend by a quarter; use resignation date for early warning.
• Absconding cases. Employees who stop turning up may be marked as exits months later. Backdate them to the last working day.
• Involuntary mixed with voluntary. A performance-management exit is an HR decision, not a retention failure. Report both but lead with voluntary.

How to put it in front of the CHRO

Lead with voluntary annualised attrition by band, with person-months shown so small bands are visibly small. One sentence: "Most exits are in the first six months, all voluntary; that points to hiring fit and onboarding, not pay." Keep individual names out of the deck; attrition data is personal data and a band view is enough to act on.

The recursive-calendar trick here is useful far beyond HR; it is part of the SQL practised in the Data Analyst programme. If you are from an HR background yourself, the skill check will show what to learn first.