Overview
The Cash Flow Forecast template forecasts your cash flow many years into the future. This solution allows you to test out different income/expense scenarios and view the impact of those changes over the long term.
This sheet looks at the long-term picture. Think years, not months. If you are looking for something that looks at the more immediate future, consider the Projected Balances sheet.
Some of the benefits of using this sheet include:
- Easy to set up
- Baselines your cashflow with the current-year budget (other ranges can be used)
- Outputs both chart and tabular data
- Provides workspace to manually add life events (e.g. children going to college, retirement, etc) with starting/ending years, starting amounts, and growth rates
- Life events can be activated/deactivated to run scenarios and quickly see long-term impacts
- Ability to set growth rates for your core income/expenses and life events
Installation
- Open either your Tiller spreadsheet or any spreadsheet.
- Install the Tiller Community Solutions add-on
- Open the add-on and choose "Cash Flow” from the tags dropdown on the Explore tab
- Click on “Cash Flow Forecast”
- Choose “Add to Spreadsheet”
The sheet works best if you have a Tiller Categories sheet with monthly Budgets in place for the current year.
Currently, this sheet only incorporates income and expenses. We are planning a companion sheet that includes assets, so this cash flow data can underlay retirement planning or FIRE methodologies.
How To Use It
Like most Tiller sheets, only enter values in the light green cells.
Core Income and Expense Budgets
The Cash Flow Forecast sheet looks at the Categories sheet to establish your baseline cashflow from your existing income/expense budget. If you have monthly budget columns for the current year, those budget values will be annualized and used.
Income or expenses in categories that are hidden from reports are not included.
If you do not have budgets for the current year or want to use a different initial range, see the FAQ #1 below for how the initial Income and Expense range is determined and can be overridden.
This sheet doesn’t use any information from the Transactions or Balance History sheets. It only uses the amounts in your budget located in the Categories sheet.
Future Years
In cell A1, enter the number of years out into the future you want to forecast. The default is 30 years, but it can be adjusted.
Yearly Growth Rates
You can add a yearly growth rate for this income and expense in the Settings section. Enter the income growth in cell E5 and the expense growth in E6. These values can be used to model inflation or cost-of-living changes. If you don’t want a growth rate, set these cells to 0.0%. See FAQ #2 for more on setting the growth rate.
Since this sheet can look far into the future, it’s recommended that you enter a growth rate at least for your expenses.
The income and expense growth rates will be applied to the initial income and expense budget totals— unless a category name matches a life event name in which case the growth rate int the life event section is used.
Life Events
The most powerful part of the sheet is the “Life Events” section. Life events are adjustments to your future income or expenses. Perhaps you will need to pay for your child’s college for 4 years. Or you plan to make a big purchase. On the income side, you might receive a big lump sum payment. Or you might expect a pension distribution or social security payment.
For each life event, you can enter the start and end year, the name, a note, the amount, and a growth rate.
Dates are entered as years (e.g. “2020”) and they are the ranges are inclusive. If the start year is 2020 and the end year is 2022, the life event will be added to the years, 2020, 2021 and 2022.
Both a start AND end year are required for life events.
Income amounts should be a positive number. Expense amounts should be negative.
The growth rate is a percentage. If you don’t expect any growth, you can just use a 0.00% growth rate.
Each life event row contains a checkbox in the Active column. By checking the box, the event will be included in the forecast. This feature allows you to quickly run scenarios and review long-term impacts from various life events on your future cash flow.
Don’t delete any rows in this or other sections of this sheet. You might delete required formulas in the hidden part of the sheet. If you need to remove data, blank it out or overwrite it with other data.
Life Event Budget-category Overrides
If you give a life event a name that (exactly) matches an existing budget category, the life event amounts and growth rates will override the amount from that category in your existing budget from the start to end years.
Also, the event name will appear in bold (so you know it’s a category match). The initial budget for the category will also be displayed in column J.
If you override a category with a life event, when the event ends the amount will go to zero. In other words, the baseline budget won’t return once the life event ends. This makes it a great fit for an event like selling a house (e.g. overriding your Mortgage category) or retiring (overriding your Paycheck category).
Chart Options
In cell G4, you can select several different chart options:
- Running Cash Flow (the running total of all the prior yearly cash flows)
- Yearly Cash Flow (the net cash flow for a single year counting core budgets and life events)
- Unadjusted Income (the income using just the initial budgeted income plus the income growth rate)
- Life Events Income (income from Life Events only)
- Unadjusted Expenses (the expenses using the initial budgeted expenses plus the expenses growth rate)
- Live Events Expenses (expenses from Life Events only)
Frequently Asked Questions (FAQ)
What date range is used by default for the baseline budget? Can you change it?
If you have a January and December budget column for the current year, the solution will use the current year to determine the starting yearly income and expense.
If you don’t have that, the sheet will look at the earliest and latest monthly budget columns and use all the budgets over that range. Since you might not have exactly 12 months in this range, the income and expense totals will use an annualized amount from this budget range.
For example, if you have 24 months of budgets, it will divide the total by 2. If you have just 6 months of budgets, it will multiply the total by 2 to get an annualized amount,
If you want to use your own budget period for the initial range, enter the starting month and year in AF5 and AF6. For this date, use the 1st of the month. If you chose to revert back to an automatic range, replace your manually added month with the formula in the notes in these cells.
What should I select as my growth rate for the Core Budgets?
Here’s some information to help you with the expenses growth rate.
The inflation rate for US consumer prices has ranged from -0.4% to 13.5% over the last 40 years. But the average yearly rate is 3.5%.
The Federal Reserve currently targets 2% inflation in the long term.
Using 2.0% or 3.0% is a good starting point if you are not sure what to use.
For the income growth rate, you can use your best guess based on your history.
Developer Notes
There are a lot of calculations going on in the hidden part of the sheet. Open Columns L:AG to view them. You can also see a yearly chart of all the different components of the cash flow equation.
Note that column M values, which are also used in the line graph, are determined by the selector in cell G4. If you want to show more lines on the graph, you can change the settings in the chart to include some of the columns from O:V.
The formulas for most of those columns use an ARRAYFORMULA() in row 2. They use the POW() function to get the growth rate in future years.