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.
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 >>
Supreme Court expresses serious concerns over AI generated judgments/paragraphs relied upon by the NCLT/NCLAT In a recent judgment, the Hon'ble…
ICAI to take disciplinary action for quoting very low and incommensurate fee by Chartered Accountants As per the announcement made…
NSDL latest e-TDS TCS RPU Version 6.0 from FY 2007-08 NSDL has revised the e-TDS TCS RPU utility for preparing…
Requirement for filing Form 67 for claiming Foreign Tax Credit (FTC) is only directory and not mandatory - ITAT In…
Limitation for invoking revisional jurisdiction u/s 263 with respect to issues not covered in re-assessment would start from the original…
AO not justified in rejecting registered valuer’s report without making a reference to the DVO - ITAT In a recent…
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.
Dear Sir,
Request you to please updated excel formula for AY 2018-19.
Superb formula & it is so simple.
Thanks buddy
Very nice of you to share this formula. Have benefited immensely. Thanks for sharing.
Pls send formula for 2019 -20 plz