Cash Flow forecast not working correctly.
I am trying to account for retirement by entering a Life Event that matches my husband and I’s income Category, setting the Start year to the year we each will retire, the End year to the last year accounted for in the Cash Flow Forecast and then Amount / Year to O. When I do this, the sheet seems to calculate that my Yearly Cash Flow takes an exponential nose dive as if the minute my income stops my expenses double each year. I don’t understand this…
Hi @Jamie.D ,
For the line where you enter the life event, does it get automatically bolded when you enter the Event/Category name?
It should look like the image below:
Also, you mention that you are setting a start and end year for each of you. Does that mean that you are entering two life event lines, and do you have a separate category name for each of your incomes in the Event/Category column? If both of your incomes are categorized with the same name, i.e. “Paycheck”, then whichever date happens first would be the start for both of you.
Hope this helps. If not, it might help for you to include a screenshot of just the life event lines (exclude the budget column).
AHB
My Income is labeled as “[first name] Paycheck”, my husband’s is labeled as “[his first name] Paycheck” plus an annual bonus.
When I create a line like you said and turn on (lines 46-48 on the Life Events table), something wonky is happening with the Duplicated income (see image).
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)
)
)
))
Wow @Jamie.D this is an impressive spreadsheet formula. For those who might be unfamiliar with some of the formulas used I created a formula breakdown.
If anyone is unfamiliar with Cash Flow, here is a Tiller blog post.
Here is the Cash Flow Forecast template for Excel
Cash Flow Forecast template for Google Sheets
-Alice
Tiller Evangelist
@Jamie.D unfortunately I don’t have any specific ideas on why your forecast might be showing duplicates. I asked AI what it thought of your formula, and unfortunately it did not have any specific solutions either. AI did suggest some troubleshooting techniques. I have copied the response into a document if you want to check it out.
-Alice
Tiller Evangelist
Oh gosh @TillerAlice I definitely did not create that HAHA - but yes, very intense formula from the very smart creator of the Cash Flow sheet. I’ll deep dive into your word doc but on first glance, a bit of feedback for everyone so kind to read and offer tips:
Isolating the problem - The issue seems to be with both of our incomes, though more dramatic with his. With my 2% salary increase estimation every year from now until retirement in 2054, my ending take home salary would be $126,344. When I toggle just my retirement on, Income Duplication column for that year shows $257,743 (which is oddly 2.04x my ending salary.) If I do the same and just toggle my husband’s retirement on, the Income Duplication column for that year shows $1,331,030 when his estimated ending salary should be $174,959 (which is 7.61x his ending salary). Though I didn’t create the sheet, I would assume that the Income Duplicated column is supposed to cancel out only the estimated income of that year of the person retiring/losing income.
@Jamie.D I was wondering if you added a new column or if it was the one from the spreadsheet 
No - have not added any columns or rows. Only editing green cells. I think it’s something with the formula in the Duplicated Income column but I am not nearly versed enough to understand what it says now, let alone how to fix it.
Hi @Jamie.D
Thanks for sharing your issues with the sheet.
I created the original sheet and formulas. But they were modified by someone else to take advantage of some of the new Google Sheets functions. And honestly, i’m having trouble using understanding some of the new formulas.
Let me reach out to someone who might have modified the formulas and see if we can figure out what’s going on.
Jon
I think I was able to fix this issue by changing this part of the formula
IFERROR(MATCH(D$17:D, $Z$2:$Z, 0), 0) → (IFERROR(MATCH(D$17:D, $Z$2:$Z, 0), 0)<>0)
I think the issue was that it was supposed to be a “binary filter’“ (1 or 0) but the MATCH function returns an index so it can be a larger number
So this is the corrected formula
=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)<>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)
)
)
))
Thank you for sharing that @mikekojder ! I am yet to use this sheet but I hope folks will chime in with feedback as they work with it.
I was experiencing the same issue and this updated formula worked Mike!! The only adjustment I had to make was copying and pasting it changed the quote marks around Income. It caused a parse error. Thanks to chatgpt, I edited it right in the google sheet and it fixed it. Thank you!
I am having similar issues with the Income Duplicated column (P). I downloaded a fresh Cash Flow Forecast template (v0.95) in a new Tiller Foundation sheet with only one row of events in the forecast table to change income but I still had the issue. So I’m fairly sure the issue is with the template itself @randy or @jono. So may be worth releasing a new version with an updated formula.
ChatGPT provided the following explanation of the issue when it compared the old formula (v0.93) to the current one (v0.95):
The formulas are conceptually equivalent
The new one accidentally multiplies by MATCH index values That creates duplicated income totals
Convert 
MATCH() back to a boolean condition (0/1) to fix it.See the new formula below. Why this fixes it:
New Formula:
=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) *
N(ISNUMBER(MATCH(D$17:D, $Z$2:$Z, 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)
)
)
))
Hope this helps!
Hi @Lulu thank you for that input. So are you confirming that new formula resolved the issue for you? and just curious had you tried @mikekojder ’s revision as well before this ChatGPT solution?
Additionally, is anyone’s sheet here relying on non-current year or partial budget values from their Categories sheet? i.e. it’s 2025 but do you perhaps have only January 2024 - December 2024 budgets currently or January 2025 - July 2025. From documentation:
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.
I’m just looking to hear from anyone’s current experience working with a previous year’s budget.
Thanks for sharing this @Lulu
We are looking into it.
Thanks for identifying the bug fix, @Lulu, and providing a detailed solution implementation. I’ve got the update staged but I’m having some trouble with the template-publish step. I’m will let you know when it is live (might be Monday).
(Sorry for the delay in responding to this but I have been traveling.)
Happy New Year.
Alright, @Lulu, I just got this published. If you open the TMF add-on and navigate to Browse Templates, the Cashflow Forecast should now show an Update option to 0.96. Can you run the update and confirm it is working properly now? Thanks and have a great weekend.
@twalane I found my solution without trying Mike’s and just took inspiration from Tiller Alice to ask AI. Luckily I had an old version of the sheet that worked correctly which I could compare against.
@randy Looking good, everything is working properly. If I put in a single line of income with a growth rate, it grows as expected.
Thanks all for looking into this and happy new year!
Thanks for the clear and easy to implement fix, @Lulu!
