Author Topic: Modeling help  (Read 485 times)

appleshampooid

  • Stubble
  • **
  • Posts: 196
  • Relentless Snacker
Modeling help
« on: January 21, 2019, 01:20:03 PM »
Hi,

I want to start doing some basic modeling for the future of our accumulation phase and am a total n00b to all the tools that are out there. I have used Portfolio Visualizer a little bit just to look at asset allocations, but here's the scenario I want to model right now:
  • Start with current investments
  • Account for ongoing contributions from current salary and other income
  • Ability to tweak asset allocation over time (e.g. to test the impact of a bond tent)
  • Output would be total portfolio value of X at N, N+1, N+2 years in the future

A similar problem I have is for our kids' 529 accounts. I would like to forecast what the ending balance would be given varying yearly contributions, so I can tune how much we contribute.

Forgive my ignorance, as I am doing some poking around right now but was hoping the wealth of experience on this forum could point me toward the best tool for these types of problems. It seems like a lot of the tools are oriented toward the post-FIRE phase (here's my stash and allocations, how long will it last) vs. including the accumulation phase as well.

Montecarlo

  • Stubble
  • **
  • Posts: 112
Re: Modeling help
« Reply #1 on: January 21, 2019, 04:09:19 PM »
It's pretty easy to build your own model in excel if you have a license.  Google sheets doesn't have all the fanciness that Excel has, but I suspect it is more than adequate to the task, and it's FREE.

If you'd like to take a stab at it and have someone review, I'd be more than happy to help.

Really all the math skills you need are to be able to run a compounding interest formula frontwards and backwards.  The rest is just getting familiar with the tool.
« Last Edit: January 21, 2019, 04:11:40 PM by Montecarlo »

ILikeDividends

  • Bristles
  • ***
  • Posts: 441
Re: Modeling help
« Reply #2 on: January 21, 2019, 05:23:37 PM »
It's pretty easy to build your own model in excel if you have a license.  Google sheets doesn't have all the fanciness that Excel has, but I suspect it is more than adequate to the task, and it's FREE.

I would agree, excel, if you have the license, is the superior tool.  Another free alternative is OpenOffice: https://www.openoffice.org/

Not quite as fully featured as excel* (called "calc" in the OpenOffice suite), but close, and probably far stronger than Google sheets**; and, again, the price is right.

* OpenOffice can even import/export many excel file formats; however, an unsupported excel formula -- there are a few -- will produce an error cell in the resulting calc spreadsheet.

** Caveat: I have never used Google sheets.
« Last Edit: January 21, 2019, 06:03:27 PM by ILikeDividends »

appleshampooid

  • Stubble
  • **
  • Posts: 196
  • Relentless Snacker
Re: Modeling help
« Reply #3 on: January 21, 2019, 06:38:56 PM »
Hmm, I may take a stab at it. I use Google Sheets for most of my existing stuff (asset allocation calcs, Roth basis tracker, tax worksheets, etc.). I've never done anything this fancy, but I'm sure I could learn.

Through work I have access to Excel, but no guarantee I will always have that at future jobs, so I wouldn't want to build anything in it. Maybe OpenOffice/LibreOffice if G sheets isn't cutting it.

There is already a lot of good work out there, I may also look at the source of cfiresim since it's open source and on GitHub. It shouldn't be that hard to add in support for a bond tent type thing.

ILikeDividends

  • Bristles
  • ***
  • Posts: 441
Re: Modeling help
« Reply #4 on: January 21, 2019, 06:43:46 PM »
Through work I have access to Excel, but no guarantee I will always have that at future jobs, so I wouldn't want to build anything in it. Maybe OpenOffice/LibreOffice if G sheets isn't cutting it.

If you are already familiar with the basics of formulaic excel, then you will have a near-zero learning curve with OpenOffice calc.  One strong advantage with calc is that there are tons of excel solutions you can download from the web that will work, unedited, in calc.

When it comes to Basic programming or macros or some other esoteric features of excel, the differences become more glaring.  Calc, just for instance, has it's own homegrown programming language (object-oriented, not procedural), but it is hideously poorly documented and, as such, is practically worthless for most homegamers.
« Last Edit: January 22, 2019, 12:15:21 AM by ILikeDividends »

MustacheAndaHalf

  • Handlebar Stache
  • *****
  • Posts: 1609
Re: Modeling help
« Reply #5 on: January 21, 2019, 08:45:30 PM »
Output would be total portfolio value of X at N, N+1, N+2 years in the future
One word of warning before you put too much effort into this: it will be wrong.  Your model has to predict the next few years of the stock market, which nobody can do accurately.  So the whole point of stock investing, to benefit from the stock market return, is something you can't forecast accurately.

Have you tried using investment tools or simulations?

For those mostly interested in withdrawal rates, Vanguard offers a simple tool that simulates thousands of runs using historical stock performance:
https://www.vanguard.com/nesteggcalculator

Tyler

  • Handlebar Stache
  • *****
  • Posts: 1112
Re: Modeling help
« Reply #6 on: January 21, 2019, 10:01:45 PM »
here's the scenario I want to model right now:
  • Start with current investments
  • Account for ongoing contributions from current salary and other income
  • Ability to tweak asset allocation over time (e.g. to test the impact of a bond tent)
  • Output would be total portfolio value of X at N, N+1, N+2 years in the future

Hi ASid. 

You might try this calculator:  https://portfoliocharts.com/portfolio/portfolio-growth/ 

It will be able to do all you're looking for except item #3, as it assumes a fixed asset allocation.  But it may also make up for that by doing an extra trick of modeling every historical timeframe since 1970 simultaneously.  Think of it as running Portfolio Visualizer 49 times for every different start year and mapping every result on the same chart.  While no portfolio is perfectly predictable, not every portfolio is equally unpredictable and this will help you visualize the full range of historical outcomes.  I'd recommend planning for the low end of results, as that will help you invest conservatively but save aggressively to reach your goal with confidence.

Hope that helps!

appleshampooid

  • Stubble
  • **
  • Posts: 196
  • Relentless Snacker
Re: Modeling help
« Reply #7 on: January 22, 2019, 07:24:18 AM »
Output would be total portfolio value of X at N, N+1, N+2 years in the future
One word of warning before you put too much effort into this: it will be wrong.  Your model has to predict the next few years of the stock market, which nobody can do accurately.  So the whole point of stock investing, to benefit from the stock market return, is something you can't forecast accurately.

Have you tried using investment tools or simulations?

For those mostly interested in withdrawal rates, Vanguard offers a simple tool that simulates thousands of runs using historical stock performance:
https://www.vanguard.com/nesteggcalculator
Oh yeah, I'm not looking for high accuracy here, understanding that it's not possible. What I want is a general forecast. Honestly I have no idea if I'm 7 years or 20 years to retirement right now (my gut says about 10, but I feel like I've been saying "about 10 years" for the past 5 years). So given market averages, I want to know when I'm most likely to hit my number. With a wide error band, of course.

The main reason is that if it comes back that I'm < 10 years away, I should be starting my bond tent NOW, as I plan to ramp from 10% bonds to 20% bonds over the 10 years leading up to retirement. Of course, I want to sim this as well. If the market is on fire, I can accelerate that, or if it dumps can easily pump the brakes in light of a further forecast date.

appleshampooid

  • Stubble
  • **
  • Posts: 196
  • Relentless Snacker
Re: Modeling help
« Reply #8 on: January 22, 2019, 07:26:27 AM »
here's the scenario I want to model right now:
  • Start with current investments
  • Account for ongoing contributions from current salary and other income
  • Ability to tweak asset allocation over time (e.g. to test the impact of a bond tent)
  • Output would be total portfolio value of X at N, N+1, N+2 years in the future

Hi ASid. 

You might try this calculator:  https://portfoliocharts.com/portfolio/portfolio-growth/ 

It will be able to do all you're looking for except item #3, as it assumes a fixed asset allocation.  But it may also make up for that by doing an extra trick of modeling every historical timeframe since 1970 simultaneously.  Think of it as running Portfolio Visualizer 49 times for every different start year and mapping every result on the same chart.  While no portfolio is perfectly predictable, not every portfolio is equally unpredictable and this will help you visualize the full range of historical outcomes.  I'd recommend planning for the low end of results, as that will help you invest conservatively but save aggressively to reach your goal with confidence.

Hope that helps!
Thanks, will check it out!

What I really need is like 4 hours without any interruptions to site down and hammer at some of these sites, or at my own spreadsheet. Sadly with full time job and a toddler and pregnant wife at home, 4 hour blocks of uninterrupted time are hard to come by!

MDM

  • Walrus Stache
  • *******
  • Posts: 9097
Re: Modeling help
« Reply #9 on: January 22, 2019, 08:14:11 AM »
You can get a simple "Time to FI" calculation in the case study spreadsheet, or use more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org.