A Handbook on Application of GST Formulae in Excel

• GST formula in Excel simplifies tax computations, reducing manual errors.
• You can easily extract GST from total amounts.
• Create structured GST and compliant invoices effortlessly using Excel.

A significant component of contemporary taxes that impacts both consumers and businesses is the Goods and Services Tax (GST) . You can save time and effort by learning how to calculate GST in Microsoft Excel, regardless of your role.Excel is a strong tool that can be used to create invoices swiftly, apply GST formula in Excel, and automate GST computations. Let's learn more!

GST is a value-added tax applied to goods and services at each stage of the supply chain. In India, GST is divided into three categories:

• CGST (Central GST) : Collected by the Central Government.
• SGST (State GST): Collected by the State Government
• IGST (Integrated GST): Applied to inter-state transactions

GST Rates in India

To apply the correct GST formulas in Excel, you should know the types and GST rates for different types of goods. To calculate GST, you should also know whether you are working with inclusive or exclusive GST values.Different goods and services attract different GST rates:

• 5%: Basic necessities like essential food items
• 12% : Processed food, some medicines
• 18%: Electrical appliances, electronics, most goods
• 28%: Luxury items like cars, high-end appliances

Before you apply the GST formula in Excel , you should be familiar with the basic GST formulas.

1. Calculating GST on a Given Amount (Exclusive GST)

If you have a price before GST and need to calculate the GST amount, use:

• GST Amount = Price × (GST Rate ÷ 100)
• Final Price = Price + GST Amount

One-step formula:

• Final Price = Price × (1 + GST Rate ÷ 100)

Excel Formula:

• =A2 + B2

Where:

• A2 is the price before GST.
• B2 is the GST rate (%).

2. Calculating Total Price After GST

Once the GST amount is calculated, the final price including GST is:

• Total Price = Price Before GST × (1 + GST Rate ÷ 100)

Excel Formula:

• = A2 + C2

Where:

• A2 is the original price.
• C2 is the GST amount.

3. Extracting GST from an Inclusive Price

If the price already includes GST and you need to extract the GST amount:

• GST Amount = (Price Including GST × GST Rate) ÷ (100 + GST Rate)

Excel Formula:

• = A2 * B2 / (100 + B2)

Where:

• A2 is the price including GST.
• B2 is the GST rate (%).

4. Calculating CGST and SGST (for Intra-state Transactions)

For intra-state transactions, GST is split equally between CGST and SGST:

• CGST = GST Amount ÷ 2
• SGST = GST Amount ÷ 2

Excel Formula:

• = C2 / 2

Where C2 is the total GST amount.

5. Reverse GST Calculation (Price Including GST to Excluding GST)

If you have a price that includes GST and need to extract the base price, use:

• =A2 / (1 + B2/100)

6. Automating GST Calculation Using IF Function

For different GST rates:

• =IF(C2=5, B25/100, IF(C2=12, B212/100, IF(C2=18, B218/100, B228/100)))

Here's a step-by-step guide to setting and executing the GST formula in Excel :

Step 1: Setting Up an Excel Sheet

Create an Excel sheet with the following headers:

Step 2: Enter GST Formulas in Excel Cells

Under each of the specified cells, enter the following formulae:

• GST Amount: =B2 * C2 / 100
• CGST: (Only for intra-state sales) =D2 / 2
• SGST: (Only for intra-state sales) =D2 / 2
• IGST: (For inter-state sales, IGST = GST Amount) =D2
• Final Price After GST =B2 + D2

Step 3: Apply Formatting

Set the currency format to "Price Before GST," "GST Amount," and "Final Price." Also, Restrict the GST Rate input to valid values (5%, 12%, 18%, 28%).

Step 4: Test the Calculator

Always test your calculations beforehand to ensure the accuracy of formulas and avoid mistakes in the future. Here's a tabular example:

A GST invoice must include:

• Seller and Buyer Details
• GSTIN Number
• Invoice Number and Date
• Tax Calculation
• Total Amount

Example Consider the following example:

You can apply Excel’s SUM() function to calculate the total automatically wherever required.

Knowing the GST formula in Excel and how to implement it is essential and highly time-efficient for accountants, financial professionals, and company owners. It significantly enhances overall financial efficacy, ensures compliance, and expedites GST calculations.Learn the formulas and use Excel's sophisticated capabilities and automation for tax planning and swift calculations for GST compliance.