Spreadsheets

CGST and SGST split formula in Excel for an intra-state invoice

Split GST into CGST and SGST in Excel: total tax, half for CGST, the balance for SGST, and how to handle an odd paisa so the halves add to the total.

Editorial illustration for CGST and SGST split formula in Excel for an intra-state invoice

For an intra-state sale, work out the total GST first, then split it in two: =ROUND(B2*C2, 2) for total GST, =ROUND(D2/2, 2) for CGST, and =D2-E2 for SGST. Computing SGST by subtraction, not by halving again, keeps CGST + SGST equal to the total even when the amount does not divide evenly.

The sheet layout

  1. A Invoice, B Taxable value, C GST rate (percentage), D Total GST, E CGST, F SGST, G Invoice total.
  2. D2: =ROUND(B2*C2, 2). This is the whole tax on the line.
  3. E2: =ROUND(D2/2, 2). CGST is half.
  4. F2: =D2-E2. SGST takes the balance.
  5. G2: =B2+E2+F2, and copy all columns down.

If you also want to show the half-rate on the invoice, add a column with =C2/2, so an 18% rate is displayed as 9% each for CGST and SGST.

What does it give on real-looking amounts?

Taxable valueRateTotal GSTCGSTSGST
₹10,000.0018%₹1,800.00₹900.00₹900.00
₹1,001.005%₹50.05₹25.03₹25.02
₹2,499.0018%₹449.82₹224.91₹224.91

The first and third rows split cleanly. The second is the edge case.

What is the rounding-paisa problem?

Take ₹1,001 at 5%. Total GST is ₹50.05. Half of that is ₹25.025, which cannot be paid, so one half must round up. If you round both halves separately, each becomes ₹25.03 and together they come to ₹50.06, a paisa more than the real total. Subtracting fixes it: CGST is ₹25.03 and SGST is ₹25.02, adding up to ₹50.05.

How do you total a long invoice?

Fill the formulas down for every line, then in the totals row use =SUM(D2:D11) for total GST, =SUM(E2:E11) for CGST and =SUM(F2:F11) for SGST. Because each line already balances (CGST + SGST = total GST for that line), the totals balance too. Do not recalculate the total CGST as half of the total GST; that would ignore the line-level rounding and can differ from the sum of the lines by a few paise. Whichever you choose, the printed invoice should be the guide.

Can you show the check on the sheet?

Yes. Add H2: =E2+F2-D2 and make the column show 0.00 on every row. Add a total row, and then a single cell that adds the check column. If that cell is not zero, you know which row to examine. It costs a minute and prevents a month-end hunt.

Which mistakes show up most often?

  • Dividing the rate by two and applying it twice with separate ROUNDs, which creates the extra paisa described above.
  • Splitting the taxable value in half instead of the tax, a slip that makes CGST and SGST far too small.
  • Typing the rate as 18 instead of 18%, which multiplies the value eighteen times.
  • Formatting the cells to two decimals without using ROUND, so what you see and what is added are not the same figure.

When does CGST and SGST not apply?

This split is for a supply within the same state. A sale to a customer in another state is treated differently, with a single IGST amount instead of two halves, and the deciding factor is the place of supply, which is not always the customer's billing address. The next step for such a sheet is an IF formula that reads the two states; rules about place of supply are for your CA to confirm.

Quick checklist

  • Rate in a percentage-formatted cell.
  • ROUND on total GST and on the first half only.
  • SGST by subtraction.
  • Check column at 0.00.
  • A note on the sheet saying which rounding convention you follow.

Figures from a sheet are working numbers. Rates and rules can change, so confirm the position with your CA or the official source before using them on a filing.

SPENDIXAI JOURNAL

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

Explore your reports