The Retirement Planner Template (Excel) attempts to provide close to full parity with the original Google Sheets version. Thank you, @jono, for the amazing original work, and for permission to migrate to Excel and share this version with the community.
The corresponding Cash Flow Template has also been migrated, and in my opinion these should be used together. The following overview has been copied and pasted from the original post:
The sheet makes many compounding assumptions about the future— listed in the Google Sheets post— and thereby should only be used as a directional model.
The Retirement Planner solution works well in combination with the Cash Flow Forecast sheet. Any expected future income or expenses (e.g. college expenses for a child, a home or car purchase, etc) should first be entered on the Cash Flow Forecast sheet.
The Retirement Planner uses your cash flow information from the Cash Flow Forecast sheet then adds Investments totals, Investment Growth, Withdrawal rates, and Transfers/Adjustments to the forecast.
Benefits of the Retirement Planner include:
- Quick & easy setup (only a handful of inputs are required)
- Presents forecast by year in both chart and table formats
- Projects when you will reach a your Portfolio Goal
- Responsive scenario planning with instantaneous updates to plan changes
- Renders multiple growth rate scenarios
- Integrates with your existing account balances and Cash Flow Forecast
- Can model changing risk profile
- Download the Retirement Planner (Excel) Template
- Follow this guide to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
Please refer to the original Google Sheets post for detailed usage instructions. When I first started using the sheet, I found it very intuitive. Note that some cell references have slightly change from the Google Sheets version.
You can enhance the chart interactivity by making it responsive to the number of years you enter in the Cash Flow Forecast sheet or manually in this sheet with just a few clicks, or a quick macro.
- Right-click on the chart and select “Select Data”
- Under Legend Entries (Series), select each series and click “Edit” and change the formulas:
- Investments End of Year →
- Upper Range Growth →
- Lower Range Growth →
- Portfolio Goal →
- Investments End of Year →
- In the Horizontal (Category) Axis Labels, click “Edit” and change the formula to
Alternative automated configuration:
If you’re comfortable with macros, you can also copy this simple macro as a new module into your workbook, run it once, and then remove the module it so you can continue saving your workbook as an .xlsx:
Sub UpdateCharts() ' Update Retirement Planner chart With ThisWorkbook.Sheets("Retirement Planner").ChartObjects(1).Chart .SeriesCollection("Investments End of Year").Values = "='Retirement Planner'!RetirementPlannerChartInvestments" .SeriesCollection("Upper Range Growth").Values = "='Retirement Planner'!RetirementPlannerChartUpperRange" .SeriesCollection("Lower Range Growth").Values = "='Retirement Planner'!RetirementPlannerLowerRange" .SeriesCollection("Portfolio Goal").Values = "='Retirement Planner'!RetirementPlannerChartGoal" .Axes(xlCategory).CategoryNames = "='Retirement Planner'!RetirementPlannerChartYears" End With End Sub
Explanation: The sheet has “Named Ranges” for the series data, which point to dynamic arrays that respond to the number of years in the Cash Flow Forecast Sheet or this sheet. However, when copying or moving a sheet, Excel removes Named Ranges from a chart’s definition, replacing them with absolute ranges. It’s an unfortunate limitation of Excel that requires a few manual steps each time you copy the sheet.
I originally migrated this for my own use, and I’m just trying to match the Google Sheets version of this template. Please let me know if you have any issues with installation or missing functionality.