Gratuity Calculator Excel Sheet Free Download India [Formula & Template]

Gratuity Calculator Excel: Hello dear, Are you searching for the gratuity excel calculator download link? Then you don’t needs to worry. Here in this article you will get the 100% working calculator excel sheet with updated formula. If you are a salaried employee in India, gratuity is not just a retirement benefit it is a legally protected financial reward for your long-term service. Yet many employees still ask basic but important questions like: What is gratuity calculation? Is gratuity calculated on basic salary or CTC? What is 15/26 in gratuity calculation?

As a finance calculation expert, I can confidently say that understanding gratuity and calculating it accurately using a Gratuity Calculator Excel sheet gives you complete financial clarity. Whether you are planning retirement, switching jobs, or simply estimating your benefits in 2026, an Excel-based gratuity calculator makes the process transparent and error-free.

In this detailed post, you will get the below details:

• Gratuity calculator Excel Sheet
• Gratuity rules in India
• Eligibility for gratuity
• The exact gratuity calculator Excel formula
• Step-by-step method to calculate gratuity in Excel 2026
• Ready-to-use template structure
• FAQs covering all practical doubts

Let’s break it down in a structured and professional manner. Read the complete post and download our pro level gratuity Calculator excel sheet. Share your experience in the below comment section. Okay, let’s get into the topic below.

Download Gratuity Calculator Excel Sheet Free

What is Gratuity Calculation?

Gratuity is a lump sum amount paid by an employer to an employee as a token of appreciation for long-term service. It is governed by the Payment of Gratuity Act, 1972. In simple terms, what is gratuity calculation?

It is the process of computing the gratuity amount based on:

• Last drawn basic salary
• Dearness allowance (if applicable)
• Total years of continuous service

The formula differs slightly depending on whether your company is covered under the Gratuity Act or not. Most private and corporate employees fall under the Act.

Gratuity Rules in India (Latest 2026 Update)

Before using a Simple gratuity calculator Excel, you must understand the core gratuity rules in India.

1. Eligibility for Gratuity

An employee becomes eligible for gratuity if:

• He or she has completed 5 years of continuous service in an organization.
• Exception: In case of death or disability, the 5-year rule does not apply.

2. Maximum Gratuity Limit

As per current regulations:

• The maximum gratuity payable is ₹20 lakh.
• Amount received up to this limit is tax-free for employees covered under the Act.

3. Coverage

The Payment of Gratuity Act applies to:

• Companies with 10 or more employees
• Private employees
• Government employees
• Educational institutions

If you are searching for a Gratuity calculator for private employees, the formula under the Act applies to you in most cases.

Is Gratuity Calculated on Basic Salary or CTC?

This is one of the most common questions.

Gratuity is calculated on:

• Basic Salary
• Dearness Allowance (DA)

It is NOT calculated on CTC, gross salary, HRA, bonuses, or allowances.

So if your CTC is ₹12,00,000 but your basic salary is ₹40,000 per month, gratuity will be calculated only on ₹40,000 (plus DA if applicable).

What is 15/26 in Gratuity Calculation?

Many people get confused when they see 15/26 in the formula.

Here is the logic:

• 15 = 15 days’ salary for every completed year of service

• 26 = Total working days in a month (excluding Sundays)

Under the Gratuity Act, the formula assumes 26 working days per month. That is why the gratuity calculator Excel formula includes 15/26.

Official Gratuity Calculation Formula

For employees covered under the Payment of Gratuity Act:

Gratuity = (Last Drawn Salary × 15 × Number of Completed Years) / 26

Where: Last Drawn Salary = Basic + DA

Example Calculation

Particulars	Value
Basic Salary	₹40,000
Years of Service	10
Formula	(40000 × 15 × 10) / 26
Gratuity Amount	₹2,30,769

This is the exact structure you will implement inside your Gratuity Calculator Excel sheet.

How Do I Calculate My Gratuity in Excel 2026?

If you are wondering, How do I calculate my gratuity in Excel 2026? follow these professional steps.

Step 1: Create Input Section

In Excel:

Cell	Description
B2	Last Drawn Basic Salary
B3	Dearness Allowance
B4	Total Years of Service

Step 2: Calculate Total Salary

In B5: =B2+B3

Step 3: Apply Gratuity Formula

In B6: =(B5*15*B4)/26

This is the exact gratuity calculator Excel formula. Now whenever you change salary or years, gratuity updates automatically.

Simple Gratuity Calculator Excel Template Structure

Here is a clean template format you can use:

Input Section

• Basic Salary
• Dearness Allowance
• Total Years of Service

Output Section

• Total Eligible Salary
• Gratuity Payable

You can also enhance your sheet by adding:

• Data validation
• Conditional formatting
• Maximum limit check (₹20 lakh cap)
• Tax exemption calculation

Advanced Gratuity Calculator Excel (Professional Version)

As a finance expert, I recommend adding these smart features:

1. Auto Year Rounding Rule

If service exceeds 6 months in a year, round up.

Example:

• 7 years 7 months = 8 years
• 7 years 5 months = 7 years

Excel Formula:

=IF(Months>=6,Years+1,Years)

2. Maximum Limit Check

=MIN(Calculated_Gratuity,2000000)

3. Taxable Portion Formula

If gratuity exceeds ₹20 lakh:

=MAX(0,Calculated_Gratuity-2000000)

This makes your Gratuity Calculator Excel industry-ready.

Gratuity Calculator for Private Employees

For private employees, the formula remains:

Gratuity = (Basic + DA) × 15/26 × Years of Service

However, check:

• Whether your company is covered under the Act
• Whether your employment contract provides better benefits

Some companies offer enhanced gratuity calculation based on 30 days instead of 26.

Special Case: Companies Not Covered Under the Act

If the company is not covered:

Gratuity = (Last Drawn Salary × 15 × Years) / 30

Here, 30 days are considered instead of 26.

Always confirm with HR before final calculation.

Common Mistakes in Gratuity Calculation

Avoid these errors:

• Calculating on CTC instead of basic salary
• Ignoring rounding rule of 6 months
• Including bonus and allowances
• Not applying ₹20 lakh limit
• Using wrong denominator (26 vs 30)

A properly structured Gratuity Calculator Excel sheet prevents all these mistakes.

Why Use a Gratuity Calculator Excel Instead of Online Tools?

Online calculators are useful, but Excel gives you:

• Full transparency
• Customization
• Scenario analysis
• Long-term financial planning
• Tax estimation integration

You can even integrate it with:

• Retirement planning sheet
• PF calculation sheet
• Pension estimation models

For financial planners and HR professionals, Excel is the superior tool.

Practical Example with 2026 Salary Growth Scenario

Let’s assume:

• Current Basic Salary: ₹50,000
• Expected Annual Growth: 8%
• Service Period: 15 years

Using Excel, you can project future salary and estimate gratuity in advance. This helps in retirement corpus planning.

FAQs on Gratuity Calculation

1. What is gratuity calculation?

Gratuity calculation is the process of determining the lump sum benefit payable to an employee after completing at least 5 years of service, based on last drawn basic salary and years of service.

2. Is gratuity calculated on basic salary or CTC?

Gratuity is calculated only on Basic Salary + Dearness Allowance, not on CTC or gross salary.

3. What is 15/26 in gratuity calculation?

15 represents 15 days’ wages for every year of service. 26 represents working days in a month under the Gratuity Act.

4. How do I calculate my gratuity in Excel 2026?

Use the formula: =(Basic+DA)*15*Years/26
Enter salary and service years as input cells and apply the formula in Excel.

5. What is the minimum eligibility for gratuity?

An employee must complete 5 years of continuous service, except in case of death or disability.

6. What is the maximum gratuity amount in India?

The maximum limit is ₹20 lakh under current rules.

7. Can private employees receive gratuity?

Yes. If the company has 10 or more employees, private employees are eligible under the Gratuity Act.

Final Words

Understanding gratuity is not just about knowing a formula; it is about securing your financial future. A professionally designed Gratuity Calculator Excel sheet empowers you with clarity, precision, and control over your long-term benefits.

By applying the correct gratuity calculator Excel formula, understanding what 15/26 in gratuity calculation means, and ensuring gratuity is calculated on basic salary rather than CTC, you eliminate costly mistakes.

Whether you are a private employee, HR professional, or financial planner, building or downloading a Simple gratuity calculator Excel template allows you to instantly estimate your retirement benefit with accuracy. In 2026 and beyond, financial awareness is no longer optional.

It is essential. With proper knowledge of gratuity rules in India and eligibility for gratuity, you can confidently plan your retirement corpus and make informed career decisions. A well-structured Excel calculator is not just a tool; it is your financial planning companion that grows with your salary and service years.

I hope the above excel calculator will help you to easily calculate your gratuity. If you still have any qustions on the above calculator or formula please feel free to ask in the below comment section. Follow this blog for more such finance related tips and calculators. Thanks for your visit.

Tags: what is gratuity calculation? gratuity calculator excel formula, How do I calculate my gratuity in Excel 2026? Is gratuity calculated on basic salary or CTC? What is 15/26 in gratuity calculation, Eligibility for gratuity, Gratuity rules in india, Simple gratuity calculator excel, Gratuity calculator for private employees.