It is advisable for individuals to calculate their income tax at the beginning of a financial year for proper tax planning.
Do you want to know your taxable income and the tax amount?
Consider calculating income tax in excel, which leaves minimal room for errors. Go through the following sections to know how to calculate income tax in excel.
Also Read: Section 115BA Of The Income Tax Act
Suppose Mr. Singh earns a monthly income of Rs. 1,00,000. Thus, his yearly income will be Rs. 12,00,000. He also earns Rs. 5,000 monthly through rent. So, his yearly rental income will become Rs. 60,000. Apart from these, his yearly interest income is Rs. 4,500.
First off, let us calculate Mr. Singh’s total yearly income in excel.
Step 1: Open Excel and enter these values one after another as shown in the image below:
Step 2: Select the box where you want to calculate the total income and type this formula —=SUM(C3:C5)
Step 3: Press Enter, and you will get his total income as Rs. 12,64,500.
Now, suppose Mr. Singh has made some investments towards —
For such investments, he would be eligible to receive tax deductions under various sections, such as –
Thus, for his investment of Rs. 2,00,000 towards Public Provident Fund (PPF), Mr. Singh will receive a maximum tax deduction worth Rs. 1,50,000. In the same way, he will get tax deductions worth Rs. 2,00,000 and Rs. 25,000 for home loan interest payments and health insurance premium payments, respectively.
Let us compute his total tax deductions in excel.
Step 4: Enter the various deductions in the excel sheet just below the income details, as shown in the image below:
Step 5: Calculate total tax deductions by applying this formula —
Step 6: Press Enter, and you will get the total deductions as Rs. 3,75,000.
Step 7: In the next step, calculate Mr. Singh’s taxable income by applying this formula — =SUM(C8-C15)
Step 8: Press Enter to get Mr. Singh’s taxable income of Rs. 8,89,500.
To calculate Mr. Singh’s total tax liability, we will have to put in the tax slabs, taxable amount and the slab rates (which the government announces) in different columns, as shown below.
Now, let us calculate the tax amount for each slab, considering the slab rates.
Step 9: For a taxable income between Rs. 0 and Rs. 2,50,000, the slab rate is 0%. Meanwhile, the taxable amount is Rs. 2,50,000 (2,50,000 – 0).
To calculate the tax value, put in this formula —
Step 10: Press the Enter key, and you will get the tax amount as Rs. 0.
Step 11: In the same way, calculate the tax amount for the remaining tax slabs by applying the respective formulae given below:
You will get the following values:
Step 12: Up next, calculate Mr. Singh’s total tax liability by applying this formula —
Step 13: Press Enter, and you will see that Mr. Singh’s total tax liability is Rs. 87,400.
So, for a taxable income of Rs. 8,89,500, Mr. Singh will have to pay tax worth Rs. 87,400.
Also Read: Income Tax Rebate Under Section 87A
This guide on how to calculate income tax in excel provides the various steps to calculate tax liability in Excel. Make sure to apply the formulae in the correct way for a hassle-free and quick calculation.
1. What is the difference between the old and new tax regimes?
The tax slabs have been changed in the new tax regime. For example, individuals who earlier fell within the taxable income group of Rs. 5,00,000 – Rs. 7,50,000 has to pay income tax at a rate of 20%. In the new regime, such individuals will be taxed at 10%. In addition, the new tax regime cancelled around 70 deductions.
2. What are a few deductions and exemptions that are not applicable in the new tax regime?
Some tax deductions and exemptions not applicable in the new tax regime are as follows:
3. What are a few deductions retained in the new tax regime?
A few exemptions and deductions that were kept in the new tax regime are as follows:
4. What are a few other ways to calculate income tax on salary?
You can also calculate income tax on salary manually by considering the various slab rates. However, manual calculations can lead to errors, which can impact your financial planning. Thus, you can also perform income tax calculations using an online calculator.
5. How can I calculate income tax manually?
You can easily calculate income tax manually by computing your gross income. After that, calculate your taxable income by subtracting all deductions from your gross income. In the last step, calculate your tax liability according to the slab rates.
This article is solely for educational purposes. Navi doesn't take any responsibility for the information or claims made in the blog.
|Section 80 G
|Section 80C, 24(b), 80EE & 80EEA
|Section 80 EEB
What is Form 26QB for TDS? How to Download and Submit it?While purchasing a property, buyers are liable to pay various taxes. The Finance Act, 2013 made TDS... Read More »
PF Withdrawal Rules 2023 – Rules, Documents Required and TypesEPF/PF Withdrawal Employees’ Provident Fund (abbreviated as EPF) is a popular retirement sav... Read More »
Stamp Duty and Property Registration Charges in Delhi 2023It is compulsory for property buyers in the Capital to pay stamp duty in Delhi during property regi... Read More »
Income Tax Return – Documents, Forms and How to File ITR Online AY 2023-24In India, it is mandatory for all taxpayers who earn more than the basic tax exemption limit to fil... Read More »
What is Section 80CCD – Deductions for National Pension Scheme and Atal Pension YojanaThe Income Tax Act provides a number of deductions and tax benefits to taxpayers, so they can strat... Read More »
Tax on Dividend Income: Sources, Tax Rate and TDS on dividend incomeWhat are Dividends? Companies may raise funds for running their operations by selling equity. Th... Read More »
Section 112A of Income Tax Act: Taxation on Long-Term Capital GainsWhat is Section 112A? Section 112A of the Income Tax Act was announced in Budget 2018 to replace... Read More »
Section 206AB of Income Tax Act: Eligibility And TDS RateSection 206AB was introduced in the Finance Bill 2021 as a new provision pertaining to higher deduc... Read More »
What is a Credit Note in GST – Example, Format and StepsA GST Credit Note is mandatory for any GST-registered supplier of goods or services. As a supplier,... Read More »
Exemptions and Deductions Under Section 10 of Income Tax ActWhat Is Section 10 of the Income Tax Act? Section 10 of the Income Tax Act, 1961 provides tax-sa... Read More »
Section 57 of the Income-tax Act – Income from Other SourcesIt is quite likely that many entities - individuals as well as businesses - have multiple sources o... Read More »