The Retirement Planner template for Google Sheets estimates the total value of your investments into the future. You can experiment with different growth rate scenarios and project outcomes in real time.
The sheet makes many compounding assumptions about the future— listed below— and thereby should only be used as a directional model.
The Retirement Planner for Google Sheets 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
Retirement planning is a very complex & personal task with individuals each bringing a unique set of goals, inputs, and expectations. We have tried to build a solution that leverages your existing Tiller information and provides best-effort future retirement guidance. We’ve made the sheet flexible so you can add your own basic adjustments and options. At the same time, we realize this sheet might not provide all the features some may want. We welcome your feedback in the comments below.
Installation
- Open either your Tiller spreadsheet or any spreadsheet.
- Install and open the Tiller Community Solutions add-on
- Open the add-on and choose “Retirement” from the tags dropdown on the Explore tab
- Click on “Retirement Planner”
- Choose “Add to Spreadsheet”
If you are now already using the Cash Flow Forecast sheet, it is recommended to install and setup that sheet first. Use the same instructions above but in step 5, Click on “Cash Flow Forecast”.
If you don’t want to use the Cash Flow Forecast, see the Questions section below.
How To Use It
The sheet requires you to enter some information in the green background cells. To avoid damaging the sheet, don’t add valves to any cell unless it has a green background. (If you damage the solution, you can always re-install it using the Tiller Community Solutions add-on.)
It is also VERY IMPORTANT not to add or delete any rows or columns of this sheet. If you add or delete columns by mistake, you should re-install a new sheet from the Tiller Community Solutions add-on.
You will need to enter the following information…
Birth Years
Enter your birth year in C5. This allows the chart to include your age in each row. (This is optional and can be left blank.)
If you want to add your spouse’s birth year, enter that in C6. (This is optional and can be left blank.)
Investment Yearly Growth Rates
For the main projection, enter a “blended” growth rate for your total investments. If you have multiple investments with different growth rates, you can make your own spreadsheet with weighted values and different rates to get a single blended rate.
This template allows you to change the blended growth rate in future years. For example, you might want to transition to more conservative investments in the future. You can enter starting years in A11 and below and a new rate in C11 and below if you want to make future rate changes. The template allows space for five blended growth rates and their start years.
You can also add an Upper Range and Lower Range Growth Rate in C17 & C18. The results will be displayed on the chart. Calculations for the growth rates appear in the hidden part of the sheet to the right of column S. Any future year (blended) growth rates set in rows 11 to 14 will not apply to these alternate growth scenarios (i.e. the upper & lower growth rates are fixed over time).
What numbers should you use for these growth rates? Unfortunately, predicting the future can be challenging. You will have to do your own research to figure out what numbers you want to use. Of course, you can try out different growth rates and see what the impact is.
According to NerdWallet, the average stock market yearly return is 10% before inflation. Bonds return less, depending on the type of bonds you own. Realize that whatever rate you pick, it’s not a guarantee.
The investment growth rate for the current year will be pro-rated based on the percentage of how many days are left in the year.
Withdrawal Rate
You can set optionally set a withdrawal rate starting in whatever year you want in C21 and C22. If you don’t want to set a withdrawal rate, use 0.0% in C21.
Investment Assets
There are two places where you can enter your investment assets.
Starting in row 25, columns A thru C, there is a section for your Investment assets. Any Asset investments you have on your Tiller Accounts sheet will be available as a dropdown option. The sheet will automatically pull in the current value of the asset in column C.
Starting in row 52, you can also add any other Asset you want. You need to manually enter the current value of these assets in column C.
For both types of Assets, you need to select the Active checkbox in Column A if you want the sheet to include the value as part of your Current Total Investments.
Portfolio Goal
You can set a portfolio goal value above the chart on the left. The sheet will project (if and) when your investment total will reached your goal.
Investment Adjustments
There is a grouped/hidden section of the sheet where you can enter Investment Adjustments.
To access this section, click on the plus sign above column S. To hide the section after it has been opened, click on the minus sign above column S.
In this section, you can create adjustments with a start and end year, a name and an amount. When the Active checkbox is selected, the adjustment will be used in the sheet calculations.
This section was designed for Transfer type adjustments that might move in or out of your Investment portfolio. If you have any future life events where income or expense values will be affected, we recommend you make those adjustments in the Cash Flow Forecast sheet in the Live Events section.
Yearly Investment Formula
For each year, the sheet calculates the Investment Value at the beginning of the year.
Based the Investment Growth Rate settings, it adds a yearly investment gain.
It also subtracts a withdrawal based on the withdrawal rate entered.
It pulls the yearly Cash Flow Forecast from the Cash Flow Forecast sheet and adds or subtracts this value.
Finally, it makes any Investment Adjustments listed in that section.
The result is a yearly net gain or loss. This gain or loss is added to the investment value at the start of the year to determine the investment value at the end of the year.
Chart Features
If you put your cursor over the lines in the chart section, you can see the portfolio values for the 3 different growth rate scenarios for the year where you cursor is.
The orange line shows your portfolio goal entered above the chart.
Assumptions
The calculations on this sheet are not like your Transactions sheet which lists actual transactions. All these numbers are future projections and estimates. There are also based on assumptions including:
- The set investment growth rate is the exact amount your investment will gain each year. This is highly unlikely to be true for every year. But in the long-run, these values can be estimated.
- This sheet doesn’t take taxes into account. There are many rates rates to consider and different type of investments (IRA’s, Roth IRA, non-retirement investments with different cost basis, etc) have different tax treatments. You can estimate tax expenses as expenses on the Cash Flow Forecast sheet and/or adjust the blended growth rate to account for taxes.
- The sheet assumes that any extra gain you have at the end of the year will be reinvested back into your investment total. Similarly, any loss you have at the end of the year will reduce the value of your investments.
Questions and Help
Visit the Tiller Community thread dedicated to this template for any questions, feedback, or help.
