Gratuity Calculator India

Download Gratuity Calculator India Excel Template

Ready-to-use Gratuity Calculator India Template in Excel, OpenOffice Calc, and Google Sheet to easily calculate the gratuity amount of employees.

With the help of this template, you can calculate gratuity for government, non-government, daily wages, and seasonal employees. Moreover, you can also calculate Gratuity for those employees that are not covered under the Gratuity Act as well as for deceased/disabled employees.

Download Gratuity Calculator Template (Excel, OpenOffice Calc & Google Sheet)

Microsoft Excel OpenOffice Calc Google Sheet

Additionally, you can also download other HR templates like Payroll Template With Attendance, Salary Slip Excel Template India, Salary Slip Format UAESimple Salary Sheet, and Employee Salary Sheet depending on the company requirement.

Feel free to contact us for the customization of this template as per your requirement. We also design new templates based on your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.

Gratuity Calculator India

How To Use Gratuity Calculator (India) Excel Template

Choose the type of employee you want to calculate the gratuity and enter the details in grey colored cells. Enter employee name, joining date, date of retirement/leaving. This will calculate the total tenure of the employee in terms of years and months.

Round of the years and enter the basic and dearness allowance of the employee. The template will calculate the following 3 things for you:

  1. Gratuity Amount.
  2. Tax-Exempt Gratuity Amount.
  3. Taxable Gratuity.

It is very easy to use as the template consists of predefined formulas. The template has been created according to the rules and guidelines of the Gratuity Act.

What is Gratuity?

Gratuity is the end of service benefit given by an employer to all the employees irrespective of his/her salary. The employer doesn’t need to pay the Gratuity to apprentices.

The employee is eligible to receive Gratuity at the time of retirement, resignation on completing at least 5 years of continuous service. For deceased and permanently disabled persons, the 5-year rule is not applicable.

This provision is governed by the Gratuity Act, 1972. Previously, the maximum limit was Rs. 10 Lakhs. In March 2018, Gratuity Act was amended and this limit has been increased to Rs. 20 Lakhs.

Formula To Calculate Gratuity

Gratuity calculations differ based on the type of employment and other factors. Thus, the formula differs accordingly.

Gratuity Formula For Government and Non-Government Employees Covered Under Gratuity Act

15/26 X (Last Drawn Basic Salary + Last Drawn Dearness Allowance) X Years of Service

Gratuity Formula Daily Wages Employees Covered Under Gratuity Act

15/26 X 90 Days Average Salary X Years of Service

Gratuity Formula Seasonal Employees Covered Under Gratuity Act

7/26 X Last Drawn Basic + DA (Terminal Salary) X Years of Service

Gratuity Formula Non-Government Employees Not Covered Under Gratuity Act

15/30 X (Last Drawn Basic Salary + Last Drawn Dearness Allowance) X Years of Service

In all the above scenarios, minimum completion of 5 years of continuous service is mandatory. However, in the case of deceased or permanently disabled employee completion of 5 years of service is not applicable. Such employees are eligible for Gratuity even if they haven’t completed 5 years.

Click on the link below to download:

Gratuity Act 1972

We have created 6 mini Gratuity calculators for 6 different scenarios. Let us understand the gratuity calculation of each type of employee in detail.

Important Note: Round of the service years based on mathematical rule. If the months are below 6 months it will not be calculated whereas if the months are 6 or above a year will be added.

Gratuity Calculator – Government Employee covered under Gratuity Act

Gratuity Government Employees

Insert the name of the employee, joining date, and retirement/leaving date. The template will calculate the duration of service. As you can see the service period is 16 years 1 month, we have rounded it to 16 years.

Insert last drawn basic salary and dearness allowance. The template consists of predefined formulas. Hence, it will automatically calculate gratuity amount, tax-exempt gratuity, taxable gratuity. The template uses the above-mentioned formula to calculate the gratuity.

The gratuity of government employees is tax-free. Thus, the maximum limit of Rs. 20 Lakhs is not applicable in this scenario.

Gratuity Calculator – Non-Government Employee covered under Gratuity Act

Gratuity Non-Government Employee

Similar to the above section, insert the name of the employee, joining date, and retirement/leaving date. The template calculates the duration of service using the DATEDIF function.

Insert last drawn basic salary and dearness allowance if applicable. Because, mostly in the private sector, very few companies pay dearness allowance. Add it to the basic salary and insert the total figure.

The template automatically calculates gratuity amount, tax-exempt gratuity, taxable gratuity. The template uses the above-mentioned formula to calculate the gratuity.

A gratuity of private-sector employee exempt up to a minimum of any of the following:

  • Rs. 20 Lakhs
  • Actual Gratuity

In simple terms, if the gratuity amount is below 20 Lakhs, it is fully exempted. Above 20 Lakhs the gratuity amount is taxable.

As you can see in the example above, the gratuity payment is Rs. 26,65,385. Thus, Rs. 20 Lakhs is exempt and the employee has to pay income-tax on Rs. 6,65,385.

Gratuity Calculator – Piece Rate Employee covered under Gratuity Act

Gratuity Piece Rate Employee

As mentioned above, the calculation for Piece Rate employees is a little bit different from the rest. First, you need to find the average of the last 3 month’s wages. Multiply the average with 90 days. You can use the working sheet to calculate the average of 90-day wages.

Insert the name of the employee, joining date, and retirement/leaving date. Calculate and enter Average daily wages. The template calculates the 90-day average wage for the employee. It calculates the gratuity, tax-exempt gratuity, and taxable gratuity.

The gratuity of Piece Rate employees is exempt up to a minimum of any of the following:

  • Rs. 20 Lakhs
  • Actual Gratuity

In simple terms, if the gratuity amount is below 20 Lakhs, it is fully exempted. Above 20 Lakhs the gratuity amount is taxable.

Gratuity Calculator – Seasonal Employee covered under Gratuity Act

Gratuity Seasonal Employee

The gratuity of seasonal employees is calculated based on the season. These employees work during a stipulated time of the year. Hence, they are known as Seasonal Employees.

Insert the name of the employee, joining date, and retirement/leaving date. Enter the last drawn basic salary and DA if applicable. Insert seasons worked per year. Usually, it is similar to the number of years.

The template automatically calculates the rest for you. Moreover, similar to the above cases, the 20 Lakh limit is also applicable to this scenario.

Gratuity Calculator – Non-Government Employee not covered under Gratuity Act

Gratuity Not Covered Under Gratuity Act

For those not covered under Gratuity Act, the calculations are done on 30 days basis instead of 26 days. Thus, their gratuity is amount is a little less compared to those covered under the act.

Enter the same details as done earlier. In the gratuity formula, the template uses 15/30 instead of 15/26. Insert seasons worked per year. Usually, it is similar to the number of years.

The template automatically calculates the rest for you. Moreover, the ceiling here is 10 Lakh instead of 20 Lakh compared to employees covered.

Gratuity Calculator – Deceased/Disabled Employee Covered Under Gratuity Act

Gratuity Deceased or Disabled Employees

While calculating the gratuity of a deceased or disabled employee the limit of 5 years of continuous service is not applicable. Hence, the template calculates the gratuity even if the service period is less than 5 years.

The user needs to enter similar details like name, joining date, retirement/resignation date, rounded off service period, basic, and DA(if applicable). 20 Lakhs limit is applicable to this category also.

Frequently Asked Questions

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.

Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.

Exit mobile version