VAT workings template for Excel, South Africa
Short answer: VAT payable is the VAT you charged on sales (output VAT) less the VAT you can claim on purchases (input VAT). This free Excel template adds both up for the period you choose and shows what you owe SARS, or what they owe you. It gives you the workings for your VAT201. It is not the return itself.
Download
| File | What is in it | Download |
|---|---|---|
| VAT workings template (.xlsx) | Settings, a Sales sheet and a Purchases sheet (500 rows each), a VAT summary, and a How to use sheet. Live formulas and drop-down lists, with sample rows to overwrite. | Download Excel |
How to use it
- Settings: type your business name, VAT number, the first and last day of the VAT period, and check the rate (15%).
- Sales: for each invoice, type the date, number and client, choose Standard-rated, Zero-rated or Exempt, and type the amount before VAT. The VAT is worked out for you. Type credit notes as negative amounts.
- Purchases: type the date, supplier and the amount you paid including VAT. The VAT inside the amount is worked out for you (the amount times 15, divided by 115). Choose Yes or No for whether you can claim it.
- VAT summary: read the result. It only counts entries dated inside the period on the Settings sheet, so you can keep adding to the same file month after month and change the dates.
What the sample shows
| Line | Amount |
|---|---|
| Standard-rated sales, before VAT (4 entries, 1 credit note) | R19,650.00 |
| Zero-rated sales, before VAT (an export) | R12,000.00 |
| VAT charged on sales (output VAT) | R2,947.50 |
| VAT on capital goods (a pipe bender) | R298.70 |
| VAT on other goods and services | R617.61 |
| Total input VAT claimed | R916.31 |
| VAT payable to SARS | R2,031.19 |
Two purchases are marked No: a client lunch, and an insurance premium that has no VAT in it. Entertainment is generally not claimable, and you cannot claim VAT that was never charged. Capital goods and other purchases are shown on separate lines because the VAT201 asks for them separately.
Things that catch people out
- You need a valid tax invoice to claim. No tax invoice, no claim. See what a tax invoice must show.
- Invoice basis or payments basis. This template counts a sale on the date of the invoice. If you account for VAT on the payments basis, enter each sale on the date you were paid and confirm with your accountant.
- Credit notes reduce output VAT. Type them as negative sales.
- Not every purchase carries VAT. Insurance, bank charges and some other items do not. Mark those No, or leave the VAT out.
- The rate is a setting. It is 15% now. If it ever changes, you change one cell.
Let the numbers work themselves out
Zuvo works out VAT charged, VAT on expenses and VAT payable from your invoices and expenses for any period, and downloads it as an Excel workbook with live formulas that you can hand to your accountant.
Try Zuvo free for 14 days, no cardHow we checked it
Every formula was recalculated with a separate calculation engine and compared with the saved figures. We then changed the inputs to test the cases the sample does not show: a sale just outside the period, entries on the first and last day, a different VAT rate, a purchase switched to claimable, and a quiet month that produces a refund. We have not opened it in every spreadsheet program. It should work in Excel, Google Sheets and LibreOffice.
Questions people ask
What is the difference between output VAT and input VAT?
Output VAT is the VAT you charge your clients on sales. Input VAT is the VAT you pay your suppliers on purchases for the business. You pay SARS the output VAT less the input VAT you can claim. If the input VAT is bigger, SARS owes you a refund.
How do I work out the VAT inside a price that already includes VAT?
Multiply the total by 15 and divide by 115. For R1,150 that is R150. The template does this on the Purchases sheet. Our VAT calculator adds or removes VAT for a single amount.
Is this the same as the VAT201?
No. The VAT201 is the return you submit to SARS. This template gives you the workings behind the main figures: output VAT, input VAT on capital goods and on other purchases, and the amount payable or refundable. Check it with your accountant before you submit.
Can I claim VAT on everything I buy?
No. You need a valid tax invoice, the purchase must be for your business, and some items are blocked, such as entertainment and most passenger cars. If you are unsure, ask your accountant.
Do I have to be VAT-registered to use it?
It is for VAT vendors. Registration is compulsory once your taxable turnover passes R2.3 million in any 12 months, and you can register voluntarily from R120,000 (these thresholds changed recently, so confirm the current position with SARS). See do I need to register for VAT.
VAT workings and general guidance, not tax advice. The standard rate is 15%. Check the rules at sars.gov.za or with your accountant before you submit a VAT201.