Employee Vacation Accrual Template

If your company uses daily vacation accrual, this spreadsheet may not work for you. It is set up for weekly, semi-weekly, monthly, or semi-monthly accrual periods.

If you are using a semi-monthly accrual period (twice per month), use the Monthly Accrual worksheet and change the Pay Period to Semi-Monthly.

The new hourly accrual worksheet allows you to enter the pay period date and the number of work hours. The benefits accrued are then calculated by multiplying the accrual rates by the number of work hours.

I would assume that your company's benefits policy states the number of hours of vacation and other benefits an employee can earn each year. The accrual rate is also shown in the worksheets, so if necessary you can manually override the formulas in those cells to enter specific accrual rates.

Besides the obvious Company Name and Employee Name fields, there are a few other values you'll need to enter.

Weekly Accrual worksheet:

• Pay Period Starting: Enter the date of the start of the first pay period (normally the start of your fiscal year, but for a new employee it may be mid-year).
• Weeks Per Pay Period: For bi-weekly pay periods, enter a 2.
• Accrued Every N Periods & Accrual Starting Period: If the employee vacation accrual period is identical to the pay period, then these values should probably all be 1 (one). However, if your policy states that employee vacation is accrued every 4 weeks, but you have a bi-weekly pay period, you'll need to modify these values.

Monthly Accrual worksheet: This one is pretty simple. Just enter the date of the start of your fiscal year and choose the pay period.

Hourly Accrual worksheet: In this version, you enter rates representing how many vacation hours are earned for each hour of work.

If your policy specifies maximum limits for vacation, sick leave, or personal leave, enter the hour limits in the "Maximum" fields located above the CUMULATIVE columns. The formulas in the EARNED columns are set up to ensure that the balance never exceeds the maximum number of hours. If there are no limits, just leave the Maximum fields blank or enter "n/a".

This spreadsheet is normally only valid for a single fiscal year because there is usually a limit to how much sick leave, personal leave, or vacation that can be carried over to the next year.

So, at the beginning of the next year, just make a copy of the worksheet and change the beginning balance to reflect the amount carried-over from the previous year.

Contracts for new employees usually state how much sick leave or vacation the employee starts with. Also, some leave might not start to accrue for 6 months or a year or whatever. To handle these cases, you can just delete the formulas in the Earned columns for the periods where the benefits should not accrue.

If your company has a policy in which the amount of vacation or other benefits earned increases based on the time the employee has been with the company, I'd recommend one of the following approaches:

1. Start a new worksheet and adjust the carry over balance and accrual rates each time the accrual rates change.
2. Delete the formulas in the EARNED columns and enter the accrued amounts manually as needed. (Not an automated approach, but it gives a lot more flexibility)

If the options in this spreadsheet for defining how the benefits are accrued do not work for your company, you have two options: (1) You can delete all of the formulas in the EARNED column and enter the hours accrued each pay period, or enter amounts only for the pay periods when the benefits are accrued. (2) You can send us an email and describe what you need and we'll see if there is another solution.

If you are a new employer, you will probably want to add a statement in your employee agreement or handbook to indicate how vacation, sick leave, and/or personal leave is accrued. The example below shows how to describe the vacation accrual where there is a maximum:

"... Vacation time will accrue at a rate of 4 hours every two weeks not to exceed 120 hours, starting with a balance of 40 hours on the Hire Date...."

Background Color: The spreadsheet uses conditional formatting to highlight every other row in the accrual table. If you need to manually change the background color, you'll need to remove the conditional formatting first.

Adding/Inserting/Deleting Rows: The vacation accrual formulas are set up to allow you to easily insert or delete rows. You can also extend the accrual table by copying the last row down however many rows you need to. If you do that, you'll need to redefine the Print Area by going to View > Page Break Preview.