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
