• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Download Gratuity Calculator (India) Excel Template

Download Gratuity Calculator (India) Excel Template

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?
  • Eligibility Criteria For Gratuity
    • Gratuity Rule For Terminated Employees
  • Gratuity Payment Process
  • Formula To Calculate Gratuity
  • Gratuity Calculator (India) Template (Excel, Google Sheet, OpenOffice Calc)
  • Content of Gratuity Calculator Template (India)
    • Employee Information Section
    • Gratuity Calculation Section
  • Taxability Rules On Gratuity (India)
  • Other Gratuity Templates
  • Frequently Asked Questions

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.

Gratuity Calculator (India)

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

Taxable Gratuity Calculator Download Taxable Gratuity Calculator Excel Template - Taxable Gratuity Calculator Excel Template is a simple and ready-to-use excel template to the amount of gratuity that is taxable under the Indian Income Tax Act. Income tax applicability differs from according to the companies falling/not falling under the Gratuity Act 1972 and also the type of company (Government or non-government). An employee is entitled […]

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.

« Download Salary Breakup Report Excel Template
Download Taxable Gratuity Calculator Excel Template »

Filed Under: HR Templates

You are Here: Home / Excel Templates / HR Templates / Download Gratuity Calculator (India) Excel Template

About Shabbir Bhimani

I have worked in Excel and like to share functional excel templates at ExcelDataPro.

HR Templates

  • Salary Sheet Excel Template
  • Daily Work Schedule Excel Template
  • Weekly Timesheet Excel Template
  • Corporate Salary Slip Excel Template
  • Employee Attendance Sheet Excel Template
  • Employee Performance Evaluation Excel Template
  • Employee Provident Fund Calculator Excel Template
  • Job Candidate Tracker Excel Template
  • Employee Resignation Schedule Excel Template
  • Employee TA Reimbursement Excel Template
  • Employee Contract Renewal Schedule Excel Template
  • Employee Overtime Calculator Excel Template
  • Simple Age Calculator Excel Template
  • Employee Mileage Log Excel Template
  • Multiple Employees Weekly Timesheet Excel Template
  • 9 Ready-To-Use Salary Slip Excel Templates
  • Salary Sheet With Attendance Register In Single Excel Template
  • Weekly Timesheet With Payslip Excel Template
  • Salary Breakup Report Excel Template
  • Gratuity Calculator (India) Excel Template
  • Taxable Gratuity Calculator Excel Template
  • Employee Training Log Excel Template
  • Employee Vacation Tracker Excel Template
  • Salary Arrears Calculator Excel Template
  • Resume/Cover Letter Excel Template
  • Professional Resignation Letter Excel Template
  • Employee Termination Letter Excel Template
  • Employee Promotion Letter Excel Template
  • Salary Certificate Format Excel Template
  • Absconding Letter Excel Template
  • Employee Disciplinary Memo Excel Template
  • Casual Leave Application Excel Template
  • Employee Transfer Letter Excel Template
  • Employee Show Cause Notice Excel Template
  • Promotion Request Letter Excel Template
  • Transfer Request Letter Excel Template
  • Job Rejection Letter Excel Template
  • Promotion Rejection Letter Excel Template
  • Transfer Refusal Letter Excel Template
  • Transfer Rejection Letter Excel Template
  • Employee Welcome Letter Excel Template
  • Employee Thank You Letter Excel Template
  • Layoff Notice Letter Excel Template
  • Employment Verification Letter Excel Template
  • Job Offer Letter Excel Template
  • Accommodation Request Letter Excel Template
  • Loan Application Letter Excel Template
  • Workplace Harassment Complaint Letter Excel Template
  • Internship Request Letter Excel Template
  • Additional Facility Request Letter Excel Template
  • Salary Increment Request Letter Excel Template
  • Employee Of The Month Certificate Excel Template

ExcelDataPro

Free Excel Pro Templates

  • Glossary
    • Accounting Glossary
    • HR & Payroll Glossary
    • Tax Glossary
  • Templates
    • Accounting
      • Financial Analysis
      • Financial Statement
    • Educational
    • Taxation
      • Federal Income Tax
      • GST
      • UAE VAT
      • UK VAT
    • Human Resources
      • HR & Payroll
      • HR Metrics
    • School Management
    • Social Media
    • Sales-Marketing
    • Personal Finance
    • Other
      • Health & Fitness
  • Functions
  • FAQ’s
  • Blog
  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2023. Content Licensed Under Creative Commons with Attribution Required

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT