Author Topic: Excel Spreadsheet Conundrum  (Read 625 times)

WSUCoug1994

  • Bristles
  • ***
  • Posts: 282
  • Location: Bay Area, California
Excel Spreadsheet Conundrum
« on: July 16, 2018, 05:28:03 PM »
I am hard core budgeter and spreadsheet enthusiast - I just want to lay that out there.

Here comes my conundrum:

As I think about certain expenses in retirement - (automobile purchases, travel, healthcare, college funds, large home improvement projects, mortgages, etc.) I am struggling how to plan for these expenses.  For example on health care - I will likley retire in my early 50's (five years from now) and I will only need to think about traditional healthcare for the next say 13 years.  How do I really calculate this from a planning perspective?  Do I average the 13 year cost over a 38 year retirement?  Same with college funds? eventually I will have those paid off - likely 12 years into my retirement.  This problem continues when I think about my mortgage (will be paid off in 10 years).  We buy our cars used every 15 years or so - same kind of problem.  I hope you get the point of my question.  Do I break down the costs over a 38 year retirement (which is what I am doing now) or is there a better way?

sokoloff

  • Handlebar Stache
  • *****
  • Posts: 1193
Re: Excel Spreadsheet Conundrum
« Reply #1 on: July 16, 2018, 05:43:39 PM »
I would look at each of those as an annual cash flow.

Healthcare gets a row and gets negative cash flow entries in 13 columns worth of cells (one per year), then nothing.
Cars can either be done the same way, or a notional average each year that represents the "smoothed" cashflow if you prefer.
College funds get a negative cashflow in years you contribute to the 529 (or equivalent) with an offsetting positive in the 529 account row.
Then, in years when you actually pay the college, enter negative amounts in the 529 line and possibly in the other cash college line.

This way, you get a picture of each year (by summing the column) and ensure you never go negative (aka "broke")

WSUCoug1994

  • Bristles
  • ***
  • Posts: 282
  • Location: Bay Area, California
Re: Excel Spreadsheet Conundrum
« Reply #2 on: July 17, 2018, 08:15:32 AM »
So you are suggesting each of the 38 years of retirement has a column (which makes sense to me).  Then as each one of the expenses "expires" it goes to zero.  Then my question is I know have my relative cost of living for each year - which it is safe to assume is going to decrease over time - how do I determine what my actual cost of living is relative to say the 4% rule over those 38 years?  Do I average those 38 years?  or do something else?

sokoloff

  • Handlebar Stache
  • *****
  • Posts: 1193
Re: Excel Spreadsheet Conundrum
« Reply #3 on: July 17, 2018, 08:34:26 AM »
Let's say that row 3 is your then-current stasche size.

I'd sum up all your expense cashflows (into, say row 6), then compute a row 4 which is "annual expenses divided by stasche size" (aka "C4's formula is =C6/C3, formatted as a percent with 1 decimal place). Then, look across row 4 and see what the percentages look like.

If you want to model different growth rates, make a cell (say B1) of expected growth rate of stasche, and then make D3 = (C3 - C6) * (1+$B$1) and then run out row 3, 4, and 6 as many years as you need.

This is fairly rough and simple-to-understand method, but not particularly directly comparable to the Trinity study 4% SWR conclusion. It just gives you an idea of what is likely to happen (hence, looking at more than 2 significant figures is misleading at best). As for how to get back to something you can directly compare to the 4% SWR, any method will be tenuous.

I could imagine making a row 5, which is, for each year, the average of cash flows out from year 1 through the current year. Then, look at how that changes over time. (To make this easier, do all calculations in real dollars [meaning already adjusted for inflation] and take cell B1 down to "gains above inflation" rather than nominal gains.)

That would make C5 = average($c6:c6) and then fill that right, so D5 = average($c6:d6), etc. If you then look across row 5, take the highest average, divide by 0.04 (which is multiplying by 25), you'll get your "kind of worst case year" stasche required to maintain 4% SWR.

This is all hand-wavy, of course, but I hope it helps. Realize that in any modeling like this, it's possible to be off by 50%.

bluebelle

  • Bristles
  • ***
  • Posts: 281
  • Location: Toronto
Re: Excel Spreadsheet Conundrum
« Reply #4 on: July 17, 2018, 09:27:52 AM »
I took  the attached spreadsheet and modified it to suit me....mine got complicated, he has a pension with a bridge benefit until he turns  65 (so extra money until he turns 65), so I added in a column for that....I used the 'routine deposit/withdrawal' column for one off things or things that occur every 5-10 years (car, extra spending for travel in the first 10 years etc).  I also added a column for DH pension, our CPP/OAS (canadian version of SS).
I also assumed I'd have different income needs at different times in retirement.  Typical retirement cycle is more active/travel in early retirement, lower expenses in mid-retirement (less travel, less activities), more expenses in later retirement (think long term care)




WSUCoug1994

  • Bristles
  • ***
  • Posts: 282
  • Location: Bay Area, California
Re: Excel Spreadsheet Conundrum
« Reply #5 on: July 17, 2018, 10:03:42 AM »
This is awesome stuff.  Thank you.  I am going to play around with your advice and I will share what I come up with.  I also love the cash flow planner your shared.  Thank you very much.

secondcor521

  • Handlebar Stache
  • *****
  • Posts: 2015
  • Age: 49
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Excel Spreadsheet Conundrum
« Reply #6 on: July 17, 2018, 10:25:03 AM »
I use two other strategies:

1.  I use the extra income/expenses capabilities built into tools like firecalc and cfiresim for some items.  For example, I have child support payments that will end in two years, so I include them in my annual expenses and then enter a non-inflation-adjusted income starting in two years for the child support amount.  This way, the expenses are incorporated properly into the overall historical analysis of those tools, which I like.

2.  I use the FV and NPV functions for some things.  So for college expenses, I have a spreadsheet set up that has a row for each of my three kids' eight college semesters.  I take the estimated cost today and inflate it by my college inflation factor to arrive at the future cost.  I then use the NPV function to determine what amount of dollars I need today in order to grow and meet that future cost.  I then try to make sure that I have the total of all of those NPVs set aside in the kids' college funds, which I segregate from my main FIRE stash.  (This is all a crapshoot anyway; my kids' college expenses are bearing no relation to the spreadsheet, but at least I'm within the same order of magnitude on funding.  I have contingency plans if it's more or less than expected.)

trollwithamustache

  • Pencil Stache
  • ****
  • Posts: 646
Re: Excel Spreadsheet Conundrum
« Reply #7 on: July 17, 2018, 10:38:03 AM »
Health care is a lumpy one. I carry an annual cost and a couple cost lumps for years that max out the deductible.  Early on I don't have that many but later there are more frequent cost lumps.