How to Calculate Gratuity in Excel (5 Easy Steps + Free Template)

🧮 How to Calculate Gratuity in Excel: Step-by-Step Guide for Indian Employees

Gratuity is a crucial part of an employee’s financial planning, yet many are unaware of how much they are entitled to or how it’s calculated. If you’ve ever wondered how to calculate gratuity in Excel, you’re in the right place.

In this guide, we’ll explain everything about gratuity, including eligibility, formula, tax exemptions, and — most importantly — how to create a simple Excel formula to compute it instantly.

💡 Try Our Online Gratuity Calculator Tool
Don’t want to use Excel? Check your gratuity instantly with our online tool here:
👉 Gratuity Calculator Online Tool

---

---

✅ What is Gratuity?

Gratuity is a lump-sum amount paid by an employer to an employee when they leave the company after serving a continuous period of five years or more. It’s a token of appreciation for the employee’s long-term service.

---

✅ Gratuity Eligibility

You are eligible for gratuity if:

• You have completed at least 5 years with the same employer.
• You have left the job due to resignation, retirement, or death/disability.

---

✅ Gratuity Calculation Formula

The formula to calculate gratuity for employees covered under the Payment of Gratuity Act, 1972 is:

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

Where:

• 15 = days of salary per year
• 26 = number of working days in a month
• Last Drawn Salary = Basic Salary + Dearness Allowance

Example:
If your last drawn salary is ₹30,000 and you worked for 10 years:

Gratuity = (15 × 30000 × 10) ÷ 26 = ₹1,73,077

---

✅ How to Calculate Gratuity in Excel

Now let’s explore the core part — how to calculate gratuity in Excel. Whether you’re an HR executive, a business owner, or an employee planning retirement, Excel is a great way to automate this calculation.

---

✅ Step-by-Step Excel Formula

Step 1: Set Up Your Excel Sheet

Create the following columns:

Employee Name	Basic Salary	Dearness Allowance	Total Salary	Years of Service	Gratuity

Step 2: Enter This Formula

Use this formula in the Gratuity column (assuming Total Salary is in column D and Years in column E):

=IF(E2>=5, (15*D2*E2)/26, 0)

This formula ensures that only employees who served 5+ years are eligible.

Step 3: AutoFill and Format

Drag the formula down to apply it to other rows. Use Excel’s number formatting for currency (₹).

---

✅ Gratuity Calculation for Private Sector Employees

Private sector employees also follow the same gratuity calculation method if the company is covered under the Payment of Gratuity Act. For unregistered companies, some employers use a different formula — for example, based on 30 working days instead of 26.

Alternate formula:

=(15 × Last Salary × No. of Years) ÷ 30

You can adjust the Excel formula accordingly:

=IF(E2>=5, (15*D2*E2)/30, 0)

---

✅ Tax Exemptions on Gratuity

Under Section 10(10) of the Income Tax Act, gratuity is tax-free up to ₹20 lakhs for government employees and ₹10 lakhs for private employees. Always consult with a tax advisor for personalized insights.

Learn more on the Income Tax Department website 📚

---

✅ Download Free Gratuity Excel Template

🎁 Save time with our ready-to-use Excel gratuity calculator.

👉 Download Free Gratuity Calculator Excel Template

You can customize it with your employee data and formulas, making HR calculations smooth and error-free.

---

✅ Benefits of Using Excel for Gratuity Calculation

• 🔄 Automatic calculation
• 💾 Save historical data
• 📈 Easy to update and modify
• 📤 Exportable for compliance or reporting

---

✅ Conclusion

Learning how to calculate gratuity in Excel is an essential skill for HR managers, accountants, and employees. Whether you want to plan your retirement or manage payroll benefits, Excel offers a fast and transparent solution.

For quick results, don’t forget to try our online gratuity tool:

👉 Click here to use our Free Gratuity Calculator

It’s 100% free, accurate, and built for Indian salary structures.

---

✅ FAQs for how to calculate gratuity in Excel in India 2025