📅 Published: January 1, 2026🔄 Last updated: November 2, 2026
📥 Download Free PTO Excel Template (CSV)
Enter your details below, then download a ready-to-use CSV file that opens in Excel, Google Sheets, or any spreadsheet app. 100% free, no sign-up.
Opens in Excel, Google Sheets, Numbers, or LibreOffice. The template includes 3 months of sample pay periods — just replace the numbers with your own.
🧮 PTO Excel Formula Calculator — Get Your Exact Excel Formulas
Your PTO Excel Formulas
Per-Period Accrual:—
—
Accrual Rate (per hour):—
Annual PTO:—
Payout (if cashed out):—
📝 Copy These Excel Formulas
—
📊 Sample Excel Table (Copy to Your Spreadsheet)
Why Use an Excel Template for PTO?
Excel is the most common tool for tracking PTO — especially for small businesses, HR teams, and employees who want their own record. But most "PTO Excel templates" online are either paywalled, outdated, or full of broken formulas. This page gives you a free, working template plus the exact formulas you need to calculate PTO accrual, usage, and payout in Excel. Download the CSV, or copy the formulas directly into your spreadsheet. No sign-up, no email — just the template.
📌 Quick answer: The core Excel formula for PTO accrual is =Annual_PTO/2080*Hours_Worked. For per-paycheck accrual: =Annual_PTO/26 (bi-weekly). For payout: =Remaining_Hours*Hourly_Rate.
📋 PTO Excel — Quick Summary
Accrual formula: =Annual_PTO/2080*Hours_Worked
Per paycheck (bi-weekly): =Annual_PTO/26
Running balance: =Previous_Balance + Accrued - Used
Payout: =Remaining_Hours*Hourly_Rate
Days conversion: =Hours/8
ℹ️ All formulas work in Excel, Google Sheets, Numbers, and LibreOffice. The 2080 number is 40 hours/week × 52 weeks — the standard US work year.
📊 The CSV template includes 3 months of sample pay periods — just replace the numbers with your own.
📈 Always check your paystub against your Excel tracker — if the numbers do not match, ask HR for a written breakdown.
👩 Sarah, 30 — HR Manager, Ohio
40 employees | 80–160 hrs/year | Bi-weekly pay
Tracks PTO for the whole team in one Excel sheet. Saves 4 hours per month vs. manual entry.
✅ "The CSV template worked perfectly in Excel — no formula errors."
👨 Carlos, 35 — Employee, Florida
120 hrs/year | Bi-weekly | Wants to verify paystub
Uses Excel to track his own PTO balance. Cross-checks with paystub every 2 weeks.
✅ "Now I catch HR mistakes before they become a problem."
How to Calculate PTO in Excel (Step by Step)
Setting up PTO tracking in Excel takes about 10 minutes. Here is the exact process:
Set up columns: Pay Period | Date | Hours Worked | Accrual Rate | PTO Accrued | PTO Used | Balance
Enter the accrual rate: In the Accrual Rate column, enter your rate per hour (=Annual_PTO/2080). For 80 hours per year, this is 0.0385.
Calculate PTO accrued: In the PTO Accrued column, enter =Hours_Worked*Accrual_Rate. For 80 hours worked: 3.08.
Track PTO used: Each pay period, enter the hours you took off in the PTO Used column.
Calculate balance: In the Balance column, enter =Previous_Balance+Accrued-Used. This gives you a running total.
For a bi-weekly employee with 80 hours per year and 0 hours starting balance, the first pay period looks like this: Hours Worked = 80, Accrual Rate = 0.0385, PTO Accrued = 3.08, PTO Used = 0, Balance = 3.08. After 13 pay periods, the balance is about 40 hours.
Excel Formula to Calculate PTO Accrual
The core formula is: =Annual_PTO/2080*Hours_Worked. For example, if annual PTO is in cell B2 and hours worked is in B3, the formula is =B2/2080*B3. This works for any annual PTO allowance and any hours worked. For per-paycheck accrual, use =Annual_PTO/26 for bi-weekly, =Annual_PTO/24 for semi-monthly, or =Annual_PTO/12 for monthly.
To make the formula more readable, use named cells. Select the cell with annual PTO, click the Name Box (top left), and type "AnnualPTO". Then your formula becomes =AnnualPTO/2080*HoursWorked. This is easier to audit and less prone to errors.
PTO Tracking Spreadsheet Layout
Here is the column layout that works best for PTO tracking in Excel:
ColumnWhat to EnterExample Formula
A — Pay PeriodPeriod number1, 2, 3...
B — DatePay date2026-01-15
C — Hours WorkedHours in period80
D — Accrual RateRate per hour=B2/2080
E — PTO AccruedHours earned=C2*D2
F — PTO UsedHours taken8
G — BalanceRunning total=G1+E2-F2
How to Calculate PTO Payout in Excel
If you are leaving a job and want to calculate your PTO payout, use the formula =Remaining_Hours*Hourly_Rate. For example, if you have 40 hours of unused PTO and earn $25 per hour, the payout is =40*25 = $1,000. For salaried employees, use =(Annual_Salary/2080) as the hourly rate. Note that payout rules vary by state — California requires payout, while Texas and Florida do not (unless the company policy says so).
Common Excel Mistakes to Avoid
Using days instead of hours: PTO is tracked in hours on paystubs. Convert days to hours (×8) before entering.
Forgetting the 2080 divisor: The formula =Annual_PTO/26 gives per-paycheck accrual, not per-hour. Use /2080 for per-hour.
Not using absolute references: If you copy the formula down, use $B$2 for annual PTO so it does not shift.
Rounding too early: Keep 4 decimal places in the accrual rate, round only the final balance.
Mixing pay frequencies: If you switch from bi-weekly to monthly, update the divisor (26 → 12).
❓ Frequently Asked Questions
How do I calculate PTO in Excel?
Use the formula =Annual_PTO/2080*Hours_Worked. For example, if annual PTO is in cell B2 and hours worked is in B3, the formula is =B2/2080*B3. This gives you the PTO hours accrued. For per-paycheck accrual, use =B2/26 for bi-weekly pay.
What is the Excel formula to calculate PTO accrual?
The Excel formula for PTO accrual is =Annual_PTO/2080*Hours_Worked. For 80 hours per year and 80 hours worked: =80/2080*80, which equals 3.08 hours. For per-pay-period accrual: =Annual_PTO/26 for bi-weekly pay.
Is there a free PTO calculator Excel template?
Yes. This page provides a free PTO calculator Excel template. Enter your annual PTO and hours worked, and the calculator generates a CSV file you can open in Excel. You can also copy the Excel formulas directly from the formula section.
How do I track PTO accrual in Excel?
Set up columns for Pay Period, Hours Worked, Accrual Rate, PTO Accrued, PTO Used, and PTO Balance. Use the formula =Annual_PTO/2080*Hours_Worked to calculate accrual, and subtract PTO Used from the running balance each period.
What is the PTO accrual formula for 40 hours per week?
For a 40-hour workweek, the accrual rate is Annual_PTO ÷ 2080. For 80 hours per year: 0.0385 hours per hour worked. After 40 hours: 1.54 hours. After 80 hours (2 weeks): 3.08 hours. After 2,080 hours (1 year): 80 hours.
Can I use Excel to calculate PTO payout?
Yes. Use the formula =Remaining_PTO_Hours*Hourly_Rate. For example, if you have 40 hours of unused PTO and earn $25 per hour, the payout is =40*25, which equals $1,000. For salaried employees, use (Annual_Salary/2080) as the hourly rate.
💡 Five Excel Tips for PTO Tracking
1. Use named ranges (AnnualPTO, HoursWorked) to make formulas readable and easier to audit.
2. Lock the annual PTO cell with $B$2 so the reference does not shift when you copy the formula.
3. Round only the final balance, not the accrual rate — keep 4 decimals in the rate column.
4. Add a "Notes" column for manual adjustments (e.g., carryover, bonuses, corrections).
5. Save a copy of the template each year so you have a historical record for tax or HR purposes.
📊 Want a Ready-Made PTO Spreadsheet?
Download the free CSV template — opens in Excel, Google Sheets, or Numbers. No sign-up required.
🛡️ MyPtoCalculator — PTO Excel template and formulas based on standard US HR practices. Last updated November 2, 2026. Disclaimer: Estimates only. Not legal advice.