See a simple Excel VAT formula you can apply to your accounts spreadsheet. In this tutorial we will answer:
If you want to follow along then you are more than welcome to download the starting exercise file. If you want the completed template, then it's at the bottom of the page.
This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everday Excel hints and tips.
Adding different VAT rates into the same spreadsheets is possible so long as you structure the data in the way below:
First thing to do is to insert a column into which we will place the VAT rate. Right click on column C and select Insert.
Add VAT Rate as the column header.
Why should you know how to add predetermined figures in Excel? The reason is that it is so easy to make mistakes. Imagine if you enter the wrong percentage or even forget to add the percentage sign. Your figures could be totally out.
To prevent that from happening you can add predetermined figures in your accounts spreadsheet. That way you can select 0%, 5%, or 20% from a dropdown list. This very same list will also prevent you from entering in any other value.
There you go! All you now need to do is to adjust the width of the column and finish entering all of the VAT rates.
I would also like to mention that the amounts in the tutorial are just my guess and are in no way reflective of actual VAT amounts. If you would like to know them I would suggest you look at the UK governments guidance on VAT rates on different goods and services.
Now it's time to use the VAT rate we've entered to work out how much VAT you have paid on a product. Insert a column to the right of Received and title it VAT Amt.
In the cell below enter the following VAT formula:
Insert a column to the right, title it Net and enter the following to work out the Net:
Select Cells F6 and G6 then copy the formula using Autofill and drag down with the mouse to make a formula apply to a whole column in a spreadsheet. To make this process quicker, you can double-click on the small dot in the bottom right corner.
Apply a light green colour to cells E2 to G119.
Next thing to do is to work out how much of an amount is VAT. In this case depending on our purchases will need to determine how much money have we already paid on our purchases depending on whether the VAT amount is 5% or 20% VAT.
Insert a column to the right of the Expense column and title it VAT Rec. (This will be the column we will add up to calculate how much vat we can claim.
In cell E6 enter the following formula:
Use Autofill to make the formula apply to the entire column.
Change the background colour of cells D2 to E119 to light blue.
Just a couple of more things to do. One is to sum up the VAT amounts and display the totals at the top. The other is to update the running total formula.
Now the last thing is to update the running total formula as currently the formula is reference the wrong cells.
Well done if you have follow the step by step instructions down to this point. As you can see creating a VAT spreadsheet that will allow multiple rates of VAT is possible.
Here's the completed file should you feel that it's all a bit much.