I have a question about the Cash Flow Forecast template from Tiller Money Labs. The incomes I put under “Life Events” are not applied to my forecast. Please help thanks!
Hi @roger.chu91 ,
Did you add or delete any rows or columns to your sheet?
Or put any data in the white cells? Only green background cells should be editable.
I’m unable to duplicate what you are seeing.
Maybe try re-installing the sheet?
Jon
Yes I inserted rows in Life Events and I think that broke it. I fixed it by restoring the sheet. Thanks!
Follow up questions.
When I put Income Budget Change / Year to 5%, it’s showing that my Yearly Cash Flow is becoming more negative. Shouldn’t it become more positive?
And when I put Expense Budget Change / Year to 3%, my Yearly Cash Flow doesn’t change?
Your image shows negative cash flow. Please see the language below which comes from the link below. Read this very closely. This template pulls amounts from the budget columns in your categories sheet. Modify those budget numbers and then those results will flow over.
Cheers,
Blake
The sheet works best if you have a Tiller Categories sheet with monthly Budgets in place for the current year.
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.
2 Likes
@Blake Hey thanks for your input but I don’t think it addresses my questions though?
Hi @roger.chu91 ,
Thanks for alerting us to this issue.
I believe there is an error in the formula in hidden cell R2.
There is a reference to E5 in the POW() function that should be changed to E6.
New formula in R2:
=IFNA(ARRAYFORMULA(IF(ISNUMBER(L2:L),C12*POW(1+E6,L2:L-AF10) ,IFERROR(1/0))),IFERROR(1/0))
Give that a try and see if it makes more sense.
We will update the template shortly.
Jon
1 Like
Thank you so much! This is too advance for me so I’m just gonna wait for the updated template lol
@roger.chu91
The template has been update to version 0.91.
You can update your sheet using the Tiller Labs add on.
2 Likes
6 posts were split to a new topic: Cas Flow Forecast “No Data” Error
Sorry, I am just lost on how this works. When i enter anything coming from the category sheet, (income) it seems to have the opposite effect. When I enter a manual Amount/Year and life events it seems to work as expected but I do not trust I am using it right now…
I have simplified an example of this. Why does bringing in your category as a life event REDUCE your “Adjusted Budget”? What is the point if using categories? I had assumed I would indicate "i expect this salary from this start to end years, and could enter another salary from an alternate job for a few other years but when i do this it basically goes negative no matter what.
Hi @Rocketghost ,
For Life Events, when you enter an Event/Category, you should also specify the Amount/Year that you expect for the Start through End period. In the example you provided for “Income - MWC Dist.” you have the start year as 2024 and the end year as 2030. The Cash Flow worksheet will automatically pull the 2024 Budget amount that you currently have specified for that category, however, you must enter an Amount/Year override. The value of $12,000 is shown as a convenient reference for you based on what you currently have in your Categories worksheet, and the intent is for you to specify a new value. Since you did not specify an Amount/Year, the calculation understands that for the period of 2024 through 2030 the “Income - MWC Dist.” is equal to zero.
For income, the amount should be positive and for expenses it should be a negative amount.
Hope this helps.
AHB
With this info i played with it a bit after simplifying it with a test sheet. I think I got it - basically anything you add into a life event overrides anything else coming from the category/budget sheet. I was totally under the wrong idea initially. Thanks. Also if you are not careful with your years, the “budget” aspect takes over again to populate the subsequent years. The only thing I am still wondering about is the “Change year” field. Is the purpose of that to simply to override what is in the initial settings field?
@Rocketghost are you referring to the Change / Year field in the table/config area? If so I believe that is supposed to be the % change growth you expect each year for that account as described here: Docs: Cash Flow Forecast
Am I using this right?? I am trying to account for temporary spends like daycare or mortgage but they don’t seem to increase my Cash Flow projections, they actually decrease them by a few cents.
Also the income thing, when checked, causes my Cash Flow projections to nose dive. Leaving them unchecked but not ideal - I’d love to account for retirement in the Cash Flow and then Retirement sheet.
Hi @Jamie.D ,
There might be a few things going on here.
Let’s start from the top:
- College for children: Are you expecting to pay $30K per year? If yes, then this is correct.
- Mortgage: It looks like after the mortgage refinance that you expect to happen in 2031 for a 20-year mortgage, you plan to be done with payments in 2050. For this to be accurately reflected, you need to enter a new life event with a start date of 2051 through end of life (say 2100) with category name Mortgage and amount per year set at zero (0), otherwise the cash flow sheet will continue to use whatever amount you have in your categories budget for the mortgage category for dates after the refi-payoff in 2050.
- For New Car, do you intend for that to be an annual payment of $14K for 7 years ($98K)?
- Child Care: Same as with Mortgage, if you intend for childcare expenses to stop after 2030, you need to enter a new life event starting in 2031 with a zero amount through end of life.
- Car Payment: Do you already have this in your categories sheet with a monthly amount? If yes, then I would just change the value for December on the categories sheet to zero and on the cash flow sheet change the life event to be from 2026 through end of life with a zero amount.
- Student loans: I would modify the line to be starting in 2027 through end of life with a zero amount if you already have it on the categories worksheet with a monthly amount.
- College Savings: It appears that you are accounting for College Savings as an income, is that correct? Isn’t this an expense where you are making contributions to a college savings account? Also, if it is on your categories worksheet, then you don’t need to enter it here, but rather only need to enter the year when contributions stop through end of life with a zero amount.
- For the two Paycheck entries, it looks like you are doing an override for cost-of-living, so those are correct. What you need to do next is add a new line for each with the year following the last paycheck as start date and end of life as the end date with zero amount.
These tips are how I have my cash flow worksheet and life events set up and it is working for me. Others may have a different approach. I hope this helps.
PS: If you are also using the Retirement Planner worksheet, I have found that I need to adjust the Withdrawal Rate on that worksheet to avoid double-counting items coming from the Cash Flow worksheet. In my case, I just set the withdrawal rate to 0% on the Retirement Worksheet.
Hope this all makes sense and helps you out.
AHB
Really appreciate the reply and insights/tips! I’ve adjusted all that you recommended but here’s where I run into an issue: Duplicated Income.
I would assume that, once I turn on the retirement Life Events for both my husband and I, that the “Unadjusted Income” and “Income Duplicated” would be the same to, in essence, cancel each other out.
What is actually happening is the Income Duplicated column grows exponentially each year so that it looks as if I’m spending a million dollars the year my husband retires (line 47 on the Life Events table).
I’ve archived and re-downloaded the spreadsheet 3 or 4 times now and get the same thing everytime.
Here’s the equation at the top of my Income Duplicated column: =MAP(L2:L, LAMBDA(YEAR,
IF(
ISBLANK(YEAR),
IFERROR(1/0),
SUMPRODUCT(
(YEAR >= VALUE($A$17:$A)) *
(YEAR <= VALUE($B$17:$B)) *
($C$17:$C) *
IFERROR(MATCH(D$17:D, $Z$2:$Z, 0), 0) *
(X$17:X = “Income”) *
IFERROR(VLOOKUP($D$17:$D, { $Z$2:$Z, $AC$2:$AC }, 2, FALSE), 0) *
POW(1+$E$5, YEAR - $AF$10)
)
)
))
Hi @Jamie.D ,
It is super confusing indeed. I have a few more pointers for you that may help shed some more light on it. I am by no means an expert on this, so maybe others can chime in as well.
In your latest screenshot it does not show the paycheck entries that you had before. Do those remain unchanged? You originally had them both from 2025 through 2060 with a 5% annual increase. In your latest screenshot you show retirement for one starting in 2054 and the other in 2056. If those are the retirement dates, then you should update the paycheck rows to reflect 2025 through 2054 for one and 2025 through 2055 for the other.
If we take one of the $71,146 income from 2025 through 2054, by the year 2055 that income would be $307,489 annually.
That same income is also included in your Categories worksheet with your default growth rate of 3% (filled in at the top of your cash flow worksheet) and that would grow annually as well and by 2055 would be $172,583.
The reason the duplicated income grows so much is because the duplicated income column will therefore include both those amounts for each of you starting in 2057 when both are retired.
What I would recommend trying next is the following steps:
- Turn off (unchecked) the two paycheck override rows for now.
- Leave the two rows to stop the paychecks turned on (checked).
- Review the cash flow results and see if it follows your expectations, considering that at 3% annual growth, the paychecks would be $172,583 for one in 2055 and $210,753 for the other in 2057.
- Once you have done the above steps, then consider enabling (checking) the paycheck override rows. You should also consider whether it is realistic to see an annual 5% increase over such a long period of time. My recommendation would be to keep the default growth rate and not use the override. In the years where the increase is indeed 5%, it would mean it is icing on the cake.
I would also recommend that you install the Retirement Planner worksheet and fill in the accounts to track them so that you can get a picture of the impact of your cash flow settings on your retirement savings and income.
Keep me posted on how it goes.
AHB
I had removed the rows prior to my screenshot from yesterday because I thought it was pretty redundant. I added those back in to see what happened but it just seems like it’s adding noise to the spreadsheet so I left them off (unchecked) like you suggested and turned on (checked) the retirement Life Events.
There is something very strange happening the year after my husband retires in the projection (2057). When I retire (2054) the following years follow a very steady, expected Cash Flow decrease that correlates to our expenses staying the same but us losing my income. However, in 2057, the year after my husband retires, suddenly we jump from a -$156,024 Yearly Cash Flow the year prior to a -$1,493,125 a year later? I have no idea why it’s assuming that losing his income (which grew from 94,696 this year to ~$171,550 if we assume an annual growth rate of 2%) means we’re down 1.2 million?
Really appreciate your input here, any help is so welcome!
Hi @Jamie.D
It looks like there is something happening with income rather than expenses. In the year 2057, it shows that duplicated income is $1,599,186 which is than subtracted from the unadjusted income, causing the significant change in cash flow that year. When you look at your categories worksheet, are there any other income amounts (positive numbers) that may cause this?
You can look at columns Z, AA, and AC on the Cash Flow worksheet as well to see if there are any income numbers that you have not yet accounted for. (The columns might be hidden).
AHB