In this article, you will learn about three quick steps on how to perform a service tax late payment interest calculation in Excel. We will be using the Microsoft 365 version; however, you can use any version of Microsoft Excel and follow this tutorial.
Download Practice Workbook
You can download the Excel file from the link below.
Service Tax
This tax is imposed by the Government of India on services provided that are not on the negative list of the taxation system. All items except those in the negative, i.e., the positive list, are taxable. This is an indirect tax. Firstly, the service providers ask the customer to pay the tax. Then, the service providers pay the amount to the government. As of June 1, 2016, this service tax rate is 15%. This tax is applicable only when the service provider generates more than 10 lakh rupees worth of service.
Step-by-Step Procedures to Perform Service Tax Late Payment Interest Calculation in Excel
We will show two quick steps to find the service tax late payment interest calculation in Excel. We have considered the revised service tax rate from 2016. Therefore, this file will not be feasible for the years before 2016. Firstly, we will type the required fields to enter the data. After that, we will find the tax payable using a simple formula. Then, we will find the duration of the delay. Lastly, we will use the service tax rate for late payments and find the total amount to be paid.
Here we can see the final snapshot of our objective after performing the two steps.
Step 1: Creating the Required Fields
We will type the required fields in this first step. There will be two sections. In the first section, we will type the fields for the tax amount, tax exemption rate (if applicable), the due date, the payment date, and finally the delay duration. Then, in the next section, there will be three columns, namely “Due”, “Rate”, and “Interest”. The late payment interest rate varies with duration. Finally, there will be fields to find the total amount.
- To begin with, type the following fields. Here, you should type the exemption in a percentage format. This field is not applicable for many services, so keep it blank for those. Moreover, the payment date should be in “month/day/year” format.
- Afterward, type these fields.
- Finally, we will complete typing the required fields for this article.
Read More: Create Late Payment Interest Calculator in Excel and Download for Free
Similar Readings
- How to Calculate Interest Between Two Dates Excel (2 Easy Ways)
- How to Find Interest Rate in Future Value Annuity (2 Examples)
- Create Post-Judgment Interest Calculator in Excel (With 2 Cases)
- How to Calculate Accrued Interest on a Loan in Excel (3 ways)
- Perform Carried Interest Calculation in Excel (with Easy Steps)
Step 2: Calculating the Values
We will calculate the total amount of interest for payment in this section. Additionally, we will use the AND, IF, and SUM functions to calculate the values in this step. The tax exemption is available for a limited sector; we have used 21%, and if your sector is not applicable for this, you can put zero percent. For simplicity, we have considered that there are 30 days in a month and 360 days in a year.
- Firstly, type the due amount of service tax.
- Secondly, type the amount of exemption, if it is not needed, then you can type zero here.
- Thirdly, type this formula in cell D6 to find the amount to be paid considering the tax exemption rate.
=(1-D5)*D4
- Afterward, type the due date and the payment date.
- Next, type another formula to calculate the delay.
=D8-D7
- The bracket for the late payment for service tax is:
| Delay Duration | Interest Rate |
|---|---|
| For First 6 Months | 15% |
| For Next 6 to 12 Months | 21% |
| For Beyond First Year | 27% |
- Then, type this formula in cell D12 to return the interest for the first six months.
=IF(D8-D7>180,D6*C12/360*180,D6*C12/360*(D8-D7))
Formula Breakdown
- Firstly, we are considering that each month consists of 30 days.
- Secondly, we are checking whether the delay is more than 180 days (6 months). Moreover, we divided the yearly interest by 360 to find the daily interest rate.
- Thirdly, if it is more than 6 months, then we are multiplying the due amount with the daily rate to calculate the interest amount.
- However, if the delay is less than 180 days, then we multiply the delay with the daily rate directly to find the interest amount.
- The formula reduces to, IF(TRUE,1805.94,17858.74)
- Output: 1805.94.
- After that, type another formula to find the interest amount for the second bracket.
=IF(D8-D7>360,D6*C13/360*180,IF(AND(D8-D7>180,D8-D7<360),D6*C13/360*(D8-D7-180),"-"))
Formula Breakdown
- Now, this formula is similar to the previous formula. Firstly, we are finding the delay date. If it is more than 1 year, then we are multiplying the amount by the daily interest and 180 days.
- Secondly, if it is more than 180 days but less than 360 days, then we subtract 180 days from the delay duration. Then, use that value to find the interest amount.
- Finally, for less than 180 days of delay, we set the output as a hyphen.
- The formula reduces to, IF(TRUE,2528.316,”-“)
- Output: 2528.32.
- Next, type this formula to return the interest amount for later than one year. This formula is similar to the previous formulas, so we are skipping the formula breakdown.
=IF(D8-D7>360,D6*C14/360*(D8-D7-360),"-")
- Next, type this formula to find the total interest.
=SUM(D12:D14)
- Lastly, type this formula to find the total due amount.
=D6+D15
- After doing so, we will complete the process. Now, to test the calculator, we can change the payment date to November 30, 2017, and it will change accordingly.
Read More: Calculate Interest in Excel with Payments (3 Examples)
Conclusion
We have shown you quick steps on how to perform a service tax late payment interest calculation in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
Related Articles
- How to Calculate Gold Loan Interest in Excel (2 Ways)
- How to Calculate Principal and Interest on a Loan in Excel
- Car Loan Calculator in Excel Sheet – Download Free Template
- How to Calculate Home Loan Interest in Excel (2 Easy Ways)
- How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)
- Bank Interest Calculator in Excel Sheet – Download Free Template
