Rafiul Hasan
Are you looking for an easy way to create GST late fees calculator in Excel? Don’t worry! You have landed in the right place. In this article, I will show you how to create GST late fees calculator in Excel.
Download Practice Workbook
You can download the practice book from the link below.
What Is GST?
GST stands for Goods and Service Tax. This is a value-added tax imposed on goods and services for domestic purposes. The final price of a good or service includes this tax. Although the consumer pays this tax, it is remitted to the government by the seller or the business.
Easy Steps to Create GST Late Fees Calculator in Excel
In this section, you will find some easy steps to create GST late fees calculator in Excel. I will demonstrate them here with proper illustrations.
Just proceed with the following steps.
Step 1: Create Variable for GST Calculator
- First of all, assign variable names for calculating GST late fees and put them in the column headings. I have assigned the following variable names for my calculator:
- Month
- Due Date
- Date of Filling
- Status
- Days of Delay
- Net Tax Liability
- GST/Day if Taxable
- Interest GST
- Amount
You can add or remove any variable name as per your need.
- Then, assign the Month and Due Date to file GST. I have created my calculator for the whole year( from January to December) and taken the Due Date 10th for every month.
Read More: How to Calculate GST in Excel (With Easy Steps)
Step 2: GST Filing Status for Calculator
- Now, put the Date of Filing GST for every month.
- After putting the filing date in the respective column, you have to find out whether GST is filed on time or delayed. For getting the filing status we will now use the IF Function. Select the first cell of the Status column and type the following formula:
=IF(D5<=C5,"On Time","Delayed")
Here,
- D5 = Date of Filing
- C5 = Due Date
- Now, press ENTER, and the cell will show the GST filing status (On Time/ Delayed).
- Here, use the Fill Handle tool to Autofill the formula for the rest of the cells.
- Hence, you will get the status for all corresponding months.
- After that, find out the Days of Delay. Apply the following formula to the column Days of Delay for this:
=D5-C5
Here,
- D5 = Date of Filing
- C5 = Due Date
- Here, in our calculator, we have found the GST status has been Delayed four times.
Read More: How to Create GST Bill Format in Excel with Formula
Step 3: Calculate GST Late Fees
Hang on! It’s time to calculate the GST Late Fees. Let’s move on.
- Assign Net Tax Liability for the concerning months. In our dataset, we have Net Tax Liability 3 times. For your case, find it from your notice and assign them as per the direction.
- Now, calculate the GST Late Fees for the number of Delayed Days. We have considered Late Fees Per Day: 30.
So for getting the total late fees, multiply the number of Delayed Days with Late Fees Per Day. In order to get the total late fees, select a cell and apply the following formula:
=F5*30
Here,
- F5 = Days of delay
- 30 = Late fees per day
- Now, hit ENTER and drag the formula down for the other cells and you will get the GST Late Fees for the whole year.
Read More: Creating GST Purchase Order Format in Excel (Free Template)
Similar Readings
- GST State Code List in Excel (Detailed Analysis)
- How to Create GST Rental Invoice Format in Excel
- Proforma Invoice Format in Excel with GST (Free Template)
Step 4: Calculate Interest on GST
Interest will be counted for the months you haven’t paid the GST on time. We have considered the Interest Rate as 16%. You can proceed with your own.
- Here, select the first cell of the Interest GST column and apply the following formula.
=G5*0.16*F5/365
Here,
- G5 = GST late fees
- F5 = Days of Delay
 💡 Formula Breakdown
G5*0.16/365 gives the interest rest per day on a year. Multiplying it by F5 (Days of Delay) returns the interest over the total days of delay.
- Now, hit ENTER and drag the formula for the other cells.
Read More: Create GST Invoice Format in Excel (Step-by-Step Guideline)
Step 5: Calculate Total Payable Amount
- In order to calculate the total amounts to be paid for every month, apply the following formula:
=H5+I5
Here,
- H5 = GST late fees
- I5 = Interest on GST
- Press ENTER and drag the formula for each cell up to the month of December.
- Now, apply the following formula to get the total amount to be paid for the whole concerning time (i.e. from January to December).
=SUM(J5:J16)
Here,
(J5:J16) = the range to sum
- Finally, press ENTER to get the total amount.
Read More: How to Remove GST from Total Amount in Excel (With Easy Steps)
Conclusion
In this article, I have tried to show you the process to create GST late fees calculator in Excel. Hope you like reading this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Stay Connected.
Happy Excel!
