Build a Headcount and Attrition Report in Excel with Python

"How many people do we have, and how many are we losing?" sounds like a simple count until you try to answer it from an HR export. Headcount changes every day, so it has to be measured at a point in time; attrition is a rate, so it needs a denominator; and the answer everyone argues about depends on whether a transfer, a contractor or a leaver on the last day of the month counts. A Python script makes those definitions explicit and computes the report the same way every month. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

hr_export.xlsx is one row per employment: employee ID, department, contract type, hire date, and for people who have left, the leave date and whether it was voluntary or involuntary. Current staff have an empty leave date. The report covers September 2025 to August 2026.

Definitions first

Write the definitions down before counting anything; most disagreements about HR numbers are disagreements about these:

• Headcount is the number of people employed at the end of the month: hired on or before that day and not yet left. A leaver whose last day is the 31st is still counted on the 31st.
• Hires and leavers in a month are people whose hire date or leave date falls in it.
• Monthly attrition is leavers divided by average headcount, the mean of the opening and closing headcount. Dividing by the closing figure alone overstates attrition in a shrinking team.
• Rolling 12-month attrition is the sum of leavers over the last twelve months divided by the average headcount over those months. It is the number HR benchmarks use, and it smooths out one bad month.

Step 1 — Headcount at each month end

For each month end, count the rows active on that day. A comparison per month is clear and fast enough for tens of thousands of employees:

import pandas as pd

staff = pd.read_excel("hr_export.xlsx", parse_dates=["HireDate", "LeaveDate"])
month_ends = pd.date_range("2024-08-31", "2026-08-31", freq="ME")   # two years, for the rolling rate

def headcount_on(day, df=staff):
    active = (df["HireDate"] <= day) & (df["LeaveDate"].isna() | (df["LeaveDate"] >= day))
    return int(active.sum())

headcount = pd.Series([headcount_on(d) for d in month_ends], index=month_ends.to_period("M"))
print(headcount.tail(4).to_string())

LeaveDate >= day is the "still counted on their last day" rule. Change it to > and anyone leaving on a month end disappears from that month's headcount; either is defensible, but pick one and keep it.

Step 2 — Hires, leavers and the monthly rate

months = headcount.index
hires = staff["HireDate"].dt.to_period("M").value_counts().reindex(months, fill_value=0)
leavers = staff["LeaveDate"].dropna().dt.to_period("M").value_counts().reindex(months, fill_value=0)
opening = headcount.shift(1)

report = pd.DataFrame({
    "Opening": opening,
    "Hires": hires,
    "Leavers": leavers,
    "Closing": headcount,
})
report["Avg headcount"] = (report["Opening"] + report["Closing"]) / 2
report["Attrition %"] = report["Leavers"] / report["Avg headcount"] * 100
report = report.iloc[1:]                      # the first month has no opening figure
print(report.tail(6).round(1).to_string())

Opening plus hires minus leavers should equal closing. It almost does: a leaver on the last day of the month is counted at that month end and removed in the next, so the identity is off by one in that month and off by one the other way in the following month. Check that this is the only difference rather than assuming it:

month_end_days = report.index.to_timestamp(how="end").normalize()
on_last_day = staff["LeaveDate"].isin(month_end_days)
last_day_leavers = on_last_day.groupby(staff["LeaveDate"].dt.to_period("M")).sum()
ldl_this = last_day_leavers.reindex(report.index, fill_value=0)
ldl_prev = last_day_leavers.reindex(report.index - 1, fill_value=0).values
gap = report["Opening"] + report["Hires"] - report["Leavers"] - report["Closing"]
print("months with a gap:", {str(m): int(g) for m, g in gap[gap != 0].items()})
print("months not explained by last-day leavers:", int(((gap + ldl_this - ldl_prev) != 0).sum()))

Step 3 — The rolling twelve-month rate

Monthly rates jump around; a team of twenty losing two people in one month shows 10%. The rolling rate is what to put in front of management:

rolling = pd.DataFrame({
    "Leavers 12m": report["Leavers"].rolling(12).sum(),
    "Avg headcount 12m": report["Avg headcount"].rolling(12).mean(),
})
rolling["Attrition 12m %"] = rolling["Leavers 12m"] / rolling["Avg headcount 12m"] * 100
last12 = rolling.dropna()
print(last12.tail(4).round(1).to_string())

The first rolling value needs twelve months of history, which is why the headcount started two years back: the report shows September 2025 to August 2026, each with a full year behind it.

Step 4 — Why people leave, and how soon

The rate says how many; the breakdown says where to look. Split the last twelve months' leavers by reason, by department, and by tenure at exit. People who leave in their first year point at hiring or onboarding rather than pay or management:

window = staff[staff["LeaveDate"].between("2025-09-01", "2026-08-31")].copy()
window["Tenure (years)"] = (window["LeaveDate"] - window["HireDate"]).dt.days / 365.25
window["First year"] = window["Tenure (years)"] < 1

print(window["LeaveReason"].value_counts().to_string())
print(f"left within their first year: {window['First year'].sum()} of {len(window)} "
      f"({window['First year'].mean():.0%})")

Department rates need their own denominators: leavers in the department over the department's average headcount. Reuse the headcount function on each department's rows:

def dept_rate(df):
    ends = pd.date_range("2025-08-31", "2026-08-31", freq="ME")
    hc = pd.Series([headcount_on(d, df) for d in ends])
    avg = ((hc + hc.shift(1)) / 2).iloc[1:].mean()
    left = df["LeaveDate"].between("2025-09-01", "2026-08-31").sum()
    vol = ((df["LeaveReason"] == "Voluntary") & df["LeaveDate"].between("2025-09-01", "2026-08-31")).sum()
    return pd.Series({"Headcount now": hc.iloc[-1], "Leavers": left,
                      "Attrition %": left / avg * 100, "Voluntary %": vol / avg * 100})

by_dept = staff.groupby("Department")[staff.columns].apply(dept_rate).sort_values("Attrition %", ascending=False)
print(by_dept.round(1).to_string())

Support has the highest rate and every one of its leavers resigned; in Engineering and Finance, half of the leavers were involuntary exits. The counts matter as much as the rates: Support's 16% is three people out of about twenty, and one more or one fewer leaver would move it by five points. Report both, and be wary of ranking small teams on rates alone.

Step 5 — Write the report

One sheet with the monthly table and chart, one with the department breakdown, and one listing the period's leavers for HR business partners to check. Percentages are written as fractions with a percent format, so Excel can chart and sort them:

out = report.join(last12["Attrition 12m %"]).loc["2025-09":]
out.index = out.index.strftime("%b %Y")

with pd.ExcelWriter("headcount_attrition_2026-08.xlsx", engine="xlsxwriter") as xw:
    table = out.assign(**{c: out[c] / 100 for c in ["Attrition %", "Attrition 12m %"]})
    table.to_excel(xw, sheet_name="Monthly", index_label="Month")
    by_dept.assign(**{c: by_dept[c] / 100 for c in ["Attrition %", "Voluntary %"]}) \
        .to_excel(xw, sheet_name="By department")
    window.sort_values("LeaveDate")[["EmployeeID", "Department", "HireDate", "LeaveDate", "LeaveReason", "Tenure (years)"]] \
        .round({"Tenure (years)": 1}).to_excel(xw, sheet_name="Leavers", index=False)

    book, ws = xw.book, xw.sheets["Monthly"]
    pct = book.add_format({"num_format": "0.0%"})
    one_dp = book.add_format({"num_format": "0.0"})
    ws.set_column("A:A", 11)
    ws.set_column("B:E", 9)
    ws.set_column("F:F", 13, one_dp)
    ws.set_column("G:H", 14, pct)
    xw.sheets["By department"].set_column("A:A", 14)
    xw.sheets["By department"].set_column("B:C", 13)
    xw.sheets["By department"].set_column("D:E", 13, pct)

    n = len(table)
    chart = book.add_chart({"type": "column"})
    chart.add_series({"name": "Headcount", "categories": ["Monthly", 1, 0, n, 0],
                      "values": ["Monthly", 1, 4, n, 4]})
    line = book.add_chart({"type": "line"})
    line.add_series({"name": "Rolling 12m attrition", "categories": ["Monthly", 1, 0, n, 0],
                     "values": ["Monthly", 1, 7, n, 7], "y2_axis": True})
    chart.combine(line)
    chart.set_y2_axis({"num_format": "0%"})
    chart.set_title({"name": "Headcount and rolling attrition"})
    ws.insert_chart("J2", chart, {"x_scale": 1.4})
print(table[["Closing", "Attrition 12m %"]].round(3).to_string())

The column-and-line combination puts headcount on the left axis and the rolling rate on the right. Add a Combo Chart with a Secondary Axis in openpyxl covers the same technique with openpyxl; xlsxwriter's combine() with y2_axis does it in two calls.

Common pitfalls

• Counting headcount over a month instead of at a point. "Everyone employed at any time in March" double-counts replacements; measure at the month end.
• Dividing by closing headcount. Use the average of opening and closing, or the rate moves with hiring as well as leaving.
• Transfers as leavers. An internal move changes department, not employment; check whether the export records transfers as a leave and a hire.
• Leave date conventions. Some systems store the last day worked, others the first day not employed. A one-day difference moves people across month ends.
• Small teams. A department of eight losing two people shows 25%; show the counts beside the rates.
• Personal data. The leavers sheet names individuals; restrict who receives it, or send the aggregate sheets only.

Conclusion

Define headcount as people active at each month end, count hires and leavers by the month of their dates, and divide leavers by average headcount for the monthly rate and by the twelve-month average for the rolling rate. Break the last year's leavers down by reason, department and tenure at exit, compute department rates with their own denominators, and write the monthly table, a combined headcount and attrition chart, and the leaver detail to Excel.

Frequently asked questions

How do I calculate employee attrition rate? Divide the number of leavers in the period by the average headcount over the period, usually the mean of opening and closing headcount for a month, or the mean of monthly averages for twelve months.

What is the difference between monthly and annual attrition? Monthly attrition is leavers in one month over that month's average headcount. Annual or rolling 12-month attrition sums twelve months of leavers over the average headcount for those months; it is roughly twelve times a typical monthly rate and is the usual benchmark.

Should headcount include part-time staff? Headcount counts people, so yes. For cost or capacity, report full-time equivalents (FTE) separately by weighting each person by their contracted hours.

How do I count someone who leaves on the last day of the month? Pick a rule and document it. Counting them in that month's closing headcount and as a leaver in the same month is common; the reconciliation between opening, hires, leavers and closing then differs by exactly those people.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance and operations reports.
• Consolidate Timesheets from Many Workbooks — another people report.
• Anonymise Personal Data in an Excel File with Python — before sharing the leaver list.
• Build a Cohort Retention Table from Excel Data — retention by hire cohort.
• Add a Combo Chart with a Secondary Axis in openpyxl — the chart used here.