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
yes i need
Refer ANNEXURE-I to Download GST Calculation Sheet.
Very Good Excel Sheet Pl let me know where I can see Interest & Late Fees? Pl help
For Late Fees and Interest Calculation Click on “+” icon Appearing after RCM Calculation at left Side
Not able to enter RCM Purchase (from Transporter).
Pl, help to compute Net Tax payable after RCM adjusment.
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
Great work sir, this sheet helps in calculating interest and cess also till date, very helpful. Keep it up sir.
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.
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.
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.
Thank you So much Sir
sir
not able to unhide cess column please tell how to do it
Very Good innovative