Calculate VAT in Excel

So How to Calculate the VAT in Excel?

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.

Net from Gross VAT Formula

Calculate the Net From Gross - 2:00

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:

Work Out Gross from Net in Excel

Work out the Gross from the Net in Excel - 1:26

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 how does the VAT formula work

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.

How the Gross from Net is Calculated?

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.

How to Work Out VAT Net Amount?

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:

1. First the Gross amount is divided by 120. This is the 100% of the actual amount, adding the 20% of VAT. This part of the formula will let you know what 1% of the value is. If we used the above example of £4,800 the result of this part of the formula would be £40.
2. The second part of the formula multiplies the £40 by 100. Why 100? That's because we want to know 100% of the value. The amount before we added 20% of VAT to it. The result in Excel will be £4,000.

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.

The Importance of Accurately Calculating VAT in Excel

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:

• All of your issued invoices.
• All received invoices.
• Records of imports and exports.
• Any debit or credit notes.
• All of your issued Invoices.
• Business entertainment and any not VAT reclaimable items.
• Any goods you give away for whatever reason. This includes private use.
• Any billing agreement where the customer prepares the invoice.

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.

• How to do Super Easy Accounts in Excel?
• How to make your Spreadsheets Look Good?
• Excel VAT Formula for different VAT rates

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.

Other Excel Accounts Tutorials

Super Easy Accounts in Excel?

How to Make Your Spreadsheet Look Good?

Calculate VAT in Excel

Excel VAT Formula

Money Made Last Month

Pivot Tables for Beginners

Excel Financial Year Formula

Add Calculated Field to Pivot Table

Pivot Table Conditional Formatting

Excel Summary Sheet

SUMIF Excel Multiple Sheets

Clean up Messy Excel Data