Download the free UAE gratuity calculation Excel sheet to estimate end-of-service gratuity using basic salary, start date, end date, unpaid leave and an optional work-pattern ratio. The workbook shows every formula instead of hiding the calculation.
UAE Gratuity Calculator Excel Sheet
Editable inputs, formula-based results and official-source notes. No macros.
What the Excel calculator includes
- Monthly basic salary input
- First and last working dates
- Unpaid-leave adjustment
- Full-time or verified work-pattern ratio
- 21-day and 30-day service split
- Two-year wage cap
- Formula notes and official-source links
How to calculate gratuity in Excel
The workbook uses the following logic:
- Adjusted service days = last working day − first working day + 1 − unpaid days.
- Adjusted service years = service days ÷ 365.25.
- Daily basic wage = monthly basic salary ÷ 30.
- First-five-year gratuity days = minimum of service years and 5 × 21.
- Later gratuity days = years above 5 × 30.
- Gross estimate = daily basic wage × gratuity days × work-pattern ratio.
- Final estimate = lower of gross estimate and the entered two-year wage cap.
Example Excel formula structure
=IF(ServiceYears<1,0,(BasicSalary/30)*(MIN(ServiceYears,5)*21+MAX(ServiceYears-5,0)*30)*WorkRatio)The downloadable sheet uses cell references rather than the named labels shown above. It is designed for transparency and easy checking, not payroll automation.
When the Excel sheet should not be used
Do not use the standard sheet as the final authority for DIFC, ADGM, domestic workers, UAE nationals, piece-rate workers, alternative savings schemes, disputed deductions or an employer plan that provides a different benefit. Use the relevant authority’s documents.
Excel sheet versus online calculator
| Feature | Excel sheet | Online calculator |
|---|---|---|
| Works offline | Yes | No |
| Shows formulas | Yes | Shows a result breakdown |
| Easy to print or attach | Yes | Print result button |
| Updates automatically | Only when you replace the file | Website can be updated centrally |
| Best use | Internal checking and record keeping | Fast personal estimate |
Keep a copy with your settlement documents
Save the completed Excel file with the employment contract, salary certificate, recent payslip, leave report and itemised final settlement. Avoid emailing sensitive salary records to unknown websites or services.