🏆 Retirement Planner Template (Excel)

Microsoft Excel Show & Tell
1

Overview

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.

image
image1346Ă—963 79.8 KB

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

  1. Download the Retirement Planner (Excel) Template
  2. 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:

  1. Right-click on the chart and select “Select Data”
  2. 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
  3. 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 
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.

Notes

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.

2

Great work. @bdunks !
Thanks so much for doing this so Excel users can get the benefits of these sheets.

Jon

3

Dear bdunks,

thanks so much for creating the Excel version of the Retirement Planner. You indicate that it should be used with the Cash Flow Planner, could you please provide the link to the Excel version of that template?
Thanks so much!
Lorraine

4

Hi Lorraine - It looks like it took a bit of time for both posts to clear; however, the cash flow template is also now live.

5

WOW! This is impressive. Great job :grin:

6

Thanks for porting the retirement planner to Excel. Seems it not working for me,
Do you have advice on how to fix it?

Retirement error
Retirement error767Ă—292 88.4 KB

7

Hi @adekunledauda - This is related to the error you’ve posted in the Cash Flow Forecast thread. The worksheet is finding the Cash Flow Forecast sheet, so it is attempting to use it; however, there are value errors in that sheet, which are cascading to this one.

Let’s troubleshoot and resolve the Cash Flow Forecast sheet; hopefully, it will solve this one as well.

8

Wow! … Just wow!

First off, @bdunks, thanks for taking the initiative to port this popular template to Excel.

Second, I’m really impressed with the fidelity to the Sheets version. Not only the UX, but I added test data and it all lined up to the decimal point to values output from the Sheets version.

Finally, you’ve done a great job with the build quality, using spill ranges and LAMBDA functions throughout the calculations. The interoperablity with your Cash Flow Forecast](Cash Flow Forecast (Excel)) is also welcome and well done.

:pray: I’ve got one really small change request for you… could you change the Learn & Discuss link to point to https://community.tillerhq.com/tag/tcs-retirement?

:trophy: If you’re familiar with the Tiller Builder Rewards Program , you know that we award a sliding scale of cash prizes based on:

  • Value of concept to the personal-finance community
  • Value & quality of Tiller integration
  • Uniqueness of concept vision
  • User experience & usability

As the Community’s first long-term planning template available in Excel, this solution with its create build quality is a homerun :baseball: and we are excited to award you $750 for building and documenting this Retirement Planner template-port to Excel.

Thank you @bdunks!

P.S. You might notice a similarly effusive award and recognition in the thread for your Cash Flow Forecast port for Excel.

1 Like
9

Hi Randy,

Thanks for the great feedback!

I appreciate the correction. I’ve made this update. Let me know if you have any other feedback.

Thanks!

10

@bdunks I have started to use your Excel version. Congrats again for making it user friendly for those who have used the Google version. Now I note that in the Google version when you insert other opening balance investments it puts the values in at the start of the year. As a result the projected income will be calculated for the whole of that year. ie the start of the projection. In your version it does not go to the “start of the year” column but rather to investment adjustments. So no income calculated for the year. Could this be adjusted ? I tried a few things to workaround it but its not as clean as Google.

11

Hi Randy,

Thanks for the great feedback!

I agree this is a bit wonky.

A few things to keep in mind:

  1. If an absolute range for a chart extends into blank cells, it will render the x-axis for the number of cells selected in the data range, even if they are blank (i.e., unlike Google Sheets, it won’t dynamically shrink the x-axis to ignore the blank cells at the end of the range)
  2. Excel charts can’t reference dynamic arrays directly:
    2.1 Series Data → =K2# will error
  3. As a work around, Excel charts can reference named ranges, and named ranges can reference dynamic arrays.
    3.1 Series Data → CashFlowChartSeriesData → =K2# will wor, where CashFlowChartSeriesData is a named range.
  4. When you copy sheets, or move them to new workbooks, Excel updates the charts to delete references to the named ranges and replace them with absolute references
    4.1 If you’ve setup Step 2.1, the copied sheet will show: Series Data → =K$2$:K$31$, or whatever K2# referenced at that time.
    4.2 Additionally, it doesn’t fully re-render the chart, so things like the “Years” legend will show as index numbers (1, 2, 3…) instead of years (2023, 2024, 2025…)

To work around these limitations as best as possible, I’ve created the named ranges so they are ready to be applied; however, I did not set them in the template charts because they will be replaced and not re-rendered correctly.

You can get dynamic rendering following the Dynamic Charts instructions in the original post, where there are manual and macro-based options for setup.

I may be missing another really obvious way to work around this issue. If you’ve got other ideas, please let me know.

Great catch! I’ve fixed this.

Also updated, thank you!

I really appreciate your thoughtful feedback. If you have any ideas to make the charts more dynamic without requiring additional manual steps after installation, let me know, and I’ll make the updates.

Thanks!

12

Hi @henryb ,

Could you clarify what you mean by:

other opening balance investments

I can’t find this input to recreate the behavior and troubleshoot. Perhaps you could provide the exact cell or range reference?

Hope we can troubleshoot this and get it solved!

13

Certainly can @bdunks . So when you put values into “investment adjustments” the totals go to cell P25. It would be better (as in the google sheet) if they went to the beginning of the year. ie cell H25. That way the opening balance would get income for the year at the set rate. See screen shot in this link Cash Flow Forecast Support - Google Docs

14

Hi @henryb -

Based on your screenshot, it looks like you’re referencing the Retirement Planner sheet, not the Cash Flow Forecast sheet, which caused my initial confusion.

I see in your screenshot the starting balance is = $0. If the accounts you’re adding are in Tiller, make sure you’ve switched them to Active = True in columns A25+. If they’re not in Tiller, you could consider adding them as a manual account.

I’ve just installed a fresh Google Sheets Retirement Planner Sheet, and the behavior is the same as Excel.

  • The investment adjustments are added to P25+, even if its for the current year
  • H25 sums active values for C25:C only, without considerations for investment adjustments

Google Sheets screenshot:

image
image1766Ă—776 146 KB

I didn’t design the original sheet, but my conclusion is that the sheet does not make assumptions about when in the year the investment is applied, meaning it won’t be included in the starting balance for in-year adjustments.

My objective is parity with the original Google Sheets version, so we’ll likely keep as-is.

Maybe not the answer you’re hoping for, but I hope this is still a helpful clarification.

15

HI Again @bdunks

Yes thanks for all that clarity and apologies for any misleading references.

I appreciate that you are wanting to keep parity with Google Sheets and I would also want to see the same.

If you go back to me link again you will see the same “Investment Adjustments” included in Google Sheets screen shot I have now pasted in. You can see that in Google Sheets its really a true or false option and it goes into the start of the year. I respectively suggest your sheet sends it to an adjustment column and assumes end of year. Or am I missing something?

Thanks for your very prompt replies

16

Hi @henryb,

In the Excel version, you’re using the “Investment Adjustments” functionality. In the Google Sheet you’re using the “Other Accounts” functionality.

You can get the same behavior in Excel by adding those accounts to the “Other Accounts” table in the Excel file. You may need to scroll down – these start at A52, just like in the Google Sheets version.

17

OMG. I am sorry for taking you on a wild goose chase. I did not see it. Thanks again for your great work.

18

I love the Retirement Planner (RP), especially when coupled with the Cash Flow Forecast (CFF) template. I started with the RP and added the CFF but ended up with a few formula errors. Then merged the two templates into a single file and started to manually populate. I structured my income and expenses as separate groups based on period executed so that I could account for major life changes, this meant I couldn’t use the typical Tiller approach for Categories within an annual budget matrix. Essentially the format parallels a manual gantt style forecast previously developed. I can easily shed/adjust expenses as we downsize or one of us passes. I used a milestone YYYY code with the duplicate/parallel category for ease of tracking. EX: “Food: Groceries 2058” and “Food: Groceries 2063” as we have 5 years age difference. Once setup and functioning, I copied the Retirement Planner tabs with different names to portray different scenarios.
I use the base Tiller Foundation Template along with the Category Tracker in a different and interactive fully linked file to understand my MoM and YoY spend which supports the Cash Flow file basis.
Really happy Tiller and very smart enthusiasts are porting Google Sheets files to the Excel format. Thank you.

1 Like
19

@bdunks fantastic work – thank you!

I’m new to Tiller in the last couple of weeks migrating from a combination of Mint and my own worksheets for more detailed tracking and analysis including retirement planning. I decided to explore what was available from the community before attempting to reimplement my own cash flow and retirement planning worksheets. I’m thrilled with how easy it was to add the cash flow and retirement sheets to my Tiller worksheet and the completeness of the capabilities. Thank you, again!

There’s a fair bit I need to work through in terms of what is feasible to adjust and customize. There are some modifications I need to figure out how to make. For example, I have some asset values like stock options and RSUs for which I need to modify the asset value for it to be accurate.

20

@bdunks Hi again. Using your template which I love!! It gives me an idea of where I will be in 20 years …I am 69 now and retired so have to manage fund.
Now in terms of investment gain shown in Col J on Retirement planner I have put amounts in other accounts. However I cant get it to calculate investment gain for first full year. Is it taking a date other than 1 January. If so how do I set to 1 Jan? It seems fine for the subsequent years. Thanks