Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
siamond
Posts: 3426
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Mon Jul 03, 2017 11:05 pm

LadyGeek wrote:Today, I am reviewing the spreadsheet in LibreOffice Calc in Linux.

In README "To Do" "- Check/update BRSIX ER once mid-year report is available". I checked.
I meant the 2017 mid-year report (not yet available). As the 0.75% ER value comes from the 2016 mid-year report. It is also on the factsheet you identified below, with the rather intriguing statement explaining the difference between 0.84% and 0.75%... I assumed 0.75% was the right value, although I am not entirely sure...

*As stated in the current prospectus, the annual gross operating expense ratio is 0.84%. The Fund's adviser has agreed to contractually waive a portion of its fees and/or reimburse expenses such that total operating expenses do not exceed 0.75%. Acquired fund fees and expenses are not paid directly by the Funds and are not included in the 0.75% expense limitation. Any material change to this policy would require a vote by shareholders.
LadyGeek wrote:The website's factsheet is dated 03/31/2017 ("Literature" tab). This is not the mid-year report, but please double-check your asset class - Small-Cap Blend is listed. Data_TR_USD tab, cell CC18 lists BRSIX as "Micro Cap". It could be a difference between your definition of "Micro Cap" vs. Morningstar's definition.
BRSIX is definitely Micro Cap (the name of the fund is 'Bridgeway Ultra Small Company Market Fund'), I believe this is the oldest micro cap fund in existence, actually, which is why the original Simba designers chose it. Yes, the Morningstar 3x3 matrix isn't subtle enough to show micro caps, so they approximated it to small cap, but this is not what it really is.
LadyGeek wrote:The BRSIX asset class in L1 is "D10", but does not match the asset class name in CC18 - currently as Micro Cap. I don't know what D10 means, but it should probably match the asset class name.
D10 means (CRSP) Decile 10, which is one possible definition of micro caps (and the way we approximated it for the pre-BRSIX years). You're right though, this abbreviation is cryptic, not necessarily fully accurate, and serves no purpose. I replaced it by Micro Cap.
LadyGeek wrote:The notes for cells A3 (ER) and A4 (YoI) mention Vanguard funds. I recommend removing "Vanguard" as several of the funds in this sheet are not Vanguard.
Good catch. Fixed.
LadyGeek wrote:Lazy_Portfolios tab - In the "Risk vs. Returns (nominal)" chart, the large number of data series results in white colored markers for several funds. The legend background is also white, which makes it impossible to identify the funds. Can you change the legend background to gray (but not too dark)? Otherwise, I cannot see the marker identifications.
Yes, I am aware of the first issue. I had logged it in the "Known issues with Libre Office" list in the README tab. Last time I looked, I didn't find an easy solution. I followed your advice for the legend background, using something more colorful, this does help.

I fixed all the other issues, and pushed the update to Google Drive. Thanks again for the eagle eye!

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

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

Post by siamond » Tue Jul 04, 2017 3:05 pm

siamond wrote:Mid-year update completed! You will find the latest spreadsheet (v16d) in the usual post:
viewtopic.php?f=10&t=2520&p=2753812#p2753812

A few highlights about the changes:
- added a glossary section
- updated Expenses Ratios (most are now lower, thank you, Vanguard)
- updated US historical numbers based on Tyler's latest Stock Index Calculator

As usual, feedback welcome.
Bump! I processed all feedback provided by Ladygeek (thank you!), and uploaded the very latest to the same place. Enjoy.

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

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

Post by LadyGeek » Wed Jul 05, 2017 7:27 pm

^^^ The changes incorporated in the latest version are in addition to my comments provided in the previous post.

In the "README" worksheet:
- The instructions have been formatted to improve readability.
- The glossary is now in alphabetical order.

We've added a definition for "backtesting".
Using historical data to predict future performance. See “Terms of Use and Disclaimer”, notably that past performance does not predict future performance.
It is critical that new investors understand the nature of this analysis. Compare the definition:

- "Using historical data to predict future performance"

with the perennially true axiom:

- "Past performance does not predict future performance."

You should not depend on this spreadsheet as the sole input for retirement planning.

For the rest of the retirement planning picture (beyond your portfolio), here's a good spreadsheet: Retiree Portfolio Model
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.

AlohaJoe
Posts: 2550
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe » Fri Jul 21, 2017 3:29 am

Since many in this thread are interested in historical stock information, here's a new (July 2017) source.
This paper presents new monthly capital gain, dividend yield, and total return indices for common equities quoted on British exchanges during 1869-1929. I construct indices for 25 domestic sectors, calculate capital asset pricing model betas for each sector, and construct a 30-stock blue chip index. I splice the new broad market index to Turner et al.'s (2009) pre-1870 index to create a century-long (1825-1929) monthly equity index. I use the new indices to examine the timing of British business cycles and compare the returns on home and foreign UK investment during 1870-1929.
https://papers.ssrn.com/sol3/papers.cfm ... id=2996666

The paper is from CEPR and, unfortunately, I haven't found a free version of it yet.

sean.mcgrath
Posts: 104
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

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

Post by sean.mcgrath » Mon Jul 24, 2017 3:27 am

AlohaJoe wrote:Since many in this thread are interested in historical stock information, here's a new (July 2017) source.
This paper presents new monthly capital gain, dividend yield, and total return indices for common equities quoted on British exchanges during 1869-1929. I construct indices for 25 domestic sectors, calculate capital asset pricing model betas for each sector, and construct a 30-stock blue chip index. I splice the new broad market index to Turner et al.'s (2009) pre-1870 index to create a century-long (1825-1929) monthly equity index. I use the new indices to examine the timing of British business cycles and compare the returns on home and foreign UK investment during 1870-1929.
https://papers.ssrn.com/sol3/papers.cfm ... id=2996666

The paper is from CEPR and, unfortunately, I haven't found a free version of it yet.
Hi Joe,

Here's a link to a free source. It's from the author's University.

cheers,
Sean

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

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

Post by LadyGeek » Mon Jul 31, 2017 7:26 pm

Has anyone performed backtesting against a robo-adviser portfolio?

We've got a new wiki page: Robo-adviser

In the support thread, jbolden1517 is claiming that a 3-fund portfolio does not follow Modern Portfolio Theory. He is also claiming the robo-adviser portfolio will have a 200 bps average gain over a 3-fund portfolio over a 10 year period. See: Re: What we know about robo advisors

I disagree and think that backtesting would tend to prove / disprove the claim - at least from a historical performance perspective.
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
Taylor Larimore
Advisory Board
Posts: 26115
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

SEC Investor Alert

Post by Taylor Larimore » Mon Jul 31, 2017 9:23 pm

LadyGeek wrote:
In the support thread, jbolden1517 is claiming that a 3-fund portfolio does not follow Modern Portfolio Theory. He is also claiming the robo-adviser portfolio will have a 200 bps average gain over a 3-fund portfolio over a 10 year period. See: Re: What we know about robo advisors

I disagree and think that backtesting would tend to prove / disprove the claim - at least from a historical performance perspective.
LadyGeek:

I believe you are correct. SEC Investor Alert: "Automated Investment Tools" contains this statement:

"Investors should be wary of tools that promise better portfolio performance."

Best wishes.
Taylor
"Simplicity is the master key to financial success." -- Jack Bogle

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

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

Post by LadyGeek » Tue Aug 01, 2017 8:38 pm

LadyGeek wrote:Has anyone performed backtesting against a robo-adviser portfolio?

We've got a new wiki page: Robo-adviser

In the support thread, jbolden1517 is claiming that a 3-fund portfolio does not follow Modern Portfolio Theory. He is also claiming the robo-adviser portfolio will have a 200 bps average gain over a 3-fund portfolio over a 10 year period. See: Re: What we know about robo advisors

I disagree and think that backtesting would tend to prove / disprove the claim - at least from a historical performance perspective.
siamond has run an analysis. See: Backtesting some robo-advisers asset allocations
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: 3426
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Mon Sep 04, 2017 11:39 am

Here is the latest update of the Simba backtesting spreadsheet:
https://drive.google.com/open?id=0B0svR ... GVIYlBZR2s

Rev16e:
1a. Added new Dividend Growth (Vanguard VDAIX); extended with S&P 500 Aristocrats index
1b. Added new High Dividend Yield (Vanguard VHDYX) data series; extended with MSCI USA High Dividend Yield index
2. Added new factor data series based on iShares ETFs similar to existing MTUM data series: Quality (iShares QUAL), Min Volatility (iShares USMV), Value (iShares VLUE); extended with corresponding MSCI indices
3. Improved growth/telltale charts to show a data label at the end of each line, and to auto-adjust the chart title (growth vs. telltale chart); also added data labels to column charts in Lazy_Portfolios

PS. for reference, we had a solid discussion about dividends data series (and also factors) here. We also had a good discussion about telltale charts and data labels here.

User avatar
nedsaid
Posts: 8708
Joined: Fri Nov 23, 2012 12:33 pm

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

Post by nedsaid » Mon Sep 04, 2017 12:57 pm

Thank you Siamond for your excellent work.
A fool and his money are good for business.

User avatar
TD2626
Posts: 519
Joined: Thu Mar 16, 2017 3:40 pm

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

Post by TD2626 » Tue Sep 05, 2017 12:15 pm

nedsaid wrote:
Mon Sep 04, 2017 12:57 pm
Thank you Siamond for your excellent work.
+1

These spreadsheets have been very helpful to me - they are a great community project. Thank you to all who worked to develop them.

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

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

Post by siamond » Wed Sep 06, 2017 3:27 pm

TD2626 wrote:
Tue Sep 05, 2017 12:15 pm
These spreadsheets have been very helpful to me - they are a great community project. Thank you to all who worked to develop them.
Thank you, it's encouraging to have such positive feedback, appreciated. Feel free to share any idea for improvement.

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

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

Post by siamond » Wed Sep 06, 2017 3:28 pm

From another thread...
Simplegift wrote:
Wed Sep 06, 2017 12:35 pm
Like so many aspects of the global market, it does appear that emerging markets' outperformance goes in cycles (chart below).
You made me ponder if we can easily create a similar chart with the Simba spreadsheet. One has to add a derived data series to compute EM minus S&P500 returns, but that is pretty trivial to do (I used a geometric difference). We already have a rolling returns chart, but then we don't have 3-years rolling returns (only 1-year, 5-years, 10-years, 15-years), but it was easy to extend the existing logic. Here is the outcome (I disabled the 15-years display, for clarity). It's based on annual returns, hence less granular that the chart you posted.

Image

Now this raises an interesting point. The Simba spreadsheet computes rolling returns FORWARD (N years starting from the year on the X axis). Quite obviously, the chart you posted computed rolling returns BACKWARDS (N years up to the current point). And although this is arbitrary, for whatever reason, the latter way seems more intuitive to me. Do you think I should fix that?

I also wonder if we should ditch the 1-year period, and only propose 3/5/10/15 years? And maybe we could automate a bit further the analysis of the premium of a given portfolio (here EM) against a portfolio of reference (here S&P 500)?

User avatar
SimpleGift
Posts: 2614
Joined: Tue Feb 08, 2011 3:45 pm
Location: Central Oregon

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

Post by SimpleGift » Wed Sep 06, 2017 3:33 pm

And I'll re-post my reply from the other thread, siamond, since this is the thread where this discussion belongs.
siamond wrote:
Wed Sep 06, 2017 2:10 pm
Now this raises an interesting point. The Simba spreadsheet computes rolling returns FORWARD (N years starting from the year on the X axis). Quite obviously, the chart you posted computed rolling returns BACKWARDS (N years up to the current point). And although this is arbitrary, for whatever reason, the latter way seems more intuitive to me. Do you think I should fix that?

I also wonder if we should ditch the 1-year period, and only propose 3/5/10/15 years? And maybe we could automate a bit further the analysis of the premium of a given portfolio (here EM) against a portfolio of reference (here S&P 500)?
Actually, the rolling returns chart you prepared is quite impressive. I don’t always think of using the Simba spreadsheet, but it obviously works nicely for this type of comparative analysis. Not sure whether the forward or backwards method makes much difference, as long as the relative performances are clear over time.

For what it's worth, most of these comparison charts (domestic vs. international, value vs. growth, etc.) use 3-year rolling returns. See Figure 1 in this article. Looking at the chart you prepared, I think either 3-year or 5-year rolling returns work fine, but the 3-year appears most explanatory and clear, to my eye. Not sure the 10-year adds much. Just my two cents.
Cordially, Todd

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

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

Post by siamond » Wed Sep 06, 2017 3:39 pm

Good point about the typical comparison charts. Maybe we could actually switch to 1-year, 3-years, 5-years, 10-years rolling returns (each of them individually selectable in the spreadsheet). 15 is kind of a weird number, plus most data series have only a few decades of data available, which makes 15-years rolling returns barely usable. I do see the value of keeping a longer-term view of 10-years, biz cycles are more or less in line with that (cf. Prof. Shiller's choice with CAPE==PE10), and various other cycles appear slower than 5 years periods. Feedback?

I just quickly prototyped the idea of allowing a choice between analyzing the target portfolio, or the premium between the analyzed portfolio and the reference portfolio, and this is quite easy to do. I always struggled a bit with the limited value-added of the Analyze_Portfolio sheet (as opposed to the Compare_Portfolio sheet), and adding more premium analysis could be an answer. I could then also add more stats about such premium in the tables above. Seems quite valuable to me. Feedback?

I am still torn on the idea of backward-looking returns vs. forward-looking returns. Views?

User avatar
SimpleGift
Posts: 2614
Joined: Tue Feb 08, 2011 3:45 pm
Location: Central Oregon

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

Post by SimpleGift » Wed Sep 06, 2017 3:45 pm

^^^ The 1-year, 3-year, 5-year and 10-year idea sounds like a fine one. I didn’t actually realize you were proposing to give folks a choice — but if it’s as easy to implement as you suggest, then wonderful.
Cordially, Todd

dcabler
Posts: 290
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Wed Sep 06, 2017 3:52 pm

siamond wrote:
Wed Sep 06, 2017 3:28 pm
From another thread...
Simplegift wrote:
Wed Sep 06, 2017 12:35 pm
Like so many aspects of the global market, it does appear that emerging markets' outperformance goes in cycles (chart below).
You made me ponder if we can easily create a similar chart with the Simba spreadsheet. One has to add a derived data series to compute EM minus S&P500 returns, but that is pretty trivial to do (I used a geometric difference). We already have a rolling returns chart, but then we don't have 3-years rolling returns (only 1-year, 5-years, 10-years, 15-years), but it was easy to extend the existing logic. Here is the outcome (I disabled the 15-years display, for clarity). It's based on annual returns, hence less granular that the chart you posted.

Image

Now this raises an interesting point. The Simba spreadsheet computes rolling returns FORWARD (N years starting from the year on the X axis). Quite obviously, the chart you posted computed rolling returns BACKWARDS (N years up to the current point). And although this is arbitrary, for whatever reason, the latter way seems more intuitive to me. Do you think I should fix that?

I also wonder if we should ditch the 1-year period, and only propose 3/5/10/15 years? And maybe we could automate a bit further the analysis of the premium of a given portfolio (here EM) against a portfolio of reference (here S&P 500)?
The answer to the question "Do you think I should fix that?". My input is yes. It's how I do it in all of my own hand-rolled spreadsheets for the reason you stated - it seems more intuitive to me.

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

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

Post by siamond » Fri Sep 08, 2017 12:38 pm

dcabler wrote:
Wed Sep 06, 2017 3:52 pm
siamond wrote:
Wed Sep 06, 2017 3:28 pm
The Simba spreadsheet computes rolling returns FORWARD (N years starting from the year on the X axis). Quite obviously, the chart you posted computed rolling returns BACKWARDS (N years up to the current point). And although this is arbitrary, for whatever reason, the latter way seems more intuitive to me. Do you think I should fix that?
The answer to the question "Do you think I should fix that?". My input is yes. It's how I do it in all of my own hand-rolled spreadsheets for the reason you stated - it seems more intuitive to me.
This is really an arbitrary decision, so I was wondering what is the typical practice, and discovered that the usual growth charts from Morningstar can easily be switched to a rolling returns representation (didn't know that!). Here is an example for VFINX (and the S&P 500 index), and quite clearly those are forward-looking rolling returns.

Then I looked at PortfolioCharts.com (I always highly respect the usability decisions from the author), and same thing, forward-looking rolling returns: https://portfoliocharts.com/portfolio/rolling-returns. I tried looking at Yahoo Finance and Google Finance, but couldn't find a way to generate a rolling returns chart (am I missing something?).

On the other hand, most of the financial sites provide a way to display moving averages (which are conceptually similar to rolling returns), which are typically displayed backwards-looking. Hmpf.

At this point, I am tempted to leave things as they are, staying consistent with Morningstar and PortfolioCharts sounds good - and that's less work for me! :wink: More thoughts welcome though.

PS. I will definitely do the other improvements though, the option to focus on the return premiums, better chart titles, and 1/3/5/10 time periods.

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

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

Post by Kevin M » Fri Sep 08, 2017 1:12 pm

FWIW, Portfolio Visualizer uses trailing returns for asset class and portfolio backtest rolling return charts.
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
LadyGeek
Site Admin
Posts: 41020
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 6:03 pm

New member Snarfanio has a question. See: Simba's Backtesting Spreadsheet: how useful is it?
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: 3426
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Thu Oct 05, 2017 8:20 pm

Thanks, Ladygeek, Snarfanio's question was answered by Tyler and myself on the corresponding thread.

About the previous posts, for the record, I ruled out changing the rolling charts methodology, I'd rather stay consistent with Morningstar and PortfolioCharts. Even if PortfolioVisualizer made another choice... This is all arbitrary in truth. I did implement the other changes in my working 'N+1' version, but will wait for more significant updates to officialize.

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

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

Post by Snarfanio » Mon Oct 09, 2017 1:03 pm

After looking more into this spreadsheet, I have a couple of questions/observations.
1) For Int'l Developed EAFE, VTMGX Admiral is used. Why not the Investor version, which is used for all other Vanguard funds in the spreadsheet?
2) When I calculate myself the CAGR and STDEV of SCV for 1927 through 2016, using the data in Data_TR_USD, I get different results than when I use the Analyze_Portfolio sheet; 12.66% CAGR vs. 12.49% and 26.44% STDEV vs. 26.39%.

I'd appreciate some feedback.

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

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

Post by Snarfanio » Tue Oct 10, 2017 12:39 pm

Siamond,

CAGR and STDEV still don't match when I subtract the ER of 0.19% for the years 1927 through 1998 (1998 being the year of inception of the real world fund).
So, was the ER not discontinued in the correct year? Mmmmm. To test that, I calculated myself CAGR and STDEV for the first 10 years, 1927 through 1936, a period when the ER would have been used for sure and compared the results to those of the Analyze_Portfolio route. Both CAGR and STDEV are closer, but not the same: CAGR 8.3872% vs. 8.3887% and STDEV 48.16% vs. 48.07%. However, for the years 2007 through 2016, I get a perfect match. What gives?

Regards, Sergio.
Last edited by Snarfanio on Tue Oct 10, 2017 1:16 pm, edited 2 times in total.

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

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

Post by Tyler9000 » Tue Oct 10, 2017 1:02 pm

Snarfanio wrote:
Tue Oct 10, 2017 12:39 pm
Siamond,

CAGR and STDEV still don't match when I subtract the ER of 0.19% for the years 1927 through 2016 (1998 being the year of inception of the real world fund). What gives?

Regards, Sergio.
I haven't verified your calculations personally, but I suspect it has to do with how you're accounting for the ER.

Note that:

1) The Data_TR_USD sheet has two sections for returns. The one starting in row 183 is the raw returns, while the one starting on row 6 adjusts those returns for the ER.

2) It does not simply subtract the ER. It uses the equation (return-ER)/(1+ER).

3) It only adjusts for the ER for years before the Vanguard fund existed and had a full year of returns to report (starting in 1999 for SCV). The ER is already baked into the VG numbers.

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

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

Post by siamond » Tue Oct 10, 2017 1:56 pm

Snarfanio wrote:
Mon Oct 09, 2017 1:03 pm
After looking more into this spreadsheet, I have a couple of questions/observations.
1) For Int'l Developed EAFE, VTMGX Admiral is used. Why not the Investor version, which is used for all other Vanguard funds in the spreadsheet
Yes, that's a good point. VTMGX (the admiral fund) has more history than VDVIX (the investor fund), the former was created in 1999 while the latter was only created in 2013, for a reason that totally escapes me. As Kevin mentioned on another thread, usually the investor fund has more history, but in this precise case, it's the reverse way around, so we used VTMGX to maximize the use of real-life data. I guess I could adjust the raw returns of VTMGX with the ER difference between the two (0.17-0.07), and then use 0.17% for the pre-inception days. Bugs me a bit to make such an exception, but this would certainly be more consistent.
=> unless somebody has a good reason to NOT do such change, I'll take a note to do it for the next version.

PS. note that we have a few other inconsistencies like that, we have a few iShares ETFs in there, and well, ETF fees are typically aligned with Admiral fees, so we have a similar issue. This being said, there is no such thing as an Admiral fund or an Investor fund for iShares, plus ETFs have their own hidden costs, so I'd rather stick to their real-life numbers than trying to tweak them around.
Snarfanio wrote:
Mon Oct 09, 2017 1:03 pm
2) When I calculate myself the CAGR and STDEV of SCV for 1927 through 2016, using the data in Data_TR_USD, I get different results than when I use the Analyze_Portfolio sheet; 12.66% CAGR vs. 12.49% and 26.44% STDEV vs. 26.39%.
If you use this formula in Data_TR_USD, you'll get 12.66% for CAGR: {=GEOMEAN(1+I64:I153%)-1}
If you use this formula in Data_TR_USD, you'll get 12.49% for CAGR: {=GEOMEAN(1+I241:I330%)-1}

The first formula (which is equivalent to what Analyze_Portfolio does) uses ER-adjusted returns as Tyler explained, geometrically adjusting for the ER for the years before VTMGX's inception. While the second formula (which doesn't convey meaningful semantics) acts on the raw data we collected. The trick is that the raw data AFTER VTMGX's inception is already ER-adjusted (this is how Vanguard reports total returns). While the older raw data (pre-inception) is based on indices and synthetic models, which is not ER-adjusted, and needs to be so that we have a consistent model to analyze.

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

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

Post by Snarfanio » Tue Oct 10, 2017 2:03 pm

Tyler and Siamond,

Thanks for your replies. In my calculations I deducted ER, but when I use ER-adjusted Return = (Raw Return-ER)/(1+Raw Return), the numbers match perfectly. Problem solved.

I understand why you used VTMGX Admiral for Int'l Developed EAFE. Maybe Vanguard wanted to start with larger minimums to keep costs down and when things got moving they opened it up to smaller minimums.

Regards, Sergio.

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

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

Post by siamond » Tue Oct 10, 2017 3:39 pm

siamond wrote:
Tue Oct 10, 2017 1:56 pm
Snarfanio wrote:
Mon Oct 09, 2017 1:03 pm
1) For Int'l Developed EAFE, VTMGX Admiral is used. Why not the Investor version, which is used for all other Vanguard funds in the spreadsheet
Yes, that's a good point. VTMGX (the admiral fund) has more history than VDVIX (the investor fund), the former was created in 1999 while the latter was only created in 2013, for a reason that totally escapes me. As Kevin mentioned on another thread, usually the investor fund has more history, but in this precise case, it's the reverse way around, so we used VTMGX to maximize the use of real-life data. I guess I could adjust the raw returns of VTMGX with the ER difference between the two (0.17-0.07), and then use 0.17% for the pre-inception days. Bugs me a bit to make such an exception, but this would certainly be more consistent.
=> unless somebody has a good reason to NOT do such change, I'll take a note to do it for the next version.
Could I get more feedback about this possible change? I would use the VDVIX returns when available, and then do the ER adjustment for the older numbers. I would do the change with the January 2018 update.

I do like the idea, as otherwise, this misrepresents the past returns by 0.10% since 1970, and creates a clear inconsistency with similar funds being tracked like VGTSX (Total Int'l)... As a reminder, VTMGX is EAFE Developed.

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

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

Post by stlutz » Tue Oct 10, 2017 6:39 pm

Could I get more feedback about this possible change?
Your approach makes complete sense to me...

User avatar
Barry Barnitz
Wiki Admin
Posts: 2971
Joined: Mon Feb 19, 2007 10:42 pm
Contact:

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

Post by Barry Barnitz » Tue Oct 10, 2017 7:24 pm

Hi Siamond:

The confusion regarding the Developed Market Index involves the merger of the former Developed Market fund into the Tax-Managed Fund, which existed with admiral shares.

We tagged this on the wiki tax history page for the former fund----> Vanguard Developed Markets Index Fund (VDMIX) tax distributions and on the like page for the post-merger fund ---> Vanguard Developed Markets Index Fund (VDVIX) tax distributions. We attempt to track the expense history of the merged fund on this page---> Vanguard Developed Markets Index Fund (VDVIX) expenses.

regards,
Image | blb | December Birthday Celebration: Ludwig van Beethoven

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

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

Post by siamond » Tue Oct 10, 2017 9:58 pm

Barry Barnitz wrote:
Tue Oct 10, 2017 7:24 pm
We tagged this on the wiki tax history page for the former fund [...]
Ah, thank you Barry, I vaguely remembered that there was more to it, but couldn't put my finger on it...

VTMGX has history since its inception in 1999. Your note 3 on the VDVIX page would seem to invalidate my proposal. You seem to imply that VTMGX only had investor shares until May 2011, so the historical returns from 2000 to 2010 would match an investor shares type of fund. And then it gets muddy, because VTMGX would have been recategorized as admiral shares in May 2011 (the sudden drop of the ER to 0.12% does seem consistent with such scenario), and near the time of the merger of funds, a new investor class fund (VDVIX) was created (Dec-13), leaving a hole of ~ three years without an investor class fund. Did I get that right? Do you have any reference for the May 2011 reclassification event?

If that is correct, then I should use VTMGX as is from 2000 to 2010, use VTMGX + 0.10% for 2011 to 2013, then use VDVIX for 2014+. And use the VDVIX ER for the pre-inception time (which is based on the MSCI EAFE NR USD index data series). Seems more accurate, but a tad clunky...

===========
Let's use another data point. I checked the historical ER for VGTSX and for VTMGX. This definitely seems to confirm that VTMGX in the old days was indeed an investor shares kind of fund.

Code: Select all

		VGTSX	VTMGX
12/31/98	0.34	0.35
12/31/99	0.34	0.35
12/31/00	0.34	0.35
10/31/01	0.35	0.35
10/31/02	0.37	0.31
10/31/03	0.36	0.28
10/31/04	0.31	0.23
10/31/05	0.31	0.20
10/31/06	0.32	0.20
10/31/07	0.27	0.15
10/31/08	0.28	0.15
10/31/09	0.32	0.20
10/31/10	0.26	0.18
10/31/11	0.22	0.12
10/31/12	0.22	0.10
10/31/13	0.22	0.09
10/31/14	0.22	0.09
10/31/15	0.19	0.09
10/31/16	0.18	0.07

User avatar
Barry Barnitz
Wiki Admin
Posts: 2971
Joined: Mon Feb 19, 2007 10:42 pm
Contact:

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

Post by Barry Barnitz » Wed Oct 11, 2017 3:23 am

Hi Siamond:
Do you have any reference for the May 2011 reclassification event?
The reference for this can be found in the fund's annual reports that include 2011 and 2012 financial highlights. The latest 2016 report includes the following direct quote:
The fund's original Investor Shares were renamed Admiral Shares as of the close of business on May 13, 2011.
regards,
Image | blb | December Birthday Celebration: Ludwig van Beethoven

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

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

Post by siamond » Sat Oct 14, 2017 12:50 pm

Here is the full quote from the VTMGX 2016 Shareholder Report, which fully confirms the timeline I had derived from Barry's description on the wiki pages:
The fund’s original Investor Shares were renamed Admiral Shares as of the close of business on May 13, 2011. The Investor Shares
recommenced on December 19, 2013, and are presented separately.
'Presented separately' means the inception of VDVIX.

The 2011 Certified Shareholder Report (Semi-Annual) doesn't provide more explanations, but the salient point is described in two places:
I want to note two important changes we’ve made that will benefit shareholders in the Tax-Managed Funds. First, Vanguard has replaced each fund’s Investor Shares with lower-cost Admiral Shares. In addition, for the four funds in this report, we have eliminated the 1% fee for redemptions of shares held for less than five years. Both changes are part of our ongoing efforts to reduce the cost and complexity of investing for Vanguard clients.
The fund offers two classes of shares: Admiral Shares and Institutional Shares. Admiral Shares are available to any investor who meets the fund’s minimum purchase requirements. Institutional Shares are designed for investors who meet certain administrative, service, and account-size criteria. Prior to May 14, 2011, the fund offered Investor Shares. Effective at the close of business on May 13, 2011, the Investor Shares were converted to Admiral Shares.
Ok, so I'll introduce the following change, as discussed in this post for the first 2018 update of the Simba spreadsheet:
[...] I should use VTMGX as is from 2000 to 2010, use VTMGX + 0.10% for 2011 to 2013, then use VDVIX for 2014+. And use the VDVIX ER for the pre-inception time (which is based on the MSCI EAFE NR USD index data series).

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

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

Post by siamond » Sat Oct 14, 2017 5:45 pm

Probably the last update for 2017, v16f is now available. You can download here.

This mostly addresses the rolling returns topics agreed upon in previous posts (allowing to display premiums; time periods 1/3/5/10 years - see here). Other changes are minor. All details in this post.

If a couple of users of the spreadsheet could run a sanity test, this would be appreciated.

User avatar
TD2626
Posts: 519
Joined: Thu Mar 16, 2017 3:40 pm

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

Post by TD2626 » Sat Oct 28, 2017 8:04 pm

A quick question and a possible minor bug fix:

-What is the Sharpe Ratio of Cash? (*Note - in this post's context, cash is something earning 0% interest and having 0 standard deviation. In most other contexts those who say "cash" mean money markets or a high-yield savings account, which earns close to the risk free rate.)

-On the Data_TR_USD sheet, Column BY, Row 160, it says that the sharpe ratio of cash is -348989.40

-Whatever the correct answer as to what the sharpe ratio of cash is, -300,000 is probably not the correct answer. Note that BY167 and BY176 also show (nonsensical, in the -100,000 range) Sharpe ratios for cash.


I've been looking into things a bit more, and it seems the answer might be zero (as suggested here and here). Both articles specifically say it would be zero. Other options include "N/A" and "Negative (or positive) Infinity".

Philosophically, since Sharpe Ratio is excess return divided by standard deviation, and excess return (return - risk free rate) is negative for cash, and standard deviation is zero (for cash earning 0% interest), would this be a case of ~(0%-2%)/0% =-infinity?

I think unless we know anything for certain, it would probably be best to display a blank, or a "N/A". Indeed, the mechanics of displaying an N/A are built into the formula.

In Cell BY160 on the Data_TR_USD sheet, it says =IF(BY157="", "", IF(ROUND(BY159,5)=0, NA(), ((AVERAGE(BY$8:BY$154))-(AVERAGE($Z$8:$Z$154)))/BY159)). The Round(BY159,5)=0,NA() essentially means "if the Standard deviation is zero, the Sharpe ratio is zero".

So I investigated the issue further. If you increase the number of decimal places displayed on cash's standard deviation (Cell BY159), you get a standard deviation that is very, very small - but nonzero. This is because, it turns out, the returns on zero-return cash were -0.0001 in 2014 and 0.0001 in 2015.

Maybe I am missing something here, and the spreadsheet is incredibly complex -- maybe there is some reason why the spreadsheet has to do it this way. It's obviously a minor bug at worst, so there's no real need to make any quick fixes.

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

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

Post by siamond » Sat Oct 28, 2017 8:28 pm

TD2626 wrote:
Sat Oct 28, 2017 8:04 pm
So I investigated the issue further. If you increase the number of decimal places displayed on cash's standard deviation (Cell BY159), you get a standard deviation that is very, very small - but nonzero. This is because, it turns out, the returns on zero-return cash were -0.0001 in 2014 and 0.0001 in 2015.

Maybe I am missing something here, and the spreadsheet is incredibly complex -- maybe there is some reason why the spreadsheet has to do it this way. It's obviously a minor bug at worst, so there's no real need to make any quick fixes.
Great sleuthing. When I introduced hard cash as an asset class (which proved very convenient to unify growth and telltale charts among other possible uses), this gave me headaches with std-deviation and correlation formulas all over the place. And I introduced this not-so-great trick with the -0.0001 and 0.0001 returns. Since then, I reorganized and streamlined the spreadsheet, and made those formulas more robust (e.g. the ROUND to 5 decimals you noticed) as I addressed other extreme cases, and didn't think to come back to the cash asset class.

I just gave it a try replacing the -0.0001 and 0.0001 by the rightful zeroes, and it seems to work pretty good. I noticed a couple of slightly unsatisfying displays, but they shouldn't be hard to fix. Thank you, very good point, I'll take care of it for the next release (at some point in Jan-18 with the 2017 returns).

User avatar
TD2626
Posts: 519
Joined: Thu Mar 16, 2017 3:40 pm

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

Post by TD2626 » Sun Oct 29, 2017 12:13 am

siamond wrote:
Sat Oct 28, 2017 8:28 pm
TD2626 wrote:
Sat Oct 28, 2017 8:04 pm
So I investigated the issue further. If you increase the number of decimal places displayed on cash's standard deviation (Cell BY159), you get a standard deviation that is very, very small - but nonzero. This is because, it turns out, the returns on zero-return cash were -0.0001 in 2014 and 0.0001 in 2015.

Maybe I am missing something here, and the spreadsheet is incredibly complex -- maybe there is some reason why the spreadsheet has to do it this way. It's obviously a minor bug at worst, so there's no real need to make any quick fixes.
Great sleuthing. When I introduced hard cash as an asset class (which proved very convenient to unify growth and telltale charts among other possible uses), this gave me headaches with std-deviation and correlation formulas all over the place. And I introduced this not-so-great trick with the -0.0001 and 0.0001 returns. Since then, I reorganized and streamlined the spreadsheet, and made those formulas more robust (e.g. the ROUND to 5 decimals you noticed) as I addressed other extreme cases, and didn't think to come back to the cash asset class.

I just gave it a try replacing the -0.0001 and 0.0001 by the rightful zeroes, and it seems to work pretty good. I noticed a couple of slightly unsatisfying displays, but they shouldn't be hard to fix. Thank you, very good point, I'll take care of it for the next release (at some point in Jan-18 with the 2017 returns).
Thanks - and no need to take care of it anytime soon - it's a minor bug. Sorry to be so nitpicky about what's really such a minor detail.

Also, the spreadsheet overall has been quite helpful. I especially like it because of the very long datasets available (Portfolio Visualizer doesn't go back to the 1800s) and the detailed graphs. (Clearly, I've been using it a good bit to see something minor like this). Thanks for all your work in maintaining it!

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

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

Post by mjb49 » Sun Oct 29, 2017 7:07 am

I enjoy using this spreadsheet. Thanks to all involved for their efforts. When using the spreadsheet to compare the Lazy Portfolios I change the equities to fixed ratios to match my portfolio. Is there an argument against doing this approach to compare portfolios?

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

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

Post by siamond » Sun Oct 29, 2017 7:21 am

mjb49 wrote:
Sun Oct 29, 2017 7:07 am
When using the spreadsheet to compare the Lazy Portfolios I change the equities to fixed ratios to match my portfolio. Is there an argument against doing this approach to compare portfolios?
First off, you're absolutely right, the portfolio definitions and allocations in the Lazy_Portfolios tab can be customized at will. They don't even have to be 'lazy' (as in predefined by an author or another) portfolios. This is a general tool to systematically compare numerous portfolios. One possible application is to forget about 'lazy' definitions, use the same portfolio style in all columns, vary the equity allocation by 5%, and see what goes. Or vary the % of international equities. Etc.

What I am trying to say is that your approach is perfectly fine when fulfilling a specific goal (set up a top-level AA, vary the portfolio components using multiple portfolio 'styles'). Now other people may have other goals and would keep the same portfolio style and vary the top-level AA. Or maybe they'd want to compare different styles AND components, and optimize on a given risk metric. It's all good!

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

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

Post by siamond » Sun Oct 29, 2017 3:10 pm

TD2626 wrote:
Sun Oct 29, 2017 12:13 am
Thanks - and no need to take care of it anytime soon - it's a minor bug. Sorry to be so nitpicky about what's really such a minor detail.
By all means, keep being nitpicky, I really appreciate the feedback. I actually took a closer look, and it seems that everything works fine in all sheets when I restore proper zeroes for cash returns, EXCEPT that the display is somewhat inconsistent when the math can't be done (e.g. because of a division by zero, or a really tiny number). In some cases, #N/A is displayed, in other cases, the cell is blanked. Sharpe, Sortino and correlations (Pearson) are the main issues at stake.

Any view on which way I should go to unify? I guess that #N/A is closer to the technical truth, but then when I see something like that, my first instinct is to think that the spreadsheet has a hiccup, while an empty cell looks more deliberate. Still, #N/A may make more sense?

User avatar
TD2626
Posts: 519
Joined: Thu Mar 16, 2017 3:40 pm

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

Post by TD2626 » Sun Oct 29, 2017 11:10 pm

siamond wrote:
Sun Oct 29, 2017 3:10 pm
TD2626 wrote:
Sun Oct 29, 2017 12:13 am
Thanks - and no need to take care of it anytime soon - it's a minor bug. Sorry to be so nitpicky about what's really such a minor detail.
By all means, keep being nitpicky, I really appreciate the feedback. I actually took a closer look, and it seems that everything works fine in all sheets when I restore proper zeroes for cash returns, EXCEPT that the display is somewhat inconsistent when the math can't be done (e.g. because of a division by zero, or a really tiny number). In some cases, #N/A is displayed, in other cases, the cell is blanked. Sharpe, Sortino and correlations (Pearson) are the main issues at stake.

Any view on which way I should go to unify? I guess that #N/A is closer to the technical truth, but then when I see something like that, my first instinct is to think that the spreadsheet has a hiccup, while an empty cell looks more deliberate. Still, #N/A may make more sense?
I'm not entirely sure what the best thing to put here is - or what the right answer is. What would the Sharpe ratio of a zero return, zero risk asset be? It's an interesting (philosophical) question.

N/A() seems to disrupt calculations (like taking an average of all Sharpe Ratios across row 160) more than leaving it blank does. Including some sort of text other than N/A() also doesn't seem to disrupt calculations either. So I'd prefer something other than N/A.

Maybe this sort of thing could be used (for the formula in Cell BY160 of Data_TR_USD):
=IF(BY157="", "", IF(ROUND(BY159,5)=0, "Sharpe Ratio Undefined", ((AVERAGE(BY$8:BY$154))-(AVERAGE($Z$8:$Z$154)))/BY159))

(Or insert some form of warning or notification text of your choosing in place of "Sharpe Ratio Undefined").

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

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

Post by siamond » Mon Oct 30, 2017 10:38 am

TD2626 wrote:
Sun Oct 29, 2017 11:10 pm
N/A() seems to disrupt calculations (like taking an average of all Sharpe Ratios across row 160) more than leaving it blank does. Including some sort of text other than N/A() also doesn't seem to disrupt calculations either. So I'd prefer something other than N/A.
You have an excellent point. Well, why not settle for the text "N/A" then, instead of the result of the function NA()?

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

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

Post by siamond » Mon Oct 30, 2017 3:56 pm

Since the change has no impact whatsoever on any regular scenario, I proceeded and updated the spreadsheet without changing the current version number (ah, the shame!), but I did change the date associated with the revision. I revisited all the error processing in all sheets, displaying a blank when appropriate (e.g. CAGR 1970+ for a data series starting in 1985) or a "N/A" text string when there is a real issue (e.g. Sharpe, Sortino, Pearson error and/or potential division by zero).

Here are a couple of ways to make the error processing apparent:
- define a portfolio as 100% cash
- look at a time period lasting a single year (e.g. 2016 to 2016)

TD2626, if you wouldn't mind checking the update with your eagle eye, and let me know if I missed something? Here is the updated spreadsheet:
https://drive.google.com/open?id=0B0svR ... zRpaURRS1U

User avatar
TD2626
Posts: 519
Joined: Thu Mar 16, 2017 3:40 pm

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

Post by TD2626 » Mon Oct 30, 2017 11:14 pm

siamond wrote:
Mon Oct 30, 2017 10:38 am
TD2626 wrote:
Sun Oct 29, 2017 11:10 pm
N/A() seems to disrupt calculations (like taking an average of all Sharpe Ratios across row 160) more than leaving it blank does. Including some sort of text other than N/A() also doesn't seem to disrupt calculations either. So I'd prefer something other than N/A.
You have an excellent point. Well, why not settle for the text "N/A" then, instead of the result of the function NA()?
I like the "N/A" string instead of the NA() function. Good call.
siamond wrote:
Mon Oct 30, 2017 3:56 pm
Since the change has no impact whatsoever on any regular scenario, I proceeded and updated the spreadsheet without changing the current version number (ah, the shame!), but I did change the date associated with the revision. I revisited all the error processing in all sheets, displaying a blank when appropriate (e.g. CAGR 1970+ for a data series starting in 1985) or a "N/A" text string when there is a real issue (e.g. Sharpe, Sortino, Pearson error and/or potential division by zero).

Here are a couple of ways to make the error processing apparent:
- define a portfolio as 100% cash
- look at a time period lasting a single year (e.g. 2016 to 2016)

TD2626, if you wouldn't mind checking the update with your eagle eye, and let me know if I missed something? Here is the updated spreadsheet:
https://drive.google.com/open?id=0B0svR ... zRpaURRS1U
I like the changes. The N/A looks good. Good idea of 100% cash portfolios and 1 year holding periods as edge cases to look at. Also consider looking at 2008 - 2008 instead of 2016 - 2016, as 2008 was a down year for nearly everything and many calculations are different if things are up vs if they are down.

I poked around and didn't see any glaring issues - this change seems to be a significant improvement, making the spreadsheet more robust to even those who put in "unusual" inputs. About the edge cases (100% cash and 1 year portfolios): There are some people who are legitimately 100% cash in zero-return checking accounts - and 100% cash is what people panic-sell to in a crash. Not saying anyone should be 100% cash in a long-term portfolio, but it's something people might put in the spreadsheet to compare. I think the zero return cash allocation is better used for those wanting to look at cash drag on their portfolios - say 5% cash. The 1 year portfolio is useful for error checking but little else. The spreadsheet doesn't include month-by-month returns because long-term investors (should) care about what happens over decades, not days.

Also - testing a 99% or 99.9% cash portfolio (with the rest in TSM or bonds) could be useful. I poked around down that line of thought and some of the metrics and ratios that it spit out were surprising. Especially look at Portfolio Alpha, Ulcer Ratio, and Portfolio Beta in a 99 or 99.9% cash portfolio. Surprising results.

Post Reply