Lumpsum Calculator Excel Sheet: Free Download, Year by Year

A lumpsum calculator gives you one number at the end. This free Excel sheet shows the value at the end of every year, the tax when you sell, and what the money is really worth after inflation, which is the number a goal should be built on.

What's in the sheet

• Your inputs: amount invested once, expected return, years, inflation, and an optional goal amount.
• The value at the end, the gain, the tax if you sell it all, and the value after that tax.
• The same value in today's money, and your return after inflation.
• How much you would need to invest today to reach your goal.
• A year-by-year table for up to 40 years.

The formula

Value = amount × (1 + yearly return)^years. In Excel: =B5*(1+B6/100)^B7, or =FV(rate, years, 0, -amount). ₹1 lakh at an assumed 12% a year for 10 years becomes about ₹3,10,585.

Then the two numbers most calculators leave out

Tax when you sell: for an equity fund held over 12 months, 12.5% plus 4% cess on gains above ₹1.25 lakh in the financial year. On that ₹2,10,585 gain it is about ₹11,126, leaving about ₹2,99,459. Sell within 12 months and it is 20% plus cess on the whole gain: ₹5 lakh that grows to ₹5.6 lakh in a year pays about ₹12,480. Debt-fund gains are taxed at your slab.

Inflation: at 6% a year, ₹2,99,459 ten years from now buys what about ₹1,67,216 buys today. A 12% return with 6% inflation is a real return of about 5.66% a year.

Working backwards: to have ₹10 lakh in 10 years at an assumed 12%, the sheet says invest about ₹3,21,973 today, before tax. The return is an assumption; equity funds can return far less, or lose money, over shorter periods.