How to Calculate Income Tax on Salary with Old Regime in Excel

The income tax is a tax imposed on individuals in respect of their income or profits. Frequently, you need to calculate the income tax on salary by yourself in some countries and pay it. In this tutorial, you will learn how to calculate income tax on salary with Old Regime in Excel.


Download Practice Workbook

You can download the Excel workbook from here.


4 Easy Methods to Calculate Income Tax on Salary with Old Regime in Excel

For the examples, we will use the following dataset. The dataset shows the tax rates both for the Old Regime and the New Regime. Clearly, the tax rates for both regimes are different. We will calculate the income tax on salary with Old Regime in this article.


1. Calculate Income Tax on Salary with Old Regime with Different Tax Slabs

Sometimes government defines Income slabs for different ranges of income. According to these slabs, tax percentage varies with income range like the following table. Now we will see how to calculate Taxable Income and Income Tax on salary with different Tax Slabs defined by the government with Old Regime in Excel.

Calculate Income Tax on Salary with Old Regime with Different Tax Slabs

Steps:

  • Firstly, click the C9 cell and type the following formula to calculate the taxable income like the previous method:
=C5-C6-C7
  • Now press Enter to calculate the Taxable Income.

Calculate Income Tax on Salary with Old Regime with Different Tax Slabs

  • After that, create a new column named Differential next to the column Tax Rate and input 10% manually in the E13.
  • Then click E14 cell, and type the following formula:
=D14-D13
  • Next, press Enter and drag the Fill Handle to the entire column Differential.

Calculate Income Tax on Salary with Old Regime with Different Tax Slabs

  • Now create a new column named Amount next to the column.
  • Then click on cell F13 and type the following formula:
=$C$9-B13
  • Furthermore, press Enter and drag the Fill Handle to the entire column Amount.

Calculate Income Tax on Salary with Old Regime with Different Tax Slabs

  • Furthermore, create a new column named Tax next to the column Amount.
  • Now, select the G13 cell and type the following formula:
=F13*E13
  • After that, press Enter and drag down the Fill Handle to the G15 cell (to the cell till the Amount column entries are positive).

  • Next, click the C10 cell and sum the cells from G13 to G15 by typing the following formula:
=SUM(G13:G15)
  • Finally, press Enter and It will calculate the income tax on your salary.

Read More: Income Tax Computation in Excel Format (4 Suitable Solutions)


2. Apply SUMPRODUCT Function to Calculate Income Tax on Salary with Old Regime In Excel

The SUMPRODUCT function can also be used to calculate income tax on salary in excel. It is quite easy to calculate the income tax by using only a single function. Now we will see how to calculate income tax on salary with Old Regime using the SUMPRODUCT Function in Excel.

Steps:

  • At first, we need to insert a new row above row 5. For that, select row 5 and right-click on your mouse. It will open a new window like the following image.
  • Then click the Insert tab.

Apply SUMPRODUCT Function to Calculate Income Tax on Salary with Old Regime In Excel

  • After that, click the Entire Row option, then OK and It will create a new row above row 5.

Apply SUMPRODUCT Function to Calculate Income Tax on Salary with Old Regime In Excel

  • Now suppose the Taxable Income is $940000 of an individual. We want to calculate the Income Tax of the individual. For that, click the C14 cell and type the following formula:
=SUMPRODUCT(D6:D11-D5:D10,C13-B6:B11,N(C13>B6:B11))

Apply SUMPRODUCT Function to Calculate Income Tax on Salary with Old Regime In Excel

  • Finally, press Enter and it will calculate the income tax of the individual.

Read More: Computation of Income Tax Format in Excel for Companies


Similar Readings


3. Calculate Income Tax on Salary with Old Regime with Excel VLOOKUP Function

The VLOOKUP function can be used to calculate income tax on salary with Old Regime in Excel. It is an easy way to calculate the income tax by using only a single VLOOKUP Function. Now we will see an example to calculate income tax on salary using VLOOKUP Function from the following data set.

Calculate Income Tax on Salary with Old Regime with Excel VLOOKUP Function

Steps:

  • First of all, create a new column named Cumulative next to the column Tax Rate.
  • Now, select the E5 cell and insert $0.
  • After that, select the E6 cell and type the following formula:
=(C5-B5)*D5
  • Now press Enter.

Calculate Income Tax on Salary with Old Regime with Excel VLOOKUP Function

  • Subsequently, select the E7 cell and type the following formula:
=(C6-B6)*D6+E6
  • Now press Enter and drag down the Fill Handle to the entire column of Cumulative from the E7.

Calculate Income Tax on Salary with Old Regime with Excel VLOOKUP Function

  • Now suppose the Taxable Income is $940000 of an individual and we want to calculate the Income Tax of the individual. For that, click the C13 cell and type the following formula:
=VLOOKUP(C12,B4:E10,4,TRUE)+(C12-VLOOKUP(C12,B4:E10,1,TRUE))*VLOOKUP(C12,B4:E10,3,TRUE)

  • Lastly, press Enter and it will calculate the income tax of the individual.

🔎 How Does the Formula Work?

  • VLOOKUP(C12,B4:E10,4,TRUE): Firstly, this part of the formula looks for C12 in the range (B4:E10). Then, returns the rate from column 4.
  • (C12-VLOOKUP(C12,B4:E10,1,TRUE)): The VLOOKUP function looks for C12 in the range B4:E10. Returns the value from the 1st column. Next, deducts the output from C12.
  • VLOOKUP(C12,B4:E10,4,TRUE)+(C12-VLOOKUP(C12,B4:E10,1,TRUE))*VLOOKUP(C12,B4:E10,3,TRUE): At last, it multiplies the outputs of VLOOKUP(C12,B4:E10,3,TRUE) and (C12-VLOOKUP(C12,B4:E10,1,TRUE)). After that, add VLOOKUP(C12,B4:E10,4,TRUE) output to it.

4. Apply IF or Nested IF Function to Calculate Income Tax on Salary with Old Regime In Excel

The IF function or Nested IF function is a very useful function if we want to calculate income tax on the salary of different persons with a single function. Now we will see an example of calculating income tax using the IF or Nested IF function. For that, we need a data set like below where the income tax slabs are given and the salary of four individuals (John, Mathew, Rachael, and Jim) are given. We will calculate income tax on their salary.

Apply IF or Nested IF Function to Calculate Income Tax on Salary with Old Regime In Excel

Steps:

  • At first, select cell D14 and type the following formula:
=IF(C14>=$B$11,C14*$D$11,IF(C14>=$B$10,C14*$D$10,IF(C14>=$B$9,C14*$D$9,IF(C14>=$B$8,C14*$D$8,IF(C14>=$B$7,C14*$D$7,IF(C14>=$B$6,C14*$D$6))))))
  • After that, press Enter and It will calculate income tax on John’s Salary.

Apply IF or Nested IF Function to Calculate Income Tax on Salary with Old Regime In Excel

  • Now select cell D14 and drag down the Fill Handle to the entire column Income Tax.
  • Finally, it will calculate the income taxes of the 4 individuals using the IF or Nested IF Function.

🔎 How Does the Formula Work?

  • Here, if C14 is greater than or equal to B11, it’ll give C14*$D$11.
  • If C14 is greater than or equal to B10, it returns C14*$D$10.
  • However, when C14 is greater than or equal to B9, it returns the output of C14*$D$9.
  • Again, when C14 is greater than or equal to B8, it returns C14*$D$8.
  • Furthermore, when C14 is greater than or equal to B7, it returns the value C14*$D$7.
  • Lastly, If C14 is greater than or equal to B6, it returns C14*$D$6.

Read More: How to Calculate Income Tax in Excel Using IF Function (With Easy Steps)


Things to Remember

  • IF or Nested IF Function is the quickest method if you want to calculate Income Tax on salary for more than one person in excel.
  • For calculating income tax for one person, SUMPRODUCT and VLOOKUP functions can be useful.

Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to calculate income tax on salary with Old Regime in Excel. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo