Gratuity Calculator Template in Excel, Google Sheet, and OpenOffice to easily calculate your gratuity amount as per the Gratuity Act 1972 in India.
Gratuity Calculator with predefined formulas helps you calculate the end of service amount also known as Gratuity.
All you need to do is to enter your last drawn salary and total years of service and it will automatically calculate your gratuity amount. Let us understand the term first.
Table of Contents
What is Gratuity?
Gratuity is the end of service benefits provided by the employer to the employee for the service rendered to the organization.
An employee is entitled to get gratuity when he/she:
- Resigns
- Retires
- Laid off
- Death or Disablement
Eligibility Criteria For Gratuity
According to the Gratuity Act, an employee is paid gratuity if he/she completes a minimum service of 5 years with that particular employer.
Furthermore, in the case of death or disablement, there is no minimum eligibility period. Disablement means the loss of the earning capacity of an employee. It is of two types; temporary disablement & permanent disablement.
Gratuity Rule For Terminated Employees
The employer can cancel the gratuity payment of the employee even though he/she has completed 5 or more years only if the following conditions:
“An employee facing termination due to disorderly conduct. Disorderly conduct includes physically harming individuals during employment.”
Gratuity Payment Process
The gratuity payment process involves the following 4 step process:
- Apply
- Acknowledgment
- Calculation
- Disbursement
Thus, the eligible employee must send an application to his/her employer to process his gratuity payment. The employer then will accept the application.
Furthermore, the employer initiates the calculation process and sends it to the concerned department for disbursement. Finally, the concerned department disburses the Gratuity to the employee.
Formula To Calculate Gratuity
The formula to calculate Gratuity is as follows:
Last Drawn Salary X Total Years of Service X 15/26
Last Drawn Salary: Basic Salary + Dearness Allowance (DA)
Total Years of Service: Number of years of service in a company. The mathematical rule of point will apply here in calculating the years.
For example, if Mr. X completes 5 years and 3 months so it will 5 years and if it is 5 years and 6 months or above then it will be 6 years.
15/26: 15 is wages for 15 days and 26 are the working days of the month. In simple terms, it is approximately a half month’s salary (Basic + DA).
The gratuity amount has a ceiling of 20 lacs. In case, if an employee is eligible to get a gratuity that is more than Rs 20 Lacs then the employer is bound to pay only Rs 20 Lakh.
Moreover, if the employer wants to pay the gratuity amount of more than 20 Lacs then it has to be added as the performance bonus or Ex-Gratia.
Gratuity Calculator (India) Template (Excel, Google Sheet, OpenOffice Calc)
We have created and simple and ready-to-use excel template for Gratuity Calculator (India). Just enter only a few data and it will calculate the gratuity for you.
Excel Google Sheets Open Office Calc
Click here to Download All HR & Payroll Excel Templates for ₹299.
Additionally, you can download other useful HR Templates like Salary Sheet, Attendance Register, etc.
Let us discuss how to use this calculator to calculate gratuity.
Content of Gratuity Calculator Template (India)
This template consists of 2 sections: Employee Information and Employee Gratuity Calculations
Employee Information Section
In this section, you need to enter the name and your organization type. It can be a government organization or a non-government organization.
Gratuity Calculation Section
To calculate gratuity, you need to enter 2 details: Last drawn salary and Total years of service.
Applying the above-mentioned formula, the template calculates the gratuity for the given years of service.
The last drawn salary will not be a total take-home salary. It is only the basic salary in addition to the dearness allowance.
If your years of service are less than 5 years the gratuity calculator will display NIL.
Disclaimer: This template is for educational purpose. Kindly consult required authority before finalizing the gratuity amount.
Taxability Rules On Gratuity (India)
Basically, the gratuity is a retirement benefit and thus it is exempted under some circumstances according to the IT Act 1961. For taxation purposes, the IT department divides the employees into two categories: Government Sector Employees and Private Sector Employees.
If an employee (government sector or private sector) that receives gratuity during his service is taxable. But, if the employee receives at the time of his retirement, death, or superannuation then tax exemption rules will be applicable.
Taxation rules for Government and Private Sector employees are different. For government sector employees the entire gratuity is exempt from Income Tax.
Rules for Private sector employees also differ based on the following:
- Employees Covered Under Gratuity Act.
- Employees Not Covered Under Gratuity Act.
To calculate the taxable gratuity amount click on the Taxable Gratuity Calculator given under the next heading.
Other Gratuity Templates
We thank our readers for liking, sharing, and following us on various social media platforms.
If you have any queries or suggestions, please share them in the comment section below. We will be more than happy to assist you.
Frequently Asked Questions
Is the 5-year service rule applicable to all scenarios of gratuity?
The 5-year service rule is not applicable if the termination of an employee is due to death or disablement.
Can temporary staff or contract workers claim for gratuity?
Temporary staff or contract workers are only eligible for gratuity if they are legally considered as employees of the company.
What is the increased Taxable Gratuity Ceiling?
Previously, the amount was 10 lacs which is now raised to 20 lacs.
How can we file a nomination for Gratuity?
You can give your nomination by filling Form “F” at the time of joining your company.
