Excel Income Tax Formula for calculating income tax in a single excel cell for individuals, senior citizen and super citizens

Excel Income Tax Formula 

Excel Income Tax Formula

Excel Income Tax Formula for calculating income tax in a single excel cell

Ever felt the need to have one compact formula that can calculate income tax in one go? If yes, Microsoft Excel Sumproduct function can be used to calculate income tax for persons under different tax slabs.

The description of the SUMPRODUCT function says that it Returns the sum of the products of corresponding ranges or arrays?

However it can be used to calculate income tax based on different slabs by making alterations to its syntax. The function can be deployed for calculating income tax based on different slabs and marginal income tax rates.

Income Tax Rates for AY 2016-17

The income tax slabs and rates for Individuals/HUF for AY 2016-17 are as under:

(a) Other than Senior Citizens  
Up to Rs. 250000 Nil
From 250001 to 500000 10%
From 500000 to 1000000 20%
1000000 or above 30%
(b) Senior Citizens  
Up to Rs. 300000 Nil
From 300001 to 500000 10%
From 500000 to 1000000 20%
1000000 or above 30%
(c) Super Senior Citizens  
Up to Rs. 500000 Nil
From 500001 to 1000000 10%
1000000 or above 30%

 Excel Income Tax Formula for AY 2016-17

(a) Other than Senior Citizens

=SUMPRODUCT(–(A1>{250000;500000;1000000}), (A1-{250000;500000;1000000}), {0.10;0.10;0.10})

(b) Senior Citizen

=SUMPRODUCT(–(A1>{300000;500000;1000000}), (A1-{300000;500000;1000000}),   {0.10;0.10;0.10})

(c) Super Senior Citizens

=SUMPRODUCT(–(A1>{500000;1000000}), (A1-{500000;1000000}), {0.20;0.10})

Here, it has been assumed that the taxable income is written in cell “A1”. In the same fashion, income tax formula can be developed for any class for any Assessment Year.

Note:

  1. Note that the last brackets are differential income tax rates derived by subtracting the marginal rate ofthe first effective tax slab from the tax rate of the immediate preceding income slab as under:(Other than senior citizens)
Start of Slab End of Slab Rate Differential Rate
Nil 250000 0.00 0.00
250001 500000 0.10 0.10
500001 1000000 0.20 0.10
> 1000000 0.30 0.10
  1. Double minus signs have been used in the beginning to make the formula work with non numeric returns and convert them into positives.
Excel Income Tax Formula

Download sample Excel Income Tax Formula Worksheet

----------- Similar Posts: -----------

5 Comments

  1. Rajib Paul February 28, 2018
  2. varma April 23, 2017
  3. varma April 22, 2017
  4. Ananth s November 4, 2016
  5. Ahmedabad Forensic CA May 2, 2016

Leave a Reply

Subscribe to ABCAUS Newsletter

Get reliable, authentic and latest updates on taxation/corporate and other laws in your mail box free.



After subscribing, please check your email (including spam or junk folder) and activate the subscription link by clicking it.