Excel template · HR & payroll

Leave Encashment & Overtime Calculator in Excel (Labour Codes)

Record overtime day by day – hours beyond 8 a day, or every hour on a weekly off – and get overtime pay at twice the ordinary rate of wages, monthly hours by employee and a check on the quarterly limit. The leave sheet works out annual leave earned (1 day per 20 days worked), what carries forward up to 30 days, and the days and amount to encash at year end or on exit.

For: Factories, workshops, warehouses, shops and service businesses that pay overtime or encash leave, and the HR and payroll staff who calculate it.

Free download · no sign-up · no macros · Excel 2010+, Google Sheets, LibreOffice

Preview of the Leave Encashment & Overtime Calculator Excel template with sample data
Main sheet with sample data

At a glance

What it is
Leave Encashment & Overtime Calculator — an Excel workbook for Indian employers, HR and payroll teams
Free edition
6 sheets (Guide, Settings, Employees, Overtime Register, Overtime Summary, Leave Encashment) — free download, no sign-up
Pro edition
8 sheets, adds Overtime Slip and Encashment Tax — ₹19 one-time (regular ₹99)
Works in
Microsoft Excel 2010 or later, Google Sheets, LibreOffice Calc — no macros
Checked
Recalculated with zero formula errors; sample data and a Guide sheet included
Made by
Prakash Bagchi, finance professional, PB Lab (Raipur, India)

Free vs Pro: what you get

SheetFreePro
Guide
What it does, how to use it, assumptions and a live status check
Settings
Normal hours, day divisor, overtime rate (2×), quarterly limit, rounding of part-hours and the leave rules
Employees
Basic + DA and allowances, wages under the Code (50% rule), daily, hourly and overtime rates
Overtime Register
Up to 1,000 entries: date, employee, hours worked, weekly off, overtime hours, rate, pay, quarter and consent
Overtime Summary
Overtime hours by employee and month, pay for the year, highest quarter against the limit, and missing consents
Leave Encashment
Opening balance, days worked, leave earned and availed, carry forward, days encashed and amount – at year end or on exit
Overtime Slip
Printable monthly overtime statement for an employee, day by day, with consent
—
Encashment Tax
Exemption on leave encashed at retirement or resignation – least of four limits, ₹25 lakh cap – and the taxable part
—

Pro edition

Get Leave Encashment & Overtime Calculator – Pro

₹19₹9981% OFF

Everything in the free edition, plus:

  • Overtime Slip — Printable monthly overtime statement for an employee, day by day, with consent
  • Encashment Tax — Exemption on leave encashed at retirement or resignation – least of four limits, ₹25 lakh cap – and the taxable part
  • One-time payment, instant .xlsx download, free updates
+91

Secure payment by Razorpay · UPI, cards, netbanking & wallets

We use your details only to deliver the file and support your purchase. See our privacy policy and terms.

Need more than one? The HR & Payroll Kit has all 9 hr & payroll Pro templates, including this one, for ₹134.

How to use it

  1. 1Download the free file and read the Guide sheet.
  2. 2On Settings, check normal hours, the divisor, the overtime rate and the leave rules.
  3. 3On Employees, enter basic + DA and other allowances for each employee.
  4. 4Add overtime to the Overtime Register day by day, marking weekly offs and consent.
  5. 5On Leave Encashment, enter opening balances, days worked, leave taken and any leaving dates.

Overtime under the labour codes

The Occupational Safety, Health and Working Conditions Code, 2020 limits normal working hours to 8 a day and 48 a week. Work beyond that is overtime, which needs the worker's consent and is paid at twice the ordinary rate of wages under the Code on Wages, 2019. The Central rules cap overtime at 144 hours a quarter; some States allow different limits, so the cap is a setting.

In the register, enter the hours worked on each day with extra hours. On a normal day, overtime is hours beyond the daily limit; on a weekly off or holiday, every hour counts. Part-hours can be rounded the way the rules describe – 15 to 30 minutes as half an hour and more than 30 minutes as an hour – or left exact.

The ordinary rate of wages

Overtime is paid on wages as defined in the Code on Wages: basic, dearness allowance and retaining allowance, plus any excess of excluded allowances over 50% of total pay. The Employees sheet works out this wage, divides it by the working days you use (26 by default) and the normal hours to get the hourly rate, and doubles it for overtime. For an employee on ₹13,800 a month, that is about ₹66 an hour and ₹133 an hour for overtime.

Annual leave and carry forward

Under section 32 of the OSH Code, a worker who has worked 180 days in a calendar year earns one day of annual leave with wages for every 20 days worked. Unused leave can be carried forward up to 30 days; leave beyond that is encashed at the end of the year, and the worker can also choose to encash leave. Shops and establishments rules in your State may be more generous – the more beneficial provision applies.

Encashment on exit

When an employee leaves – by resignation, retirement, termination or death – leave earned in the year is paid along with the balance brought forward, even if they had not yet completed 180 days that year. It forms part of the full and final settlement, which under the Code on Wages must be paid within two working days. Leave encashed at retirement or resignation is exempt from tax up to the least of four limits (with a ₹25 lakh overall cap for non-government employees); the Pro edition works this out.

Free vs Pro

The free edition has the overtime register and summary with the quarterly check, and the full leave encashment calculation. The Pro edition adds a monthly overtime slip for each employee and the tax exemption calculator for encashment on leaving.

Written by Prakash Bagchi, finance professional and founder of PB Lab. Last reviewed .

Frequently asked questions

What is the overtime rate under the new labour codes?

Twice the ordinary rate of wages for work beyond 8 hours a day or 48 hours a week, with the worker's consent.

How many overtime hours are allowed?

The Central rules allow up to 144 hours in a quarter. States can set their own limits, so check your State's rules and change the setting.

How is the hourly rate for overtime worked out?

Monthly wages under the Code on Wages ÷ working days (usually 26) ÷ normal daily hours (8), then doubled for overtime.

How much annual leave does a worker get?

One day for every 20 days worked, once the worker has worked 180 days in the calendar year, under section 32 of the OSH Code.

How many days of leave can be carried forward?

Up to 30 days. Leave above that is encashed at the end of the calendar year, and all leave due is paid when the employee leaves.

Is leave encashment taxable?

Encashment while in service is fully taxable. On retirement or resignation, non-government employees are exempt up to the least of four limits with a ₹25 lakh overall cap.

Which daily rate is used for leave encashment?

The template uses monthly wages under the Code ÷ the working days in Settings. Change the divisor to 30 or use your policy's rate if different.

How much does the Pro edition cost and how do I get it?

The Pro edition is ₹19 (regular price ₹99), a one-time payment through Razorpay by UPI, card, netbanking or wallet. You download the .xlsx file right after paying, and the download page works again later if you bookmark it. Pro adds: Overtime Slip, Encashment Tax.

Related free calculators

More Excel templates

See all templates →

Disclaimer: This template is a working aid, not legal, tax or accounting advice. Sample data is fictional. Laws, rates and thresholds change — verify them against the current Act, rules and notifications, or with a qualified professional, before relying on the results. PB Lab is not responsible for decisions made using this file.