Split a GST-inclusive MRP into taxable value and tax, paise-exact

Why this comes up

Shops, distributors and e-commerce sellers price goods at an inclusive figure. For packaged goods, the Legal Metrology (Packaged Commodities) Rules, 2011 require the retail sale price to be declared as the maximum price inclusive of all taxes; the consolidated text is published by the Department of Consumer Affairs. The books, however, need the taxable value and the tax separately. An analyst meets the problem when reconciling a point-of-sale export with the sales register, when building margin reports on taxable value, or when a manager asks why the GST report and the till summary differ by ₹3.40.

The formula, and the mistake beside it

The last row is the source of most one-paisa differences. When taxable value and tax are each rounded on their own, they can together come to a paisa more or less than the price. Deriving one of them as the remainder removes the problem by construction.

Line versus unit, in code

The bill below is synthetic, and the GST rates are illustrative values chosen to show the arithmetic, not a statement of the rate for any product. Confirm rates for real items from official notifications.

from decimal import Decimal as D, ROUND_HALF_UP
import pandas as pd

P = D("0.01")
def r2(x: D) -> D:
    return x.quantize(P, rounding=ROUND_HALF_UP)

# Synthetic retail bill: items sold at a GST-inclusive price (MRP or a discounted price).
lines = pd.DataFrame({
    "item": ["Shampoo 340 ml", "Biscuits 200 g", "Pressure cooker 3 L", "Toothpaste 150 g"],
    "qty": [3, 12, 1, 2],
    "price_incl": [D("249.00"), D("30.00"), D("1899.00"), D("99.00")],
    "gst_rate": [D("18"), D("5"), D("12"), D("18")],   # illustrative rates, not a rate list
})

def split_inclusive(gross: D, rate: D, intra_state: bool = True) -> dict:
    """Back out taxable value and tax from a tax-inclusive amount. Tax = gross - taxable,
    so the pieces always add back to exactly the price the customer paid."""
    taxable = r2(gross * 100 / (100 + rate))
    tax = gross - taxable
    if not intra_state:
        return {"taxable": taxable, "igst": tax}
    cgst = r2(tax / 2)
    return {"taxable": taxable, "cgst": cgst, "sgst": tax - cgst}

# Method A: split the line total (qty x inclusive price) once.
lines["gross"] = lines["qty"] * lines["price_incl"]
a = pd.DataFrame([split_inclusive(g, r) for g, r in zip(lines["gross"], lines["gst_rate"])])

# Method B: split one unit, then multiply by quantity (what some billing screens do).
unit = pd.DataFrame([split_inclusive(p, r) for p, r in zip(lines["price_incl"], lines["gst_rate"])])
b = unit.mul(lines["qty"], axis=0)

out = pd.concat([lines[["item", "qty", "gross"]], a.add_prefix("A_"), b.add_prefix("B_")], axis=1)
out["A_check"] = out["A_taxable"] + out["A_cgst"] + out["A_sgst"] - out["gross"]
print(out[["item", "gross", "A_taxable", "A_cgst", "A_sgst", "B_taxable", "B_cgst", "B_sgst"]]
      .to_string(index=False))

print("\nbill total paid:", lines["gross"].sum())
print("taxable  A:", a["taxable"].sum(), " B:", b["taxable"].sum())
print("tax      A:", (a["cgst"] + a["sgst"]).sum(), " B:", (b["cgst"] + b["sgst"]).sum())
assert (out["A_check"] == 0).all()                      # method A always adds back
assert (a["taxable"].sum() + a["cgst"].sum() + a["sgst"].sum()) == D("3204.00")
assert b["taxable"].sum() != a["taxable"].sum()         # the two methods disagree by paise

Both methods agree on the pressure cooker and toothpaste, where the numbers happen to divide cleanly. They disagree on the other two lines:

• Shampoo, 3 at ₹249. Splitting the ₹747 line gives a taxable value of ₹633.05. Splitting one ₹249 unit gives ₹211.02, and three of those make ₹633.06.
• Biscuits, 12 at ₹30 with 5%. One unit carries ₹1.43 of tax, which halves to 0.715. Rounded half-up, CGST becomes ₹0.72 and SGST ₹0.71; multiplied by twelve that is ₹8.64 and ₹8.52, so the two halves differ by 12 paise on one line. Splitting the line total gives ₹8.57 each.

Across the bill the difference is a single paisa, which sounds trivial. Over a month of retail transactions it becomes a gap between the tax a billing system reports and the tax recomputed from the sales register, and explaining it takes much longer than preventing it.

What the function guarantees

split_inclusive has three properties worth keeping if you rewrite it. It uses Decimal, so ₹0.715 rounds half-up as a person would expect rather than according to how the float happens to be stored. It rounds exactly once for the taxable value and once for CGST, and everything else is a subtraction, so the parts always sum to the inclusive price; the A_check column proves that for every line. And it has an inter-State branch returning a single IGST amount, since the same back-calculation applies with the whole tax in one column. Which of the two applies is a separate question, covered in checking IGST against CGST and SGST on every invoice line.

In Excel

With quantity in B2, the inclusive unit price in C2 and the rate as a whole number in D2:

Gross     =B2*C2
Taxable   =ROUND(E2*100/(100+D2), 2)
Tax       =E2-F2
CGST      =ROUND(G2/2, 2)
SGST      =G2-H2
Check     =E2-(F2+H2+I2)

Excel's ROUND rounds halves away from zero, which matches half-up for positive amounts, but the input can still carry floating-point noise. Keep the Check column and a sum of it at the foot of the sheet; it should read 0.00. If your billing software splits per unit, reproduce its method in a second set of columns and report the difference, rather than silently choosing one.

Discounts on an inclusive price

A discount on an inclusive price reduces the amount paid, and the split is then applied to the discounted amount. Apply the discount first, round the discounted line total, then split. Splitting the MRP and discounting the taxable value separately produces a tax figure that no longer corresponds to what the customer paid.

If the result has to be printed on a voucher, writing the amount in words in lakh and crore style handles that step with the same Decimal rounding. Getting small money calculations exactly right, with checks that prove it, is part of the finance-data practice in our Data Analyst programme.