GST stands for Goods and Services Tax. GST is a value-added tax that is applied to the sale of goods and services in many countries, including India, Canada, and Australia. Calculating GST is going to be made easy, with these simple steps. We are going to calculate it together.
Data preparation
Prepare a data table. Should contain:
- amount excluding GST tax
- the amount of gst
- gst rate
- amount including GST tax
In this Excel lesson, let's create a GST calculator and calculate each of these values.
You should first layout the price before the GST.
Gst formula
The formula for calculating the gst tax is:
GST amount = amount before gst * gst rate
Click on the column beside GST amount, and decide the rate you would like the GST to be. We are going with 15%, and therefore writing = B3 * 0.15
Note: the GST amount has to be in form of percentage, and use only between 0.0 to 1.
In my table, the amount before taxes is in cell B3. The tax rate is 15%. The formula is = B3 * 0.15, but you can also write it as = B3 * B5 because I entered the tax rate in cell B5.
Calculations show that the gst amount is $18.
Calculation of the gst rate
In case you want to calculate the GST tax rate, you have two options.
Calculation of the gst rate based on the price of the commodity
If you know the price of an item including tax and price excluding tax, use the following formula:
GST rate = (price with gst - price without gst) / price without gst
In my case, it will be the formula =(B6-B3)/B3
Calculating the gst rate based on the tax amount and the price of the goods
If you know the amount of gst and the price of the goods excluding tax, then you should use the following formula:
GST rate = GST amount / price excluding GST
In the case I am discussing, you should use the formula =B4/B3
Calculation of the price including gst
To calculate an amount that includes gst, you need an amount without gst. You also need to know the gst rate.
To calculate the price containing gst, use this formula:
Price including gst = price without gst + (price without gst * gst rate)
In the example below, the Excel formula is: =B3+(B3*B5). Use it to get the answer.
Calculation of the price without GST
To calculate a gst-free price, you need a gst-inclusive price. You also need to know the gst rate.
To calculate the price containing gst, use this formula:
Price without gst = price including gst / (1 + gst rate)
In the example below, the Excel formula is: =B6/(1+B5)
This is how to calculate GST in Excel. You can download free GST calculator template here
Things to remember
- When issuing an invoice or calculating the GST for your own needs, remember that the GST rates in different countries are different. It may also happen that one country has several different GST rates. Some goods may also be exempt from GST.
- Gst is also known as vat (value added tax). The principle of operation of gst and vat is similar. You can use the same Excel formulas to calculate them.
- When calculating GST, make sure the commodity price includes GST or not.