Scenario
Calculating VAT is one of the staple business functions of Excel. There are dozens of different formulas you can use to calculate VAT. This one is without doubt the most efficient.
You can watch our Excel tutorial video on YouTube to find out how to calculate VAT from the gross amount, as well as a quick one-step formula to calculate VAT at 20%. Or if you’d prefer, keep reading for a step-by-step written explanation.
We start off by setting up a table that will tell us both the Gross and Net amounts. Columns A and B are populated by the user. Columns C and D are calculated fields. The examples below are taken from our own Expense claim forms.
The number format in Column B is set to ‘Percentage’. This means that a value of 0.2 will be displayed as 20%.
VAT calculations work on the premise that the gross amount is 120% of the net amount. The most efficient formula we can use to express this is to divide the gross amount by 120%. However, we only want to do this if the VAT Rate in Column B is 20%.
Since dividing any number by 100% (i.e. dividing by 1) doesn’t change the number, we can safely use the formula “1 + the value in column B” as our calculation, as below:
Using this formula, any expenses with a VAT rate of 0% will display the same net total and gross total. Any expenses with a VAT rate of 20% will display the net total minus the VAT.
Column D should then display the difference between the gross and the net amounts, i.e. the amount designated as VAT. To achieve this, we can simply deduct one from the other, as below.
And there you have it. Sometimes there is beauty in simplicity.
Calculate VAT – Download The Workbook
Get The Formula
The full formulas used in this blog are as follows:-
=A2/(1+B2) =A2-C2
Eoin Sharkey
Thanks for posting the Tax Year formula.
Simple but effective. My brain was too tired to work this out, and copying and pasting this allowed me to classify 5 years worth of Child Benefit statements into correct tax year in 30 seconds so …Yay !