Have you ever wondered what’s the formula for VAT calculation in Excel? Maybe you haven’t. I mean, nobody loves VAT. Now, I was going to start this post by saying “VAT Love it or Hate it” but let’s face facts. Nobody in their right mind loves VAT. However, getting the correct VAT formula, that’s something you could love. You could love the fact that Excel will automatically calculate your VAT without you ever having to think about it. You could love the fact that you can then copy, or AutoFill, that formula down and apply it to all of the other rows in your spreadsheet. Perhaps you may even love the fact that you can then put this VAT calculation in a Pivot Table.
What is the formula for calculating VAT in Excel?
So let me show you what’s the formula for VAT calculation in Excel in a way we can all understand. Well that depends. It depends on whether you want to work out gross from net, or net from gross.
Working Out the Gross for the Net
Let us look at the first formula, finding out the gross from the net. The formula would read as follows:
GROSS = (NET*VAT_RATE) + NET
Imagine that this was written as an Excel formula with the NET amount in cell A2 the VAT rate in cell B2. Your answer would be in cell C2. If all of this is true then the formula would be written as:
=(A2*B2)+A2.
Once you have entered the formula you could then AutoFill the formula down as far as you need.
How to Calculate the VAT Net from the VAT Gross?
Now that we’ve put that calculation to bed, it’s time to focus on the net from the gross. First, let’s assume VAT is 20%, the formula would be.
NET = (GROSS / 120) * 100.
If you’re wondering how this VAT formula calculates the NET from the GROSS this is how. You see, we can’t just remove 20% from the gross amount. What we first need to do is to divide the gross amount by 120. That way we would know how much one percent is.
After we’ve done that, we take the amount of one percent and multiply by 100. As a result we know how much the Net was.
Right then, now you know what the formula is for Vat calculation in Excel. We have a lesson on operator precedence in Excel if you want to know more.