• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Blog
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Blog
  • Contact Us

Calculate VAT in Excel

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.
Calculate_VAT_01
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:
Calculate_VAT_02
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.
Calculate_VAT_03
And there you have it. Sometimes there is beauty in simplicity.

Calculate VAT – Download The Workbook

Calculating VAT (workbook)

Get The Formula

The full formulas used in this blog are as follows:-

=A2/(1+B2)
=A2-C2
  • 14 Jan
  • Tobias Owen
  • Excel
  •  0 Comments
  •  Like
  • Excel , Expenses , Net Expense , VAT

Share This

Related Posts

  • Tax Year Excel Formula
  • Date Functions : Excel Calendar (Part 2)
  • Rock Paper Scissors
  • Date Functions : Excel Calendar (Part 1)
Supplier Performance Dashboard →← Calculate VAT in Excel
2

Comments

  1. Excel Calendar - Part 2 - Change This Limited
    8th March 2016 at 10:47 am

    […] blog builds upon the custom calendar that was created as part of my previous Excel Calendar blog. Using the monthly template that was created, we wanted to build a dynamic calendar that would […]

    Reply
  2. Date Functions : Excel Calendar (Part 2) - Change this Limited
    9th March 2017 at 10:55 am

    […] blog builds upon the custom calendar that was created as part of my previous Excel Calendar blog. Using the monthly template that was created, we wanted to build a dynamic calendar that would […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.

We also use cookies to understand how visitors use our website AcceptReject

To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy