How to Calculate GST in Excel Sheets?

Goods and Services Tax (GST) is an indirect tax levied on the final value of goods and services provided to consumers. With GST rates spread across multiple slabs of 0%, 5%, 12%, 18%, and 28%, it can feel daunting to determine your GST liability at the time of returns filing. We demystify GST calculations by elaborating on the applicable GST formula in excel below.

How to Calculate GST in Excel Sheets?

The GST Act provides three tax components: CGST, SGST, and IGST. If the goods and services are supplied within the state, then the GST levy gets subdivided between CGST and SGST. However, on the interstate movement of goods, say between Delhi and Mumbai, only IGST is levied. 

For simplification, we will initially restrict our excel formula for GST calculations to IGST.

Computing GST is easy when done in an excel sheet. All you need is a base price and applicable GST rates, which you can, then plug into the GST formula in an excel sheet.

GST formula for excel = Original Base Price * GST Rate/100  

Please note that a supplier must deduct the trade discount from the base price before calculating GST. Similarly, freight and courier charges must be added to the base price before computing GST liability.

Let’s illustrate the working of the GST formula in excel below.

Assume the base price, i.e., the price excluding any GST, is Rs.20,000, and the good falls under the 18% tax slab. Further, assume the wholesaler offers a 5% tax discount and 3% freight charges for the delivery of goods. The GST calculation in the worksheet excel will be as follows:

Step 1: Tabulate your cost price, discount, freight charges, and tax rate in the excel sheet.

Step 2: Reduce the original price with the cash discount and add freight charges to it. In Excel, 

Base Price = Original Price – Discount + Freight Charges

B6 = B2-B3+B5

B6 = Rs. 20,000-1,000+600 = Rs.19,600

Step 3: Now, multiply the base price (arrived at in Step 2) by the GST rate. In excel, the base price of Rs.19,600 (given in B6) will be multiplied by the IGST rate, formatted as a percentage, stated in cell F6. Hence, the GST formula in excel is:

GST levy = B6 * F6 

GST levy = Rs.19,600 * 18% = Rs.3,528

Step 4: The GST chargeable to the goods is computed in B9. Add this GST amount to the base price to arrive at the final price in B10. Thus,

Final Price = B9 + B10

Final Price = Rs. 19,600 + 3,528 = Rs. 23,128

We can also modify our excel formula for GST calculations by separately calculating CGST and IGST. For this, follow the same procedure as mentioned above but split the IGST calculation into two rates of 9% each. Steps 1 and 2 remain the same as before. We explain how to add the GST formula in excel below:

 Step 1: Tabulate the original price, discount, freight charges, and tax rate in the excel sheet. 

 Step 2: Reduce the original price with the cash discount and add freight charges to it. In Excel, 

 Base Price = Original Price – Discount + Freight Charges

C6 = C2-C3+C5

C6 = Rs. 20,000-1,000+600 = Rs.19,600

Step 3: Now, multiply the base price (arrived at in Step 2) by the GST rate. In excel, the base price of Rs. 19,600 (given in C6) will be multiplied by the CGST rate and SGST rate given in cells F4 and F5, formatted as a percentage. Hence, 

CGST levy = C6 * F4

CGST levy = Rs. 19,600 * 9% = Rs. 1,764

SGST levy = C6 * F5

SGST levy = Rs. 19,600 * 9% = Rs. 1,764

Total GST levy = CGST + SGST 

Total GST levy = C7+C8 = Rs.1,764+1,764 = Rs.3,528

Step 4: The GST chargeable to the goods is the summation of C7 and C8. Add this GST amount to the base price to arrive at the final price in C10. Thus,

 C10 = C6 + C7 + C8

Final Price = Rs. 19,600 + 1,764 + 1,764 = Rs. 23,128

Another way to use the GST formula in excel is when we already know the final price, also known as the Maximum Retail Price (MRP) or GST-inclusive price. So, armed with MRP and the applicable GST slab, GST can be computed as follows:

Base Price = (“GST-inclusive price” * 100/100+GST rate) -1

GST formula for Excel = MRP-Base Price

To illustrate, assume a scenario where MRP is Rs. 23,128 and the GST slab is 18%. Then, the GST calculation in the excel sheet will be:

Base Price = B14*100/(100+D13)

Base Price = Rs.23,128*100/(100+18)= Rs. 19,600

GST Levy = B14-B15

GST Levy = Rs 23,128- 19,600 = Rs. 3,528

Continuing with the above scenario, we can further adjust the GST liability for the input tax credit (ITC) claimed in the excel sheet. Let’s assume an ITC amount of Rs. 1,000 can be claimed. Thus,

GST Payable = GST Levy - ITC claimed

B18 = B16 - B17

GST Payable = Rs.3,528 - 1,000 = Rs.2,528

Benefits of Using Excel Sheets for GST Calculation

Using excel sheets for calculating GST is not without benefits. We enumerate some of them down below.

• Excel sheets are simple to use and allow users to compute their tax liabilities without any difficulty.
• By automating calculations, taxpayers can minimise the risk of human errors, thus ensuring accurate computations and time savings.
• Excel sheets enable taxpayers to bifurcate their GST liabilities into CGST, SGST, or IGST.
• By using excel sheets, users can precisely calculate their GST levy, either by using pre-GST prices or by employing GST-inclusive prices.
• It is easier to keep track of computations on an excel worksheet, which further saves time.

Drawbacks of Using Excel Sheets for GST Calculation

While it is common to use excel sheets for calculating GST, they can be prone to some copying and formulaic errors. For instance, missing out on a key figure or incorrect formula copying can result in excel sheets flashing red errors. Besides, excel sheets are infamous for their loading issues, especially when voluminous data is involved. To learn more about Excel's drawbacks, click here.

Best Alternatives for GST Calculation Worksheet Excel

Consider using Clear's GST online calculator to avoid any fallouts from incorrect GST calculations that can hurt your regulatory compliance. It is a ready-to-use online calculator capable of calculating GST payable monthly or quarterly. It can also be used by buyers, wholesalers, and manufacturers. Best of all, it results in massive time and cost savings while making computations hassle and error-free.

To use Clear’s GST calculator, enter the net price of the good or service you buy and its applicable GST rate. Press ‘Enter’, and the GST calculator will show you the total tax payable and its breakdown between CGST and SGST.

By using the GST formula in excel, taxpayers can easily estimate their GST liabilities. While excel sheets can ease the arduous process of making GST computations, they are disposed to mistakes. This can complicate the GST filing process while making meeting regulatory compliance requirements difficult. 

GST taxpayers are, thus, advised to pay close attention while inputting data and making calculations. A better option is to use Clear’s GST calculator to minimise computation errors.