AKAC GUIDE TO RULE 37 WORKING:
A detailed step by step approach to identify invoices “not paid timely as per Rule 37” through excel spreadsheets for GST ANNUAL RETURN AND GST AUDITS
With the advent of the countdown of GST Annual Return and GST Audits, the assesses and the accounting professionals are on their toes to prepare and reconcile the data and compiling the furnished GST Returns so as to ensure accurately and timely filing of this annual extravaganza. At this moment of time , the reporting of Clause- 7A of GSTR-9 (reversal of ITC as per Rule 37) has become one of the most discussed yet most confusing topic. The difficulty lies in the fact that tracking of such non paid invoices on day to day basis would require unpaid invoice ageing for each day. This makes this exercise (by manual ways) quite non-feasible so as to verify thoroughly by the management as well as GST Auditors.
This Article aims to share a detailed step by step approach to identify all such unpaid invoices through excel spreadsheets. This innovative Auditing technique, being labelled by the author as “AKAC GUIDE TO RULE 37 WORKING – PART-I ”.
Let’ s see the relevant Excerpts of “Second proviso to sub-section(2) of section 16” of CGST Act,2017 and corresponding Rule 37 of CGST Rules,2017 as below :
Second proviso to sub-section(2) of section 16
Provided further that where a recipient fails to pay to the supplier of goods or services or both, other than the supplies on which tax is payable on reverse charge basis, the amount towards the value of supply along with tax payable thereon within period of one hundred and eighty days from the date of issue of invoice by the supplier, an amount equal to the input tax credit availed by the recipient shall be added to his output tax liability, along with interest thereon, in such manner as may be prescribed.
Provided also that the recipient shall be entitled to avail of the credit of input tax on payment made by him of the amount towards the value of supply of goods or services or both along with tax payable thereon.
Rule 37
A registered person, who has availed of input tax credit on any inward supply of goods or services or both, but fails to pay to the supplier thereof, the value of such supply along with the tax payable thereon, within the time limit specified in the second proviso to sub-section(2) of section 16, shall furnish the details of such supply, the amount of value not paid and the amount of input tax credit availed of proportionate to such amount not paid to the supplier in FORM GSTR-2 for the month immediately following the period of one hundred and eighty days from the date of the issue of the invoice
(2) The amount of input tax credit referred to in sub-rule (1) shall be added to the output tax liability of the registered person for the month in which the details are furnished.
(3) The registered person shall be liable to pay interest at the rate notified under sub-section (1) of section 50 for the period starting from the date of availing credit on such supplies till the date when the amount added to the output tax liability, as mentioned in sub-rule (2), is paid.
| Step No | Detailed Procedure |
| 1 | Download all ledgers combined for the financial Year ( one below the other) |
| 2 | Now rearrange the ledgers in Following given at the end of this article. (The author believes only Column No A to Column No H Should be extracted from assessee and rest will be generated as per subsequent steps of this exercise) |
| 3 | Add one column and generate/compute “Net Debit” (Debit Amount less Credit Amount) |
| 4 | Add one column and generate/compute “Running Balance” (for each ledger) with following formulae: – for first row (i.e. cell J2): =+G2-H2 – for subsequent rows ( i.e. in Cell J3, J4 and so on : +=IF(A3=A2,(J2+G3-H3),(G3-H3)) |
| 5 | Add one more column (Column K) with name “T+180” and add 180 to corresponding cell in Column B |
| 6 | Add one more column and copy the ledger name here again (i.e. “=+A2” in Cell L2) |
| 7 | Add one more column and mark “Purchase” against those ledgers of those parties from whom purchase is made. ( Use combination of concatenate, remove duplicate, and Vlookup for such marking in Column L) |
| 8 | Add column N named “Purchase_Transaction” and mark “Yes” against Parties Ledgers of suppliers, Purchase Ledgers and other expense ledgers on which Input is to claimed in GSTR-3B ( as filtered in Column A) and having Net Debit Balance as positive in Column I |
| 9 | Add one more column named Unique ID and generate Unique ID combing date, Voucher Type and Voucher No against each row (in Column O) |
| 10 | Now ensure that all Voucher Types of Sales are renamed as “sales” and all payment vouchers to be renamed as “Payment”. These nomenclatures will be used in further formulas in this working |
| 11 | Now we need to compute the amount of payments made within 60 days of such booking of invoice with the help of following Formulae:
+IF(J2<0,SUM(SUMIFS(G2:$G$8289,E2:$E$8289,{“payment”,”Sales”},B2:$B$8289,”<“&K2:$K$8289,A2:$A$8289,L2:$L$8289)), “Debit balance”) This formulae is to be fed in Column P (the above given formulae is for Cell P2 and assumes the total no of rows to be 8289 in the data. The actual no. of total rows should be fed in the formula as per the data. |
| 12 | Now add one more column with Name “Unpaid Invoices” to identify the unpaid invoices using following formulae in Column Q:
IF(P2=”Debit Balance”,0,(MAX((P2+J2)*(-1),0))) |
| 13 | The Positive values computed in Column Q now marks all the cases where invoices have not been paid within 180 days of booking of invoices. But these cases involves all ledgers including cash, bank, duties and taxes, fixed assets so on and so forth. This needs to be further processed for making it relevant for decision making |
| 14 | Now filter out the rows where Column N does not contain “Yes” and also those rows whose Value in Column Q is “0″. The unique Ids of such remaining rows should be copied elsewhere in a new tab |
| 15 | Now enter a New Column R with name Input Credit Reversal and map those rows of GST ledgers with Yes which contains the same Unique Ids as noted in the preceding point (using Vlookup command) |
| 16 | The net Debit Values of mapped rows in the immediately preceding step will be the GST amounts which needs to be reversed. The date in column A represents the date of booking of ITC in books and date in Column K represents the date when the ITC was due to be reversed as per Rule 37 |
–
| ROW1 | ROW2 | |
| Column A | Ledger Name | ABC LTD |
| Column B | Date | 03-07-2018 |
| Column C | Dr/Cr | Dr |
| Column D | Particulars | Purchase A/c |
| Column E | Voucher Type | Purchase |
| Column F | Voucher No. | 1 |
| Column G | Debit Amount | |
| Column H | Credit Amount | 4261950 |
| Column I | Net Debit | -4261950 |
| Column J | Running Balance | -4261950 |
| Column K | 6month date | 30-12-2018 |
| Column L | Ledger Name | ABC LTD |
| Column M | Purchase | Yes |
| Column N | Purchase _Transaction | Yes |
| Column O | Unique ID | 43284Purchase1 |
| Column P | Amount Paid in next 6 months | Debit balance |
| Column Q | Unpaid Invoices | 0 |
| Column R | Input Reversal Status |
(Due to shortage of width of the text area, the table has been shown here in transpose manner for comfortable viewing experience of the reader of this article)
Disclaimer: The information contained in this document is intended solely for dissemination of information and doesn’t aim at soliciting work in any manner. Though meticulous care has been taken but the author assumes no liability in respect of any loss/damage incurred while acting on the basis of information provided. The above AUDITING TECHNIQUE has been developed by the author after researching since long time and is a proprietary intellectual property of the author. The author can be reached at AKSHAY.AKAC@GMAIL.COM and can be called at +91-7011503210.
Author Bio
Sir, please provide the excel sheel.
Sir please provide excel sheet will be thankful for helping me
Sir, kindly provide an excel format so that we can easily track invoices.
IF WE PAY 90% OF THE BASIC OF BILL VALUE+100 OF GST, BALANCE 10 % IS TO BE PAID AFTER WARRANTY PERIOD OF 2 YEARS, HOW ABOUT INPUT CREDIT?? AS PER RULE 16(2)
Dear Sir,
Rule 37 has any exemptions i.e MSME firms are not pay the amount within the deadline of 180 days. They have also reverse the ITC amount
IAM also your fen sir ,
Thankyou for provide Great ideas for tax .