Planning for retirement can feel overwhelming—especially when your target is a hefty ₹6 crore corpus. Yet with a simple, well‑structured Excel sheet, you can map out exactly how much to save, where to invest, and how long it will take to reach that goal. By the end, you’ll have a fully functional Excel template—no advanced formulas required—that shows you exactly how to build a ₹6 crore retirement corpus, whether you’re 25 or 45 today.
1. Defining Your Goal & Timeline
Before opening Excel, clarify:
- Target Corpus: ₹6 crore in today’s rupees
- Time Horizon: e.g., retire at age 60; if you’re 30 now, you have 30 years
- Withdrawal Rate: A conservative 4% safe‑withdrawal rate implies ₹6 crore generates ₹24 lakh/year
These inputs set up your model: you need to accumulate ₹6 crore in 30 years (or whatever your timeframe is).
2. Laying Out the Excel Model
2.1. Sheet Structure
Create columns as follows:
| Column | Header | Description |
| A | Year | 1, 2, 3, …, N |
| B | Age | Starting age + Year – 1 |
| C | Starting Balance (₹) | Corpus at year‑start |
| D | Annual Contribution (₹) | Your annual savings |
| E | Return Rate (%) | Assumed blended rate |
| F | Interest Earned (₹) | = C × E |
| G | End Balance Before Contrib | = C + F |
| H | End Balance (₹) | = G + D |
2.2. Setting Up the First Row
- Year 1:
- Age = your current age (e.g., 30)
- Starting Balance = 0 (or existing retirement savings)
- Annual Contribution = your planned yearly investment (e.g., ₹2 lakh)
- Return Rate = a constant cell (we’ll discuss next)
- Age = your current age (e.g., 30)
- Formulas:
- Interest Earned (F2): =C2 * E$1 (where E1 holds the return rate)
- End Balance Before Contrib (G2): =C2 + F2
- End Balance (H2): =G2 + D2
- Interest Earned (F2): =C2 * E$1 (where E1 holds the return rate)
- Drag Down: Copy formulas down for N rows (equal to your time horizon).
3. Choosing Realistic Return Assumptions
Your blended return drives the outcome. In India today:
- Equities: Nifty 50 delivered a 10‑year CAGR of 11.7%.
- EPF: Employees’ Provident Fund offers 8.25% for FY 2024–25 .
- PPF: Public Provident Fund is at 7.1% p.a. through June 2025.
If you plan a 60/20/20 split (Equity/EPF/PPF), your blended return is:
(0.6×11.7%)+(0.2×8.25%)+(0.2×7.1%)≈10.4%
Enter 10.4% in cell E1.
4. Running Scenarios & Sensitivity
4.1. Base Case
- Contribution: ₹2 lakh/year
- Return: 10.4%
- Years: 30
Check the final End Balance in row 30. If it’s below ₹6 crore, you know either to increase contributions, extend the horizon, or boost return assumptions.
4.2. Conservative vs. Aggressive
Create a small table next to your model:
| Scenario | Return (%) | Contribution (₹) |
| Conservative | 8.5 | 2,00,000 |
| Base Case | 10.4 | 2,00,000 |
| Aggressive | 12.0 | 3,00,000 |
Link these inputs to your model with dropdowns (Data Validation) so you can instantly recalculate outcomes for each scenario.
5. Optimizing Contributions & Allocation
5.1. Goal Seek for Contributions
Use Excel’s Goal Seek under the Data → What‑If Analysis menu:
- Set cell: Final End Balance (e.g., H31)
- To value: 60,000,000
- By changing cell: Annual Contribution (D2)
Excel tells you exactly how much you must invest yearly to hit ₹6 crore at your assumed return.
5.2. Allocation Adjustments
If the required contribution feels too high, you can test higher return mixes:
- Move from 60% equity to 70% equity and re‑calculate blended return.
- Re‑run Goal Seek to see lower required contributions.
6. Accounting for Inflation & Taxes
6.1. Inflation Adjustment
If you want a real ₹6 crore corpus (today’s value), adjust for 6% inflation:
- Use Excel’s FV function: =FV(inflationRate, years, 0, -60000000) gives the nominal target.
- Update your model’s “Target” to that inflated number.
6.2. Tax Impact
- Equity SIPs: Long‑term capital gains taxed at 10% above ₹1 lakh/year exemption.
- PPF & EPF: Tax‑free.
To approximate, reduce your equity return by 0.5–1% (e.g., from 11.7% to 11.0%) to factor in taxes and re‑run scenarios.
7. Visualizing Progress with Charts
- Balance Over Time:
- Select columns A (Year) and H (End Balance).
- Insert a line chart to see your corpus growth curve.
- Select columns A (Year) and H (End Balance).
- Contribution vs. Growth:
- Plot a stacked area chart with columns D (Contribution) and F (Interest Earned) to see how returns accelerate your wealth.
- Plot a stacked area chart with columns D (Contribution) and F (Interest Earned) to see how returns accelerate your wealth.
Charts help you stay motivated as you watch steady growth year by year.
8. Tips for Staying on Track
- Automate Monthly: Convert annual contributions to monthly (=annual/12) and set up SIPs/auto‑debits.
- Quarterly Reviews: Update your actual portfolio returns vs. assumed in E1; adjust as needed.
- Buffer for Market Dips: If markets underperform for a year or two, you can temporarily increase contributions to catch up.
- Use Named Ranges: Name your key inputs (ReturnRate, Contribution) so formulas stay clear and easy to update.
- Backup Your Sheet: Keep a cloud copy and a version history so you can revisit past scenarios.
Conclusion
With this Excel sheet, you hold a powerful tool: plug in your age, timeline, contribution capacity, and return assumptions, and watch your projected corpus unfold. By adjusting variables and running scenarios, you can precisely chart the path to a ₹6 crore retirement corpus—no guesswork, just clear numbers. Start building your model today, automate your savings, and let the spreadsheet guide you toward a financially secure retirement.
Source : thepumumedia.com