Gratuity Calculation in Excel: Can You Spot Any Errors in My Sheet?

I prepared an Excel sheet to calculate Gratuity. Kindly let me know if any corrections are needed.

Please clarify the calculation of gratuity. I know the formula, which is basic + DA * 15/26 * no. of years. Please provide an example to explain this.

Regards,
Manpreet

Dear Vivek Sang, thank you for your advice. Actually, when I try to calculate this formula, the payable amount is not calculated correctly. That's why I need an example. Please help me if you can.

Regards,
Manpreet

Please inform me of the figures you entered and the calculation result before I can advise you on the procedure.

Gratuity Calculation Explained

For gratuity, the minimum period of service is 4 years and 8 months. Suppose an employee has completed 5 years of service; then he is entitled to 15 days of the average salary last drawn by the employee per completed year. In a month, there are 26 working days. Hence, the average 15 days' salary for 5 completed years will be (15/26) * 5 years.

Salary here means Basic + Dearness Allowance, and that too, the last salary drawn by the employee. If the employee's basic salary is 4000 and the Dearness Allowance is 3000, then the complete formula for gratuity will be 7000 * (15/26) * 5. Hope it's clear to all.

I am Ganesh from Hyderabad. Nice to meet you in this forum. The gratuity calculation is: Basic x No. of years of service x 15 / 26

For example: If a person worked for 6 years and their basic salary is Rs. 10,000/-, then the gratuity amount would be Rs. 10,000/- x 6 x 15 / 26 = Rs. 34,615/-. Here, 15 represents half-month salary, and 26 represents the total number of days in a month (accounting for weekends and holidays).

If you have any queries, please feel free to contact me at [Phone Number Removed For Privacy Reasons].

Regards,
PGR

Updated the file. Please check and let me know in case of any errors.

Regards,
Vivek Sang

New Updated Gratuity Calculation Utility in Excel

Please let me know your reviews regarding the same.

Am I eligible for gratuity? If yes, please let me know the detailed calculation. How much will I receive in the form of gratuity?

My present Basic + DA is $18,500 per month.

Help me please, it's an urgent matter.

Regards,
Prashant

First of all, thank you for the Excel sheet. You have mentioned that if an employee completes 4 years and 8 months in a company, then he/she is eligible for gratuity. So, if an employee completes 4 years and 9 months in a company, how will the gratuity be calculated? I tried using the Excel sheet, but no value is shown in this case. Do I need to consider it as completing 5 years?

Regards,
Sarita

In BPOs, the monthly working days are 22 as there is a weekly off for Saturday as well. So in that case, will it be counted as 15/22 or will the formula of 15/26 still be applied?

Thanks,
Sarath

Dear All,

Can anyone suggest, if an employee joins an organization in the month of December (Let's say 01.12.1991) and retires on (Let's say 15.05.2015), what is the number of years he is eligible for the payment of gratuity?