Retirement Planner Template

Overview

The Retirement Planner template from Tiller Money Labs 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

  1. Open either your Tiller spreadsheet or any spreadsheet.
  2. Install and open the Tiller Money Labs add-on
  3. Choose “Add a Solution” in the sidebar
  4. Click on “Retirement Planner
  5. 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 Money Labs 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 Money Labs 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.

4 Likes

Jon,

I think you nailed it. I think you have a nice and awesome template here. The flow is simple. It is real easy to follow. It is really intuitive. It can be used with or without cash flow. That switch at the top of column S is slick. Nice language in the instructions about how to create a blended rate to handle that issue. Need to mention to users not to delete any rows or columns. Looks like you use 30 years but I guess I see that hidden section which allows that to be modified, is that right? What if you what/need more rows between 25-49 and rows 52-79. You need more because I suspect users will demand more than you have provided here today.

I suspect this will be more than enough to satisfy most users. Of course there will users like me who will want more. You have already done a good job explaining this in the instructions but you might be more aggressive and say…while this is version 1 and there may be future versions, we have gone about as far as we can and if you do not like, tough.

I have used it enough this evening to know it inside and out. I did not notice any bugs.

For me, I need to stay with and use the software that I currently use, as we have already discussed. It has so much more horsepower that Tiller cannot ever match, but that is okay. Tiller has limitations based on what they have to work with, as we previously discussed. Based on what you have to work with, this is a solid template and Tiller has absolutely nothing to be ashamed of. Really, it is something Tiller should be proud of. I am proud of it. I am sure you are proud of it too and you should be.

Please let me know if I can be of further assistance.

Thanks,

Blake

1 Like

@Blake,
Thanks for your detailed feedback here and for all the extra time and help you provided during the development to make this template better. Your input helped us cross the finish line on publishing the sheet.

I’ll add a mention about not deleting rows or columns.

Yes, the hidden part lets you customize the number of years if you are not using the Cash Flow sheet. If you are using the Cash Flow sheet, the number of years is set there and then matched on the Retirement Planner sheet.

You raise a good point about what if more assets are needed. I tried to provide enough for most users. Just adding rows won’t work unless some formulas are added. I added some instructions on how to get more rows for assets without breaking the sheet.

I’m glad you put it to the test and didn’t find any bugs.

This is the first version of it and if we get more feedback, we will try to keep making it better.
But it can’t really compete with some other 3rd party products that have their entire focus built around a retirement planner. There is also a limit (a high limit but still a limit) on what can be achieved using just formulas in a Google Sheet compared to a backend fully scriptable website.

Thanks again,
Jon

3 Likes