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: 4515
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: 51096
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: 4515
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: 4515
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: 4515
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: 4515
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: 486
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: 4515
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: 486
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: 4515
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: 194
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: 35
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: 4515
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: 4515
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: 4515
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: 4515
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+

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

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

Post by siamond » Thu Nov 29, 2018 1:32 pm

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

Rev17e:
1. Added new distribution of returns chart in Analyze_Portfolio
2. Fixed bug in Perpetual Withdrawal Rate cycles formula in Lazy_Portfolios (row 125, percentile cell was improperly referenced).

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

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

Post by siamond » Thu Nov 29, 2018 1:58 pm

Here are two example of the new chart. The point is to analyze the distribution of returns for a given portfolio during a given time interval and compare to a benchmark portfolio.

The first example shows the distribution of one-year nominal returns between 1970 and 2017. You can see that the first portfolio's (in blue) most common returns were in the [15%, 25%[ range (in other words, 5% variation around 20%) and displayed some 'fat tails' (some returns around -30% and +40%). While the second portfolio's most common returns were in the [5%, 15%[ range.

Image

The second example shows more advanced features of the chart. Here I selected real returns instead of nominal returns, and I looked at rolling periods of 10 years contained in the time interval of interest. The horizontal axis shows annualized returns for such rolling periods. Also note that I chose a smaller granularity for the chart's range (horizontal axis).

Image

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

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

Post by AlohaJoe » Thu Nov 29, 2018 8:10 pm

siamond wrote:
Thu Nov 29, 2018 1:58 pm
The second example shows more advanced features of the chart. Here I selected real returns instead of nominal returns, and I looked at rolling periods of 10 years contained in the time interval of interest. The horizontal axis shows annualized returns for such rolling periods. Also note that I chose a smaller granularity for the chart's range (horizontal axis).
Nice work! I'm a big fan of rolling returns charts like this. It's nice to see them made more easily available by being in the Simba spreadsheet.

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

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

Post by siamond » Fri Jan 04, 2019 11:03 pm

Here is the first version with the 2018 returns and a few more updates. Most numbers are final, some are still temporary (e.g. inflation) and will be updated in a few weeks, when the official data will become available. Most changes were previously discussed on this thread and/or on related threads.

Here is the short URL for download: http://bit.ly/2BXrZt2. If a couple of folks could run a quick sanity test to verify that it works for them, this would be appreciated. I'll officialize via the wiki page over the week-end.

Rev18a
1. Spreadsheet updated for 2018 returns and latest Expense Ratios (ER)
2. Inflation and some historical 2018 returns (e.g. Shiller) are only an educated guess for now, waiting for official numbers to come later in January
3. Updated the LCV, LCG, MCV, MCB, MCG, SCV, SCB, SCG data series with the latest data from the Stock Index Calculator (Tyler 2018) for pre-index history
4. Updated the LTT, ITT, STT, TBM, STB and TIPS data series with the latest data from the Bond Fund Simulator (Longinvest 1.19) for pre-index history
5. Renamed the VDVIX data series "International Developed" (in short, "Int'l Dev") to better reflect the new orientation of the fund since 2016 (i.e. Canada now included)
6a. Redefined the Global Bond data series: it used to be anchored on Pimco PGBDX (unhedged), it is now anchored on low-cost Vanguard BNDW (hedged)
6b. Global Bond data series now uses FTSE WGBI USD (hedged) index starting in 1985, Bloomberg Barclays Global Aggregate TR USD (hedged) index starting in 1990, then…
6c. … Bloomberg Barclays Global Agg. Float Adjusted TR USD (hedged) index starting in 2010; Vanguard Total World Bond ETF (BNDW) will start in 2019 (fund's inception was in Sep-18)
6d. The old PGBDX (+ corresponding index returns) data series was moved to Data_Misc. Beware: no ER adjustment was applied to the index returns.
7a. Redefined the Precious Metals data series: it used to be anchored on Vanguard VGPMX, it is now anchored on Fidelity Select Gold (FSAGX)
7b. Precious Metals data series now uses INIVX (VanEck International Investors Gold, 1957-1985), then FSAGX (Fidelity Select Gold, 1986+, ER 0.84%)
7c. The VGPMX data series was moved to Data_Misc. Beware: starting from Sep-18, the fund changed its strategy (and its name to Vanguard Global Capital Cycles Fund).
8a. Added new Vanguard Extended Duration Treasury ETF (EDV) data series, aka LT STRIPS (2008+)
8b. LT STRIPS data series extended back in time with Longinvest's Bond Fund EqPar Zero 30-11 model till 1996, and Bloomberg Barclays 20-30Y Treasury Strips TR USD till 2007
9. Updated data sources for Canadian returns in Data_Misc (e.g. reducing dependency on Libra's approximate numbers)

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

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

Post by Kevin M » Sat Jan 05, 2019 12:52 pm

This is nothing new, but when I save in LibreOffice Calc, I am warned that the document may contain formatting or content that cannot be saved in Excel 2007-20019 format. In the past I've saved as ODF, which is what is suggested. What do you recommend?

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

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

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

Post by siamond » Sat Jan 05, 2019 2:44 pm

Kevin M wrote:
Sat Jan 05, 2019 12:52 pm
This is nothing new, but when I save in LibreOffice Calc, I am warned that the document may contain formatting or content that cannot be saved in Excel 2007-20019 format. In the past I've saved as ODF, which is what is suggested. What do you recommend?
Yes, LibreOffice does mess up a bit the original Excel formatting when going from Excel to LibreOffice to Excel. But then why would anybody want to do that? I would advise you to stick to the ODF format if your intent is to save a copy for further editing with LibreOffice. Or am I missing your point?

PS. as a side note, I made a very minor update today, a couple of small things I forgot to do yesterday. The few of you who downloaded a copy of v18a last night or this morning may want to resync. Same link: http://bit.ly/2BXrZt2.

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

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

Post by Kevin M » Sat Jan 05, 2019 3:46 pm

siamond wrote:
Sat Jan 05, 2019 2:44 pm
I would advise you to stick to the ODF format if your intent is to save a copy for further editing with LibreOffice.
That pretty much answers my question, and is what I assumed. I was wondering if there was any advantage to keeping it in the Excel format when using LibreOffice.

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

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

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

Post by siamond » Mon Jan 07, 2019 3:38 pm

For the next version, besides finalizing a few 2018 numbers (notably inflation), I had a new thought while reacting to this thread. The OPs' question is actually a fairly common one and it would be good to have some numbers readily available to show the consequences of such 'income-based' approach. It may not be the best idea in the world, but since it's a common question, it would be good to give the means to explore it further.

It turns out we already have an S&P500 (price-only) data series in Data_Misc and a historical S&P500 (Shiller Total-Return) in the main data series. Those two series are actually a tad inconsistent because Prof. Shiller does this weird averaging prices & dividends over January. There is also a historical data series for IT Treasuries. I am thinking to clean that up and have:
- a consistent price-only and total-return data series for S&P500 (Data_TR_USD tab, feeding the regular tools)
- a consistent price-only and total-return data series for IT Treasuries (Data_TR_USD tab, feeding the regular tools)
- move the S&P 500 'Shiller' data series to Data_Misc

To do so, I'll use index data series for S&P 500 and IT Treasuries, but we need to go further back in time for the bonds. Which requires an extension of the bond fund model, hence my request here, and longinvest's graceful answer.

jmk
Posts: 413
Joined: Tue Nov 01, 2011 7:48 pm

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by jmk » Sat Jan 12, 2019 12:52 pm

Kevin M wrote:
Tue Jan 12, 2016 7:26 pm
For actual backtesting, Chromebook and other non-Excel (or Excel-clone) users are best served by Portfolio Visualizer. Maybe PV will incorporate the pre-1972 data someday.
You can upload your own data sets into PV going earlier than its defaults. There is some limit on first year though (1900?), but it's earlier than the defaults.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Jan 12, 2019 11:02 pm

jmk wrote:
Sat Jan 12, 2019 12:52 pm
Kevin M wrote:
Tue Jan 12, 2016 7:26 pm
For actual backtesting, Chromebook and other non-Excel (or Excel-clone) users are best served by Portfolio Visualizer. Maybe PV will incorporate the pre-1972 data someday.
You can upload your own data sets into PV going earlier than its defaults. There is some limit on first year though (1900?), but it's earlier than the defaults.
What I did when I was using a Chromebook almost exclusively was to open the backtest spreadsheet in a free Excel clone (e.g., LibreOffice Calc) on a Windows PC (which I otherwise used only to do tax returns), delete all the sheets except the one(s) with the data, then import the data into a Google Sheets spreadsheet. I still use a Google Sheets spreadsheet with the data loaded from the backtest spreadsheet from time to time to generate data for posts here.

One could use a similar approach to load the older data from the backtest spreadsheet into PV. I've never loaded any data into PV.

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

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

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

Post by siamond » Sun Jan 13, 2019 12:00 pm

An interesting question which was asked on the bond fund model thread (click on the tiny arrows to check the context). Here is my initial feedback. More opinions welcome.
siamond wrote:
Sun Jan 13, 2019 11:00 am
dcabler wrote:
Sun Jan 13, 2019 10:45 am
Second, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
Yes, this is a legitimate question. I am not too hot about making a spliced series with years of active fund (e.g. VUSTX) followed by more recent years of passive fund (e.g. VLGSX). What we could do is to switch to a series splicing the bond model of relevance, then the index until 2009, then the corresponding passive fund. Such series would undoubtedly be more consistent. We would lose more than 20 years of real-life history though. Plus, I wonder how many Bogleheads actually use those truly passive bond funds as opposed to the more well-known active funds. Feedback welcome, it is clearly a valid question, but I am not too sure such a change would be worth it.
PS. there is another consideration. Vanguard Intermediate Term Treasury funds (passive and active) follow the Barclays US Treasury 3-10 Yr index. Unfortunately, this 10-3 index has limited history, its history only goes back to Jan-2002. If we give up on using VFITX (the active Vanguard fund, 1992+), we would have to rely on the bond fund simulator until 2001. This doesn't seem desirable.

EDIT: PS was wrong, I got mixed up... see next posts for clarification.
Last edited by siamond on Tue Jan 15, 2019 11:40 am, edited 3 times in total.

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

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

Post by dcabler » Sun Jan 13, 2019 5:16 pm

siamond wrote:
Sun Jan 13, 2019 12:00 pm
An interesting question which was asked on the bond fund model thread (click on the tiny arrows to check the context). Here is my thinking. More opinions welcome.
siamond wrote:
Sun Jan 13, 2019 11:00 am
dcabler wrote:
Sun Jan 13, 2019 10:45 am
Second, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
Yes, this is a legitimate question. I am not too hot about making a spliced series with years of active fund (e.g. VUSTX) followed by more recent years of passive fund (e.g. VLGSX). What we could do is to switch to a series splicing the bond model of relevance, then the index until 2009, then the corresponding passive fund. Such series would undoubtedly be more consistent. We would lose more than 20 years of real-life history though. Plus, I wonder how many Bogleheads actually use those truly passive bond funds as opposed to the more well-known active funds. Feedback welcome, it is clearly a valid question, but I am not too sure such a change would be worth it.
PS. there is another consideration. Vanguard Intermediate Term Treasury funds (passive and active) follow the Barclays US Treasury 3-10 Yr index. Unfortunately, this 10-3 index has limited history, its history only goes back to Jan-2002. If we give up on using VFITX (the active Vanguard fund, 1992+), we would have to rely on the bond fund simulator until 2001. This doesn't seem desirable.
Here's a place where there are only opinions and no facts. Not like that ever happens on this forum, though, eh? :D

So, if Barclays 3-10 index only goes back to 2002, what did VFITX do before that? If it wasn't the Barclays 3-10 index, then I'd argue that there's already a splice. Yeah, it's a splice with real data instead of derived data, but a splice nonetheless.

Similar thing happens with other Simba Series. For example consider SCV
1927-78: Tyler's Pseudo-CRSP Series
1979-92: Russell 2000 Value TR (USD)
1993-98: MSCI US Small Cap Value GR
1999-Present: VISVX
But VISVX itself is spliced due to changing indices several times.
1999--May 2003 it's S&P Small Cap Value600
May 2003-April 2013 it's MSCI US Small Cap Value Index
April 2013- Present, it's CRSP US Small Cap Value Index

I didn't intend this post to ask questions about SCV until I looked at it, but why isn't everything from 1927 to 1998 using the Pseudo-CRSP series? Why the trip through Russell and MSCI?

Anyway, as somebody who uses VISVX (VSIAX Admiral shares actually), I prefer to look as far back as possible using the closest thing to the actual index currently used by Vanguard for this fund. So I usually manually edit Simba to replace the entire series with VSIAX (post switchover to CRSP), and before switchover, I use the Pseudo-CRSP series, adjusted for E/R, all the way back to 1927 - even if it means losing actual VISVX/VSIAX returns as they actually occurred. If some day Vanguard change their index again, I'll switch over - though I doubt there is any decent way to create a pseudo-index for any other series besides CRSP as Tyler did.

For me, it goes to the question of what I'm looking for. I'm looking for something that most closely matches what I hold today, looking as far back as I can. And of course there's a reality check here: past performance not guaranteeing future performance, the difference between indices might be in the noise over such a long time period anyway. And, no, I'm not trying to get tenth of a degree accuracy in my backtests. I just have my own view of consistency. :D

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

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

Post by siamond » Sun Jan 13, 2019 11:17 pm

dcabler wrote:
Sun Jan 13, 2019 5:16 pm
So, if Barclays 3-10 index only goes back to 2002, what did VFITX do before that? If it wasn't the Barclays 3-10 index, then I'd argue that there's already a splice.
Actually, I got mixed up, apologies. Both VFITX and its Barclays index have been in existence since 1982. Confusingly enough:

1) Vanguard passive ITT fund (VSIGX) tracks the 3-10 Barclays index:
https://investor.vanguard.com/mutual-fu ... ance/vsigx

2) Vanguard active ITT fund (VFITX/VFIUX) tracks the 5-10 Barclays index (like Fidelity FIBAX does):
https://investor.vanguard.com/mutual-fu ... ance/vfitx
dcabler wrote:
Sun Jan 13, 2019 5:16 pm
I didn't intend this post to ask questions about SCV until I looked at it, but why isn't everything from 1927 to 1998 using the Pseudo-CRSP series? Why the trip through Russell and MSCI?
Well, my order of priority (which I believe is the same as the original Simba designers) is:
1) real-life fund
2) real-life index matching the fund
3) real-life index matching the asset class
4) synthetic model

There are just too many simplifying assumptions about synthetic models, I would be quite reluctant to use them past such order of priority. This being said, I share some of the angst about the Russell index, which seems to be a bit of an antiquated design.

Russell is used because it starts way before the other indices (in 1979). MSCI is used because most of Vanguard's early SCV history is anchored on it, plus MSCI goes further back in time than S&P.

But... one thing at a time, let's sort out the bond funds first...
dcabler wrote:
Sun Jan 13, 2019 5:16 pm
For me, it goes to the question of what I'm looking for. I'm looking for something that most closely matches what I hold today, looking as far back as I can.
Yes, I understand the viewpoint. Simba is kind of stuck between two goals: 1) study history for the sake of history 2) study history for the sake of making analogies with what is available today. There is no perfect answer here. I tried to ease the life of advanced users for them to add their own data series at will (and I often do it myself for a point test or another). Maybe there are ways to go further in this direction, but I fear the extra-complexity coming with it...

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

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

Post by siamond » Tue Jan 15, 2019 11:37 am

dcabler wrote:
Sun Jan 13, 2019 10:45 am
Second, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
I am starting to ponder if we should not leave the active treasury bond fund data series as is (if only because of the long history of corresponding Vanguard funds), while adding separate passive treasury bond index funds data series (which tend to be anchored on separate indices, confusingly enough).

But let's not jump to conclusions yet, I'd like first to complete the discussion about the proper mapping to synthetic bond fund models that I initiated here.

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

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

Post by siamond » Sat Jan 26, 2019 1:01 pm

siamond wrote:
Tue Jan 15, 2019 11:37 am
dcabler wrote:
Sun Jan 13, 2019 10:45 am
Second, but related question, is whether Simba should switch over to the pure index funds instead of the managed ones
I am starting to ponder if we should not leave the active treasury bond fund data series as is (if only because of the long history of corresponding Vanguard funds), while adding separate passive treasury bond index funds data series (which tend to be anchored on separate indices, confusingly enough).

But let's not jump to conclusions yet, I'd like first to complete the discussion about the proper mapping to synthetic bond fund models that I initiated here.
Following the discussion about which bond fund models are the best matches for real-life bond funds, it emerged that there is more continuity than one might perceive between the active bond funds from Vanguard and their passive counterparts, even if the benchmark index is different. This post about average maturity illustrates the point.

It is therefore tempting to splice the active fund series with the more recent index fund series, while using the benchmark index of the passive fund for the early years (preceded by the corresponding bond fund model). For example, for the Intermediate-Term fund, it would be something like:
- Matching model: Bond Fund (10 to 4-year)
- Bloomberg Barclays U.S. Treasury 3–10 Year Bond Index
- Vanguard Intermediate-Term Treasury Fund (VFITX)
- Vanguard Intermediate-Term Treasury Index Fund (VSIGX)

Trouble is those index bond funds only exist in Admiral shares flavor while Simba only tracks Investor shares funds. There is actually a higher-level issue, which is that Investor shares are probably on their way out. Furthermore, most investors actually use Admiral shares, so the Simba current tack wasn't optimal (albeit more convenient to get more historical data from real-life funds). After reflecting on all of this, I think it would be wiser to postpone a change on the bond funds, wait for the Investor/Admiral transition dust to settle, and then and only then issue one big Simba revision dedicated to such changes towards Admiral funds (incl. bond index funds).

The next Simba revision will be issued in a week or so (I'm still waiting for some data to come in by the end of January), notably including the final inflation 2018 numbers as well as a new chart about comparative rolling returns. And we'll wait a few more months before tackling the Admiral funds and bond funds changes.

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

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

Post by siamond » Wed Feb 13, 2019 9:46 am

The long-awaited Feb-19 update... Here it is, please download here.

Rev18b
1. Updated US inflation (Data_TR_USD) with final 2018 number (1.91%); updated Canadian inflation (Data_Misc) with final 2018 number (1.99%)
2. Revisited S&P500(h), IT Bonds(h), T-Bills(h) series:
2a. Removed S&P 500 Price only from Data_Misc, removed S&P 500(h) aka Shiller series from Data_TR_USD, removed IT Bonds(h) from Data_TR_USD
2b. Introduced historical TR (total returns) and PR (price returns) index series for S&P 500 and Total-Bonds (US); T-Bills is TR only (bills' price doesn't change much) - see details in Data_Sources
3. One more pass with latest Expense Ratios (ER): EFV now at 0.38
4. Updated the LCV, LCG, MCV, MCB, MCG, SCV, SCB, SCG data series with the latest data from the Stock Index Calculator (Tyler 2018; FF Dec-18) for pre-index history - a few extremely minor changes
5. Made legends more dynamic for charts with show/no-show settings: display empty string instead of data series' name if no show; also increased size of legend's display
6. Introduced NEW rolling returns chart in Compare_Portfolios
7. Revisited Precious Metals (again!): switched to ASA 1970+ for pre-FSAGX returns (INIVX history as well as pre-70 ASA proved inadequate)
Last edited by siamond on Wed Feb 13, 2019 1:17 pm, edited 1 time in total.

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

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

Post by siamond » Wed Feb 13, 2019 10:23 am

Here is a little more context for the v18b update (cf. post above). Feedback welcome.

About the PR/TR data series for S&P 500 and IT Bonds, you can find more background in this post. Not only does this allow some analysis of price changes, but this also indirectly allows to figure out historical distributions (usually made of dividends) by simply subtracting PR returns from TR returns -- better do it geometrically, i.e. (1+TR%)/(1+PR%)-1. Note that this effort triggered an update of the bond fund simulator to isolate the price-only trajectory (aka capital returns).

About precious metal miners, we had a good discussion on this thread, and settled on the ASA/FSAGX combo. A bit of a dubious compromise due to the use of active funds, but well, it is what it is.

Finally, about the new chart (rolling returns in Compare_Portfolios), this is a handy way to compare two data series. Tyler9000 started to use such charts a long time ago while comparing various indices for the same categories, and I always thought it was handy. Here is an example (note the various toggles allowing to switch from nominal to real returns, set the duration of the rolling period, and decide which data series to show or not show). One notable use of such chart is to set the rolling period to one year, and compare annual returns over a couple of decades.

Image

Post Reply