Income tax formula for use in spreadsheets
Say you need to calculate net (after-tax) income from gross (pre-tax) income or vice-versa. You can use an online calculator, but those can’t be integrated into a spreadsheet, and won’t automatically get recalculated when the input changes. For that, I’ve developed the below formulas:
net = gross * .688 + 26883.3333333
gross = net / .688 - 39074.6124031
Paste this into your spreadsheet.
Assumptions
These formulas assume that:
You’re Indian
You’re an individual or LLP
You’re not a senior citizen.
You fall under the 30% slab.
Your income is not so high as to trigger a surcharge.
Professional tax is charged at a flat fee of ₹5K/month.
Derivation
If you’re happy with the above formula and assumptions, you can stop reading.
If you want to know how the above formulas were derived, read on:
Step 1
We’ll calculate tax in two stages: first, we’ll calculate tax for someone just at the beginning of the 30% slab1:
How was column B calculated? Let me show you the formulas2:
Step 2
Now we’ll calculate the extra tax incurred beyond the beginning of the 30% slab.
tax = 161000 + (gross - 1550000) * .312
(where 1550000 = 15 lakh + 50K std deduction)
= gross * .312 - 322600
net = gross - tax
= gross - (gross * .312 - 322600)
= gross - gross * .312 + 322600
net = gross * .688 + 322600Step 3: Convert to monthly
Till now, both net and gross have been annual. Let’s convert them to monthly:
net * 12 = (gross * 12) * .688 + 322600
net * 12 = gross * 12 * .688 + 322600
net = gross * .688 + 26883.3333333Reversing:
gross * .688 + 26883.3333333 = net
gross * .688 = net - 26883.3333333
gross = (net - 26883.3333333) / .688
gross = net / .688 - 39074.6124031Taking a step back from all these details, the two formulas at the top of this blog post can be copy-pasted into a spreadsheet.
In other words, he has filled up the 20% slab. The last rupee he has earned was taxed at 20%, and the next rupee will be taxed at 30%.
Considering the 4% health and education cess.