The introduction of GST has brought many benefits to both consumers and the government by streamlining the indirect tax system in India. The significant reforms brought by GST are the formation of a comprehensive tax structure, better compliance, a reduction of tax evasion, increased transparency, and the removal of tax barriers between states. This has helped to ease trade across the country for businesses, and created a wider market for traders. The inclusion of small businesses and unorganized sectors into the formal economy has brought unprecedented economic growth to the country. Now, as per GST law, every business enterprise involved in the purchase and sale of goods and services in India is mandatorily required to register under GST and obtain the goods and services identification number (GSTIN).
It is mandatory for all taxpayers registered under GST to be compliant with the rules and regulations specified under the GST laws. Filing of returns online and payment of tax are part of the compliance process for the taxpayers. Accurate calculation of GST is important for the business to stay tax-compliant and avoid penalties and other legal consequences. GST Calculator by CaptainBiz is one of the best and easy to use tool to calculate GST. However Excel sheet is also a common and popular medium to calculate GST.
Excel sheet calculation is very useful for large businesses that have a huge number of invoices to report due to their big volume of trade. Here we discuss in detail how to calculate GST on an Excel sheet.
Calculate GST in an Excel Sheet/ Spread Sheet
MS Excel is perfect for working with large data as it can be represented in tables, and calculations can be done easily with the help of formulas. Moreover, rows and columns can be inserted or deleted whenever any additions or modifications need to be made while entering the invoice details. When new invoices are added, the formula can be applied to calculate the tax. Tax is automatically calculated when the cell with the formula is dragged to the tax field of the newly added invoice record. Calculations are fast and easy with this method. Human errors can be minimized as calculations are automated in the Excel sheet.
To calculate GST, the taxpayer must first determine the tax slab or tax category under which the goods or services supplied come and the rate of tax applicable. The four-tier structure of GST is made up of 0%, 5%, 12–18%, and 28%. Then the taxpayer has to find out the applicability of IGST, CGST, or SGST. If the goods and services are supplied within the state, then SGST and CGST are applicable. But in cases of interstate movement of goods, for example, from Karnataka to Maharashtra, only IGST is applicable. Calculating GST using an Excel sheet is easy using the formula, the base price, and the applicable GST rate. The formula for calculating GST is as follows:
The formula for calculating GST is as follows:
- GST = (Original Cost x Rate of GST)/100
- Net Price =Original Cost + GST
- GST Inclusive Amount: This term refers to the total value of the product or good in which GST is included. In this case, the buyer does not have to pay the tax separately, as it is included in the price.
- GST Exclusive Amount: This term refers to the price of the product or good, which is not inclusive of the GST amount.
Steps Involved in the Calculation of GST
The steps involved in the calculation of GST are as follows:
Step 1: After confirming that GST is applicable for the particular goods or services, note the HSN/SAC code (the HSN code is applicable for goods and the SAC code for services).
Step 2: The next step is to determine whether SGST, CGST, or IGST are applicable for the goods and services. For trades within the state, both SGST and CGST are applicable, and for interstate trades, IGST is applicable. For example, for trade between Karnataka and Maharashtra,
Step 3: Then it has to be determined whether the trade is a B2B (business-to-business) transaction or a B2C (business-to-customer) transaction. And if it is a B2C transaction, then whether the transaction value is above 2.5 lakhs or less than 2.5 lakhs
Step 4: Then the GST slab must be decided. There are four slabs in GST: 0%, 5%, 12–18%, and 28%. The taxpayer has to determine which rate is applicable for the particular trade.
Calculation of GST in an Excel Sheet
The calculation of GST is simpler using the formula: GST = original cost price x GST rate / 100. An important point to be noted here is that the discount charges are to be deducted before computing the GST, and freight and courier have to be included in the cost amount for calculating the GST.
Example: 1
Suppose the cost of manufacturing a particular item is Rs. 100,000 and it is transported from Bangalore to Mumbai. If the rate slab is 12%, then GST can be computed in the Excel sheet using the following method:
GST = Original cost price x (Rate of GST/100)
In this case, the IGST is calculated as follows:
GST = Rs. 10000 * 12/100 = Rs. 1200/-
Similarly, SGST and CGST can be calculated.
In the above example, GST can be bifurcated into SGST = Rs. 600/- and CGST = Rs. 600/-.
Example: 2-Interstate supply-computation of IGST in Excel Sheet
Mr. A makes an interstate supply to Mr. B at a cost price of Rs. 20000/-. there is a discount of 10%. He incurs transportation charges of Rs. 600.00. The applicable GST rate is 18%. IGST can be calculated in the Excel sheet as below:
Here, the base price is calculated by deducting the discount and adding the transportation.
base price is cell C5=C1-C2+C4
IGST is cell C6=C5*0.18 (IGST at 18%) (Formula in cell C6)
The final price, inclusive of IGST, is cell C7=C5+C6
| A | B | C | D | E |
| Sr. no. | Description | Amount (Rs) | Rate | |
| 1 | Original cost | 20000 | Discount | 10% |
| 2 | Discount | 2000 | transportation charges | 3% |
| 3 | Price after discount | 18000 | CGST slab | 9% |
| 4 | transportation charges | 600 | SGST slab | 9% |
| 5 | Base Price | 18600 | IGST slab | 18% |
| 6 | IGST @18% | 3348 | ||
| 7 | The final price is inclusive of GST | 21948 |
Example: 3-Intrastate supply-computation of CGST and SGST in Excel Sheet
In the above example, if the trader has to supply the goods within the state, then the calculation is as follows in the Excel sheet:
base price is cell C5=C1-C2+C4
SGST is cell C6=C5*9/100 (Formula in cell C6)
CGST is cell C7=C5*9/100 (Formula in cell C7)
final price is cell C8=C5+C6+C7
| Sr. no. | Description | Amount (Rs) | Rate | |
| 1 | Original cost | 20000 | Discount | 10% |
| 2 | Discount | 2000 | transportation charges | 3% |
| 3 | Price after discount | 18000 | CGST slab | 9% |
| 4 | transportation charges | 600 | SGST slab | 9% |
| 5 | Base Price | 18600 | IGST slab | 18% |
| 6 | SGST @9% | 1674 | ||
| 7 | CGST @9% | 1674 | ||
| 8 | The final price is inclusive of GST | 21948 |
Mastering GST Calculations in India: Your Guide to Using Online GST Calculators
GST Calculator
Many platforms now provide an online GST calculator, which helps in the accurate calculation of GST. It is very easy to use this calculator. The taxpayer has to just input the variables like the original cost, the rate of tax applicable, and the GST amount, which will be automatically calculated and displayed. Online GST calculators are accurate and save a lot of time.
Simplify Your Tax Calculations: Exploring an Easy GST Calculator
Offline Excel-Based Tool by GSTN
All the GST-related activities, like registration, filing returns, and refunds, can be done online through the GST portal. As per GST law, all taxpayers have to file returns of all their sales and purchases made in a month online in the GST portal before the 10th of the following month. But taxpayers living in remote areas with limited access to the internet were finding it difficult to file their returns on time. To solve this problem, GSTN introduced the offline Excel-based format to make compliance easier and more convenient for taxpayers. In this method, traders can upload their sales data to the GST portal in Excel format by downloading the Excel template from the portal. The downloaded files can be filled out offline and then uploaded to the GST portal directly, completing the filing process online. The filing can be done without an internet connection.
The taxpayers can download this Excel template comprising of 8 worksheets from the GST portal (www.gst.gov.in) and enter all the invoice details on a regular basis. The taxpayer can download, extract, and run the Returns offline tool and upload the invoice and other data in offline mode. In this Excel sheet, the taxpayers have to mention the details of the transactions, like details of invoices issued to registered taxpayers, export invoice details, supplies to consumers, details of credit and debit notes to registered and unregistered taxpayers, and details of documents issued on a regular basis. This data is imported from the Excel sheet into the related fields of the return to be filed on the GST portal. It then generates a JSON (Java script object notation) file, which can be uploaded directly to the GST portal to complete the GST return filing process.
Conclusion
Accurate calculation of GST is crucial as part of the compliance process. Calculation of GST in an Excel sheet using formulas is easier and more accurate, especially for large businesses where the volume of transactions is higher and it is difficult to record the individual invoice details in the portal. The government has also provided an offline Excel-based tool for businesses in remote areas with no access to an internet connection. Many platforms also provide an online GST calculator, with which tax can be computed accurately and returns can be filed on time.
Have questions about GST, tax, or invoicing? Get all your doubts resolved with a CaptainBiz expert in a FREE 1:1 consultation.
