ITC Reconciliation Template in Excel

Last updated: 27 June 2026 · Reflects IMS and the GSTR-2B-driven ITC rules for 2026.

An ITC reconciliation template in Excel matches the Input Tax Credit in your purchase register against what appears in GSTR-2B, removes blocked credits under Section 17(5), and totals the ITC you can actually claim in GSTR-3B. Because the portal now restricts ITC to what's in GSTR-2B and blocks 3B where you over-claim, this reconciliation is the difference between a clean return and a notice.

Key takeaways

• Claimable ITC = matched to GSTR-2B − blocked credits (Sec 17(5)) − ineligible/personal use.
• Match purchases to GSTR-2B on GSTIN + invoice number; only matched invoices are eligible.
• Flag Section 17(5) blocked credits (e.g. certain motor vehicles, food & beverage, club memberships) and exclude them.
• The portal blocks GSTR-3B where ITC claimed exceeds GSTR-2B beyond a tiny tolerance — so the template must tie out.
• Reverse wrongly-availed ITC in Table 4(B) with interest at 24% p.a.
• Section 16(4) deadline: claim a year's ITC by 30 November following the FY or the annual-return date, whichever is earlier.

Fact box. Input Tax Credit is the GST you paid on purchases that you set off against the GST you owe on sales. From 2026, eligible ITC is largely restricted to invoices appearing in GSTR-2B, and the portal blocks a GSTR-3B that claims more ITC than GSTR-2B supports. Tax portals widely report a ₹1 rounding tolerance before the auto-block triggers, but this has not been confirmed in a primary CBIC advisory — treat any over-claim as potentially blocking and aim for an exact match. (Source: CBIC; GSTN.)

What does an ITC reconciliation template do?

It takes three inputs and produces one answer — how much ITC you may claim:

1. Purchase register — every purchase with GST, by head (IGST/CGST/SGST).
2. GSTR-2B — what the system says is available.
3. Eligibility rules — Section 17(5) blocked credits and any personal/exempt-use apportionment.

The output is the claimable ITC figure for Table 4 of GSTR-3B, with a clear trail of what was excluded and why.

How do I structure the template in Excel?

Use one row per purchase invoice with these columns:

Build the match flag against your 2B sheet:

In 2B?      =IF(ISNA(XLOOKUP(Key, TwoB!Key, TwoB!Key)), "No", "Yes")
Eligible    =IF(AND([In 2B?]="Yes", [17(5)]="No"), (IGST+CGST+SGST)*(1-Personal%), 0)

That single Eligible column does the work: zero unless the invoice is in 2B and not blocked, net of any personal-use share.

Fact box. Section 17(5) lists "blocked credits" on which ITC cannot be claimed — including certain motor vehicles (seating capacity ≤ 13), food and beverages, outdoor catering, and club/health/fitness memberships, except where statutorily obligatory. Wrongly-availed ITC must be reversed with interest at 24% p.a. (Source: CGST Act, Section 17(5).)

How do I handle blocked credits (Section 17(5))?

Mark each purchase that falls under a blocked category and force its eligible ITC to zero. Common blocked items to watch:

• Motor vehicles for transporting persons with seating capacity ≤ 13 (with exceptions like further supply or passenger transport).
• Food & beverages, outdoor catering, club/gym memberships — unless the law obliges you to provide them.
• Works contract / construction of immovable property on your own account.
• Goods lost, stolen, destroyed, gifted, or given as free samples.

Keep a "block reason" column so an auditor (or future you) can see why each was excluded.

How do I total claimable ITC for GSTR-3B?

1. SUM the Eligible ITC column by head (IGST, CGST, SGST).
2. Compare each head's total to the GSTR-2B figure — your claim should not exceed it.
3. Enter the totals in Table 4(A) of GSTR-3B; put any reversals in Table 4(B).
4. If you find ITC wrongly availed earlier, reverse it with interest at 24% p.a.

A check cell — =Claimed_ITC - GSTR2B_ITC — should be zero or negative; a positive value means you're over-claiming and the portal may block the return.

How Ankeshan helps: Ankeshan matches your purchases to GSTR-2B, applies the Section 17(5) blocks, and totals the exact claimable ITC for Table 4 — flagging any over-claim before the portal does, inside Excel. It's launching soon; join the waitlist.

Frequently asked questions

What is ITC reconciliation? Matching the Input Tax Credit recorded in your books against GSTR-2B and the eligibility rules, to arrive at the ITC you can legitimately claim.

Why can't I claim all my purchase ITC? Because some invoices may not appear in GSTR-2B, and some credits are blocked under Section 17(5) or relate to exempt/personal use.

What are blocked credits under Section 17(5)? A defined list — including certain motor vehicles, food and beverages, and club memberships — on which ITC cannot be claimed unless an exception applies.

What interest applies to wrongly-claimed ITC? 24% per annum on the wrongly-availed and utilised credit, reversed in Table 4(B) of GSTR-3B.

What's the last date to claim ITC? The earlier of 30 November following the financial year or the date of filing the annual return, under Section 16(4).

Sources

• CGST Act, Sections 16, 16(4) and 17(5); ITC eligibility and blocked credits — cbic-gst.gov.in.
• GSTN: GSTR-2B and GSTR-3B Table 4 — gst.gov.in.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: GST in Excel — complete guide » · GSTR-2B reconciliation in Excel » · Reverse charge (RCM) in Excel » · Fix GSTR-1 vs GSTR-3B mismatch »