Accurate GST calculation is vital for businesses to ensure compliance with tax regulations and avoid penalties. For large enterprises handling numerous invoices, Excel sheets offer a practical solution to manage and compute GST efficiently. This guide will provide detailed steps, including using the Excel formula for GST, making it valuable for businesses with significant trade volumes.
Table of Contents
What Is GST Calculation in an Excel Sheet?
MS Excel is an indispensable tool for businesses, owing to its ability to handle large datasets and perform calculations efficiently. Using Excel for GST calculations can automate processes, significantly reducing the risk of human errors. This is beneficial for quick and error-free GST computations.
Understanding the tax slab or category for goods or services supplied is essential in GST calculations. The GST structure in India comprises four tiers: 0%, 5%, 12%, 18% and 28%. Additionally, it’s important to know what is CGST and IGST and when IGST, CGST and SGST apply based on the supply of goods and services.
-
SGST and CGST apply when goods and services are supplied within the same state.
-
IGST applies to the inter-state supply of goods and services.
GST Calculation Formula in Excel
Here’s a breakdown of the GST calculation formula in Excel –
-
GST = (Original Cost x Rate of GST) / 100
-
Net Price = Original Cost + GST
Understanding these GST terms is crucial:
1. GST Inclusive Amount:
Applicable when the total price includes GST – significant for buyers as it shows the total cost they pay.
2. GST Exclusive Amount:
Applicable when the price does not include GST – relevant for product pricing transparency.
How do you calculate GST in an Excel sheet?
To illustrate the process of how to calculate GST in Excel, suppose the base price of a product, excluding GST, is ₹35,000, and it falls under the 18% tax slab. Additionally, the wholesaler offers a 7% discount and incurs 3% freight charges for the delivery of goods.
1. Alternative A:
Here’s how to calculate the GST amount in Excel in the case of inter-state supplies:
Step 1: Tabulate Your Data
Create columns in an Excel sheet for the original price, discount, freight charges and tax rate.
Step 2: Calculate the Base Price
Deduct the discount from the original price and add the freight charges.
Base Price = Original Price – Discount + Freight Charges
In Excel, it will look like:
B6 = B2 – B3 + B5
If:
-
B2 =₹35,000
-
B3 = 7% of ₹35,000 = ₹2,450
-
B5 = 3% of ₹35,000 = ₹1,050
Then:
B6 = ₹35,000 – ₹2,450 + ₹1,050 = ₹33,600
Step 3: Calculate the GST Levy
In Excel, multiply the base price (B6) by the IGST rate (18%), as stated in F6. So, the formula is:
GST levy = B6 × F6 = ₹33,600 × 18% = ₹6,048
Step 4: Calculate the Final Price
To get the final price, add the GST amount to the base price.
Final Price = B6 + B7 = ₹33,600 + ₹6,048 = ₹39,648
2. Alternative B:
To separately calculate CGST and SGST, in the case of intra-state supplies, split the calculation into two rates of 9% each. Steps 1 and 2 will be the same as Alternative A, with changes in the calculation part of GST:
Step 1: Calculate the GST levy by multiplying the base price (C6) with the CGST and SGST rates given in cells F4 and F5, respectively:
CGST levy = C6 × F4 = ₹33,600 × 9% = ₹3,024
SGST levy = C6 × F5 = ₹33,600 × 9% = ₹3,024
Total GST levy = C7 + C8 = ₹3,024 + ₹3,024= ₹6,048
Step 2: Calculate the final price:
C9 = C6 + C7 + C8
Final Price = ₹33,600 + ₹3,024 + ₹3,024 = ₹39,648
3. Alternative C:
GST Calculation from GST-Inclusive Price
You can also calculate GST in Excel when you already have the final price, also known as the Maximum Retail Price (MRP) or the price that includes GST. With the MRP and the GST rate, you can calculate the GST like this:
Base Price = (GST – “inclusive price” * 100 / 100 + GST rate)
GST formula for Excel = MRP – Base Price
For example, imagine the price is ₹39,648 and the GST rate is 18%. In the Excel sheet, the GST calculation will look like this:
Base Price = B23 * 100 / (100 + E22)
Base Price = ₹39,648 * 100 / (100 + 18) = ₹33,600
GST Levy = B23 – B24 = ₹39,648 – ₹33,600 = ₹6,048
Building on what we’ve discussed on how to calculate CGST and SGST in Excel, you can also adjust the GST you owe for the input tax credit (ITC) claimed. Let’s say you can claim ₹2,000 as ITC. So, the GST you need to pay will be:
GST Payable = GST Levy – ITC claimed
B27 = B25 – B26
GST Payable = ₹6,048 – ₹2,000 = ₹4,048
Benefits of Using Excel Sheets for GST Calculation
1. Simplicity and User-Friendly Interface:
Excel is intuitive and easy to use, making it accessible for business owners with minimal technical skills. It ensures you can quickly adapt to creating and using GST calculation sheets without extensive training.
2. Error Minimisation through Automation:
Automated calculations in Excel reduce the likelihood of manual errors, ensuring accuracy in GST computation. By setting up formulas once, you can consistently apply them across multiple transactions, reducing the risk of calculation mistakes.
3. Bifurcation of GST Liabilities:
Excel can effectively bifurcate GST liabilities into CGST, SGST, and IGST, simplifying the accounting process. This capability is useful for businesses operating in multiple states, as it ensures accurate tracking of different tax components.
Accurate Calculation with Pricing Options:
Excel allows for precise pricing calculations, whether GST is inclusive or exclusive, aiding in transparent pricing strategies. You can easily switch between different pricing models and see the impact on the final price, helping in better decision-making.
4. Efficient Computation Tracking:
Excel tracks all computations, providing a clear audit trail that can be referenced for compliance and reporting purposes. This feature is crucial for maintaining accurate records and simplifying the auditing process, as all calculations are documented and easily accessible.
Disadvantages of Excel Sheets for GST Calculation
1. Prone to Copying and Formulaic Errors:
Errors can occur from copying formulas incorrectly or entering the wrong data. Even a small mistake in a formula can propagate through the entire spreadsheet, causing significant inaccuracies.
2. Loading Issues With Voluminous Data:
Excel may become sluggish or crash when handling extremely large datasets, which can be a problem for businesses with high transaction volumes. This limitation can hinder productivity and lead to delays in processing data.
3. Limited Scalability and Integration:
Excel lacks advanced integration capabilities with other accounting software, limiting its scalability for growing businesses. As businesses expand, the need for seamless integration with other systems becomes critical, and Excel may not always meet these requirements.
4. Manual Updates and Maintenance:
Excel sheets require regular updates and maintenance, which can be time-consuming. Keeping formulas and data accurate and up-to-date requires constant attention, adding to the workload of the accounting team.
Best Alternatives for GST Calculation on Worksheet / Excel
For a more efficient and user-friendly alternative to the Excel formula for GST calculations, consider Razorpay’s GST Calculator to calculate GST online. This tool is particularly effective for calculating GST monthly or quarterly, ensuring businesses can maintain accurate records without the manual effort involved in Excel. Razorpay’s GST Calculator offers significant time and cost savings by automating the GST computation process.
Steps for using Razorpay’s GST Calculator:
-
Enter the net price
-
Enter the applicable GST rate
-
Obtain immediate results, displaying the total tax payable, along with the detailed breakdown between CGST and SGST.
This user-friendly tool ensures accurate and error-free GST calculations, reducing the potential for mistakes that can occur in Excel sheets. Razorpay’s GST Calculator simplifies the GST filing process, making it a more efficient solution for businesses.
Conclusion
Accurate GST calculation is essential for compliance and operational efficiency. Excel sheets provide a practical method for calculating GST, especially for businesses managing numerous transactions. There are various formulas and functions available in Excel that can assist you in computing GST accurately. However, alternatives like Razorpay’s GST Calculator offer enhanced accuracy and ease of use, making them worth considering for your GST computation needs.
Frequently Asked Questions (FAQs):
1. How do I remove 18% GST from my total amount?
To remove 18% GST from the total amount, divide the total amount by 1.18. For example, if the total amount is ₹11,800, the base price will be 11900/1.18 = ₹10,000.
2. How do you calculate GST payable?
GST payable is calculated by subtracting ITC under GST from the total GST collected on sales.
GST Payable = GST collected by Sales – ITC
3. How do I remove tax from the total in Excel?
Use the formula:
Original Amount = Total Amount / 1 + Tax Rate
For example, if the total amount (including tax) is in cell A1 and the tax rate is 10%:
-
Enter the total amount in A1 (e.g., 110).
-
Use this formula in another cell (e.g., B1):
Amount = A1 / 1.10
This will give you the original amount before tax. For a total of ₹110 with a 10% tax, B1 will show ₹100, which is the original amount before the tax was added.
4. Can we make a GST bill in Excel?
Yes, you can create a GST bill in Excel by including details such as invoice number, date, buyer and seller information, item details, tax rates, and total amounts with GST calculations.
5. How do I download GSTR-1 in Excel?
You can download the GSTR-1 form in Excel from the GST portal. Log in to your account, navigate to the ‘Returns Dashboard,’ select the relevant financial period, and download the GSTR-1 in Excel format.
