Sold shares or mutual fund units this year, maybe IPO shares on listing day? The tax depends on how long you held each one, and losses on some sales can cut the tax on others. This free Excel sheet takes a list of your sales, decides short or long term from the dates, and works out the year's tax.
⬇ Download: capital gains calculator in Excel (.xlsx, free)The rules for listed shares and equity funds
- Held 12 months or less: short-term, taxed at 20%.
- Held more than 12 months: long-term, taxed at 12.5% on gains above ₹1,25,000 in the year.
- 4% cess on top in both cases.
- A short-term loss can reduce long-term gains, but a long-term loss can only reduce long-term gains.
How the sheet works
Each row is one sale: the buy date, sale date, cost and sale value, both after brokerage and charges. The sheet checks whether the sale date is more than 12 months after the buy date with =IF(sold>EDATE(bought,12),"Long","Short"), adds up each kind with SUMIF, sets off a net short-term loss, and applies the rates.
Example in the sheet: IPO shares bought for ₹15,000 and sold six weeks later for ₹18,000 (₹3,000 short-term gain, ₹600 tax), and a fund held for two years that gained ₹2,20,000 (₹95,000 taxable above the ₹1.25 lakh limit, ₹11,875 tax). With cess the year's tax is ₹12,974.
Prefer it online? Capital gains tax calculatorRead: capital gains tax on stocks and mutual fundsFrequently asked questions
- How do I calculate capital gains in Excel?
- Gain = sale value − purchase cost (both after charges). Use =IF(sale date>EDATE(buy date,12),"Long","Short") for the holding period, then 20% on short-term gains and 12.5% on long-term gains above ₹1.25 lakh, plus 4% cess.
- How is tax calculated on IPO shares sold on listing day?
- As a short-term capital gain at 20% plus cess, on the difference between the sale value and your allotment cost, after charges.
- Can I set off a loss on shares against gains?
- A short-term loss can be set off against both short- and long-term gains; a long-term loss only against long-term gains. Unused losses carry forward for 8 years if you file on time.
- Does this apply to debt funds or property?
- No. Debt funds, property, gold and unlisted shares follow different rules. This sheet is for listed shares and equity mutual funds.