Calculate GST in Excel

When you work a lot with Excel, it just feels unnatural for you to use any other tool for calculations and mathematical operations.

Having this in mind, we can also use it to calculate GST, or goods and services tax.

We will show how this can be done in the example below.

Calculate Cost of Goods with GST Included

First thing first, we need to define what is the meaning behind GST. GST is the abbreviation for Goods and Services Tax. This is the indirect tax that is imposed on the supply of goods and services.

So, if you want to find out the final price of goods and services, you should always calculate the GST. This tax can vary from country to country, and we need to make sure our formulas function regardless of that.

Let us say that we have a value of the goods and GST percentage as follows:

Graphical user interface

Description automatically generated with low confidence

To calculate the value of the goods with this tax of 12 percent included, the easiest way would be to calculate the GST amount first and then just sum this figure with the value of the goods. We first calculate the GST amount in cell B4 with the following formula:

=B2*B3

This is the result we end up with:

Graphical user interface, text, application, Word

Description automatically generated

In cell B5 we need to sum the value of the goods and the GST amount. We will insert the following formula in this cell:

=B2+B4

Our final result looks like this:

Graphical user interface, text, application, Word

Description automatically generated

Of course, there is an easier way to do this, in just one line. If we know all the numbers, and if we have a value of the goods in cell B8, we can use the following formula to calculate the value of the goods with GST:

=B8+(B8*12%)

This is what we get in cell B9:

Graphical user interface

Description automatically generated

So basically, the same result but in one line.

Calculate GST from Total Value

Now, we have seen how to calculate the total value, and that is easy. We need to see how to do a reverse process, i.e. what formula to use to get the GST value out of the total price. Let us say that we have the total value and the GST percentage:

Text

Description automatically generated with medium confidence

To calculate the GST amount from these figures, we will use this formula:

=B12*B13/(1+B13)

This means that to calculate the GST amount whilst having the total value of the goods and GST percentage, you need to: multiply the value with the GST percentage and then divide that number by the sum of 100% and GST percentage (in our case we use full numbers, so we need to get number 1.12).

Once we click enter, we will have our result (264):