In India, Gratuity is paid to an employee by an employer after completing 5 years of continuous service. Do you want to know what how to calculate gratuity amount in Excel and the Excel formula for Gratuity?
Then you will find this article useful. Not only we will learn how to use gratuity calculation formula 2023 but also use a gratuity calculator to do few examples of gratuity calculation in excel for different types of employees working in India (2023).
What is Gratuity & What is Eligibility for Gratuity?
Gratuity is a statutory benefit provided to employees by their employers as a gesture of appreciation for their long-term service. It is is calculated based on the employee’s last drawn salary and the number of years of service.
According to the Payment of Gratuity Act, 1972, all employees in India, regardless of their job roles, are eligible for getting gratuity benefits. But there are few conditions that need to be met.
So if you were to ask “Am I eligible to receive Employees Gratuity Benefit Amount?”, then the answer is as follows:
You become eligible to receive the gratuity benefit after completing 5 years of continuous service with the same company. Gratuity is payable to the employee on retirement/termination of employment after completing continuous service for not less than 5 years.
Now let’s see how gratuity benefit amount is calculated
What is Gratuity Formula (2023)?
To understand how to calculate the gratuity amount, you first need to know the simple formula of Gratuity Calculation. This formula is applicable to all the employees who are covered under the Payment of Gratuity Act, 1972.)
Formula for Gratuity = (15/26) x No. of Completed Years of Service x Last Drawn Salary
This is also referred to as the 15/26 Gratuity Formula by many.
In the above gratuity calculation formula, the definition of ‘last drawn salary’ means your Basic Salary + DA (Dearness Allowance if any). However, it excludes any allowances for housing, transport, or medical benefits.
The Number of Completed years of service is the number of years that the employee has worked for the current employer. To be eligible for gratuity payout, the employee must have completed a minimum of 5 years of continuous service.
So that is the Gratuity Calculation Formula 2023 or as many like to know the Gratuity Formula in Excel. If you are looking for the calculation formula from the previous years, even then the same formula is to be used. So you can consider it to be as the Gratuity Calculation Formula 2022, Gratuity Calculation Formula 2021 and Gratuity Calculation Formula 2020. You can use this gratuity computation formula to understand how is retirement gratuity calculated.
If you are not comfortable in calculating your gratuity due amount using the above formulas, there are lot of online gratuity calculators that are available. You may consider using the below ones
There are a number of gratuity calculators available online (like the one available on Income Tax portal as well – link). But if you want to do the calculation yourself, then you can do so using the Gratuity 15-26 Formula given above and as explained in the next section.
You can also use this simple Gratuity Calculation in Excel Calculator given for free download below:
Gratuity Calculation Excel Calculator (2023) – Free Download
Now let’s see how to calculate the gratuity amount by ourselves.
How is Gratuity Benefit Amount Calculated (2023)?
Let’s see how to calculate the gratuity amount using the 15-26 Gratuity Formula for the employees who are covered under the Gratuity Act:
Gratuity Amount = Last Drawn Salary x 15/26 x No. of Completed Years of Service
Here, the figure ‘15’ represents 15 days of salary for each year of completed service, and the figure of ‘26’ refers to the approximate number of working days in a given month.
Suppose you have served your organization for 12 years and your last drawn salary is Rs 1 lakh per month. Using the gratuity formula, we can calculate his gratuity as follows:
Gratuity = (1,00,000 × 15 × 12) / 26 = Rs 6.92 lakh
Let’s take another example.
Suppose a person is about to retire from his organization after 25 years and his last drawn salary is Rs 1.75 lakh per month. Using the gratuity formula, we can calculate his gratuity as follows:
Gratuity = (1,75,000 × 15 × 20) / 26 = Rs 20.19 lakh
And what to do in case the number of years in service is not a round figure? What if it is say 6 years and 3 months?
The rule says that after completion of 5 years, any part of the service (in months) which is in excess of 6 months is to considered as 1 year. And this rule doesn’t depend on whether it’s a 5-day week or 6-day week work rule.
For example – If you have completed 9 years and eight months in your organization, then your service period for gratuity calculation will be considered to be 10 years. But if your service tenure is 9 years and four months, then for the purpose of this calculation of gratuity, your tenure will be taken to be 9 years only.
Here is the link to Gratuity Calculator 2023 again for free download below:
Gratuity Calculator (2023) – Free Excel Download
That is a simple tool to help you do gratuity calculation in excel sheet.
But what about taxation of this amount?
Let’s see that angle now.
Is Gratuity Amount Taxable in India (2023)?
This is an important question. Does Employee’s Gratuity Benefit has any tax exemption or it is taxed?
As per the current gratuity rules, any gratuity amount received by an employee (whether a government or private sector employee) during his service is taxable as per the income tax slab rates.
But when gratuity is received by the employee at retirement, death or superannuation, then tax exemption rules change. In case of the Government Employees, the entire gratuity amount received is exempted from Income tax.
In case, of private sector employees, gratuity received is tax exempted only up to the extent of Statutory limit of Rs 20 Lakh. So if the gratuity paid is less than this amount, it is tax-free. If the gratuity exceeds the limit mentioned above, then it becomes taxable.
So if for example you work in private sector and on retirement, you receive Rs 12 lakh as gratuity benefit from your employer. But as per the Gratuity 15/26 Formula calculation, you are eligible to receive, let’s say only Rs 9 Lakh. And let’s remember that the maximum notified limit for tax-free gratuity is Rs 20 Lakh. So out of these three figures (Rs 12 lakh, Rs 9 lakh and Rs 20 lakh), the figure of Rs 9 lakh is the least one. So, the tax exemption is limited to the extent of Rs 9 Lakh only. This means that you have pay income tax on remaining Rs 3 lakh (Actual gratuity received Rs 12 lakh – Tax exempted gratuity amount of Rs 9 lakh)
It is important to note that the gratuity formula is only applicable to employees who are covered by the Payment of Gratuity Act, 1972.
So that was how to do Gratuity Calculation in Excel using the simple 15/26 Gratuity Formula. I hope this article helped you understand how to calculate gratuity using a gratuity calculator in India (2023) that gives you the simple to use gratuity calculation formula in excel.