Gratuity Calculation in Excel (15/26 Formula)

🧮 Prefer a quick answer? Try our free gratuity calculator — instant, no sign-up.

Last updated: 27 June 2026 · Formulas tested in Excel 2010 and later.

Gratuity in Excel is calculated as last drawn (Basic + DA) × 15 × completed years of service ÷ 26. The "15/26" means 15 days' wages for every completed year, treating a month as 26 working days. Service beyond 6 months rounds up to the next full year. The result is tax-exempt up to ₹20 lakh in the private sector. A single cell — =ROUND(BasicDA*15*Years/26,0) — gives the figure.

Key takeaways

Formula: Gratuity = Last drawn (Basic + DA) × 15 × completed years ÷ 26.
Eligibility: 5 years' continuous service, in an establishment with 10+ employees; the 5-year rule is waived on death or disability.
Rounding: service of more than 6 months in the final year counts as a full year; 6 months or less is dropped.
Tax-free cap: ₹20 lakh in the private sector — gratuity above this is taxable.
New under the Code on Social Security (since 21 Nov 2025): fixed-term employees qualify after just 1 year.
Payment timeline: within 30 days of gratuity becoming due.

Fact box. The statutory gratuity formula is last drawn (Basic + DA) × 15 × completed years of service ÷ 26, where 26 represents the working days in a month and 15 the days of wages payable per year. The amount is tax-exempt up to ₹20 lakh for private-sector employees. (Source: Payment of Gratuity Act, 1972 / Code on Social Security, 2020.)

How do I calculate gratuity in Excel?

Put last drawn Basic + DA in BasicDA and completed years in Years. The core formula is:

=ROUND(BasicDA*15*Years/26,0)

To compute the years from join and exit dates with the 6-month rounding rule built in:

Field	Formula
Raw years	`=(ExitDate-JoinDate)/365.25`
Completed years (6-month round)	`=ROUND((ExitDate-JoinDate)/365.25,0)`
Gratuity	`=ROUND(BasicDA15Years/26,0)`
Taxable portion	`=MAX(Gratuity-2000000,0)`

The ROUND(...,0) on years approximates the rule that more than 6 months counts as a full year and 6 months or less is dropped. For precise control, compute full years with DATEDIF(JoinDate,ExitDate,"Y") and add 1 if the leftover months exceed 6.

Tip: Use DATEDIF(JoinDate,ExitDate,"Y") for completed years and DATEDIF(JoinDate,ExitDate,"YM") for the leftover months, then =Years+IF(LeftoverMonths>6,1,0) to apply the rounding rule exactly.

Fact box. On a worked example — last drawn Basic + DA of ₹40,000 and 10 completed years — gratuity is 40,000 × 15 × 10 ÷ 26 = ₹2,30,769, fully tax-exempt because it is below the ₹20 lakh cap. (Source: Payment of Gratuity Act formula.)

Who is eligible for gratuity?

Gratuity is payable to an employee who completes 5 years of continuous service in an establishment with 10 or more employees. The 5-year condition is waived if service ends due to death or disability, in which case gratuity is paid for the actual service.

Under the Code on Social Security, 2020 (in force since 21 November 2025), fixed-term employees become eligible after one year of continuous service — a significant change from the 5-year rule for regular employees.

How does the wage rule affect gratuity?

The Code on Wages requires wages (Basic + DA) to be at least 50% of total CTC. Because gratuity is computed on Basic + DA, restructuring salaries to meet the 50% floor raises the gratuity base and therefore the employer's liability. Recalculate gratuity provisions whenever salary structures change to stay 50%-compliant.

How to calculate gratuity step by step

Capture join date, exit date and last drawn Basic + DA.
Compute completed years with DATEDIF plus the 6-month rounding rule.
Apply the formula =ROUND(BasicDA*15*Years/26,0).
Check the ₹20 lakh cap — flag any taxable excess with =MAX(Gratuity-2000000,0).
Confirm eligibility (5 years, or 1 year for fixed-term, or waived on death/disability).
Pay within 30 days of the gratuity becoming due.

How Ankeshan helps: Ankeshan computes gratuity inside Excel from join and exit dates — applying the 15/26 formula, the 6-month rounding and the ₹20 lakh cap — and flags the fixed-term 1-year eligibility under the new labour codes. (Launching soon — join the waitlist.)

Frequently asked questions

Why is 26 used in the gratuity formula? Because a working month is treated as 26 days (excluding 4 weekly offs). Fifteen days' wages per year is therefore Basic+DA × 15 ÷ 26.

Does gratuity count completed years only? Mostly yes, but with a rounding rule: service of more than 6 months in the final year counts as a full year, while 6 months or less is ignored.

What is the maximum tax-free gratuity? ₹20 lakh for private-sector employees. Gratuity received above this ceiling is taxable in the employee's hands.

Can an employee get gratuity before 5 years? Regular employees need 5 years, except on death or disability. Fixed-term employees now qualify after 1 year under the Code on Social Security.

Is gratuity calculated on gross salary? No — only on last drawn Basic + DA, not gross. The 50%-of-CTC wage rule can enlarge this base.

Sources

Payment of Gratuity Act, 1972 (formula, eligibility, 30-day payment).
Code on Social Security, 2020 — labour.gov.in (fixed-term 1-year eligibility, in force 21 Nov 2025).
Income Tax Department — ₹20 lakh exemption ceiling (long-standing cap; no upward revision confirmed under the SS Code central rules notified May 2026).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.