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.
Table of Contents
Download Gratuity Calculator Template (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 UAE, Simple 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.
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:
- Gratuity Amount.
- Tax-Exempt Gratuity Amount.
- 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:
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
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
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
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
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
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
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
The following interruptions are permitted: Sickness, accident, leave, absence from duty without leave, temporary lay off, strikes, lockouts, or Cessation not due to any fault of the employee. An employee is considered to be in continuous service for a season only if he/she has actually worked for not less than 75% of the total number of days his/her establishment was operational. 15 is wages for 15 days and 26 is the total working days of the month. In simple terms, it is a half month’s salary (Basic + DA). The maximum amount of gratuity payable is 20 lakhs. If an employee's gratuity is above 20 lakh, the employer is bound to pay only Rs 20 Lakh. Moreover, an employer can pay at will but it has to be reported as a performance bonus or ex-gratia. It is a 4 step process. First, the employee makes an application. The employer acknowledges the receipt of the application. After the acknowledgment, the same is sent for calculation and finally, the employer disburses the amount.What are the permitted interruptions in continuous service rule?
What is the minimum amount of time a seasonal employee has to work to avail gratuity?
What is 15/26 in the Gratuity Formula?
If gratuity is above 20 Lakhs, is an employer liable to pay the total sum?
What is the process to obtain Gratuity?
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.