A Google Sheets Toolbox

[Chuckanut]

For those of you who need something to do on rainy days or when you are snowed in or just bored with being outside to much. Here's a SWR created by Early Retirement Now:

In Octave, we can calculate a large number of simulations and calculate safe withdrawal rates over a wide range of parameter value assumptions. Millions and millions of SWRs over many different combinations of parameter values (retirement horizons, final asset value target, equity shares, other withdrawal assumptions). That would have been cumbersome, probably even impossible to implement in Excel. But a quick snapshot on how one single set of SWR parameters would have performed over time? That’s actually quite easy to do, even though there are 1,700+ different retirement cohorts between 1871 and 2015.

The Ultimate Guide to Safe Withdrawal Rates – Part 7: A Google Sheets Toolbox

https://earlyretirementnow.com/2017...uide-to-safe-withdrawal-rates-part-7-toolbox/

 

[target2019]

Thanks for posting the link. A lot of information to consider is there.

The google sheet has historical returns in one tab, and that is useful, for sure.

 

[donheff]

Fun spreadsheet

 

[2017ish]

The ERN website overall is good and provides a lot of apparently sound number crunching--especially for the [very] early retiree cohort and those of us 50-somethings who are planning for a very long-lived spouse.

Too bad its owner wasn't able to interact well during his brief sojourn here as JohnDoe123....

 

[Lsbcal]

I saw the spreadsheet which currently is set at 90/10 stocks/bonds.

It is Viewonly which is unfortunate.

When I look at the case study I'm wondering about the decline in the first year, 2000. The sheet shows a year end value of about $843k i.e. a decline of about 16%. It seems like that is too much based on:

The spending was $40k during the year. From my data the SP500 (I am using VFINX for this) dropped about 9.1%. The Vanguard Total Bond Market fund was up 11.1% and Intermediate Treasury was up 14.2%.

So I'm wondering if the author included SP500 dividends in his calculations. This is a very quick check on my part and so perhaps I am wrong.

 

[target2019]

When someone shares a spreadsheet, it is usually view only. If everyone could open the original and make changes, there would be no baseline.
To make changes, save it to your own gdrive, or use Save a copy command.
That worked for me.

 

[Lsbcal]

OK, I saved it as an Excel file. Thanks.

The basic numbers in the case study look alright to me now. I tested a 100% equity situation and the decline seemed correct.

If one uses a 60/40 AA then that case study looks a lot less scary.

Things look even better if using a withdrawal rate of maybe 3.3%. Funny how that number (or near it) keeps coming up a lot.

 

[target2019]

I just noticed there are quite a few comments at the site. The author seems to respond to all.

My work week ends tomorrow, so I may have time on the weekend to play a bit. It does appear that some adjustments are necessary in the values on first tab. This is what I changed:

Stocks 60%
Bonds 40%
ExpRatio (p.a.) 0.20%

Retirement Horizon (Months) 360
Final Value Target (%of initial) 25%

 

[Lsbcal]

...
Retirement Horizon (Months) 360
Final Value Target (%of initial) 25%

This gets at something I've been wondering for a long time. What is my tolerance for how low the portfolio could go. One path might be down to 25% and then upwards to the end of life. This has happened in the past markets like starting with a 1968 retirement.

But would I not be super worried if I was down to 25% (in real terms) of today's portfolio. This is why in VPW I set the age of full portfolio depletion to 110. That gave me a simulation for a bad sequence (starting in 1968) of about 50% minimum around 14 years out (about 1982) before the portfolio recovered in the wonderful 1980's and 1990's markets. For us it still led to quite reasonable withdrawals. For others, if the numbers make it look like you won't have such a good withdrawal number they might be tempting to go with the low final value which might be inconsistent with the true risk tolerance.

Maybe I should start a thread about "How low can you go". Thoughts? I don't mean to hijack this thread.

 

[bingybear]

so is this using octave for the calculation, or just spreadsheet calculation? I have not downloaded it yet. I assume it is just a back testing.

 

[target2019]

Maybe I should start a thread about "How low can you go". Thoughts? I don't mean to hijack this thread.

I would stick around here. I am all ears.

 

[target2019]

so is this using octave for the calculation, or just spreadsheet calculation? I have not downloaded it yet. I assume it is just a back testing.

What is octave calculation? My google skills have failed me.

 

[DrBrisket]

What is octave calculation? My google skills have failed me.

Octave is a [-]freeware[/-] open source programming language:
https://www.gnu.org/software/octave/

 

[target2019]

Octave is a freeware programming language:
https://www.gnu.org/software/octave/

Thanks.
I don't think that is involved at all with the google sheet being discussed. But an opportunity to learn even more, for the cost of e-r admission.

 

[bingybear]

What is octave calculation? My google skills have failed me.

as noted it is a opensource... not freeware software package that that allows programming is scripts similar to matlab. The OP used a quote that referenced that. I was wondering if that was included in the google sheet.

 

[target2019]

I see that now that I've read it.


Octave was used to create one of the sets of data, SWR time series, used in the spreadsheet.

 

[DrBrisket]

Thanks.
I don't think that is involved at all with the google sheet being discussed. But an opportunity to learn even more, for the cost of e-r admission.

You are correct--there is no need for Octave to use the google sheet toolbox from ERN. ERN does not appear to post his Octave code anywhere that I could find.