You’re right - it’s doing it to both of our incomes, but my husband’s much more so (hence that 2057 - the year after he retires - duplicated income of 1.6M). I checked out those columns and everything looks as it should:
Maybe the sheet author, @jono, might have some more insight / advice?
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
Hey everyone! Sorry to be so late to this. I have been working on some other development priorities and am returning to this thread today. Does anyone have a simple/minimum repro for this bug?
Thanks for your patience.
P.S. As @jono noted, he is the original creator of this template, but the bugs were almost certainly introduced when I tried to streamline some formulas about a year ago. Apologies.
Trying to work through the formula logic a bit, I think this is the intent…
AC from your budget scaled to the Income growth for any “… Paycheck” categories that are scheduled to be replaced. (Are you seeing numbers in AC for the overridden categories that could sum to ~$1.6MM? Or is it possible your budget has redundant rows for the “… Paycheck” category you are overriding?)Essentially, your Yearly Cash Flow for Income will be the Unadjusted Income baseline - Income Duplicated for scheduled replacements + Life Events Income which is the scheduled replacement value.
One thing that is confusing to me about the screenshot you shared is that the Life Events column is zero. When you have overrides like 2025-2056 of $811k, I’d expect that value to be part of the total in column Q up to 2056.
As for the formula you shared for Income Duplicated, it is summing values matching all of these criteria…
YEAR >= VALUE($A$17:$A) - If the year in column A is greater than or equal to the active row L.YEAR <= VALUE($B$17:$B) - If the year in column B is less than or equal to the active row L.$C$17:$C - If the life event is toggled active.IFERROR(MATCH(D$17:D, $Z$2:$Z, 0), 0) - If the name of the life event in column D matches a budgeted category listed in column Z.X$17:X = “Income” - If the “Life Event Type” finds a category match type of “Income” as shown for the life event row in column X.IFERROR(VLOOKUP($D$17:$D, { $Z$2:$Z, $AC$2:$AC }, 2, FALSE), 0) - Retrieves baseline budget value for category via lookup from life event name to match on category name (column Z) then category baseline budget (column AC).POW(1+$E$5, YEAR - $AF$10) - Applies budget scaling factor from E5 “Income Budget Change / Year”.I know this isn’t a solution but hopefully it provides enough context to get closer to the answer. Really curious to hear if this helps.
Thanks for looking into this @randy
For what it’s worth, i asked ChatGPT to compare the old formulas in Column O, P and Q to the new ones and see if they would yield different results. It basically said they SHOULD produce the same results.
It also said the new formulas are cleaner , easier to debug, and better for future-proofing.
So i’m not sure either where the issue is coming from.
I like the idea of having ChatGPT compare the formulas.
No guarantees but I wonder if there is something in the dataset that is causing this problem? Like a category that is listed twice in the budget.