Simba's backtesting spreadsheet [a Bogleheads community project]

[siamond]

Deleted post

[siamond]

Also, I am pondering some changes to the regular TIPS and short-term TIPS series. Again triggered by dcabler.

Those series used to track Fidelity FIPDX and Vanguard VTAPX (both starting annual returns in 2013) and to be complemented by the relevant Barclays index.

The question is to possibly make good use of iShares TIP and iShares STIP. TIP has solid history (2004+) while STIP has a little more than VTAPX (2011+).

I see three options, plus a variation:
1) not bother because the index returns are already good enough (and the Fidelity and Vanguard funds are popular and provide very low ERs)
2) replace FPIDX by TIP and VTAPX by STIP (there is no question that iShares is an excellent provider of passive index funds)
3) assemble more complex series, splicing index, then iShares, then Fidelity/Vanguard

The variation would be the same as 3), but disable the use of the iShares funds by default, which would result in 1) by default, while either 2) or 3) would be very easy to activate by just toggling a boolean in the spreadsheet. I'm not very fond of this one, to be honest, it's too complicated.

I drafted the third approach, if only to have all data handy before making a decision. I am kind of inclined to pick option 2, if only for its simplicity and consistency, but I don't feel that strongly about it. Thoughts?

I think I found a better variation / compromise for version 23b (to come in February):
- I'll continue to track Fidelity FIPDX, but at a lower splicing priority than iShares TIP
- I'll continue to track Vanguard VTAPX, but at a lower splicing priority than iShares STIP

This is basically option 2) above, plus the option for end users to find the history of FIPDX and VTAPX and/or disable the iShares index funds if they feel like it. I might remove FIPDX and VTAPX next year for simplicity sake, but I'd rather keep them tracked for now.

[siamond]

I have been adapting the excel file each year based on my personal preferences.
[....]
Link to edited Excel (based on rev22b).
//edit - Link deleted: the file host service seems to cause issues

GraySwan e-mailed me his custom spreadsheet and we agreed that I should make it available for other people to opine on his various ideas.

https://docs.google.com/spreadsheets/d/ ... ue&sd=true

After discussing with my wife (who is more savvy about colors than me), we agreed that most of the color changes might be a little too aggressive for regular users, but there are nevertheless several interesting ideas which could become mainstream after some tweaking:

1. List of funds in custom portfolios: top-level asset class discreet coloring looks good (to automate) + add extra rows to visualize the asset classes breakdown in a small table (and move the pie chart here for Analyze_Portfolio). As a reminder, each data series in Raw_Data is classified as US stocks, Int'l stocks, Bonds, Others (with weights for the few cases spanning several asset classes).

2. Asset Allocation in custom portfolios: emphasize the significant values, but more discreetly (e.g. automate empty cells to use a white background, remove bold for numbers to better differentiate from portfolio names)

3. Portfolio Balance and Drawdowns charts: make line colors consistent between the 2 types of chart

4. Risk/return chart: remove orange background + restore automatic colors; also, axis should use single digit after decimal point
=> DONE in my work version

5. Growth/Telltale chart: here brighter lines might indeed be a good improvement, but don't use yellow (I tried to restore automatic choices and... meh!). Seek some level of consistency with Portfolio Balance chart.

Here is a screen shot of a prototype for the extra rows computing the asset classes breakdown for Analyze_Portfolio. In Compare_Portfolios and Lazy_Portfolios, we'd have a similar table, extended to all custom portfolios in there, but no pie chart.

[firetrax]

Sorry for the delay, I got a bit hung up finalizing a last-minute feature request. Here is the latest version, with 2023 annual returns. The corresponding wiki update will be done in a couple of days, to allow for feedback in case I messed up something!

Do pay attention to item 3b. The ability to assemble a growth chart with annual extra investments (or withdrawals) is useful, but not terribly original, while the ability to visualize the same data in a telltale chart format is definitely less common AND quite informative. All credits to dcabler for this idea.

You can download the spreadsheet here: https://bit.ly/3TUduQl

(copy the link to a separate browser tab/window if it doesn't download from here)

Rev23a
1a. Added 2023 annual returns: funds and index returns updated
1b. Expense Ratios (ER) updated, too many changes to list (numerous ERs increased!)
1c. Inflation is a rough estimate for now (Nov to Nov); updates for a few synthetic models are pending
2. Added RMSE computation to the Portfolio Cycles Comparison charts in Analyze_Portfolio
3a. Added an explicit toggle to choose between growth charts (nominal or inflation-adjusted) and telltale charts in Compare_Portfolios. This should make things clearer for new users.
3b. Added a new feature to the growth/telltale charts in Compare_Portfolios: ability to make an extra (inflation-adjusted) investment or withdrawal on an annual basis
4. Normalized annual returns raw data to numbers rounded to 5 decimal places (ongoing process, five most recent years were normalized while introducing 2023 returns)

Hi this link doesn't appear to be working - is it down? Is there another way to download the v23 version?

[SnowDolphin]

I just tried the link. Works for me.

[siamond]

Hi this link doesn't appear to be working - is it down? Is there another way to download the v23 version?

I double-checked, the link works well for me, and 300+ folks used it since I posted it. This being said, I don't know why, but some folks had problems in the past with those shortened URLs. Maybe copy and paste in a separate browser tab and try again? If you keep having problems, please send me a private message and I'll give you a more direct link.

Or... you can wait for a couple of days, as I am going to upload a new version (with final 2023 inflation numbers) very soon!

[Raspberry-503]

thanks guys! I'm only interested in total US, total Int'l and total bond for my own simplified backtesting, but it was easy enough to find that info in the sheet.

[firetrax]

Hi this link doesn't appear to be working - is it down? Is there another way to download the v23 version?

I double-checked, the link works well for me, and 300+ folks used it since I posted it. This being said, I don't know why, but some folks had problems in the past with those shortened URLs. Maybe copy and paste in a separate browser tab and try again? If you keep having problems, please send me a private message and I'll give you a more direct link.

Or... you can wait for a couple of days, as I am going to upload a new version (with final 2023 inflation numbers) very soon!

Hi - thankyou! I tried opening in a separate tab and still not working for some reason. If you could DM me a direct lkink that would be great and I look forward to the updated 2023 spreadsheet!

Cheers

[SnowDolphin]

Hi - thankyou! I tried opening in a separate tab and still not working for some reason. If you could DM me a direct lkink that would be great and I look forward to the updated 2023 spreadsheet!

Cheers

You can decode the original link by pasting it in the prompt box here: https://support.bitly.com/hc/en-us/p/link-checker

[siamond]

Well, I was waiting for Prof. Shiller's latest update, but it doesn't seem to be coming. Also I hesitated to tackle a new form of ER adjustment to synthetic/index returns in this release, then I felt that it might be better to do it in v23c, keeping it experimental for a while.

Here is the latest version, with all 2023 annual returns and inflation numbers finalized (except for Prof. Shiller series). The corresponding wiki update will come in a few days, to allow for feedback in case I messed up something...

You can download the spreadsheet here: https://bit.ly/49mNIsS

(copy the link to a separate browser tab/window if it doesn't download from here; send me a PM if this doesn't work for you)

Rev23b
1. Simba 2023 update completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2023 data
1b. Updated Stock Calculator data with latest numbers derived from French-Fama library
1c. Updated Bond Calculator data with latest numbers derived from historical yields
1d. Updated T-Bills with latest numbers derived from FRED TB3MS
2. Added new data series for Int'l Small Value (passive series based on iShares ISVL, its FTSE index and the corresponding S&P index; active series based on DFA DISVX and the S&P index)
3a. Redefined the TIPS data series as index, iShares TIP; iShares TIPS has more real-life history than Fidelity FPIDX
3b. Redefined the ST TIPS data series as index, iShares STIP; iShares STIP has more real-life history than Vanguard VTAPX
4. Bridgeway BRSIX now tracks the Russell Microcap index instead of CRSP Decile 10 => reorganized the corresponding data series
5. Cosmetic changes to various charts: restored automatic colors, removed bright orange background, used single digit (instead of two) after the decimal for some axis
6. Added new rows (in Analyze_Portfolio, Compare_Portfolios, Lazy_Portfolios) computing the top-level asset class breakdown for each custom portfolio, e.g. US stocks, Int'l Stocks, Bonds, Others
7. Added multiple long-lived data series in Raw_Data:
7a. Putnam Investors Fund (PINVX, 1926-2017, stocks); Putnam Income Fund (PINCX, 1955+, IG bonds)
7b. Mass Investors Growth (MIGFX, 1936+, stocks); AF Investment Company of America (AIVSX, 1935+, stocks)
7c. Templeton Growth (TEPLX, 1955+, world stocks), Invesco / Oppenheimer Global (OPPAX, 1970+, world stocks)
7d. Loomis-Sayles / CGM Mutual Fund (LOMMX, 1930-2021, balanced); iShares Core S&P 500 (IVV, 2001+, stocks)
7e. Also now tracking PIODX (Class A) instead of PIORX (Class R) for the long-lived Pioneer fund
8. Added sorted list of raw data series in Data_Sources (funds, indices and synthetic models)

[siamond]

Wiki page updated, linking to the previous post, plus a few overdue updates to the main text.

[siamond]

We need to re-open the discussion for the Global Bonds Unhedged data series (the ACTIVE one), see details here:
viewtopic.php?p=7733977#p7733977

[siamond]

Now that v23b is officially released, let's focus on the bigger project: improve the spreadsheet's ER (expense ratio) processing.

Since the creation of the Simba spreadsheet (around 2012), the model was fairly simple. Spliced series were created by combining ER-adjusted index returns (or ER-adjusted synthetic returns) with known past returns of the funds of interest.
- For example, the US large-cap-blend spliced series combined past synthetic returns from Prof. Shiller (simulating the S&P 500 since 1871), past index returns (factual S&P 500 since 1936) and the Vanguard 500 Index Fund past returns since its inception in 1977. The fund's past returns are themselves a combination of investor class (VFINX) and the more recent admiral class (VFIAX).
- Both synthetic returns from Prof. Shiller and S&P 500 index returns were ER-adjusted using the current ER of the Vanguard fund (VFIAX).

Obviously, this isn't a terribly satisfying model. The current ER for VFIAX is 0.04%. In the early years of the fund's existence (mid 70s), the fund's returns account for an ER between 0.35% and 0.45%, while right before that, index returns are adjusted by 0.04%. Quite a discontinuity.

To compensate for this not-so-satisfying model, two mechanisms were introduced:
1. the ER used to adjust a given spliced series is customizable, so one could plug 0.35 in there, which would probably be more sensible.
2. the splicing process is explicit and flexible, with toggles allowing to entirely disable the use of real-life fund returns and solely use index return with a fixed & consistent ER adjustment.

It is doubtful that people used #1, as it would be tedious given the number of funds we now track, and hard to calibrate. As to #2, it was used by some folks, but it would only satisfy people looking at past returns to get an idea of possible futures, while definitely NOT satisfying people more interested in history per se. Note that in the past, funds expenses were often burdened by expensive transaction/distribution fees, which are (mostly) a historical thing which shouldn't occur again in the future.

The goal of the ongoing project is therefore the following

Step 1: Assemble solid historical ER data, as representative as possible to what would have been past ER values for a given type of fund. Iterate for ALL fund series tracked in Simba.

Step 2: Provide several options (with easy to use global toggles) to the ER-adjustment process:
a) only use synthetic/index returns, ER-adjusted with the current (last known) ER
b) only use synthetic/index returns, ER-adjusted with historical ER data
c) use a mix of synthetic/index returns and real-life fund returns, while ER-adjusting the synthetic/index returns with historical ER data
d) and for the sake of upward compatibility, same as c), but using the current ER

Options a) and c) are by far the most interesting ones. Option a) aims at people more interested to plan for the future, option c) aiming at people more interested in studying the past per se.

The next post (and experimental Simba spreadsheet) will focus on Step 1.

[siamond]

Those of you curious about this topic and/or eager to provide some early feedback on STEP 1 are welcome to download Simba v23c DRAFT1: https://bit.ly/4bPDfrD

You will notice the addition of two new tabs:
*) Raw_ER provides historical ER series and a corresponding splicing model (this tab will stay in the official release)
*) TallyER allows to tally and visualize raw ER series (those tables and charts will be reworked before the official release)

Raw_ER can be used for historical purposes and will be the foundation for adjusting synthetic/index returns in step 2. Raw ER numbers came from quite extensive research of the history of all funds tracked by Simba, plus various long-lived funds. Data sources for such research were:
- Vanguard itself (full ER history for existing Vanguard funds)
- SEC annual reports (through EDGAR in the US and SEDAR in Canada)
- Wiesenberger "investment companies" yearbooks (1942 to 1996 editions, thanks to the amazing New York Public Library
_{Many thanks to McQ who provided a seminal 'friendly push' to go study Wiesenberger books and supported this research as I was trying to find my way in a blizzard of data!}

Armed with such history, the general idea is to splice ER series for all funds being tracked in Simba, combining:
- a reasonably realistic fixed value (very early days)
- a long-lived 'analogue series', plus an increment (early days)
- one or two sets of known ER fund values (e.g. investor/admiral or ETF/admiral or single set)

There are three 'analogue' series, one for US stocks, one for Int'l stocks, one for bonds. They are themselves spliced:
- US stocks is fixed value, then Mass Investor Trust (MITTX) which was quite index-like for decades, then Vanguard VFINX/VFIAX.
- Int'l stocks is derived from Vanguard VTMGX, but it doesn't have much 'investor' history, so the Europe/Pacific investor series are used to fill the gap, and extended back in time with the US analogue plus increment. I couldn't find a proper long-lived Int'l fund, nothing similar to MITTX.
- Bonds is fixed value, then Keystone B-1 (KBONX) which was investment-grade and low price for decades, then Vanguard VBMFX/VBTLX.

For a given fund series (say Mid Cap Value), the US analogue series gives a good pattern over the years, but an MCV fund is typically more expensive than a S&P 500 fund (notably in the early years). Hence the increment, which was calibrated as best as possible based on actuals.

And there are various long-lived ER series at the very end, for historical AND calibration purposes. This is where the Wiesenberger data proved the most useful. Some of those funds still operate today and are close to 100 years of existence!

[siamond]

Here is a screen shot for the construction of the US analogue series and the Mid-Cap Value ER series. It should be reasonably intuitive when checking the spreadsheet (Raw_ER tab) and the explanations posted above.

In columns G to J, you see how the US analogue series was assembled and the fixed value of choice for the very early days (0.30). Then in column AJ, you see how the US analogue was used, with an increment of 0.10. In columns AK to AM, you see the Vanguard Mid-Cap Value Index Fund known ER history and the splicing process. Note that for the very early days (e.g. 1871!), a fixed value is used for MCV, adding the 0.10 increment to the fixed value used for the US analogue (0.30).

FEEDBACK WELCOME (on the general idea, the spreadsheet structure as well as the detailed calibration).



Now you might be wondering what is up with this 0.30 fixed value and why Mass Investors Trust (MITTX) took such a prominent role. Check this chart (from TallyER, around row 150 and column FA). VFINX is the Vanguard S&P 500 fund (launched in 1976). VWELX is the long-lived Vanguard Wellington stock fund. LOMMX is a long-lived balanced fund (roughly 60% stocks, 40% bonds), now called CGM Mutual Fund. AVERAGE is, well, the average of all known ERs for all funds being tracked.

See how MITTX was remarkably low cost from the 1940s to the mid 70s? See how its general ER trajectory in those decades is remarkably close to VFINX ER trajectory in the following decades? I also compared MITTX returns during those decades and the S&P 500 index returns using a Telltale chart. This showed that those returns tracked the index remarkably well. MITTX was NOT an index fund, but it was as close as it gets in terms of index tracking and in terms of low-cost structure until the mid 70s (then it got caught in the greed of the 80s and 90s). Quite impressive, isn't it?

[siamond]

Minor update (I meant to add the Franklin Utilities fund, 1948+, then forgot, then finally did it!). Same link:

Simba v23c DRAFT1b (experimental): https://bit.ly/4bPDfrD

[McQ]

For the record: Siamond did all the work, while I kibbitzed from the sidelines.

If you'd like to learn more about the early history of mutual funds, including average expense ratios, and average loads, please see the appendix to this paper: https://papers.ssrn.com/sol3/papers.cfm ... id=4457203

[CraigTester]

Well, I was waiting for Prof. Shiller's latest update, but it doesn't seem to be coming.

It's now updated using a new site, which should be updated regularly: https://shillerdata.com/

[siamond]

I got a bit derailed by a long fishing trip! I am back!

Here is a new downloadable version of the Simba spreadsheet (Rev23c, Draft 2a): https://bit.ly/3wW0gsW

This is is the EXPERIMENTAL implementation of Step 2 (see full context in in this post), providing several primary options (with easy to use global toggles) to the ER-adjustment process:
a) only use synthetic/index returns, ER-adjusted with the current (last known) ER
b) only use synthetic/index returns, ER-adjusted with historical ER data
c) use a mix of synthetic/index returns and real-life fund returns, while ER-adjusting the synthetic/index returns with historical ERs
d) and for the sake of upward compatibility, same as c), but using the current (last known) ER

By default, option c) is pre-selected. This is controlled by simple toggles in the Raw_Data tab, cells B2 to C9 (read the notes attached to the cells in blue). Finer-grain controls about data sources can be found in following rows (cells B12 to B26), but I don't expect they will be used often.

In practice, this means that backtesting results will look a little less rosy (and more realistic!) than in previous versions. Depending on the portfolio, you might see an impact of approximately:
*) 0.05% to 0.10% for 1985-2023 returns
*) 0.10% to 0.15% for 1970-2023 returns
*) a little more (up to 0.20%) when testing farther back in time

Feedback welcome. I need to do some minor clean-up (e.g. update the README tab to elaborate on ER processing), plus I want to re-check the 2023 ERs (now that I understood that ER updates might have to wait until mid-March, cf. the delays of filing SEC reports when a fund's fiscal year ends in Nov or Dec). I also want to find a way to (partly) automate such annual ER update process for next years...

[arttoday]

Siamond, so after several years, I am back with a minor correction and a potentially very minor correction. This one should be a lot less work than the correlations that you add to the Analyze_Portfolio tab (thank you for that).

Very minor first, on the Lazy_Portfolios tab, the "second grader" portfolio does not have attribution. It is originally from Allan Roth (the second grader in question is his son, Kevin); however, I believe Paul Farrell was the one who first popularized it. Is there no attribution because of conflicting online references? Paul Farrell attributes it to Allan Roth in at least one post and similarly Allan Roth expresses his gratitude to Paul Farrell for including it as his 8th lazy portfolio. I think it should probably be attributed to Roth.

OK, now the minor correction. The Lazy_Portfolios tab has the Second Grader as 60% Total US Market, 30% Total International, and 10% TIPs. TIPS is not correct (TIPS almost don't even appear in the book). The last 10% is total bond market. The references I find online (including one by Kevin himself) also call out the total bond market.

Thanks for everything you do on this spreadsheet. It remains a gem.

[siamond]

Siamond, so after several years, I am back with a minor correction and a potentially very minor correction. [...]

I implemented both of your suggestions in the last iteration, see link below. Thank you for your valuable feedback.

I want to re-check the 2023 ERs (now that I understood that ER updates might have to wait until mid-March, cf. the delays of filing SEC reports when a fund's fiscal year ends in Nov or Dec). I also want to find a way to (partly) automate such annual ER update process for next years...

All ERs have been updated with the latest from corresponding SEC/SEDAR annual reports (I have a half decent update process now). Note that such ERs are not always in perfect sync with the current fund's prospectus, which is used by Morningstar and the likes to display the current ER. Annual reports ERs represent actuals for the last fiscal year (more precisely ratio of actual fund expenses to net average assets).

Here is the latest experimental version of the Simba spreadsheet (Rev23c, Draft 2b): https://bit.ly/3wW0gsW. I'll update the README instructions and officially publish v23c around mid-April. Feedback always welcome.

[B4Xt3r]

hi guys, where can I find the information on whether dividends are included in the returns listed in Data_Series tab?

[siamond]

hi guys, where can I find the information on whether dividends are included in the returns listed in Data_Series tab?

Those are total returns, dividends included. That is, except for a couple of price-only series.

[prettycode]

Any chance of getting Managed Futures/Trend added? My understanding (according to testfol.io/help site) is the SG CTA Index (e.g. DBMF approximation) goes back to 2000 and KFA MLM Index (e.g. KMLM approximation) goes back to 1992. Not great but seeing a ton of talk about Trend lately from Meb Faber and other folks. Wonder what they use for their backtests.

By the way, thank you siamond for creating such an incredible tool. We're all deeply indebted to you. Amazing to have this resource!

[B4Xt3r]

hi guys, where can I find the information on whether dividends are included in the returns listed in Data_Series tab?

Those are total returns, dividends included. That is, except for a couple of price-only series.

[siamond]

Any chance of getting Managed Futures/Trend added? My understanding (according to testfol.io/help site) is the SG CTA Index (e.g. DBMF approximation) goes back to 2000 and KFA MLM Index (e.g. KMLM approximation) goes back to 1992. Not great but seeing a ton of talk about Trend lately from Meb Faber and other folks. Wonder what they use for their backtests.

By the way, thank you siamond for creating such an incredible tool. We're all deeply indebted to you. Amazing to have this resource!

Thanks for the nice words. Sorry for the late answer.

We have a momentum factor data series tracking the MTUM ETF, extended back to 1982 thanks to the corresponding index. It is somewhat similar, but also a bit different from Trend as a Momentum factor is only based on past 12 months data while Trend is supposed to equally-weighted between past 12 months and past 6 months (well, at least, this is what I just read!). For Managed Futures, there is a Commodity Futures data series tracking the GSG Trust, extended back to 1970. Let me know if this is good enough.

[mwgr5]

Possible to add AQR long / short fund? Thanks
https://funds.aqr.com/funds/alternative ... leix#about

[TrendingValue]

Any chance of getting Managed Futures/Trend added? My understanding (according to testfol.io/help site) is the SG CTA Index (e.g. DBMF approximation) goes back to 2000 and KFA MLM Index (e.g. KMLM approximation) goes back to 1992. Not great but seeing a ton of talk about Trend lately from Meb Faber and other folks. Wonder what they use for their backtests.

By the way, thank you siamond for creating such an incredible tool. We're all deeply indebted to you. Amazing to have this resource!

Thanks for the nice words. Sorry for the late answer.

We have a momentum factor data series tracking the MTUM ETF, extended back to 1982 thanks to the corresponding index. It is somewhat similar, but also a bit different from Trend as a Momentum factor is only based on past 12 months data while Trend is supposed to equally-weighted between past 12 months and past 6 months (well, at least, this is what I just read!). For Managed Futures, there is a Commodity Futures data series tracking the GSG Trust, extended back to 1970. Let me know if this is good enough.

Trend and momentum are related but different. One is cross sectional, i.e. relative, and long only (MTUM, SPMO, QMOM, etc) and the other is "time series", absolute, plus many but not all funds are long/short. These would be DBMF, KMLM, AHLT, QMHIX, etc

Big thank you Siamond for all that you do!

[HansT]

I've enhanced the latest spreadsheet to allow examination of the benefits of diversification. It's used for the "bridge" charts in the following discussion:

The Diversification Playbook: Slice-n-Dice, 1/N, Barbell

Here's an example of an easily created analysis:

• Adding gold asset (vs. 100% VTI) adds 66bp of diversification benefit, but suffers 438bp of lower blended return, for a net 373bp lower return over that time frame:
  

Would this be useful for the wider community? I tried to make a clean enhancement to the spreadsheet: 6 new columns in Portfolio_Math, one new sheet (of the "Compare_Portfolios" style).

[siamond]

I've enhanced the latest spreadsheet to allow examination of the benefits of diversification. [...]

Would this be useful for the wider community? I tried to make a clean enhancement to the spreadsheet: 6 new columns in Portfolio_Math, one new sheet (of the "Compare_Portfolios" style).

As I indicated in the thread you linked to, I think this is terrific research. I am not too sure about applying your extensions for the wider community though. The existing spreadsheet is already borderline bloated and truth is there are MANY other charts which could be added... Still, I am very appreciative of the work you've done on this.

[siamond]

Here is a new downloadable version of the Simba spreadsheet (Rev23c, Draft 2a): https://bit.ly/3wW0gsW

This is the EXPERIMENTAL implementation of Step 2 (see full context in in this post), providing several primary options (with easy to use global toggles) to the ER-adjustment process:
a) only use synthetic/index returns, ER-adjusted with the current (last known) ER
b) only use synthetic/index returns, ER-adjusted with historical ER data
c) use a mix of synthetic/index returns and real-life fund returns, while ER-adjusting the synthetic/index returns with historical ERs
d) and for the sake of upward compatibility, same as c), but using the current (last known) ER

Back to this. I ended up postponing this big change to the first 2025 official version, to avoid possible confusion between 2024 versions. As I am preparing the update, I felt the need to document the process I intend to follow for the annual spreadsheet updates and the new Raw_ER tab, while adding a row for last year's expense ratios. It is a bit of a painstaking process...

As a reminder, all ERs in this worksheet are intended to represent annual fiscal years as documented in certified annual SEC reports, and fiscal years do not always align with calendar years. Also, annual financial reports are usually publicly available 2.5 months after filing, so a fiscal year ending in December is actually inconvenient as we need to wait until mid March to get such a report. Finally, the ER prominently displayed on most financial Web sites for a given fund is the Prospectus Net ER, which doesn't always exactly align with the Annual Report Net ER.

Simba January update
- If the fund's fiscal year ends on Oct/Nov/Dec, enter a temporary update using the current Prospectus ER, marked in red
==> alternatively (better!), seek the semiannual report (same procedure as below) and use the corresponding ER
- If the fund's fiscal year ends in Jan/.../Sep, seek the corresponding annual report ER, enter it marked in black:
==> a convenient shortcut for Mutual Funds is to go to Morningstar's Price tab, scroll down and look for the historical ER table
==> for Vanguard and iShares ETFs/trusts, go to the fund's page on respective Web sites, seek the Literature tab and find the annual report
==> alternatively (this works for all funds), check the fund's CIK value (around row 170 in Raw_ER) and build a SEC link like this one
==> in the annual report, look for the Financial Highlights section for this specific fund (beware, some reports document multiple funds)
==> the Net ER is usually listed as the "Ratio of Total Expenses to Average Net Assets"
- ERs of interest should include interest expenses and account for waivers. Beware: some trusts have special rules (e.g. GSG).
- For Canadian funds, use the MER (instead of ER) and the SEDAR database (instead of SEC), starting from the fund's Web page

Simba March update
- Wait until March 16th to proceed, try first with a fund with a Dec fiscal year to ensure SEC/SEDAR databases include the latest annual reports
- Follow the same procedure as above to find the annual report's ER and update the entries previously marked in red

PS. I elected to use annual reports and fiscal years as this is the only information where one can find proper historical records. A Prospectus ER is an easily accessible quantity for the current year, but this is fleeting information. Also annual reports provide certified factual information.

PS2. it would be tempting to use the Yahoo Finance Profile tab for a given fund, as it displays both the prospectus ER and the annual report ER. Trouble is I found multiple cases where the information was factually wrong (when comparing to the corresponding SEC report)... The Morningstar Price tab is much more reliable, although I did notice a couple of hiccups on unusual funds (e.g. ASA).

[Dr.Lightning]

Please forgive me if I'm violating protocol. Where at Bogleheads does one ask questions about Simba's backtesting spreadsheet? If moderated, please feel free to discard my post but let me know directly where to post instead, or move the post. Thanks very much.

I've recently started using Rev23b. I unexpectedly encountered striking performance by Wellington (VWENX). The Vanguard webpage for VWENX say it started 5/14/2001, but the Rev32b spreadsheet has data beginning in 1930. Is there a way to find out *exactly* how VWENX was constructed backward from 2001 to 1930, in order to judge for myself the reliability of this construction? While VWENX is described as 2/3 stocks and 1/3 bonds, I believe it's actively managed.

I've used Rev32b to compare VWENX with 66.67%TSM/33.33%TBM. From 2002-2023, performance was roughly comparable, although VWENX was a little better. From 1970-2001, they were again comparable, except that VWENX had a big bump surrounding 1999 (pre-existence). But from 1930-1969, VWENX (pre-existence) distinctly outperformed the 66.67%TSM/33.33%TBM mix. Conjoining some of these date ranges, from 1970-2023 they're still comparable, as expected. So the big difference I saw overall stemmed from the 1930-1969 date range, long before VWENX existed.

So, bottom line, how was the older history for VWENX constructed. Knowing this, of course, will help me with all the other backward constructions.

Thanks very much,
Dr.Lightning.

[sycamore]

I've recently started using Rev23b. I unexpectedly encountered striking performance by Wellington (VWENX). The Vanguard webpage for VWENX say it started 5/14/2001, but the Rev32b spreadsheet has data beginning in 1930.

The Vanguard Wellington Fund has multiple share classes.

VWENX is the Admiral share class and hasn't existed for nearly as long as the Investor share class VWELX.

Vanguard's website shows VWELX "since inception" performance as of 7/1/1929. 1930 is the first full year of performance.

[Dr.Lightning]

Thanks @sycamore. I should have thought of that. I already knew about both VWINX and VWIAX.

In my defense, lol, at the same time, the spreadsheet really ought to use strictly VWELX and not have a column title "VWENX"! Was VWELX history spliced in front of VWENX history? I suggest that's not appropriate when VWELX has it's own history all that time. And they do have slightly different performance, due at least to the differing expense ratios. (I looked closer before submitting. It's a tiny difference, a hair less than even the expense ratio difference for 1yr. But it is different. Why splice the wrong heading on top? Oh, now I'm just complaining about fractional details like an engineer would complain -- Oh, wait... Anyway, like it will make a difference in the past predicting the future? )

Well, I really shouldn't look a gift horse in the mouth. The spreadsheet is a great asset for increasing understanding. Seriously. Thanks to all those who have worked on it.

[siamond]

Thanks @sycamore. I should have thought of that. I already knew about both VWINX and VWIAX.

In my defense, lol, at the same time, the spreadsheet really ought to use strictly VWELX and not have a column title "VWENX"! Was VWELX history spliced in front of VWENX history? I suggest that's not appropriate when VWELX has it's own history all that time. And they do have slightly different performance, due at least to the differing expense ratios. (I looked closer before submitting. It's a tiny difference, a hair less than even the expense ratio difference for 1yr. But it is different. Why splice the wrong heading on top? Oh, now I'm just complaining about fractional details like an engineer would complain -- Oh, wait... Anyway, like it will make a difference in the past predicting the future? )

Well, I really shouldn't look a gift horse in the mouth. The spreadsheet is a great asset for increasing understanding. Seriously. Thanks to all those who have worked on it.

Hi there. I am the volunteer currently maintaining the spreadsheet. To answer your first post, feel free to ask any question or make any suggestion for improvement in this thread. I would just suggest that you carefully read the README tab, as you should find quite a few answers in there. Still, this is a complicated spreadsheet, which went through many iterations, so no worry, ask away... And thank you for your interest!

The Vanguard history about investor vs. admiral is a tad contorted. The investor funds were created first and some of them (notably Wellington VWELX) had a really long life. Then admiral funds were introduced (like VWENX), as separate funds with their own ticker, primary difference being the lower expense ratio. Then Vanguard started to deprecate the investor funds. If you search for VFINX, the historical S&P 500 investor fund, you won't find it on Vanguard's Web site (while VFIAX, the admiral flavor, is definitely there). VWELX is still open for investors, but it's probably on the chopping block. Fact is Vanguard is converging on ETFs and Admiral funds, and we need to keep in mind this end goal.

As part of the deprecation process, Vanguard itself spliced the history of admiral funds with the history of investor funds. If you search for VWENX on Morningstar, you will find history going back to July 1929. Similarly VFIAX history extends back to 1976. You're right that Vanguard kept the inception date unchanged though, which is confusing. When we transitioned the whole Simba spreadsheet from investor funds to admiral funds a while ago, I elected to stay consistent with Vanguard's way with history. Personally, I would have preferred to show the splicing in a more explicit manner in Raw_Data as we usually do when extending data series back in time with indices, but well, better stay consistent with Vanguard and its end goal (also the transition year would have been inconsistent otherwise)... I hope this clarifies.

PS. in the next iteration, there will be a new tab with historical expense ratios, where the investor/admiral series will stay separated, as this is valuable historical information as you pointed out.

[elglanto]

Hi,

First of all, congratulation for this amazing work, quite impressive. I would have a question regarding MSCI data. From the spreadsheet, the source is https://www.msci.com/end-of-day-data-search and if I look at raw data for e.g. MSCI World GR, the earliest year with data is 1970. However, if I go to the chart page whare data can be downloaded (https://app2.msci.com/products/index-da ... LD&suite=C) and use Full History, the first year available in the xls file is 1998. The issue is similar for the MSCI ACWI index. Am I doing something stupid or is that some obvious option that I don't see.

I apologize if this has already been answered and I did not found it.

best regards,
François

[siamond]

First of all, congratulation for this amazing work, quite impressive. I would have a question regarding MSCI data. From the spreadsheet, the source is https://www.msci.com/end-of-day-data-search and if I look at raw data for e.g. MSCI World GR, the earliest year with data is 1970. However, if I go to the chart page whare data can be downloaded (https://app2.msci.com/products/index-da ... LD&suite=C) and use Full History, the first year available in the xls file is 1998. The issue is similar for the MSCI ACWI index. Am I doing something stupid or is that some obvious option that I don't see.

It's a weird MSCI quirk. The series is clearly documented as starting on Dec 31st, 1969 when you select Term = Full History. And yet the download misses quite a few years (while the corresponding price series is fine, go figure). I got the full series by other means, PM me if curious.

Unrelated side note: I am working on the next Simba update, with 2024 returns and full ER historical data. Should not take very long to finish.

[siamond]

Here is the latest version, with 2024 annual returns. The corresponding wiki page has been updated.

You can download the spreadsheet here: https://bit.ly/3Ps2jLB

(copy the link to a separate browser tab/window if it doesn't download from here; send me a PM if this doesn't work for you)

This is a MAJOR release, with the Expense Ratio (ER) processing being completely revamped. Long story short, we now use much more credible historical ERs (extended back in time) to adjust the models/indices historical returns. This makes historical returns slightly lower than in previous releases, notably as you go back in time. A better understanding of the whole thing can be gained by checking these posts and a few following posts:
viewtopic.php?p=7738521#p7738521
viewtopic.php?p=8176951#p8176951

===========================================
Rev24a
1a. Added a NEW worksheet for historical expense ratios (ERs): Raw_ER provides historical ER series and a corresponding splicing model
1b. Updated all historical ERs with the latest from annual reports (SEC and SEDAR) - Beware, due to fiscal year boundaries, this may not exactly match prospectus ER which are used by Morningstar and the likes.
1c. Modified Raw_Data to fully take in account historical ERs through two primary models, adjusting models/indices returns via the last fund's ER or by the fund's extended ER history (new default)
1d. Added corresponding instructions for advanced users in the README section
2a. Restored PIMCO Global Bond Fund Unhedged (PIGLX/PAGPX) as primary fund for Global Bonds series. It turns out that Invesco AUBAX changed strategy in 2016, making its history irrelevant.
2b. Decided to keep the existing Global Bonds (unhedged) spliced series although PIMCO PAGPX ceased to exist in 2022.
2c. Added a new long-lived data series in Raw_Data: Franklin Utilities Fund (FKUTX), one of the oldest sector funds still in existence (1948+)
3a. Improved portfolio definition validity tests to ignore 0% allocations
3b. Finished normalizing all values in Raw_Data to not exceed 5 decimals (formatting displays no more than two decimals)
3c. Adjusted Drawdown charts background for consistency with the other charts (in Analyze_Portfolio)
4. Fixed 'Second Grader' lazy portfolio definition (10% total bonds instead of TIPS; authored by Allan Roth)
5a. Added new data series for Int'l Bonds (unhedged; passive series based on iShares IGOV, its corresponding S&P index and an older FTSE WGBI index)
5b. Added new data series for LT Investment Grade Bonds (active series based on Vanguard VWETX and its corresponding Barclays index; this fund started in 1973 as the Westminster fund)
5c. Replaced Barclays US Treasury by Barclays US Government/Credit for Total Bonds series (better match overall, hence improved 1973-1975 splicing)
6. In Data_Series worksheet, moved Int'l Small Value close to Int'l Small; added LT Invest. Grade close to ST Invest. Grade; adjusted Lazy_Portfolios definitions accordingly
7a. Added 2024 annual returns: funds and index returns updated
7b. Inflation is a rough estimate for now (Nov to Nov); updates for a few synthetic models and indices are pending

[HansT]

Thank you Siamond!

[elglanto]

It's a weird MSCI quirk. The series is clearly documented as starting on Dec 31st, 1969 when you select Term = Full History. And yet the download misses quite a few years (while the corresponding price series is fine, go figure). I got the full series by other means, PM me if curious.

Thanks for the answer, I thought I was going crazy...

Very cool spreadsheet update !

[Tyler9000]

I would have a question regarding MSCI data. From the spreadsheet, the source is https://www.msci.com/end-of-day-data-search and if I look at raw data for e.g. MSCI World GR, the earliest year with data is 1970. However, if I go to the chart page whare data can be downloaded (https://app2.msci.com/products/index-da ... LD&suite=C) and use Full History, the first year available in the xls file is 1998. The issue is similar for the MSCI ACWI index. Am I doing something stupid or is that some obvious option that I don't see.

Thanks for pointing that out, as it appears to be a new bug in their search tool. After trying a few things, I found that you can access the older data all the way back to 1970 by changing the end date to 2000. So you'll have to piece together two spreadsheets to get the full history, but at least it's still available.

[elglanto]

Thanks for pointing that out, as it appears to be a new bug in their search tool. After trying a few things, I found that you can access the older data all the way back to 1970 by changing the end date to 2000. So you'll have to piece together two spreadsheets to get the full history, but at least it's still available.

Thanks for the tip, I will see if I can write some script to do that automatically with some webscrapping.