Follow Us :

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.

ITC of Output IGST Liability Output CGST Liability Output SGST / UTGST Liability
IGST (I) (II) – In any order and in any proportion
(III) ITC of IGST to be completely exhausted mandatory
CGST (V) (IV) Not permitted
SGST (VII)

Only after ITC of CGST has been utilized fully

Not permitted (VI)

 In Addition to that I also Incorporated impact of Rule 86B and Rule 36(4) in GSTR-3B Calculation Sheet.

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.

1. Refer ANNEXURE-I to Download GST Calculation Sheet.

2. Refer ANNEXURE-II for Adjustment entries in Books of Accounts due to Different Treatment of ITC in GSTR-3B and In Books of Accounts.

 ANNEXURE-I GST Calculation Sheet.

 To Download GST Calculation Sheet (Updated on 03.03.2021) , Click Here.

 Notes:

1. Excel Sheet attached herewith is Password protected to Protect Any Alteration or Modification in this Sheet. However, you can Unprotect Sheet by clicking on Unprotect Sheet for your customization.

2. Do Not Change the Password of this Sheet.

3. After Changing Password Sheet May Not work Properly.

4. Unprotect Sheet Option Given only for your Customisation.

 ANNEXURE-II 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%.

> Also, with effect from 01.01.2021, Notification No. 94/2020 CT dated 22.12.2020 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 10% to 5%.

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 5% 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-2B 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-2B 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-2B 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-2B 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.

Disclaimer:- GST Calculation Sheet provided here is based on Authors’ View Only. It might be possible that other people may have different viewpoint or requirements. If other people have different views then they may customize this sheet according to their requirements.

(Republished with Amendments)

Author Bio


Join Taxguru’s Network for Latest updates on Income Tax, GST, Company Law, Corporate Laws and other related subjects.

18 Comments

    1. Ashokmali752 says:

      You can put RCM Calculation at Bottom of Calculation Sheet after Net GST Payable. If you need further Clarification You can contact me at Mail Id ashokmali752@gmail

  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 Post by Date
March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031