Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Rom1b
Posts: 36
Joined: Fri Jan 05, 2018 5:34 am
Contact:

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Rom1b » Wed Feb 28, 2018 8:00 am

siamond wrote:
Fri Feb 23, 2018 7:29 pm
LadyGeek wrote:
Fri Feb 23, 2018 7:18 pm
All credits to Simba (whoever he/she is) for starting this terrific project.
Not to all, you're too humble, you've been doing this for years already; but yet and yes of course 'Kudos' (is that still trendy Im a foreigner ? or was it ever?) - to Simba and all the best to him.

Once again great version, it would be nice to get rid of the old link where possible. I'll get in touch or am there if you need, only slightly busy at the moment (cant swear it will change or aren't we all - even you early retirees).
Dr Bernstein - If You Can / free PDF, google it | 3Fund Portfolio www.bogleheads.org/forum/viewtopic.php?f=10&t=88005 | CollectiveThought www.bogleheads.org/forum/viewtopic.php?f=10&t=7353

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Wed Mar 14, 2018 4:54 pm

siamond wrote:
Fri Feb 23, 2018 9:19 am
I was wondering how many people download this spreadsheet. Sometimes, I have doubts, due to the fairly limited amount of feedback about it (e.g. just a few posters). So I set up a counter associated with the short URL which allows people to download it.

In the past 2 weeks, v17b has been downloaded more than 150 times. Call me impressed. :beer
300+ downloads, and counting. Cool! :happy

jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by jlhod1 » Tue Apr 10, 2018 9:52 pm

Awesome work!

Unfortunately, I cannot download the excel spreadsheet because I get a "too many redirects" error when clicking on the 17b link. Any way to link more directly to the document?

Thanks!

John

User avatar
LadyGeek
Site Admin
Posts: 47520
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by LadyGeek » Tue Apr 10, 2018 9:59 pm

A quick google search suggests the problem is a bug in the Chrome browser. I'm using Firefox and the link worked for me.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Tue Apr 10, 2018 10:01 pm

Hm, actually, I use Chrome on MacOS and I have no problem. I see more than 400 clicks on the stats, seems to work pretty well overall.

jlhod1, if you continue to have an issue, please contact me via private message, and I'll try to help you.

jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by jlhod1 » Tue Apr 10, 2018 10:30 pm

Had same problem when trying again with Safari, but worked fine with Firefox thanks!

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Fri Apr 13, 2018 6:32 pm

For reference, here is a new discussion on how to extend the Emerging Markets data series back in time:
viewtopic.php?f=10&t=246941

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sat Apr 28, 2018 2:39 pm

For reference, the discussion about International Small was re-opened in light of a new data source (Independence International Associates, aka IIA):
viewtopic.php?f=10&t=206489&p=3902671#p3902228

Kelgroup
Posts: 1
Joined: Sun May 13, 2018 1:29 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Kelgroup » Sun May 13, 2018 1:35 pm

Regarding Backtest-Portfolio-returns-rev17b - I noticed that the annual returns from 1985 are no longer listed year by year on the lazy portfolio tab. Am I missing something - is there an easy way to display it? What I found valuable about this page was that even though annual data - it at least gives me a better idea on things perform year to year.

Thx

Ed

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun May 13, 2018 3:52 pm

Kelgroup wrote:
Sun May 13, 2018 1:35 pm
Regarding Backtest-Portfolio-returns-rev17b - I noticed that the annual returns from 1985 are no longer listed year by year on the lazy portfolio tab. Am I missing something - is there an easy way to display it? What I found valuable about this page was that even though annual data - it at least gives me a better idea on things perform year to year.
Most of the portfolio-level math has been moved to the Portfolio_Math tab. The 'lazy' portfolios start around column AV. If you scroll down, you can find nominal portfolio returns starting row 97, and real (inflation-adjusted) portfolio returns starting row 255, per year. I would suggest to freeze (or split) the window display around cell D5 for convenience.

gips
Posts: 463
Joined: Mon May 13, 2013 5:42 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by gips » Fri May 25, 2018 11:39 am

thank you for this fine work! I was reading a thread about merriman that included this link to published returns of his portfolio:
https://paulmerriman.com/wp-content/upl ... Update.pdf

when I change the starting year to 1970 in the lazy portfolio tab, his portfolio doesn't perform as well. Could it be different data sources/selection of funds/indexes? The pdf also notes yearly vs. monthly rebalancing, does the backtesting spreadsheet perform rebalancing?

thanks again for this wonderful tool!

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Fri May 25, 2018 8:59 pm

gips wrote:
Fri May 25, 2018 11:39 am
thank you for this fine work! I was reading a thread about merriman that included this link to published returns of his portfolio:
https://paulmerriman.com/wp-content/upl ... Update.pdf

when I change the starting year to 1970 in the lazy portfolio tab, his portfolio doesn't perform as well. Could it be different data sources/selection of funds/indexes? The pdf also notes yearly vs. monthly rebalancing, does the backtesting spreadsheet perform rebalancing?

thanks again for this wonderful tool!
Hi there. Thanks for your interest and for the nice words. As to your questions:
- the spreadsheet computations assume annual rebalancing (and I can tell you from another experiment that monthly rebalancing doesn't improve performance)
- in the lazy tab, if you change the start date to 1970, you should see quite a few cells in column D being displayed in red. This is a warning that the spreadsheet doesn't have data coming back that far for a given asset class (e.g. Emerging, Int'l Small, TIPS: all of them apply to the Merriman portfolio). The spreadsheet still does the math, but then returns for the missing years are empty cells, hence zeroes, hence a negatively skewed impact on the stats.
- I do not know how Merriman computed his own stats back to 1970, but this probably involved some approximations. Note that the PDF file you provided only documents the equity part of the portfolio.
- Note that in the next update of the spreadsheet, we'll fill the gaps for EM and Int'l Small, as we recently identified a new data source for those.

gips
Posts: 463
Joined: Mon May 13, 2013 5:42 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by gips » Sat May 26, 2018 11:59 am

thank you for the information and, again, thank you for the great work.

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Wed Jul 04, 2018 5:36 pm

What a better way to celebrate July 4th than a new version of the Simba spreadsheet? :wink:

Here is the link to download: Backtest-Portfolio-returns-rev17c.xlsx

Rev17c:
1. Updated Expense Ratios (ERs) with latest known values, e.g. latest reductions Vanguard announced in Apr-18 and more
=> Changed ERs: VTSMX 0.14, VIVAX/VIGRX/VIMSX/NAESX/VGTSX 0.17, VFSVX 0.25, VWNDX 0.31, VGENX/VGHCX 0.38, VGPMX 0.36, VWNFX 0.34, VTIBX 0.13, VTWSX 0.19, VDAIX 0.15, EFV 0.39
2. Replaced the Emerging Markets pre-1988 numbers by IFCG EM data series, from Credit Suisse Global Investment Returns Yearbook 2010, starting in 1976
3. Replaced the Int'l Small pre-1995 numbers by IIA Int'l Small data series (eliminating the dependency on DFA data), starting in 1975
4. Improved the dynamic title for portfolio cycles comparison chart, adding the metric's name to the title

Feedback welcome.

Bob
Posts: 187
Joined: Mon Feb 26, 2007 5:15 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Bob » Wed Jul 04, 2018 6:58 pm

Thank you ! Happy 4th of July to U and everyone who supports this great effort. It is appreciated.

mjb49
Posts: 30
Joined: Sat Jan 07, 2017 2:25 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by mjb49 » Sun Jul 08, 2018 2:10 pm

Thank you for the update and all your hard work!

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Fri Aug 31, 2018 11:27 am

A new update to the Simba backtesting spreadsheet is now available, click here to download it. Feedback welcome.

Rev17d:
1. Improved rolling returns tables parameterization (e.g. starting year) in Data_TR_USD
2. Consolidated growth charts in Analyze_Portfolio with toggle nominal/real, option to remove the unbalanced portfolio display, and label displaying last value
3. Added new portfolio cycles chart in Analyze_Portfolio, tracking the portfolio end value when making (fixed, inflation-adjusted) annual contributions or withdrawals
4. Settled on the more intuitive 'Maximum Withdrawal Rate' (MWR) and 'Perpetual Withdrawal Rate' (PWR) to replace the SSR/PSR little-known terminology. Linkage to SWR terminology added in explanation of percentile table.

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sat Sep 01, 2018 3:08 pm

Here are two examples of the new chart. The point is to look at every cycle of N years (in this case, 30) contained in the time period being studied (in this case, 1970-2017) and compare the outcomes for two portfolios (first one is 50% US stocks, 30% Int'l stocks, 20% bonds; second one is 60% US stocks, 40% bonds) at the end of the cycle.

The first chart assumes a portfolio of $1,000,000 at the beginning of each cycle, and an annual withdrawal of $40,000. The second chart assumes a portfolio of $10,000 at the beginning of each cycle, and an annual contribution of $25,000. All numbers are inflation-adjusted. As you can see, the starting year of the cycle REALLY matters...

Note for nerds like me (!!): in order to keep the spreadsheet reasonably efficient, I used a variation of the wonderfully compact formula described here.

Image

Image

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Fri Sep 07, 2018 10:19 am

As discussed in this thread, the Vanguard Precious Metals and Mining Fund (VGPMX) will radically change its strategy in Sep-18 and no longer properly represent the precious metals and mining (PME) sector. We have such a PME data series in Simba, so we have to decide what to do from 2018 onward.

Starting from this post, various options are explored. Current proposal (with rationale) is detailed here. Feedback on the corresponding thread would be welcome.

User avatar
siamond
Posts: 4168
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond » Sun Sep 09, 2018 3:06 pm

Another update to plan for the first 2019 Simba revision. The Global Bonds data series was anchored on the Pimco Global Bond Fund (unhedged) Class D (PGBDX). This was never terribly satisfying, as this is a rather active fund with a very high ER (0.95). Or I should say "it was", as the fund now disappeared, which kind of settles it. Also, there was no currency hedging with this Pimco fund, which might not be what most US investors would prefer, and wasn't consistent with the Int'l bonds data series.

Fortunately, a new Vanguard Total World Bond ETF (BNDW) fund is now available to address the same space (with currency hedging though), with a sweet ER 0.09%, see fund page and press release. And its benchmark is the Bloomberg Barclays Global Aggregate Float Adjusted Composite Index, which is consistent with the type of index we were using for the years before PGBDX's inception. So the logical move would probably be to redefine this data series as:

FTSE WGBI USD (hedged) 1985-1989
Bloomberg Barclays Global Aggregate TR USD (hedged) 1990-2009
Bloomberg Barclays Global Aggregate Float Adjusted TR USD (hedged) 2010-2018
Vanguard Total World Bond ETF (BNDW) 2019+

Post Reply