A retirement number on its own is hard to trust. This free Excel sheet shows its working: what your expenses will be when you retire, the corpus that pays for them until the age you choose, the monthly SIP that closes the gap from today, and a year-by-year table where the balance runs down to zero at the end.
⬇ Download: retirement calculator in Excel (.xlsx, free)What's in the sheet
- Inputs: today's monthly expenses, your age, retirement age, the age the money should last to, inflation, returns before and after retirement, and what you have saved.
- Expenses at retirement, the corpus needed, your savings grown, the gap and the monthly SIP.
- A drawdown table from retirement: the balance, the year's withdrawal (rising with inflation) and what is left.
The formulas, with an example
Expenses at retirement = today's expenses × (1 + inflation)^years to go. The corpus is what can pay those expenses, rising with inflation every year, while it earns the after-retirement return: expenses × 12 × [1 − (1 + real return)^−years] ÷ real return, where real return = (1 + return) ÷ (1 + inflation) − 1. The SIP is =PMT(return/12, months, 0, −gap, 1).
Example: ₹50,000 a month today, age 30, retiring at 60, money to last to 85, 6% inflation, 11% before and 7% after retirement. Monthly expenses at 60 would be about ₹2.87 lakh, and the corpus needed about ₹7.64 crore. ₹5 lakh already saved grows to about ₹1.14 crore, leaving a gap a SIP of about ₹23,000 a month would fill. The numbers are large because 30 years of 6% inflation multiplies prices almost six times.
Prefer it online? Retirement calculatorRead: how much you need to retire in IndiaFrequently asked questions
- How do I calculate a retirement corpus in Excel?
- Find expenses at retirement with =expenses*(1+inflation)^years, then the corpus with =expenses*12*(1-(1+real)^-years_in_retirement)/real, where real = (1+return)/(1+inflation)-1. The free sheet on this page has it set up with a drawdown check.
- How much SIP do I need for retirement?
- Use =PMT(return/12, months to retirement, 0, -gap, 1), where the gap is the corpus needed minus your current savings grown to retirement.
- What inflation should I assume for retirement planning in India?
- Many planners use 6%. Try 5%, 6% and 7% in the sheet: the corpus changes a lot, which is why starting early matters more than the exact rate.
- Why is the retirement corpus so large?
- Because inflation compounds over decades: at 6% a year, prices rise almost six times in 30 years, and the money has to last 20 to 25 years after you stop working.