The Retirement Planner Template (Excel) attempts to provide close to full parity with the original Google Sheets version.
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
Installation
- 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.
Usage
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.
Excel Specific Instructions
Dynamic Charts
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.
Manual configuration:
- 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 →
='Retirement Planner'!RetirementPlannerChartInvestments - Upper Range Growth →
='Retirement Planner'!RetirementPlannerChartUpperRange - Lower Range Growth →
='Retirement Planner'!RetirementPlannerLowerRange - Portfolio Goal →
='Retirement Planner'!RetirementPlannerLowerRange
- Investments End of Year →
- In the Horizontal (Category) Axis Labels, click “Edit” and change the formula to
='Retirement Planner'!RetirementPlannerChartYears
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: VBA Macro
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.
Questions and Help
Visit the Tiller Community thread dedicated to this template for any questions, feedback, or help.
