How to Create GST Late Fees Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Create GST Late Fees Calculator in Excel: with Easy Steps

In this section, you will find some easy steps to create a 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.

Variable for GST Late Fees Calculator

  • 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.


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

Formula to Find GST Filing Status

  • 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.

GST Filing Status to Create Late Fees Calculator

  • Hence, you will get the status for all corresponding months.

Autofill GST Filing Status Formula

  • 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.


Step 3: Calculate GST Late Fees

Hang on! It’s time to calculate 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 to get 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.


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.

Formula to Find GST Late Fees Interests

  • Now, hit ENTER and drag the formula for the other cells.


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

Amount of Late Fees for Each Month to Create Calculator

  • 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.

Create GST Late Fees Calculator in Excel


Download Practice Workbook

You can download the practice book from the link below.


Conclusion

In this article, I have tried to show you the process of creating a 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.
Happy Excel!


Related Articles

<< Go Back to Excel Tax Calculator | Finance Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo