Income Tax

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

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

We often feel the need to have one compact excel 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.

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

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 oft he 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

2. Double minus signs have been used in the beginning to make the formula work with non numeric returns and convert them into positives.

Download Excel Income Tax Formula Click Here >>

Share

View Comments

  • Kindly let me know the purpose of using colon in place of semicolon or why colons are used & not semi colons?

  • Could you please explain a little bit why curly brackets, semicolons are used without multiplication sign* in the sumproduct formulas shown above sir ?
    Regards
    Ramesh Babu

  • Dear Sir,

    Thanks for sharing the above formula.
    What would be the formula if a person has to pay a minimum tax of 5000.00 if his/her income exceeds 250,000 using the above example, say his/her income is 255,000.00

    Regards
    Rajib Paul

  • Sir,
    i replaced 0.10;0.10;0.10 as 0.05;0.15;0.10 and it is working, if wrong please correct me.

  • Very nice of you to share this formula. Have benefited immensely. Thanks for sharing.

Recent Posts

  • Income Tax

AO while issuing certificate u/s 197 should not be guided by the revenue generation

Supreme Court dismisses challenge to High Court order quashing order u/s 197 observing that AO should not be guided by…

2 days ago
  • Income Tax

Notional rent can not be taxed when rent was reduced due to covid conditions – ITAT

Notional rent based on the agreement can not be taxed when rent was subsequently reduced due to covid conditions -…

2 days ago
  • Prevention of Corruption Act

CBDT Instruction can not absolve persons to explain source of gold within limits specified – SC

Mere CBDT Instructions cannot absolve individuals of their obligation to explain the source and nature of their gold holdings, even…

1 week ago
  • ICSI

Empanelment of General Observers for ICSI Examinations December 2026. Last date 16.11.2026

Empanelment of General Observers for ICSI Examinations December 2026 ICSI has invited interested members to enroll as General Observers for…

1 week ago
  • Income Tax

ITAT address some important issues relating to deduction u/s 80IA

ITAT answers some important and inter-related issues relating to deduction u/s 80IA In a recent judgment, ITAT has dealt with…

1 week ago
  • Income Tax

Mere carry forward of loss can not be denied for belated return – ITAT

Carry forward of loss can not be denied for belated return, it is only the set off in subsequent year…

3 weeks ago