GSTR-3B Calculation Sheet,

Hello, My Self Ashok Mali and I have prepared the GSTR-3B Calculation Sheet for Monthly GST Calculation. This GSTR-3B calculation Sheet can be used to calculate Monthly GST liability along with late fees and Interest if Any. In this calculation sheet Set-Off of GST done Automatically as per provisions of Section 49(5), Section 49A, Section 49B, Rule 88A and Circular No. 98/17/2019 GST Dated. 23.04.2019. Here on the Following basis GST Set-Off is Calculated.

GSTR-3B Calculation SheetSource of above Image: Chapter 8: Input Tax Credit (Page No. 8.85)

Presently Taxpayer Directly fill GST amount at GST Portal and Further Set-Off is Confusing at GST Portal, if taxpayer use this excel sheet for calculation of GST payable then Monthly GST payment can be derived accurately and accordingly GST amount can be filled directly at GST portal from this Sheet. Also, taxpayers can save this Sheet for their reference on Monthly basis to have an idea and for a Set-off entry in Books of Accounts as per this Sheet.

[ See Note give below (Annexure-I) & Download Excel file (Annexure -II) for More Discussion on Adjustment entries for ITC Reconciliation

ANNEXURE- I

Adjustment entries for ITC Reconciliation

Note: For Adjustment entries in Books of Accounts due to Different Treatment of ITC in GSTR-3B and In Books of Accounts

1. Treatment in GSTR-3B as per following Provision in GST Act

> With effect from 09.10.2019, a new sub-rule (4) has been introduced in rule 36 vide Notification No. 49/2019 CT dated 09.10.2019 to specify the quantum of ITC that can be claimed against the invoices/debit notes uploaded and invoices/debit notes not uploaded, by the supplier. As per sub -rule (4) of rule 36, ITC to be availed by a registered person in respect of invoices or debit notes, the details of which have not been uploaded by the suppliers in GSTR-1, cannot exceed 20% of the eligible credit available in respect of invoices or debit notes the details of which have been uploaded by the suppliers in GSTR-1.

> With effect from 01.01.2020, Notification No. 75/2019 CT dated 26.12.2019 has amended the said sub-rule to reduce the percentage of ITC that can be availed on invoices not uploaded by the suppliers in their GSTR-1s from 20% to 10%.

Case Amount of ITC to be claimed by recipient
Where invoice/debit note has been uploaded by the supplier in his GTSR-1 Full ITC, if all other conditions of availing ITC are Fulfilled
Where invoice/debit note has not been uploaded by supplier in his GTSR-1 10% of the eligible ITC available in respect of the uploaded invoices/debit notes. However, the ITC so claimed should not exceed the actual eligible ITC available in respect of the invoices not uploaded.

2. Treatment in Books of Accounts and Comment on Difference

> In Books of Accounts entries for Input Tax Credit Made as per General Accepted Accounting Principles (Hereinafter referred as GAAP). As per GAAP entries for Input Tax Credits is Made as per Tax Invoice received by Registered Taxpayer provided the Conditions Mentioned in Chapter V of CGST Act (Section 16 to 21) & Chapter V Input Tax Credit of CGST Rules (Rule 36-45) are Satisfy.

> Assuming All Such Conditions as Mentioned in Chapter V of CGST Act (Section 16 to 21) & Chapter V Input Tax Credit of CGST Rules (Rule 36-45) are Satisfied.

> Then Obviously due to this different treatment in Books of Accounts and GST Returns Matching of Input Tax Credit is difficult task and also sometime can lead to Confusing.

> For this reason, Taxpayer Needs to Maintain the Track of Above treatment in Addition to Preparing this GSTR-3B Calculation Sheet.

3. Basic Idea on Track over different treatment in Books of Accounts and GSTR-3B Return

> Taxpayer after Calculating eligible ITC as per Books of Accounts Compare Such ITC with ITC Available in GSTR-2a of the Taxpayer.

> After Comparing Such ITC There can be two Possibility:

a. Eligible ITC as per Books of Accounts is Lower than that of ITC appearing in GSTR-2a of Taxpayer then there is No Question arise for applying this Rule. Taxpayer can take Full ITC as appearing in his Books of Accounts.

b. Eligible ITC as per Books of Accounts is More than that of ITC appearing in GSTR-2a of Taxpayer then Taxpayer has to apply aforementioned Rule for availing ITC. In Such a Case there is Mismatch arise in relation to ITC as appearing in Books of Accounts and as taken in GSTR-3B. For tracking this Difference taxpayer have to Prepare Additional Documents for Such Difference and Make entries in Books of Accounts accordingly to Track Additional Credit which is Not Appearing in GSTR-2a which are Yet to be Taken.

i. Taxpayer Can transfer Credit which is Yet to be taken in GST Control Account and Match Input Tax Credit as per GST Portal.

ii. Subsequently if there is Higher Credit due to Reversal of Such Difference in Subsequent Months returns then taxpayer Can utilise Such Difference from GST Control Account. But Taxpayer needs to check Such ITC which is taken in Next Month that this ITC is not higher than the ITC appearing in GST Control Account. Due to Creation of this Ledger in Books of Accounts Error of taking Excess ITC, Higher Interest on Such Excess ITC and Penalty for Wrong Availment of ITC can be avoided.

ANNEXURE- II

Download Excel File containing Adjustment entries for ITC Reconciliation (Protected)

Note: Excel Sheet attached herewith is Password protected to Protect Any Alteration or Modification in this Sheet.

Download Excel File containing Adjustment entries for ITC Reconciliation (Unprotected)

Note- In above sheet author has put a Button Called “Unprotect Sheet” if You Click on it You Can Customise GSTR 3B Sheet according to Your Requirements.

GST Calculation Sheet provided here are based on Authors View Only. It might be possible that other person can have different viewpoint. If other person have different view then they can customise this sheet according to their requirements.

GST Calculation Sheet for GST Compensation Cess

1. GST Compensation Cess Calculation. [ To Enable Cess Calculation Click on [+] icon appearing above Nth Column ]

2. Interest Calculation Bifurcated On Late Payment of GST and Excess ITC was taken by Taxpayer.

3. Summary for Payment of Tax are Provided at Bottom of Calculation Sheet.

4. If Any Person want any Modification in this Sheet they can do after pressing the Button Called ” Unprotect Sheet”

Download GST Calculation Sheet for GST Compensation Cess

GST Calculation Sheet provided here are based on Authors View Only. It might be possible that other person can have different viewpoint. If other person have different view then they can customise this sheet according to their requirements.

Author Bio

Qualification: Student - CA/CS/CMA
Company: N/A
Location: Ahmedabad, Gujarat, IN
Member Since: 19 Oct 2020 | Total Posts: 1

More Under Goods and Services Tax

7 Comments

  1. C.L. RAVIKUMAR says:

    Sir Very important working sheet for 3B calculation in right time. Please provide CESS column. My best wishes and keep it up Ji.
    C.L. RAVIKUMAR, GSTP, PONDICHERRY.

  2. Ashokmali752 says:

    As many people want Interest Calculation for excess ITC taken Seperately and also 10%/20% Rule application in this Sheet Only I will arrange for the Same Only after December, 2020 Month as this will take time to Prepare.

    As of Now you can Mannully do Calculation for 10%/20% Rule and if ITC as per GSTR 2a is Less compare to Books of accounts then after applying this Rule Net Diff Can be Written in Column 18/19 in Nagative Figures named as ” ITC Not taken for applying 10%/20% Rule ” this Balance Can be Matched with GST Control Account if You are preparing GST Control Account in Your Books of accounts.

    Thanks for Your valuable feedback.

  3. Vikas says:

    Very Nice This Excel Sheet also Provides Tax Advice for IGST Balance Set-off. Can you Provide Interest and Late Fees Calculations more In Details if Possible.

Leave a Comment

Your email address will not be published. Required fields are marked *

Search Posts by Date

November 2020
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30