Measuring employee attrition is one of the most important tasks for HR teams. Yet, many HR managers struggle with applying the correct attrition formula.
If attrition is calculated incorrectly, it can mislead workforce planning, increase recruitment costs, and even hide underlying retention issues. For example, if a company only considers exits at year-end without factoring in average workforce size, the attrition percentage can look inflated.
Among sectors, traditional or “old economy” industries in India like manufacturing, automobiles, metals & mining generally have much lower attrition rates compared to new-age / tech / services sectors.For example, engineering ~14%, chemicals ~12.9%, automobiles ~12.4%, metals & mining ~8.6%. Meanwhile, IT / e-commerce / hi-tech / professional services have attrition rates in the 20-25% range.
This guide explains attrition formulas, step-by-step calculation methods, Excel-ready examples, and HR use cases.
What is Attrition Rate?
In HR terms, attrition rate is the percentage of employees who leave an organization during a specific period compared to the average number of employees in that period.
Attrition vs Turnover
While both terms are often used interchangeably, they have subtle differences:
- Attrition → Employees leave, and the company may not replace them (retirement, redundancy, voluntary exit).
- Turnover → Employees leave, and the company replaces them (resignations, backfilling roles).
In practice, HR teams calculate both to understand workforce shrinkage and mobility.
The Basic Attrition Formula
The most widely used formula is:
Attrition Rate (%) = (Number of Exits during Period ÷ Average Number of Employees) × 100
Where:
- Number of Exits → Employees who left (resignations, retirements, terminations).
- Average Number of Employees → (Opening headcount + Closing headcount) ÷ 2.
Example Calculation
- Exits in 2024: 12 employees
- Opening headcount: 240, Closing headcount: 240 → Average = 240
- Attrition Rate = (12 ÷ 240) × 100 = 5%
This means 5% of the workforce left during the year.
Types of Attrition Formulas
Monthly Attrition Formula
Useful for short-term HR reporting and spotting early trends.
Monthly Attrition Formula
Attrition percentage for a month compares how many people exited in that month to the average number of employees for the same month.
Formula
Attrition % (Monthly) = Exits ÷ ((Opening Headcount + Closing Headcount) ÷ 2) × 100
Worked Example
| Month | Opening Headcount | Closing Headcount | Avg. Employees | Exits | Attrition % |
|---|---|---|---|---|---|
| Jan | 500 | 495 | 497.5 | 5 | 1.00% |
| Feb | 495 | 490 | 492.5 | 7 | 1.42% |
| Mar | 490 | 485 | 487.5 | 3 | 0.62% |
Excel Ready Formula (Copy and paste)
Put Opening in column B, Closing in C, and Exits in E. Use this in F2 and copy down:
=IFERROR((E2/((B2+C2)/2))*100,0)
Format column F as Percentage with 2 decimals.
Monthly Attrition Calculator
- Use average headcount for the month not just the opening or closing figure.
- Separate voluntary and involuntary exits if you track sub-metrics.
- For multi-site teams, calculate at team level then roll up to the company view.
Quarterly Attrition Formula
Quarterly attrition compares total exits in a quarter to the average number of employees across the quarter.
Formula
Attrition % (Quarterly) = Quarterly Exits ÷ ((Quarter Opening Headcount + Quarter Closing Headcount) ÷ 2) × 100
Tip: If you track monthly averages inside the quarter, you can also use (Jan Avg + Feb Avg + Mar Avg) ÷ 3 as the denominator for Q1.
Worked Example
| Quarter | Opening Headcount | Closing Headcount | Avg. Employees | Exits (Quarter) | Attrition % |
|---|---|---|---|---|---|
| Q1 | 500 | 492 | 496.0 | 13 | 2.62% |
| Q2 | 492 | 488 | 490.0 | 9 | 1.84% |
| Q3 | 488 | 482 | 485.0 | 11 | 2.27% |
Excel Ready Formula (Copy and paste)
Put Quarter Opening in column B, Quarter Closing in C, and Quarterly Exits in E. Use this in F2 and copy down:
=IFERROR((E2/((B2+C2)/2))*100,0)
Alternative using monthly averages for the quarter (Jan Avg in G2, Feb Avg in H2, Mar Avg in I2):
=IFERROR((E2/AVERAGE(G2:I2))*100,0)
Format the result column as Percentage with 2 decimals.
Quarterly Attrition Calculator
- Use quarter opening and closing headcount for a simple average. If you have monthly averages, average those for higher precision.
- Separate voluntary and involuntary exits if you track them.
- For multi-site teams, calculate per business unit and then roll up to an enterprise view.
Annual Attrition Formula
Annual attrition compares total exits in a year to the average number of employees across the year.
Formula
Attrition % (Annual) = Annual Exits ÷ ((Year Opening Headcount + Year Closing Headcount) ÷ 2) × 100
Tip: If you track monthly averages, you can use (Avg Jan + Avg Feb + ... + Avg Dec) ÷ 12 as the denominator for higher precision. You can also average quarterly headcount (Q1 Avg + Q2 Avg + Q3 Avg + Q4 Avg) ÷ 4.
Worked Example
| Year | Opening Headcount | Closing Headcount | Avg. Employees | Exits (Year) | Attrition % |
|---|---|---|---|---|---|
| 2023 | 480 | 495 | 487.5 | 52 | 10.67% |
| 2024 | 495 | 505 | 500.0 | 45 | 9.00% |
| 2025 | 505 | 498 | 501.5 | 38 | 7.58% |
Excel Ready Formula (Copy and paste)
Put Year Opening in column B, Year Closing in C, and Annual Exits in E. Use this in F2 and copy down:
=IFERROR((E2/((B2+C2)/2))*100,0)
Alternative using monthly averages for the year (Avg Jan in G2 ... Avg Dec in R2):
=IFERROR((E2/AVERAGE(G2:R2))*100,0)
Format the result column as Percentage with 2 decimals.
Quick Annual Attrition Calculator
- Use opening and closing headcount for a simple annual average. For precision, average monthly or quarterly headcount.
- Separate voluntary and involuntary exits if you track sub-metrics like regrettable attrition.
- For multi-site or multi BU organizations, calculate at unit level and roll up to an enterprise view.
Rolling 12-Month Attrition Formula
Rolling 12-month attrition sums exits over the most recent 12 months and divides by the average headcount over the same window. It smooths seasonality and shows the true trend.
Formula
Attrition % (Rolling 12-Month) = Exits in Last 12 Months ÷ Average Employees in Last 12 Months × 100
Define the 12-month window as the current month plus the previous 11 months. Recalculate each month by dropping the oldest month and adding the newest.
Worked Example (illustrative)
| Window (Last 12 Months Ending) | Sum of Exits (12 mo) | Avg. Employees (12 mo) | Rolling 12-M Attrition % |
|---|---|---|---|
| Jun 2025 | 58 | 492.0 | 11.79% |
| Jul 2025 | 56 | 493.5 | 11.35% |
| Aug 2025 | 54 | 495.0 | 10.91% |
Excel Ready Formula (Copy & paste)
Suppose your monthly table has Date (A), Exits (B), and AvgHeadcount (C), oldest at row 2:
-
In D13 (first full 12-month window), then copy down:
=IFERROR( (SUM(B2:B13) / AVERAGE(C2:C13)) * 100 , 0 ) -
Moving window using OFFSET at each row (example shown in D13):
=IFERROR( (SUM(OFFSET(B13,-11,0,12,1)) / AVERAGE(OFFSET(C13,-11,0,12,1))) * 100 , 0 ) -
Dynamic arrays with table
Table1:=LET(r, ROWS(Table1[Exits]), e, TAKE(Table1[Exits], 12), h, TAKE(Table1[AvgHeadcount], 12), IF(r<12, 0, (SUM(e)/AVERAGE(h))*100))
Format as Percentage with 2 decimals.
Quick Rolling 12-Month Attrition Calculator
Simple Mode (enter totals)
Advanced Mode (enter each of the last 12 months)
Enter exits and average headcount for each month (most recent at the bottom). The calculator will sum exits and average the headcount across the 12 months.
| Month | Exits | Avg. Headcount |
|---|---|---|
| Totals | 0 | 0.00 |
- Use the same 12-month window for both numerator (sum of exits) and denominator (average headcount).
- Ideal for dashboards: it smooths month-to-month noise and reveals trajectory.
- Break out voluntary vs. involuntary exits for deeper diagnostics.
Annualized Attrition Formula
Annualized attrition projects the yearly attrition rate based on exits observed in a shorter period (e.g., 3 or 6 months).
Formula
Attrition % (Annualized) = (Exits ÷ Avg. Employees × 100) × (12 ÷ Months Considered)
Example: If you have 6 months of data, multiply the 6-month attrition rate by (12 ÷ 6 = 2) to project for the full year.
Worked Example
| Period | Opening Headcount | Closing Headcount | Avg. Employees | Exits (Period) | Months Considered | Annualized Attrition % |
|---|---|---|---|---|---|---|
| H1 2024 (6 months) | 500 | 495 | 497.5 | 20 | 6 | 8.04% |
| Q1 2025 (3 months) | 495 | 490 | 492.5 | 12 | 3 | 9.76% |
Excel Ready Formula (Copy and paste)
Put Opening in column B, Closing in C, Exits in E, and Months Considered in G. Use this in H2:
=IFERROR(((E2/((B2+C2)/2))*100)*(12/G2),0)
Format column H as Percentage with 2 decimals.
Quick Annualized Attrition Calculator
- Use annualized attrition when you have partial-year data but want to forecast a full-year rate.
- Multiply the observed attrition rate by (12 ÷ months considered) to project annualized values.
- Best used for interim reporting, not as a replacement for actual annual attrition once full-year data is available.
YTD (Year-to-Date) Attrition Formula
YTD attrition measures exits from the start of the year up to the current month, divided by the average headcount across the same YTD period.
Formula
Attrition % (YTD) = Exits from Jan → Current Month ÷ Average Employees over Jan → Current Month × 100
Denominator options:
(a) Simple average using opening-of-year & current closing headcount → ((Year Opening + Current Closing) ÷ 2).
(b) More precise average of monthly average headcounts across months elapsed.
Worked Example (illustrative)
| YTD Window | YTD Exits | Avg. Employees (YTD) | YTD Attrition % |
|---|---|---|---|
| YTD to Mar 2025 | 15 | 498.0 | 3.01% |
| YTD to Jun 2025 | 28 | 497.0 | 5.63% |
| YTD to Sep 2025 | 41 | 496.5 | 8.26% |
Excel Ready Formulas
Assume a monthly sheet with Month (A), Exits (B), Opening HC (C), Closing HC (D), and AvgHC (E) where E = (C + D) / 2. Row 2 is January of the current year.
-
YTD using precise monthly averages (place in F2 and copy down):
=IFERROR( (SUM($B$2:B2) / AVERAGE($E$2:E2)) * 100 , 0 ) -
YTD using simple opening/closing average (place in G2 and copy down):
=IFERROR( (SUM($B$2:B2) / ((INDEX($C$2:$C$100,1) + D2)/2)) * 100 , 0 )HereINDEX($C$2:$C$100,1)grabs the year opening headcount (January opening), andD2is current month's closing headcount.
Format results as Percentage with 2 decimals.
Quick YTD Attrition Calculator
Simple Mode (enter totals)
Advanced Mode (enter months elapsed this year)
Enter data from January to the current month only. The calculator sums exits and averages headcount across months provided.
| Month | Exits | Avg. Headcount |
|---|---|---|
| Totals (Jan → Current) | 0 | 0.00 |
- Ensure numerator and denominator cover exactly the same YTD window.
- Use monthly-average denominator for precision when headcount fluctuates within the year.
- Once the year completes, switch to actual Annual Attrition instead of YTD.
Employee Turnover Rate vs Attrition Formula
Aspect | Attrition Formula | Turnover Formula |
Replacement | Not always replaced | Always replaced |
Focus | Workforce shrinkage | Workforce mobility |
Example | Retirement, redundancy | Resignation + backfill |
In other words, attrition measures shrinkage, turnover measures churn.
Why Calculating Attrition is Important for HR
- Workforce Planning: Helps HR forecast hiring needs.
- Hiring Strategy: High attrition → higher recruitment costs.
- Retention Programs: Identifies departments with high exits.
- Industry Benchmarking: Compare with competitors.
- Leadership Decisions: Attrition insights affect budgets, promotions, and restructures.
Common Mistakes in Attrition Calculations
- Using only start or end headcount instead of the average.
- Ignoring seasonal hiring spikes (common in retail, BPO, and IT).
- Mixing voluntary and involuntary exits (retirement vs termination).
- Not differentiating attrition types (functional, non-functional, regrettable).
- Forgetting to annualize short-period data.
Table: Attrition Formula Types
Formula Type | Formula | Use Case |
Monthly Attrition | Exits ÷ Avg. employees × 100 | Short-term HR reporting |
Annual Attrition | Yearly exits ÷ Avg. employees × 100 | Long-term workforce trends |
Annualized Attrition | (Exits ÷ Avg. employees × 100) × (12 ÷ months) | Projects yearly attrition |
Rolling 12-Month | Last 12 months exits ÷ Avg. employees × 100 | Trend analysis |
YTD Attrition | YTD exits ÷ Avg. employees × 100 | Current-year tracking |
Conclusion
The attrition formula is more than just a percentage, it is a key HR metric that drives workforce strategy. By using correct formulas (monthly, annual, rolling, or YTD), companies gain deeper insight into employee exit patterns.
HR professionals should track attrition alongside turnover rate for a complete picture of workforce health. With the right Excel tools and formulas, attrition analysis becomes a powerful decision-making resource.
FAQs on Attrition Formula
Q1: What is the attrition formula in HR?
Attrition Rate = (Number of Exits ÷ Average Employees) × 100.
Q2: How do you calculate attrition rate in Excel?
Use the formula =(Exits/((Opening+Closing)/2))*100.
Q3: What is the difference between attrition rate and turnover rate?
Attrition refers to employees leaving without replacement, turnover includes replacements.
Q4: How do you calculate annualized attrition?
Multiply partial attrition rate by (12 ÷ months considered).
Q5: What is a good attrition rate for a company?
It varies by industry. For IT/BPO, under 15% is healthy, while retail may tolerate 25–30%.
