Simba's Backtesting Spreadsheet: how useful is it?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Snarfanio
Posts: 9
Joined: Tue Oct 03, 2017 2:10 pm

Simba's Backtesting Spreadsheet: how useful is it?

Post by Snarfanio » Thu Oct 05, 2017 2:41 pm

I'm at a loss at to what data is actually used to analyze portfolios. Would greatly appreciate feedback, particularly from siamond!

Let's use small cap value stock returns in 1930 and 2008 as examples. I found two data sources in the spreadsheet, portfoliocharts and DFA.
portfoliocharts: 1930 -36.54%, 2008 -30.30%
DFA: 2008 -45.0%

The Ibbotson SBBI book has yet other numbers: 1930 -46.38%, 2008 -44.39%.

Vanguard reports: Small-Cap Value Investors VISVX 2008 at -32.05%.

When I use the spreadsheet's Analyze Portfolio sheet I get: 1930 -36.66%, 2008 -32.05%.

I'm confused (nothing new here) but more importantly, how reliable are the results when using this spreadsheet, particularly as they relate to actual Vanguard funds?

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

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

Post by LadyGeek » Thu Oct 05, 2017 3:35 pm

Welcome! I moved your post into the Simba Backtesting support thread. There's a very good chance siamond will answer your post here.

Thread split back into a separate thread, see below.
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.

Snarfanio
Posts: 9
Joined: Tue Oct 03, 2017 2:10 pm

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

Post by Snarfanio » Thu Oct 05, 2017 4:44 pm

And I would like this post to stand on its own as it may get lost in this mega-thread. Could you please reverse.

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

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by LadyGeek » Thu Oct 05, 2017 6:05 pm

Sure, no problem. I reversed (split) the thread back to your single post.

I also posted in Re: Simba's backtesting spreadsheet [a Bogleheads community project] to help siamond find your question.
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.

stlutz
Posts: 4024
Joined: Fri Jan 02, 2009 1:08 am

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by stlutz » Thu Oct 05, 2017 6:31 pm

Wherever an actual Vanguard index fund existed, that is the data that is used in this spreadsheet.

User avatar
Tyler9000
Posts: 296
Joined: Fri Aug 21, 2015 11:57 am

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by Tyler9000 » Thu Oct 05, 2017 6:43 pm

I'm no Siamond, but I'm the author of PortfolioCharts and calculated many of the returns used in the Simba spreadsheet. So I know a bit about how the numbers are generated and how well they track known funds.

As for the small discrepancy in PortfolioCharts returns for the same year, that's simply a matter of Siamond adjusting the numbers to have a similar expense ratio to the modern Vanguard fund used in the series. It's a very reasonable adjustment to make sure the numbers are fair.

When it comes to different numbers between Vanguard, PC, and Ibbotson, the thing to remember is that "small" and "value" are somewhat arbitrary and different data providers may have different definitions. In addition, real-world index providers use various band methodologies to minimize trading (and keep down costs) that can stray a bit from a "pure" series based only on company fundamentals. I generated the PC numbers (to the best of my ability with the free source data provided by Pr. French) using CRSP methodology that Vanguard currently uses for their funds. You can read more about that here if you're interested in the nitty-gritty details. But if you just want to skip to one chart, this one might be helpful for this conversation:

Image

This displays the Small Cap Value returns from a variety of different professional indices. The data is from the Bogleheads wiki and the red columns are my own calculations. As you can see, different companies have different values for the same index but they're all generally in the same ballpark. For reference, Vanguard currently uses CRSP and used to use MSCI just a few years ago. Simba also uses the Russell numbers for when MSCI is not available and the PC numbers before that. The general priority is:

1) Actual Vanguard index fund data
2) When that is not available, the most similar (freely available) professional index data (the thing Vanguard attempts to track)
3) When that is not available, the most similar reconstructed index data (PortfolioCharts, Longinvest, etc).

That may be way more info than you were looking for, but I thought it might be helpful to illustrate all the effort that has gone into the Simba spreadheet. So to your question of how reliable the numbers are, I guess this is a long way of saying "as reliable as possible for a free data series". It's not perfect by any means and I would not expect the numbers in the spreadsheet to track any one index fund history precisely, but I do believe they are reasonably accurate for general backtesting purposes. Siamond (and everyone else that has contributed) has done a great job! :beer
Last edited by Tyler9000 on Sat Oct 07, 2017 9:45 pm, edited 2 times in total.

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

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by siamond » Thu Oct 05, 2017 7:45 pm

Tyler's answer is right on, I fully concur. I'll just add a couple of details:

1. to find the data sources used in the Simba spreadsheet, please go check the 'Data_Sources' tab. In the case of Small-Cap Value (SCV), you'll find the following succinct description, which matches the general approach described by Tyler. All pre-Vanguard data is ER-adjusted.
- Stock Index Calculator (Tyler) 1927-1978
- Russell 2000 Value TR USD 1979-1992
- MSCI US Small Cap Value GR USD 1993-1998
- Vanguard Small Cap Value Index Fund (VISVX) 1999+

2. as to SBBI, in recent editions, the editors started to use data from the Fama-French library for SCV. Unfortunately, such data is highly theoretical, and the corresponding methodology bears little resemblance to what a real-life index would do (or has done). I came to believe that the corresponding data is basically useless, and Tyler came to the rescue with terrific ideas on how to model data series that seem more credible (see the pointers he provided). This does remain a synthetic model for the pre-1978 data, nothing more, but at least we convinced ourselves that this looks more realistic. Feedback welcome, of course.

3. Oh, and since the OP made a reference to DFA, I'd venture to guess that a pretty old version of the Simba spreadsheet was used. Please check this wiki page to find the latest.

Snarfanio
Posts: 9
Joined: Tue Oct 03, 2017 2:10 pm

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by Snarfanio » Fri Oct 06, 2017 1:54 pm

Tyler and Siamond,

Thank you much for your feedback to clarify things. I'll be studying this tool a lot more and I may have additional questions later on; I have a habit of doing that.

I downloaded Rev16e, according to the Readme file, so I think I'm good there.

I noticed that for SCV the Vanguard return data, when available, is for the Investor class. Maybe you should consider using the Admiral class?

User avatar
czeckers
Posts: 933
Joined: Thu May 17, 2007 3:49 pm
Location: Upstate NY

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by czeckers » Fri Oct 06, 2017 2:14 pm

Keep in mind that this tool should be used with extreme caution. There is the temptation to try various portfolios and decide on the one that has the best results in the backtest. However, past returns do not predict future returns. Oftentimes, the results are skewed in favor of whatever asset class has performed the best in recent years. Unfortunately, that means that in the backtest, portfolios weighted heavily toward overvalued assets tend to do the best, but are set up for poor returns going forward.

The backtest tool is most useful for testing different portfolios under different market conditions. Pick some time periods that have had high inflation, deflation, market crashes, etc. You'd like your portfolio to perform reasonably well under the various market conditions, rather than just the most recent.
The Espresso portfolio: | | 16% LCV, 16% SCV, 16% EM, 8% Int'l Value, 8% Int'l Sm, 8% US REIT, 8% Int'l REIT, 20% Inter-term US Treas | | "A journey of a thousand miles begins with a single step."

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

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by siamond » Fri Oct 06, 2017 3:11 pm

Snarfanio wrote:
Fri Oct 06, 2017 1:54 pm
I noticed that for SCV the Vanguard return data, when available, is for the Investor class. Maybe you should consider using the Admiral class?
Yes, this is a historical thing, the Simba spreadsheet has always used Investor class funds (and ER). I am not fully enthused about that, but I am not too eager to break continuity either... Adjust the results upwards by 0.10% a year if you wish...

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

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by siamond » Fri Oct 06, 2017 4:11 pm

PS. you're welcome to ask clarification questions on this thread, but please use the 'Simba mega thread' for improvement suggestions.

User avatar
Kevin M
Posts: 8879
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Simba's Backtesting Spreadsheet: how useful is it?

Post by Kevin M » Sat Oct 07, 2017 1:34 pm

siamond wrote:
Fri Oct 06, 2017 3:11 pm
Snarfanio wrote:
Fri Oct 06, 2017 1:54 pm
I noticed that for SCV the Vanguard return data, when available, is for the Investor class. Maybe you should consider using the Admiral class?
Yes, this is a historical thing, the Simba spreadsheet has always used Investor class funds (and ER). I am not fully enthused about that, but I am not too eager to break continuity either... Adjust the results upwards by 0.10% a year if you wish...
The investor class shares generally have a longer history, which is a reason in itself to use them.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

Post Reply