Est. 2002
Tell Me More
Calculate VAT in Excel finding out the NET from the Gross and the Gross from the NET. Excel Percentage calculation to see how to work out the vat net amount.
If you want to find out the Net from the Gross in Excel then you can use the following formula:
=A2/(1+B2)
Cell A2 would contain the Gross amount, Cell B2 would contain the current VAT rate and the above formula you would enter in Cell C2. So when you create an Excel VAT spreadsheet it should look like:
Now it's time to work out the the Gross from the Net in Excel. Many find this the easier of the two calculations and can be achieved by using the following formula:
=A2*(1+B2)
In the above formula Cell A2 would be the Net amount, Cell B2 would be the VAT rate and resulting Gross amount formula that you can see above would be entered into cell C2.
So if you've read this far you'll be wanting to know how the VAT formula works. This next section will give you the confidence that 2 formulas will give you an accurate result. Let's look at the Gross from the next first.
This formula can be written as:
Gross = NET * 1.2
So whatever amount the Gross is you multiply it by 1 with the percentage .2 added on. So if the VAT rate was 17.5% the formula would be:
Gross = NET * 1.175
You're basically taking the total and adding .175. That's the way you want to tackle percentages in Excel. That's because percentages are decimals. (.10 = 10% | .20 = 20% | .99 = 99%) and so on.
Now that you know that it should be relatively straightforward to add on a percentage amount to a figure.
Working out the VAT Net amount is a little trickier to explain. this is because you are having to divide the figure. Let's have a look at the problem.
Say the Gross amount is £4,800, you might think that you need to simply take 20% away to get the total. So you happily enter a formula like =4800 * .8 and think that it will be correct? Wrong!
The problem here is that you are removing 20% from a figure that's already had 20% added to it. So what you really need to do is find out what the original figure is. Before you added 20% to it.
Now, the formula I gave you earlier is the easiest and shortest way to solve this problem. However, it might not be the easiest way to explain how it's done. So I want you to look at the following formula, which might be a little easier to digest.
Net = Gross / 120 * 100
The way this formula works is:
Because in excel we already know that .2 is equivalent to 20%. Instead of dividing the total by 120 then multiplying by 100. We can simply divide the total by itself add .2, in effect 120%, to arrive at the same result.
It's important and a legal requirement to keep accurate VAT records. Using Excel it can make the job a whole lot easier.
Some of the records you'll have to keep are:
Please see our other accounts tutorials in this series to show how you can apply different VAT amounts in your accounting sheets.
So there you have it, how you calculate VAT in Excel. Not so bad as you thought it might have been eh? Please take the time to see other Excel tutorials in this series.
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 everyday Excel hints and tips.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!
At Computer Tutoring, we prioritise your privacy and only collect the necessary information to provide our services. We will only share the personal data you provide in a form if you have opted in for those services. Our website also uses Google Analytics. You have the right to access, amend, or request deletion of your personal data by contacting us at info@computertutoring.co.uk.
For more details, please read our full Privacy Policy.
