Employee PTO Tracker & Calculator – Free Excel Template

Posted on
Employee PTO Calculator v2 - PTO Balance

Do you want to easily find out how many days of PTO (Paid Time Off) you have available? Wondering if there is a simple spreadsheet that can be used as vacation tracker and PTO vacation accrual calculator? You have come to the right place – Employee PTO Tracker Excel Template. You can download this free Excel template to track and calculate Employee’s PTO (or leave or vacation) accrual balances.

Thank you so much for the feedback to the first version released in 2015. I have incorporated the feedback in this new version released in Oct 2016. Read list of enhancements

 

KEY FEATURES OF PTO CALCULATOR

  • Several settings available to cover most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates PTO balances for today and any future date
  • Vacation dates can be entered as date ranges
  • File is designed for one employee only. Make a copy of the workbook to use for the second employee.

If you would like to manage PTO for multiple employees, please visit Small Business PTO Manager Excel template.  

 

Employee PTO (Paid Time Off) Calculator v2 - PTO Balance
Employee PTO (Paid Time Off) Calculator v2 – PTO Balance

 

FREE DOWNLOAD

Employee PTO Calculator & Tracker Excel Template v2

 

VIDEO DEMO

 

HOW TO USE PTO CALCULATOR

  1. Enter inputs in the Employee PTO sheet

    Monthly Accrual PTO Calculator - Inputs to Template
    Monthly Accrual PTO Calculator – Inputs to Template

     

  2. Review the PTO Policy and first accrual window details

    Monthly Accrual PTO Calculator - Review PTO Policy
    Monthly Accrual PTO Calculator – Review PTO Policy

     

  3. Fix if there are any data validation errors

  4. When employee takes PTO, enter PTO info

    Enter Vacation rates in PTO Calculator
    Enter Vacation rates in PTO Calculator

     

  5. View PTO balance and balance trend

    View PTO Balance calculated by Template
    View PTO Balance calculated by Template

 

COMPONENTS OF PTO POLICY

Though the template is very simple to use, there are quite a few terms to understand and several calculations that happen behind the scenes. Let’s start from the beginning. Let’s start with the simple terms first.

User Inputs on PTO Policy
User Inputs on PTO Policy

 

EMPLOYEE NAME
This does not need any explanation. Enter name of employee for whom we will be tracking and calculating PTO balance.

HIRE DATE
A lot of the calculations for employee’s PTO balance depends on the Hire date of employee. Just enter Hire date. Even if you have been tracking PTO using some other tool and now want to use this template, enter the actual hire date of the employee. Tenure (how long an employee has been with the organization) is calculated from the hire date and companies may have tenure based increase in PTO.

PTO UNIT
We can choose to track employee PTO in units of days or hours. If we choose Hours, we have to enter PTO taken by employee in Hours. If we choose Days, we can just enter PTO dates (which we will discuss later) and ignore hours taken off.

ANNUAL PTO ACCRUAL RATE
Annual Accrual Rate is the PTO that an employee accrues in one year. For example, a company may offer 120 hours of PTO per year.

PTO ACCRUAL PERIOD
This is to inform how we accrue the annual PTO rate. Continuing with the above example of 120 hours per year, how will the employee receive these 120 hours. We have 6 options here: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.

 

PTO Accrual Period Options - Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual
PTO Accrual Period Options – Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual

 

Let’s see how a Monthly scenario would work.

PTO Accrual Frequency and Annual PTO Accrual Rate
PTO Accrual Frequency and Annual PTO Accrual Rate

 

120 hours will be given to the employee at 10 hours each month for 12 months.

 

FIRST ACCRUAL PERIOD BEGIN DAY and ACCRUAL TIMING
In order to discuss the next two terms, we need to take an example. Let me use a Weekly accrual example to demonstrate.

Weekly PTO Accrual Example Inputs for Template
Weekly PTO Accrual Example Inputs for Template

 

In this example, the employee’s hire date is Jan 1st, 2016. Employee’s Annual PTO accrual rate is 120 hours and that is accrued weekly.  When we think of accrual periods, we have to think of a window with a start date and an end date.
Let’s enter First Accrual Period Begin Date as Jan 1st, 2016 (this is user input). So, the first accrual period window will be 1st Jan to 7th Jan.
When does the employee receive the accrued PTO? Is it on 1st or 7th? This can be controlled easily. In the above example, we have chosen ‘End of accrual period’. So, the employee receives the PTO accrued on 7th Jan.

We don’t have to remember all these calculations because that’s why we use such a PTO calculator tool. 🙂  Let’s review the policy as calculated by the template.

Weekly PTO Accrual Example - Review Policy
Weekly PTO Accrual Example – Review Policy

 

The Policy shows that the employee will accrue 2.308 hours per week. The first accrual window is 1st Jan to 7th Jan. First accrual day where PTO will be awarded to the employee is 7th Jan. The amount on that day will be 2.308 hours. This amount is the same as the weekly rate, because the employee starts on 1st Jan and the weekly window also begins on 7th.
We all know that employees can start in a new job on any day. So, let’s take the same example but for an employee who started on 3rd Jan.

Weekly PTO Accrual Example where Employee Starts in the middle of accrual window
Weekly PTO Accrual Example where Employee Starts in the middle of accrual window

 

We can see that the first valid accrual window is still 1st Jan to 7th Jan, and the accrual happens in 7th Jan (end of window).  However, the amount if only 1.648 hours because the employee only accrues for 5 days (3rd Jan to 7th Jan). Thus the template can easily prorate the PTO awarded when an employee joins in the middle of an accrual window.

The approach is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. Twice a Month and Monthly are slightly different.

 

TWICE A MONTH

For Twice a Month, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days.

Twice a Month PTO Accrual - Enter 2 days. 2nd day can be 'Last Day'
Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’

 

The template will then take those two days as the accrual days every month. You can choose ‘Last day’ for the second day and the template can automatically assign the last day of each month, whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

 

MONTHLY

For Monthly, we don’t need to provide First Accrual Period Begin Date. Instead we will choose a day of Month. The options are 1 to 28 and Last day.

Monthly PTO Accrual - Input day of month - First day example
Monthly PTO Accrual – Input day of month – First day example

 

The ‘Last day’ will be accounted for, correctly whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

Monthly PTO Accrual - Input Day of Month - Last Day Example
Monthly PTO Accrual – Input Day of Month – Last Day Example

 

Now, let’s look at some more options we have with setting PTO/Vacation policy.
ANNUAL PTO ROLLOVER POLICY
As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no vacations taken. Typically, companies do not want employees to accrue a very large balance. Two reasons:

  1. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
  2. Companies may consider remaining PTO balance as cash that needs to be paid to employee if employee leaves the company. So, very high balance could mean more cash out the door for the company. So usually, there is a rollover policy. This determines how many hours of PTO can the employee carry over from one year to the next year.

The template allows three possibilities.

PTO Rollover Policy Settings - Zero Rollover, Rollover Limit, Unlimited Rollover
PTO Rollover Policy Settings – Zero Rollover, Rollover Limit, Unlimited Rollover

 

  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. Unlimited Rollover: Here the employee does not lose any PTO, and will carry over everything to next year. This is an unusual policy for a company.

 

To see this in action, I have chosen a 40 hour roll over limit.

PTO Rollover Limit - Example - Monthly Accrual Rate
PTO Rollover Limit – Example – Monthly Accrual Rate

 

At the end of the year, the employee’s PTO balance comes from 120 to 50. It is 50 because 40 is carried over and the employee accrues 10 hours for January of next year.
Now with this rollover policy, there is another variation. Companies may apply rollover at calendar year change or on work anniversary dates. You can easily change that setting.

PTO Rollover timing can be Calendar year or Work Anniversary
PTO Rollover timing can be Calendar year or Work Anniversary

 

The next section covers the remaining options in PTO policy.

More PTO policy settings and options - Probationary period, Maximum allowed PTO and tenure based PTO
More PTO policy settings and options – Probationary period, Maximum allowed PTO and tenure based PTO

 

PROBATIONARY PERIOD
In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee does not earn any PTO during the first 30 days of employment. You can set that easily in this template.

 

MAXIMUM ALLOWED PTO BALANCE
The rollover limit only applies to the end of the year balance. Some companies can set a limit on maximum balance at any time. We can set the amount in the Maximum Allowed PTO Balance.

 

ACCRUAL RATES VARY BY TENURE
Companies increase the annual accrual rate for employees who stay with the company for more years. We can handle such scenarios as well. We would choose YES to this first and then fill out the table below.

Employee Vacation Accrual - Annual Rate increased by Tenure
Employee Vacation Accrual – Annual Rate increased by Tenure

 

We can set the Annual PTO Accrual rate and Maximum PTO balance. In the example above, the employee will receive at the rate of 56 hours in the first year, then rate of 106 hours in the second and third year, 144 hours in years 4 to 10.

Important: Please make sure that the first entry here is for 0 completed years.

You can enter more rows as needed.  Read how to enter and delete data in Excel tables

Now we have gone through the various input options in the PTO calculator. These settings have to be entered only once for an employee. After these are finalized, we will enter PTO dates whenever an employee is taking vacation.

 

PTO OR VACATION DATES

 

Enter Vacation dates in PTO Calculator - Enter range of dates and hours for each day
Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day

 

If we track PTO in hours, we have to enter the PTO hours column. We can ignore it if our PTO unit is days. We can enter date ranges to enter multi-day vacation. However, if it is a single day vacation, please enter both start and date as the same date.
In the above example, 3 hours of PTO for each of the 3 days (June 2, June 3 and June 4) – in total 9 hours – will be subtracted from the PTO balance. 2 hours will be subtracted on 5th July.
You can enter more vacations by just typing new row of data in the table.

 

PTO BALANCE
As we enter PTO dates, the balances get updated.

Current PTO Balance shown by default and PTO Balance shown from Hire Date
Current PTO Balance shown by default and PTO Balance shown from Hire Date

 

By default, today’s PTO balance is shown at the top. You can modify the date and can view PTO balance any date. To put it back to today, just type =TODAY().
Similarly, the balance trend chart shows data by default from Hire Date of Employee. You can edit and modify that as well.
Enter the number of days to control the duration displayed in the chart

 

PTO ADJUSTMENT

If you would like to add or remove PTO, outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

Make positive and negative adjustments to PTO Balance easily
Make positive and negative adjustments to PTO Balance easily

 

An example would be an employee who has been with the company for a few years. You were using some system to track the PTO balance and now you want to migrate to this template. You don’t have to enter all the vacation dates from the past. You can just enter the adjustment amount to bring the current balance to the correct amount. If the employee has taken 60 hours of PTO already, then enter -60 as adjustment.

 

PRORATING WHEN ACCRUAL RATE CHANGES
As we had discussed earlier, the accrual rate can vary by employee tenure. If the work anniversary happens to be in the middle of an accrual window, then we have to prorate the PTO accrued.

Let’s take an example where an employee’s hire date is Jan 16th 2016. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2017, he will earn 15.16 hrs. 15 days at the rate of 10 hrs per month and 16 days at the rate of 20 hrs a month.

The template does this prorating calculation by default.

 

DATA VALIDATIONS

When you enter the First Accrual Period Begin Date, if it is earlier than or after the first valid accrual window, an error will appear. Let’s look at an example.

 

Data Validations built in the template - Example Inputs
Data Validations built in the template – Example Inputs

 

Though the  employee starts on Jan 1st, 2016, we have entered Dec 1, 2016 as First Accrual Period Begin date.

First Accrual Period Begin Date is too late
First Accrual Period Begin Date is too late

 

Employee is eligible to accrue from Jan 1, 2016, but the first accrual window is Dec 1, 2016 to Dec 7, 2016. If the employee will not accrue any balance from Jan 1st to Dec 1st, it is incorrect. This is due to the data entry error. Once we update the First Accrual Period Begin Date correctly, error will go away.


I would like to hear your feedback. Have I missed any of the scenarios that happen in your workplace? Do you find this useful? Please leave your comments below. Please share with your friends.

 

RECOMMENDED TEMPLATE

PTO Manager Excel Template - Product Features
PTO Manager Excel Template – Product Features


 RELATED HR EXCEL TEMPLATES

183 thoughts on “Employee PTO Tracker & Calculator – Free Excel Template

  1. What if the accrual policy is every 30 hours worked, one hour of PTO is accrued.

    How can that be calculated?

    1. Hello,

      PTO can accrue in hours. However, the working days are calculated in days.

      Best wishes

  2. Good Afternoon,

    I am looking to use this template, however we recently went to PTO January 2019. Thus older employees have a little different amount as we switched from vacation time to PTO. Is there a way to say or add a section for Carry Over, plus accrued equals the final amount?

    Thank you for your help and time.

    1. Thanks for your interest.
      Any one-time adjustments can be entered in the Adjustments sheet. Please provide more details if there are any further questions.
      Best wishes.

      1. Awesome! So lets say… I had before the new PTO, carried over 136 hours. I would place that on the third tab, and continue to place the current information on the Balance Calculator, and the spreadsheet would automatically adjust the end balance?
        Another question, when a Data Validation Error occurs, I have edited the window however the error remains?

        Any suggestions?

        1. Yes, you are correct. Please email your file to contact@indzara.com and we can review the error you are referring to.
          Best wishes.

  3. i need help i need to learn how to do this step by step. from what i was told you calculate by the years you been to the company/agency, not by hired date, is that correct. How do I begin?

    1. Thanks. You can find details on how to use the template in the above post, along with the video demo.
      The template calculates accrual rates based on tenure or fixed accrual rate.
      Best wishes.

  4. I have been using this for a year now. I really like it. However, lately it is doing something really weird in cell P4 (PTO Balance Hours). I currently have pto hours in cells P34, P35 & P36. So, it is showing 127 hours in cell P4 (I have it set as unlimited rollover). I need to add 80 hours in cell P37 for a vacation I took in Aug 2018. When I insert 80, it changes the amount in cell P4 (PTO Balance) to negative 833 hours. So, I tried adding just 8 hours in cell P37 and it changed the balance (P4) to 31 hours. It’s still wrong.I have no idea what is going on but it is frustrating. Please help. Thanks!

    1. Hello

      Please send your file along with the list of issues to contact@indzara.com

      Best wishes

  5. The template is awesome! How can I add an additional PTO option? example PTO 1, PTO 2, PTO 3

    thanks!

    1. Hello

      Do you wish to track PTO for more than one employee? Please use the premium template https://indzara.com/product/small-business-paid-time-off-manager/

      Best wishes

  6. Hi –

    I am interested in purchasing this template, it looks really good. I have a question, though. Is there a way to not prorate and employees first month of accrual? We allocate our PTO in full days and do not want to deal with partial accruals.
    Thanks!

    1. Thank you for the interest.
      The template allows adjustments table where we can enter negative values to reduce any pro-rated PTO accrual. I am assuming you are referring to employee not getting any PTO accrual if they start in the middle of an accrual period. If you want to provide the whole accrual amount without pro-rating, then please use the adjustments table to add PTO balance to the employee.
      This would have to be done once for each employee who starts in the middle of the accrual period.
      Please let us know if there are any questions.
      Best wishes.

  7. Is there a way to update the calculation for hours under PT0 balance to account for 1/2 hour increments? Say if someone takes 2 and half hours instead of 3 hours off.

    It shows the correct balance on the chart but I’d like to see it reflected in the balance as well.

    1. We can enter partial hours in the PTO hours table. The Balance at the top is set to not show decimals by default. However, we can just increase the decimals in the Number formatting menu.
      Then, the balance should show in decimals.
      We have also emailed you these details with screenshots.

      Best wishes.

  8. Is there a template similar to this for PTO that is front-loaded and then as the year goes along any new hires are given a pro rata percentage that matches the percentage of time left in the year?

    1. Hello

      Please review Small Business – Paid Time Off (PTO) Manager – Excel Template at https://indzara.com/product/small-business-paid-time-off-manager.

      Best wishes

  9. First of all, amazing work, great job! The structure and usability is awesome and meets almost every need imaginable. I wanted to ask if there is a way to track any PTO plan where it is accrued PER HOUR rather than per pay period. Is there a way to do this with the existing file or is there a formula I can add somewhere that can enable this. I’d greatly appreciate your help. Thank you for providing this for those of us who need it

    1. Hello,

      Thank you for your feedback.
      Please check “Hours” in the PTO incurred unit.

      Best wishes

  10. Amazing tool; however, your PTO adjustment table is not “talking” to the CAL table. If I need to add an additional 40 hours to an employee’s balance, I put in the date and type in 40, but this does not adjust the balance. Can you please take a look and determine where the error occurs?

    1. Hello,

      Please ensure that you have entered the dates correctly. In case it still does not work, please send your file with the list of issues to contact@indzara.com.

      Best wishes

Leave a Reply

Your email address will not be published. Required fields are marked *