Employee Vacation Accrual Template

If your company uses daily vacation accrual, this spreadsheet probably won't 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, use the Monthly Accrual worksheet and change the Pay Period to Semi-Monthly.

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.

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 try deleting or modifying the formulas in the Earned columns for the non-accrual periods.

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 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 highlighting on every other row in the accrual table is controlled using Conditional Formatting. If you need to manually change the background color, you'll need to remove the conditional formatting first.

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.