UAE Gratuity Calculation Excel Template
Streamline your payroll and HR workflows with our official-rule UAE gratuity calculation formula in Excel. Built according to Federal Decree-Law No. 33 of 2021 with two-tier accrual, pro-rata service, and the statutory 2-year cap.
Master UAE Gratuity Calculation Formula in Excel
Single-Cell FormulaCopy and paste this universal formula directly into your spreadsheet. It automatically evaluates the 1-year eligibility threshold, applies 21 days for the first 5 years, 30 days for subsequent years, and caps the result at 24 months of basic salary.
=IF(F2<1, 0, MIN(B2*24, (MIN(5, F2)*21*(B2/30)) + (MAX(0, F2-5)*30*(B2/30))))B2= Monthly Basic Salary in AEDF2= Net Continuous Service in YearsB2/30= Daily Basic Wage per UAE Standard
- Returns
0if service is less than 1 full year (F2 < 1). - Pro-rates partial years accurately.
- Limits total payout to 2 years' salary (
MIN(B2*24, ...)).
How to Build Your UAE Gratuity Spreadsheet Table
Follow this structured column architecture to manage multi-employee end-of-service calculations accurately:
| Column | Header Name | Format / Formula | Explanation |
|---|---|---|---|
| A | Employee Name | Text | Worker identification |
| B | Monthly Basic Salary | Currency (AED) | Excludes housing, transport, utilities |
| C | Start Date | Date (YYYY-MM-DD) | First official working day |
| D | End Date | Date (YYYY-MM-DD) | Last working day |
| E | Unpaid Leave Days | Integer (e.g. 0) | Deducted from service per Art 51(5) |
| F | Continuous Years | =(D2-C2+1-E2)/365 | Exact decimal service duration |
| G | Daily Basic Wage | =B2/30 | MOHRE standard 30-day divisor |
| H | Final Statutory Gratuity | =MIN(B2*24, IF(F2<1, 0, (MIN(5,F2)*21*G2) + (MAX(0,F2-5)*30*G2))) | Final net payable severance in AED |
Validation: Excel Formula vs. Official Online Engine
We validated the Excel formula across standard tenure bands against our online calculation engine. Both yield 100% identical dirham figures:
| Scenario | Basic Salary | Tenure | Excel Result | Website Calculator | Status |
|---|---|---|---|---|---|
| Probation (< 1 Year) | AED 10,000 | 0.67 Years | AED 0.00 | AED 0.00 | 100% Match |
| 2 Years Service | AED 8,000 | 2.00 Years | AED 11,200.00 | AED 11,200.00 | 100% Match |
| 4 Years Service | AED 12,000 | 4.00 Years | AED 33,600.00 | AED 33,600.00 | 100% Match |
| 5 Years Max Tier 1 | AED 15,000 | 5.00 Years | AED 52,500.00 | AED 52,500.00 | 100% Match |
| 8 Years Senior | AED 20,000 | 8.00 Years | AED 130,000.00 | AED 130,000.00 | 100% Match |
| 25 Years Capped | AED 25,000 | 25.00 Years | AED 600,000.00 | AED 600,000.00 | 100% Match |
Need an instant calculation without opening Excel?
Use our interactive online calculator to test different dates, part-time hour splits, and currency conversions in real-time.
Official Legal Disclaimer & Scope of Application
This UAE Gratuity Calculator is an automated computation tool built strictly according to statutory standards codified under Federal Decree-Law No. 33 of 2021 (Regulation of Labour Relations) and Cabinet Resolution No. 1 of 2022. It is provided for personal evaluation, budgeting, and general estimation purposes only.
Individual contracts may carry explicit clauses, pre-existing valid agreements, non-compete terms, or approved alternative workplace savings funds (Cabinet Resolution No. 96 of 2023). For official labor disputes, file a grievance via the MOHRE Smart App or call the toll-free helpline at 600 590 000.