Excel template · HR & payroll

Gratuity Calculator in Excel (New Labour Code, 15/26 Formula)

Work out gratuity for one employee – eligibility, years counted, the 15/26 formula, the ₹20 lakh ceiling and the tax-exempt part – and keep a register of the gratuity liability for all your staff as on any date.

For: Employers, HR and accounts teams handling exits and year-end gratuity provisions; employees checking their gratuity.

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

Preview of the Gratuity Calculator & Register Excel template with sample data
Main sheet with sample data

At a glance

What it is
Gratuity Calculator & Register — an Excel workbook for Indian employers, HR and payroll teams
Free edition
3 sheets (Guide, Calculator, Register) — free download, no sign-up
Pro edition
5 sheets, adds Provision Estimate and Gratuity Statement — ₹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
Calculator
Eligibility, service, years counted, gratuity payable, tax-exempt part and payment date
Register
Accrued and vested gratuity for up to 200 employees as on any date
Provision Estimate
Projected-unit-credit style year-end provision with discount rate, salary escalation and attrition
—
Gratuity Statement
Print-ready gratuity settlement statement for the employee
—

Pro edition

Get Gratuity Calculator & Register – Pro

₹19₹9981% OFF

Everything in the free edition, plus:

  • Provision Estimate — Projected-unit-credit style year-end provision with discount rate, salary escalation and attrition
  • Gratuity Statement — Print-ready gratuity settlement statement for the employee
  • 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 Calculator, enter the employee, joining and leaving dates, last drawn Basic + DA, employment type and reason for leaving.
  3. 3Check eligibility, years counted and the gratuity payable and tax-exempt amounts.
  4. 4On Register, set the as-on date and list employees for the company-wide liability.
  5. 5Pay gratuity within 30 days of it becoming payable.

Gratuity under the labour codes

Gratuity is now governed by the Code on Social Security, 2020, in force from 21 November 2025, which replaced the Payment of Gratuity Act, 1972. The basic scheme is unchanged: an employee who has rendered at least five years of continuous service gets 15 days' wages for every completed year of service when they leave, retire, resign or are retrenched. The five-year condition doesn't apply on death or disablement.

The big change is for fixed-term employees, who now become eligible after one year of service, on a pro-rata basis. Wages also follow the new single definition – Basic + DA, with allowances above 50% of pay added back – and the last drawn wages under that definition set the amount for the whole service period.

The formula

For monthly-paid employees the usual formula is last drawn monthly wages × 15 ÷ 26 × years of service. The 26 represents working days in a month, so 15/26 of a month's wages is 15 days' pay. A part-year of more than six months counts as a full year: 8 years and 7 months is counted as 9 years. The maximum gratuity is ₹20 lakh.

Example: last drawn Basic + DA of ₹32,500 and 8 years 7 months of service gives ₹32,500 × 15 ÷ 26 × 9 = ₹1,68,750.

Employers not covered by the gratuity law who pay gratuity under their own policy often use 15/30 and count only completed years. The template lets you change the days, the divisor and the minimum service if your policy differs.

Tax on gratuity

For private-sector employees, the tax-free amount is the least of the formula amount, the gratuity actually received and ₹20 lakh – a lifetime limit, so exemptions used with earlier employers reduce it. Any excess is taxed as salary. Gratuity received by government employees is fully exempt.

Gratuity as a liability

Every month of service adds to the employer's gratuity liability. The Register shows, for each employee, the gratuity accrued so far, what would be payable if they left today, and how far they are from qualifying. Companies following AS 15 or Ind AS 19 must account for gratuity on an actuarial basis; the Pro edition gives a projected-unit-credit style estimate with discount rate, salary escalation and attrition assumptions – a useful cross-check, but not a substitute for an actuary's report.

Free vs Pro

The free edition covers the calculator and the liability register. The Pro edition adds the provision estimate with the movement for the year and a printable gratuity settlement statement.

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

Frequently asked questions

How is gratuity calculated?

Last drawn monthly wages (Basic + DA) × 15 ÷ 26 × years of service, where a part-year of more than six months counts as a full year. The maximum is ₹20 lakh.

Is gratuity payable after 1 year?

For fixed-term employees, yes – the Code on Social Security, 2020 makes them eligible after one year, pro-rata. Permanent employees still need five years, except on death or disablement.

Does 4 years and 7 months count as 5 years?

The rounding rule (more than six months counts as a year) applies to the amount, but eligibility needs five years of continuous service. Some courts have treated 4 years and 240 days as continuous service of five years – take advice in such cases.

How much gratuity is tax-free?

For private-sector employees, the least of the formula amount, the actual gratuity and ₹20 lakh over your lifetime. Government employees are fully exempt.

When must gratuity be paid?

Within 30 days of it becoming payable; interest is payable on delays.

What wages are used for gratuity under the new code?

Basic + DA (and retaining allowance), with any allowances above 50% of total pay added back, based on the last drawn wages.

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: Provision Estimate, Gratuity Statement.

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.