Forward Looking Spreadsheet Examples?

Ask your investment, budget, and other money related questions here
Post Reply
Gilberto de Piento
Posts: 1942
Joined: Tue Nov 12, 2013 10:23 pm

Forward Looking Spreadsheet Examples?

Post by Gilberto de Piento »

A couple of posts here have mentioned creating spreadsheets that project out finances through end of life. I'm assuming it is something like a column for each year you might reasonably expect to live, with expected income and expenses and a total. I'm thinking about implementing something like this including work income, when pension starts, when social security starts, when house would be paid off, likely income from various investments etc. Does anyone have any more detail to share on this idea? Is anyone using one?

AxelHeyst
Posts: 2118
Joined: Thu Jan 09, 2020 4:55 pm
Contact:

Re: Forward Looking Spreadsheet Examples?

Post by AxelHeyst »

Image

The orange bordered cell is "this month". Cells above are historical/tracked data. Cells below are future/projected. I put a white vertical line in my charts that represents "this month", indicating the difference between historical data and projected data, like this:
Image

jacob
Site Admin
Posts: 15907
Joined: Fri Jun 28, 2013 8:38 pm
Location: USA, Zone 5b, Koppen Dfa, Elev. 620ft, Walkscore 77
Contact:

Re: Forward Looking Spreadsheet Examples?

Post by jacob »

I have mine (google sheets) set up so each row is a time. Original I went today(), above+365 days for all of them but eventually switched to monthly for the first year in the future, quarters for second, yearly for the decade, and then every other ... just to compress it a bit. It currently goes out 30 years to 2051.

I only run one projection based on fixed numbers for inflation and ROIC. I think iDave is the go-to for scenario planning. Having an SWR of around 2/3 of 1% I haven't bothered.

sky
Posts: 1726
Joined: Tue Jan 04, 2011 2:20 am

Re: Forward Looking Spreadsheet Examples?

Post by sky »

I made a spreadsheet many years ago. The first page was milestone years for expenses and income, such as social security, medicare health insurance, quitting my job, pension start dates, mortgage payoff, future car payments based on guesses as to life of current vehicle. I tried to predict any major change in income or expense.

The second sheet was columns of years out into the future until age 100. The rows were income and expenses. I tried to estimate income increase percentages on investments, and used an inflation factor for each year on the expense rows. Income minus expenses for each year was added to an investment row.

Gilberto de Piento
Posts: 1942
Joined: Tue Nov 12, 2013 10:23 pm

Re: Forward Looking Spreadsheet Examples?

Post by Gilberto de Piento »

Thank you for the discussion and the images. I will give it a try and maybe put the results in my journal.

I think this will be a useful and necessary exercise as my future has become more "locked in" but I'm a little down on it for two reasons. One, it seems a little like back when I used to create budgets. They were never accurate because they missed all the expenses I hadn't foreseen. Two, it will feel like my life is all planned out and I dont like that feeling even if it is right.

sky
Posts: 1726
Joined: Tue Jan 04, 2011 2:20 am

Re: Forward Looking Spreadsheet Examples?

Post by sky »

A long term projection is a soft estimate. Its best use is to identify problems in your financial plan, not to dictate your spending habits. The younger you are, the less accuracy you can expect.

It is depressing to do end-of-life projections. The brutality of reality shows its face.

Frugalchicos
Posts: 658
Joined: Wed Apr 19, 2017 9:23 pm
Contact:

Re: Forward Looking Spreadsheet Examples?

Post by Frugalchicos »

@gilberto de Piento
I was actually thinking in doing something very similar some time this week. I would like to do projections through end of life. I will be happy to share any file I come across with.

disk_poet
Posts: 125
Joined: Thu Aug 06, 2020 3:33 am

Re: Forward Looking Spreadsheet Examples?

Post by disk_poet »

Doing this exercise is what led me to ERE in the long run. Mine is similar to sky's.

I basically have a spreadsheet that has one column pear year. It is divided into accumulation and drawdown phase. Each column has the year + my age in the header. The columns have rows for planned savings, running total savings, COL, extra savings (like insurances), interest (pick your number - I use 3% at this point) and pension (which is at 0 until I officially retire at 67 or whatever will be in Germany at that point). The spreadsheet goes up to an age of 100 but I got some data for life expectancy and colored the headings into three color: green = now until average life expectancy, yellow = average until average + 10 years, red = average + 10 until 100. I then put in my projected savings up to start of the drawdown phase (right now at a very classical 60 years of age) and projected COL onwards (corrected for 2% inflation). Given how I play with the numbers at some point in my life the savings will go into negative. Right now the "deal" I made with myself is that if that happens in the red area of my life expectancy I am ok with it.

I think it's a not very ERE at this point. For me it was due diligence. It's an exercise for me to find out how much security I need and want and also an attempt to quiet down the voice in my head, that I am screwing over my future self by not planning ahead. I know it has some big assumptions but for me it was better than the alternative of no plan at all. I planned to re-evaluate every 5ish years. I hope if my confidence and my ERE-skills grow I will maybe change the plan but so far it is my backup and I stay on track just to keep the deal that I made with myself. I am not sure that's wise to be frank. It might be, that I am just not mentally flexible enough to throw the plan overboard. I hope I'll get there. I think at this point I am still too much giving a shit about what society thinks and have too little self confidence. So having a more traditional plan B makes me feel a bit more secure.

WFJ
Posts: 416
Joined: Sat Apr 24, 2021 11:32 am

Re: Forward Looking Spreadsheet Examples?

Post by WFJ »

Gilberto de Piento wrote:
Wed Sep 08, 2021 11:13 am
A couple of posts here have mentioned creating spreadsheets that project out finances through end of life. I'm assuming it is something like a column for each year you might reasonably expect to live, with expected income and expenses and a total. I'm thinking about implementing something like this including work income, when pension starts, when social security starts, when house would be paid off, likely income from various investments etc. Does anyone have any more detail to share on this idea? Is anyone using one?
It is a great exercise to build these on your own, but this self made simulations become quite complex and subject to all kind of data/programming gremlins as one adds more variables. Many brokerage firms provide canned programs that are excellent and will save you the 40-500 hours to program these yourself. eMoneyguide is provided by at least one discount broker as part of their free financial planning services and the best way to estimate long term values until age 100+ with amazing number of variables. If you can do this one your own, you have a $200+/hr skill.

Gilberto de Piento
Posts: 1942
Joined: Tue Nov 12, 2013 10:23 pm

Re: Forward Looking Spreadsheet Examples?

Post by Gilberto de Piento »

WFJ wrote:
Tue Sep 21, 2021 2:51 pm
It is a great exercise to build these on your own, but this self made simulations become quite complex and subject to all kind of data/programming gremlins...
I started working on this project. I knew it was going to be complicated. So far I've added some estimates about my likely income and expenses in the future. To do that took about an hour and was easy. To do it really right would be a huge time investment, both in research and programming. I can do the math/programming but I am not interested in investing so much time into the project given that I don't think I will get much additional value from it.

That said, it was helpful just to lay out the obvious income and expenses for the next 50+ years. It leaves out a lot (example: taxes) but it was good to think about it. This would be an easier exercise for someone with fewer variables.

Firecalc is somewhat similar if you enter data beyond the first tab.

WFJ
Posts: 416
Joined: Sat Apr 24, 2021 11:32 am

Re: Forward Looking Spreadsheet Examples?

Post by WFJ »

Ditto, think I spent about 4 hours before realizing the issues of these estimates and used eMoneyguide but understood all the assumptions in their model. I'm still shocked at how very small changes in the shape of the distribution of returns or even a tiny increase in volatility will destroy any SWR. 1% is the only SWR that survived a 10% negative shift in these variables for a 50 yr time horizon (my objective is to minimize probability of failure. One also has to clearly define their objective (AUM, minimize taxes or minimize probability of failure) before building their estimates.

Post Reply