Overview
The Retirement Planner template 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 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
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
Can the sheet be used without the Cash Flow Forecast?
Yes. If the Cash Flow Forecast sheet is not installed, the sheet will still work. It just won’t import your yearly Cash Flows in column N.
You can set the number of years to show in the hidden part of the sheet in cell AD2. The default is 30.
Why doesn’t the current year show the full investment gain based on the growth rate?
The first year investment gain is pro-rated based on the day of the year. For example, if the growth rate is 10%, on July 1st, it will use 5% to calculate the growth rate.
Where are the calculations for the Upper and Lower range growth rates?
Look at the hidden section of the sheet to the right of column S.
What if I have more Assets than fit in Rows 25 to 49 or more Other Accounts?
Try this:
- Under the View menu, select Show formulas.
- Select row 49 or 79, right click and select Insert 1 below.
- Notice that some of the formulas will be missing on the new row. So, fill down the formulas from the row above the gap to fill in the blank row. Do this both in the viewable and the hidden part of the sheet.
- When all the gaps are filled in, de-select the View menu Show formulas.
This should allow additional rows to list your assets.
Video Replay
Q&A
- How do you recommend modeling inflation so you can play with different inflation amounts and impacts?
Enter different “Expense Budget Change / Year” growth rates in the Cash Flow Forecast sheet in cell E6. That adjusts the inflation rate of your yearly expense budget. If you want to set different inflation rates for different Categories, set up Life Events for those categories and use the Change/Year column to set inflation rates for a category.
- Is the community badge checked by someone at Tiller to verify it doesn’t store user data?
Per the guidelines the solution should not store nor transmit user data in order to use the badge, but we don’t have tooling that automates any type of verification of this right now. Any solutions that we decided to feature in the add-on or gallery after being submitted would have more of an official verification process. Hopefully, that helps. What are the guidelines for using the Tiller Community Badge?
- I downloaded this solution, and contrary to Jono’s statement, the cash flow sheet is pre-populated with values I do not recognize
These are just examples of what you might include on the cash flow sheet. Feel free to clear those out and customize based on your situation.
- I’m semi-retired (52/coast fire) and the sheet seems to be intended for those fully employed and contributing to retirement. Could you talk about the sheet in this scenario?
The sheet should work for people fully employed, semi-retired, and fully retired. The Withdrawal Rate can be set to start at any year in the future. Your salary (whether fully employed or semi-retired) would get recorded based on the income amount listed in your Budget. If you want to pick a year that you decide to stop working and have no salary income, add a Life Event to the Cash Flow Forecast with the name of the category that matches your Salary category and make the amount/year zero and the change/year zero. This will remove your salary from the future cash flow. If you predict you will have just half your salary in a future year, you can set up a Life Event for that as well.
- Is the Other Accounts section a good place to enter annual savings goals per year to capture planned additions to investments? Example 2021 - $5,000, 2022 - $6,000.
The Other Accounts section doesn’t let you add an amount for future years. If an amount is listed there, it will be applied to the current value of your investments, not starting in a future year.
You might want to use the Investment Adjustments to model future planned additions to your investments, since that section allows you to set a future year. You might use the same start and end year if the amount is for just one year.
One problem with this is that you have to get the money to add to your savings from somewhere. Ideally, you would have excess Cash Flow generated by your model. And any excess Cash Flow gets automatically added to the investment value at the end of the year and beginning of the next year. You wouldn’t have to make an Investment Adjustment to add this excess Cash Flow.
- Please review that investment adj again, please. How did that window open?
Click the Plus sign above column S. To close it, click the Minus sign.
- Looks like Investment Adjustments might be the answer to my question.
Good. The Investment Adjustments section can be used for a wide variety of scenarios. In general, they will likely be used with Transfer transactions. Income and Expense changes should happen in the budgets in your Category sheet for the current year and in Life Events on the Cash Flow Forecast sheet for future years.
- I’m not really understanding why I might use investment adjustments. Seems like large expenses like college are covered by cash flow sheet. Can you give an example?
Yes, expenses should be handled by the cash flow sheet. See the answer above.
- Shouldnt “Investment Adjustment” also be made for you regular contributions to retirements accounts (ex. 19,500 + Employer match for each year)?
If you consider your regular contributions to retirement accounts as Transfer type, then yes, you might include them as Investment Adjustments.
- Can I make the sheet go out more years?
Use the number value in the Cash Flow Forecast sheet in cell A1 to adjust the number of future years in the Cash Flow Forecast and the Retirement Planner. Since the original sheet only goes down to row 79, if you add years that go below row 79 of the Retirement Planner, you will need to manually fill down all of row 79 (including the hidden part of the sheet) to fill down to your final year. Some of the table columns use formulas in each cell.
- In the yearly growth rate table can I add more years?
I believe this can work, but it hasn’t been fully tested. Pick a row like row 12 or 13 and insert a row below it. I did a small test and it seemed to work, but I can’t 100% guarantee it.
- Have you examined the other free retirement forecast tools out there? Does this sheet have any major differences?
Yes, we looked at a bunch of other forecast tools before designing this sheet. We also used our community for feedback. See this post: Retirement Planner / FIRE Sheet - Request for Feedback - #15 by adekunledauda
I believe some other tools were mentioned in that post.
We discovered there are many different approaches and financial models for people take to planning for retirement. We tried to build a solution that leveraged the data you already have in Tiller as well as making it flexible for many scenarios.
- Better example is how to add the annual IRA contribution
I’m not exactly clear on this question. But to record an annual IRA contribution, I would consider it a Transfer transaction from the funding account to the IRA account. This wouldn’t show up as Income or Expense. Prior IRA contributions would show up as part of the current IRA investment asset value. Future IRA contribution could probably be added using the Investment Adjustments section. There might be other ways to do it as well.
- If you were going to modify either the cash flow or retirement planner sheets, what future features do you think would be really cool to add? This is purely a hypothetical question that’s along the lines of brainstorming for future things to add.
Let us know what you think we should add. I thought a spreadsheet that helped manage and optimize with Investments should be withdrawn and in what order might make a useful addition. But solving the tax impact details might make it too complex.
- There are three rows that are green and blank in the investment yearly growth rate - implying that you could add more years in rows 13 through 15. You should make that area white.
You can add more years in the green rows in Row 13 and 14. But you don’t have to. Since you can add values, the cells are green.
You shouldn’t add values in Row 15, which is white. Values there won’t be picked up in the chart.
- Cash Flow Forecast is not getting pulled into my retirement planner sheet. Any ideas what I might have done wrong? I haven’t changed anything outside of the green cells. :\
I’d try re-loading both sheets from the Add-On. I haven’t seen this issue before.
- Unrelated to Retirement Planning, or not really specific to it…but is there a way you can add multiple accounts to Foundation and only make some of them active for transactions
There isn’t a way to pick which accounts feed transactions vs balances right now, but there is a feature request for this already you can vote for.