How to Make UAE VAT Return Format in Microsoft Excel

A compact, well-structured Excel template can make preparing and submitting VAT returns to the Federal Tax Authority (FTA) much simpler. The following guide shows how to build a practical UAE VAT return template in Microsoft Excel that records daily VAT transactions, calculates VAT automatically, and links key totals to the return fields used in the FTA portal.

Why Build an Excel VAT template?

• Immediate visibility of how much VAT is payable or refundable at any time.
• Simple and low-cost solution for businesses without accounting software.
• Reusable structure that links transaction-level details to the VAT return boxes required by the FTA.

Overview of the Workbook Structure

Use one workbook with these sheets:

1. Info – Company details and reporting period.
2. Output –Standard-rated sales (taxable supplies).
3. Input – Taxable purchases and expenses (recoverable input tax).
4. Return – The VAT return layout that pulls totals from other sheets.
5. Export – zero-rated supplies (exports outside the UAE).
6. Import – imported goods/services and reverse charge calculations.

Step-by-Step: Build the template

1. Create the Info Sheet

Use this sheet to store the business basics that can be referenced across the workbook. Typical fields:

• Company name (example: ABC LLC)
• Tax Registration Number (TRN)
• VAT return period (example: 1 January 2025 to 31 March 2025)

2. Build the Output Sheet (Sales)

Record each sales invoice here. Suggested column headings:

• Invoice Number
• Invoice Date
• Customer Name
• Customer TRN
• Taxable Amount
• VAT Amount (formula)
• Net Amount

Use formulas so entries calculate automatically:

• VAT Amount = Taxable Amount * 5% (or 0.05)
• Net Amount = Taxable Amount + VAT Amount

Add an autosum row to total each column. Display company name and reporting period at the top by linking to the Info sheet.

3. Build the Input Sheet (Purchases and Expenses)

Record purchases and expenses from which input tax is recoverable. Use similar columns to the Output sheet:

• Invoice Number
• Invoice Date
• Supplier Name
• Supplier TRN
• Taxable Amount
• VAT Amount = Taxable Amount * 5%
• Net Amount = Taxable Amount + VAT Amount

Add totals and format the sheet for easy data entry.

4. Create the Return Sheet (VAT Return Mapping)

Design the Return sheet to match the FTA VAT return fields. Link the relevant totals from the Output, Input, Export, and Import sheets so the return fields populate automatically. Typical items to map:

• Company name, TRN, and return period (link to Info)
• Standard-rated supplies total (link to Output sheet totals)
• Zero-rated supplies total (link to Export sheet totals)
• Imports subject to reverse charge (link to Import sheet totals)
• Recoverable input tax (link to Input sheet VAT totals)
• Net VAT payable or refundable (calculated field)

You can find the reference number for a new VAT return in the FTA portal dashboard. Link that and other static fields as required.

5. Build the Export Sheet (Zero-rated supplies)

Use this sheet to record sales outside the UAE that are zero-rated. Minimal columns are sufficient:

• Invoice Number
• Invoice Date
• Customer Name
• Invoice Amount

Do not calculate VAT for these lines since the tax rate is zero. Link the export total to the zero-rated supplies field on the Return sheet.

6. Build the Import sheet

Record all purchases of goods and services from outside the UAE. Include customs details used for VAT and reverse charge calculations:

• Customs Declaration Number
• Customs Authority Name
• HS Code
• Value of goods (as per customs declaration)
• Customs Duty
• Total Value of Imports
• VAT Rate and VAT Amount

Typical formulas:

• Customs Duty = Value of goods * Customs duty rate (for example 5% if applicable)
• Total Value of Import = Value of goods + Customs Duty
• VAT Amount = Total Value of Import * 5%

Link the relevant totals to the Return sheet so imports populate boxes related to reverse charge and taxable imports.

Example Entries and How They Flow to the Return

Once the template is ready, enter transactions in the respective sheets. A few example entries illustrate how the return populates automatically:

• Record three sales invoices in Output. Taxable amounts calculate VAT and net automatically. The Output total links to the standard-rated supplies box on the Return sheet.
• Record purchases and expenses in Input, such as telephone or utility invoices. Input VAT totals link to the recoverable input tax field on the Return sheet.
• Record export invoices in Export. Totals appear in zero-rated supplies on the Return sheet.
• Record import transactions in Import using customs declaration data. The import totals and reverse charge VAT amounts auto-populate the appropriate boxes on the Return sheet.

Mapping to FTA return boxes

The Return sheet should mirror the FTA VAT return layout so totals pull through directly. Common mappings include:

• Standard rated supplies -> Standard rated supplies box
• Zero-rated supplies -> Zero-rated supplies box
• Imports -> Box for imports and reverse charge provisions
• Total VAT payable or refundable -> Final calculated field on the Return sheet

Verify the mappings against the FTA portal each period, and copy the reference number and any portal-specific fields into the Return sheet as needed.

Who benefits most from this template?

Small businesses and sole traders without dedicated accounting software will find this Excel approach particularly useful. The template provides immediate clarity on payable VAT, recoverable input tax, zero-rated sales, and import reverse charge liabilities.

For Our Accounting Services: Book a Meeting with Us.