Before anything, do you know what attrition rate means?
In the simplest of words, attrition rate means the rate at which the employees of a company leave the company during a given period.
The same is what we know as employee turnover rate or employee churn. In short, how many employees left XYZ company during a given period?
The attrition rate stands out as one of the key performance ratios for many companies. By computing and analyzing it, a company can identify alarming trends in its employee retention ratio, if any.
Also, many companies would benchmark this rate against the attrition rate observed in the industry, the past, or of their competitors. This not only enables a healthy comparison but also tells if a company is treating its employees right.
Calculating attrition rate is no big of a science. Especially if you’re doing it using Excel – it is only a matter of a few clicks. Want to learn how? Dive into the tutorial below.
Attrition Rate Formula
There are two commonly used formulas to calculate the attrition rate.
Number of Employees that left during the period / Average No. of Employees during the period
Another formula that you can alternatively use to calculate this rate is as follows:
Number of Employees that left during the period / Closing No. of Employees for that period
Closer attention to both formulas explains that the only difference between them both is the denominator.
The attrition rate can be computed using the average number of employees retained by a company during the period. And also by analyzing (can be done using pivot tables) it as a ratio of the number of employees retained by a company at the end of the period.
Calculating the Attrition Rate in Excel
Let us now put together a few examples to see how you can calculate the attrition rate in Excel. Strap in tight; we will see how to do this using both the formulas discussed above.
Calculate Attrition Rate Using Average No. of Employees
Here’s the first example.
The image below demonstrates the employee records of XYZ Company. Where we have the number of employees as at the start of the year and as at the end of the year.
To calculate the attrition rate using the data above, follow the steps below.
- Calculate the number of employees who left during the period using the formula below.
= Employees at period start – Employees at period end
Which in our case, equates to the following:
=B4 – C4
And there we have it! 27 employees left during the period.
- Calculate the average number of employees for the period using the following formula.
= (Employees at period start + Employees at period end) / 2
We will write it as follows:
=(B4 + C4) / 2
The average number of employees equates to 186.5 in our example.
TIP! Alternatively, you can also write it using the in-built AVERAGE function of Excel as below.
= AVERAGE (B4,C4)
- Next, calculate the attrition rate by applying the attrition rate formula as follows:
Number of Employees that left during the period / Average No. of Employees during the period
Here’s how in Excel.
=B6 / B7
- Hit Enter.
The present answer must be in decimals. However, as attrition is a rate, it must be in percentage terms. Here’s how to convert it into a percentage.
- Select the cell containing the attrition rate (in decimals).
- Go to the Home Tab > Number > Percentage Icon.
Excel will convert the decimal number into a percentage as follows.
TIP! You can adjust the number of decimals points in the percentage by using Increase Decimal / Decrease decimal buttons on the Home Tab.
And that’s it! You have the attrition rate (based on the average number of employees) all calculated.
XYZ Company stands at a 14.5% attrition rate.
If you want to make a comparison between attrition rates between certain periods (e.g. yearly), you can also calculate their percentage difference.
Calculate Attrition Rate Using the Closing No. of Employees
Let’s now see how you can compute the attrition rate in Excel using the closing number of employees (instead of the average number of employees).
If you have learned how to do it using the average number formula, this is going to be child’s play for you. So let’s go.
For ease of reference, we will use the same example and numbers as above.
To calculate the attrition rate using the data (which you can save without a formula) above, follow the steps below.
- Firstly, calculate the number of employees who left during the period using the formula below.
= Employees at period start – Employees at period end
In our case as follows:
= B4 – C4
- Apply the formula to calculate the attrition rate as follows:
Number of Employees that left during the period / No. of Employees as at the period end
Here’s how in Excel.
= B6 / C4
TIP! Here’s the difference. No need to calculate the average number of employees in this case. We will simply use the closing number of employees in this formula. Easier, right?
- Hit Enter.
If your answer is in decimals, convert it to percentages (if needed) as follows:
- Select the cell containing the attrition rate (in decimals).
- Go to the Home Tab > Number > Percentage Icon.
Here’s your attrition rate in a percentage format.
Based on this formula, the attrition rate comes out a little higher than before.
And that’s it! You have the attrition rate (based on the average number of employees) all calculated.
This time, the XYZ Company stands at a 15.6% attrition rate. The change of formula can cause the figures to escalate or dive down a little, as demonstrated above.
Conclusion
Hope you enjoyed reading the guide above as much as we enjoyed writing it. The attrition rate is a key performance ratio monitored in the corporate sector.
The above guide discusses two different formulas and how you can calculate them and demonstrates how you can use them both for calculation in Excel.
Go through the above examples and try practicing them yourself to learn what goes behind calculating the attrition rate. You will find it super easy and interesting.
Happy spreadsheeting to you!