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.
By the way if you live in and around London and are looking for Excel training for your organisation then check out this Excel 365 Intermediate Training Course in London run by our close partners Computer Tutoring.
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.
Accounts tutorial on VAT calculation training video.
In today’s swiftly moving professional world, time is of the essence. Our dynamic Excel 365 beginners’ course, designed as a 3-hour session, offers an efficient and engaging way to master Excel quickly. Tailored specifically for those pressed for time, this half-day course is far from your typical training session; it’s an interactive, fun-filled learning experience, akin to a game show, led by live instructors. Not just confined to the classroom, we provide a full month of post-training support, ensuring that your newly acquired skills are applied effectively in real-world scenarios. Whether you’re a spreadsheet sceptic or a busy professional, our course promises to transform your Excel abilities in just half a day, offering the perfect blend of practicality and enjoyment.