Assuming you’ve downloaded our free resource, let’s get started.
1. Gather relevant employee information
When you open our workbook, you’ll find a table for relevant employee information. Here, you’ll be able to calculate vacation pay for 20+ shift-based employees. (You can add more rows, i.e. more employees.)
Start by entering your employees’ names in column E (see screenshot below).
Next, you’ll need to determine an appropriate accrual rate. (If you’re in the US, there are recommended accrual rates for each industry.) Once confirmed, enter the accrual rate in cell B21.
Now, input the hourly rate for each employee in column H. After that, enter the number of hours worked per period in column I.
Note: you can multiply the number of work hours in a week at your establishment by the number of weeks that you grant each employee vacation. So, if your employees work 40 hours a week and you give them 2 weeks of vacation, you would input 80 hours in the appropriate cell.
Hurray! Your work ends here—our template will do the rest. For each employee, you will have the following calculations: vacation pay earned per pay period, vacation hours earned per pay period, total vacation pay earned per year, and total vacation hours earned per year. You don’t have to worry about anything else. But, if you like math and are interested in learning how to calculate this information by yourself, continue reading. By the end of this article, you’ll be able to calculate vacation pay without any template. Go you!
2. Calculate the rate of vacation accrual
Let’s give the first formula a go. To calculate the rate of vacation accrual, you’ll need to divide your employees’ total vacation hours per year by the total number of annual hours worked. For instance, let’s say one of your employees works 40 hours a week, paid on a bi-weekly basis, with 80 vacation hours per year.
To calculate the number of hours that this employee is expected to work in a year, take the number of hours worked per pay period (80 hours in this case) and multiply it by the number of paychecks your employee receives in a year (26 paychecks as per our example). Eighty hours multiplied by 26 paychecks equals 2,080.
Now, divide your employees’ total vacation hours per year by the total number of hours they work annually. Eighty hours divided by 2,080 equals 3.85%, or 4% if rounded up. That’s how you get the rate of vacation accrual. That wasn’t so bad, right?
3. Calculate total vacation hours earned per year
Time to unpack the next set of calculations. To calculate total vacation hours earned per year, you’ll multiply vacation hours per pay period by the number of paychecks an employee receives in a year.
To get the number of vacation hours per pay period, you’ll multiply the vacation accrual rate (e.g. 4%) by hours worked per pay period. Let’s continue with the example above—if your pay period is 80 hours and the industry standard is 4%, then 4% (or 0.04) multiplied by 80 hours equals 3.2 hours.
Now, multiply vacation hours per pay period (3.2 hours in this case) by the number of paychecks an employee gets in a year (26). Thus, 3.2 hours multiplied by 26 paychecks equals 83.2 hours. You’re on a roll!
4. Calculate vacation pay per pay period and yearly
To calculate vacation pay per period, you’ll multiply vacation hours worked per period by the hourly rate. Let’s assume an employee works three vacation hours per pay period (as previously calculated), with an hourly rate of $15/hr. Three hours multiplied by the rate of $15/hr equals $48.
Lastly, to calculate total vacation pay in a year, you’ll multiply total vacation hours per year by the hourly rate. Let’s take our calculations from section 3 as an example. If total vacation hours earned per year is 83, the total vacation pay will be: 83 hours multiplied by the rate of $15/hr equals $1,248.
Good job!
You now have a complete understanding of the formulas at work in our Excel template. These calculations get easier with practice, but this task can be time-consuming, especially if you’re doing it alone. Web-based tools can help you calculate vacation pay, although they have their own setbacks. For instance, some government websites offer vacation pay calculators for shift-based employees, but you have to answer a set of questions before getting your numbers, which can waste your time even more.
Instead, consider more advanced tech solutions. The opportunity to track time and attendance will relieve you of calculating it all by yourself—even though you’ve just proven you’re more than capable of doing so. Our scheduling software allows you to keep track of shift hours and the number of hours worked by each employee, sync with your payroll system, and manage reports. Whether or not math is your strong suit, these features will be a huge help to your business.
Have you taken a “staycation” this year? Tell us about it in the comments below!
