Spreadsheets

Back-calculate GST from a GST-inclusive amount in Excel

Split a GST-inclusive total into base amount and tax in Excel using a rate cell, with CGST and SGST halves, and avoid the common total-times-rate error.

Editorial illustration for Back-calculate GST from a GST-inclusive amount in Excel

To find the base amount from a total that already includes GST, divide the total by 1 plus the rate: base = total / (1 + rate). The tax is then total minus base. Keep the rate in its own cell so the same sheet works for every item, and treat the result as an estimate until you have checked it against the invoice.

What is the formula, and why not total times rate?

The GST on an invoice is charged on the base price, not on the total. So if the total is ₹1,100 and the rate is 10 percent (a number chosen only to keep the arithmetic easy, not a real GST rate), the base is ₹1,000 and the tax is ₹100. Multiplying the total by the rate gives ₹110, which is wrong because it applies the rate to an amount that already includes tax.

StepExcelExample with ₹1,100 and 10%
Base amount=B2/(1+$H$1)1,100 / 1.10 = 1,000
GST amount=B2-C21,100 - 1,000 = 100
Check=C2+D2-B2Should be 0
Wrong shortcut=B2*$H$1110, not 100

How do I lay out the sheet?

Put the rate in H1, formatted as a percentage, and label it in G1. Then use these columns, starting in row 2:

  • A: description of the purchase
  • B: total paid, GST included
  • C: base amount, =ROUND(B2/(1+$H$1),2)
  • D: GST, =B2-C2
  • E: CGST, =D2/2
  • F: SGST, =D2/2

Type the rate as 15% or as 0.15 (hypothetical figures). If you type a plain 15, Excel reads it as 1,500 percent and every base amount collapses. Data validation on H1, allowing a decimal between 0 and 1, stops that slip.

What are the CGST, SGST and IGST columns?

For a purchase within one state, the tax is usually shown on the invoice as two equal halves, central and state. For a purchase across states, the same tax appears as a single integrated amount. So in the sheet, columns E and F split the GST amount in two, and for cross-state invoices you simply use column D as the one figure. Check which applies on the invoice itself.

What if one bill has items at different rates?

Then one rate cell is not enough. Add a rate column next to each total, so every row has its own: =ROUND(B2/(1+G2),2), with G2 holding that line's rate. Copy the rate from the invoice line, never from memory. Rates and rules change, so confirm the position with your CA or the official source.

A monthly total

To total the GST for one category across a month, use SUMIFS. Say the category is in column G, the date in I, and the GST in D:

=SUMIFS($D$2:$D$500,$G$2:$G$500,"Office supplies",$I$2:$I$500,">="&DATE(2026,4,1),$I$2:$I$500,"<="&DATE(2026,4,30))

The dates here are hypothetical; point them at your own month. A sum like this is an estimate of the tax sitting inside your spending, useful for conversations with your accountant, not a statement of what is claimable.

When a sheet stops being enough

If most of your purchases do not show a clear rate, or you are re-typing totals from many bills, the sheet becomes slow work. SpendixAI lets you set a GST rate on each category and its reports estimate the GST inside your spending; those are estimates, so check them against invoices and with a CA.

  • Rate cell is a percentage, not a typed whole number
  • Base plus GST equals the total, row by row
  • Invoices with several rates have their own rate column
  • Your accountant has reviewed any figure you plan to rely on
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports