Simba's backtesting spreadsheet [a Bogleheads community project]

siamond · Post by **siamond** » Sat Nov 21, 2015 10:25 pm

I was taking a look at the Fama-French Web site and noticed this warning:

Please note, CRSP just completed an extensive review of their shares outstanding data for 1925-1946. The file they released in January 2015 (with data through December 2014) incorporates over 4000 changes that affect 400 Permnos. As a result, many of the returns we report for 1925-1946 change in our January 2015 update and some of the changes are large.

As I was looking at that, I actually noticed that the more recent FF numbers apparently changed a tad as well (which is a little hard to understand).

Some of the Simba historical numbers depend on the Fama-French data library (e.g. LCG: Fama and French 1972-1978), some other historical numbers depend directly on the CRSP database (e.g. MCB = US Mid Blend: CRSP Decile 3-5 1972-1978), and I suspect others (e.g. Ibbotson-originated) might indirectly depend on it.

I can help double-checking and updating the FF data, as I just imported the updated numbers in one of my own spreadsheets, but I don't have access to other sources, starting by CRSP itself.

PS. I took a quick look at the new FF averages, and they didn't change that much from previous numbers. Rest assured that small-cap value still has a solid (historical) advantage!

Post by **LadyGeek** » Sat Dec 12, 2015 2:19 pm

FYI - longinvest is proposing a method to calculate a bond's historical total return from the interest rate. See: Historical Bond Returns - Shiller: From Rates to Returns

If this method is accurate, longinvest would like to incorporate this method into the backtesting spreadsheet.

siamond · Post by **siamond** » Sat Dec 12, 2015 3:50 pm

LadyGeek wrote:FYI - longinvest is proposing a method to calculate a bond's historical total return from the interest rate. See: Historical Bond Returns - Shiller: From Rates to Returns

If this method is accurate, longinvest would like to incorporate this method into the backtesting spreadsheet.

Let me repeat what I posted on the other thread... YES, it would be really useful to have a single reference we agree on, and can refer to for stocks & bonds long-term historical returns, and related stats. Was actually an old request of mine at some point in this thread...

Actually, I'd go one step further, and suggest that we format it in such a way that it is really easy for people with access to other types of historical data (e.g. UK returns, SBBI/Ibbotson US returns, French-Fama factor-oriented returns, DMS dataset, etc) to plug such numbers in their own customized version of this oh-so-useful spreadsheet.

And I'd be happy to help doing so.

siamond · Post by **siamond** » Sat Dec 12, 2015 7:18 pm

Before I forget it, let me log a really minor issue I just noticed. We have tabs named "Returns_85_13" which include 2014 data!

A small thing to fix with the 2015 update, I would suggest.

siamond · Post by **siamond** » Tue Dec 15, 2015 12:32 pm

Another small bug report: cell L2 in the Data_xx tabs currently contains "VTMGX/VTMGX". Which breaks the look-up done by the returns tabs and the results in the portfolio tab. Should be "VTMGX", of course.

Kevin M · Post by **Kevin M** » Tue Dec 15, 2015 9:11 pm

And let me repeat something I said on the other thread: please make this data available in something besides a HUGE spreadsheet that requires a bloated OS to run. Easy enough to pull it into the backtesting spreadsheet for those who want to use that, and still make the data more easily available to those who just want the data.

Kevin

siamond · Post by **siamond** » Sat Dec 19, 2015 12:07 pm

LadyGeek wrote:If this method is accurate, longinvest would like to incorporate this method into the backtesting spreadsheet.

This was actually my proposal to start with!

I am working on it, this proves more work than I expected, lots of formulas, names and graphs... I should post a draft by the end of the week-end. I took the opportunity to fix the couple of minor bugs I identified earlier on this thread, and some minor cosmetics.

cully · Post by **cully** » Sat Dec 19, 2015 12:31 pm

Does anyone have monthly ^W4500 data - there was a time you could download from Wilshire before they sold to Dow Jones? Also does anyone have monthly MSCI EAFE data? Much appreciated. Seems like historical data is so hard to get.

siamond · Post by **siamond** » Sun Dec 20, 2015 10:13 am

siamond wrote:
LadyGeek wrote:If this method is accurate, longinvest would like to incorporate this method into the backtesting spreadsheet.
This was actually my proposal to start with!

I am working on it, this proves more work than I expected, lots of formulas, names and graphs... I should post a draft by the end of the week-end. I took the opportunity to fix the couple of minor bugs I identified earlier on this thread, and some minor cosmetics.

Ok, here is a decent first draft (temporary link):
https://drive.google.com/open?id=0B0svR ... VBubkFLOW8

I am still working on a few things, but this first draft should be consistent. If somebody with a sharp eye would be kind enough to take a look, this would be great.

Post by **LadyGeek** » Sun Dec 20, 2015 11:09 am

I'm evaluating in LibreOffice Calc. For compatibility, this spreadsheet is developed in LibreOffice Calc and saved in MS Excel .xls format. Here's an old post which explains the approach: Re: Spreadsheet for backtesting (includes TrevH's data)

Code: Select all

- cell A124 and L173 in Returns_72_14 do not seem to make much sense, nor do they have any dependent. Clear them? Similar issue in other returns tabs

(Tools --> Detective) I don't see any dependencies and agree they can be cleared.

Code: Select all

- it seems that the names date1/2, pdrawdown1/2x, plot1/2x and so on are now obsolete. True? Delete?

(Insert --> Names --> Manage) I do not see the names defined.

Code: Select all

- another one apparently not used: Symbols72 (no 85 equivalent)

(Insert --> Names --> Manage) Symbol72 is defined as $Portfolio.$B$3:$B$39

Code: Select all

- another one apparently not used: Compare85_Date

(Insert --> Names --> Manage) Compare85_Date is defined as OFFSET($Compare_Portfolios.$K$118,$Compare_Portfolios.$L$62,0,$Compare_Portfolios.$L$61,1)

Code: Select all

- another one apparently not used: Inflation72, Inflation85

(Insert --> Names --> Manage) Inflation72 is defined as $Data_Oldies.$AA$8:$AA$152
Inflation85 is defined as $Data_85_14.$AZ$8:$AZ$38

siamond · Post by **siamond** » Sun Dec 20, 2015 1:52 pm

Ok, I fixed a few lingering issues, cleaned up the names, the chart titles and the chart legends. Added credits and sources. Should be operational now.

Same (temporary) link: https://drive.google.com/open?id=0B0svR ... VBubkFLOW8

I also pondered to add gold in the pre-1972 "Oldies", then realized that there was no point, since it's basically a flat line (in nominal terms) before 1970, due to the fixed parity till then.

siamond · Post by **siamond** » Sun Dec 27, 2015 3:27 pm

I added placeholders for French-Fama data series (1927+, small value, large value, etc), and improved a few formulas accordingly. Same (temporary) link: https://drive.google.com/open?id=0B0svR ... VBubkFLOW8

I put on hold the project of adding Canadian and UK historical returns. First, because it is a more complex project due to currency/inflation issues. Next, because I'm still waiting for the authorization to copy the Barclays UK historical numbers. Finally, because I'd rather give it a try *after* the Simba spreadsheet is updated with the 2015 annual returns, so that people have a stable reference while some of us play around with structural changes...

I also put on hold the few changes suggested by the following post of mine, to ease the comparison of the previous official version and my working version. We should process this comment when adding the 2015 returns.
viewtopic.php?f=10&t=2520&p=2733971#p2695935

Post by **LadyGeek** » Sun Dec 27, 2015 5:28 pm

The spreadsheet is working in LibreOffice Calc.

Portfolio tab - The chart check-boxes are working. (Corrected since the last version.)

Data_Oldies tab, cell A4. Does "YOI" mean "Year of Inception"? I could not find a cell comment containing the definition.

Also (in Data_Oldies), can you please clarify how the "Inception" year is implemented?

For example, does Column E ("Historical IT bond fund returns, combined with 1972+ Vanguard returns.") mean that the data before 1992 is utilizing data from Vanguard returns and earlier historical data?

The same question applies for Column G, which is for YOI = 1984.

siamond · Post by **siamond** » Sun Dec 27, 2015 9:33 pm

LadyGeek wrote:Data_Oldies tab, cell A4. Does "YOI" mean "Year of Inception"? I could not find a cell comment containing the definition.

Also (in Data_Oldies), can you please clarify how the "Inception" year is implemented?

For example, does Column E ("Historical IT bond fund returns, combined with 1972+ Vanguard returns.") mean that the data before 1992 is utilizing data from Vanguard returns and earlier historical data?

The same question applies for Column G, which is for YOI = 1984.

I stayed consistent with the YOI mechanism implemented in the other tabs, which applies the ER in the years before the inception of the fund that is used in the years after the YOI (since Vanguard includes ER% in its own total return math). In the 'Oldies' tab, I used it for the 'combined' columns, the ones using Shiller-derived data before 1972, combined with the 1972+ data that we had in the Data_72 tab. And for the other columns, I set it up so that the ER math is a no-op. So that we can compute historical returns with or without a fund-like ER logic.

Yes, cell comments are sorely lacking in the spreadsheet. I wasn't trying to fix that aspect (I just introduced comments with the new stuff I was creating), but it might not be a bad idea to do a pass and improve this aspect of the spreadsheet to help various volunteers to update & improve it.
=> I can take a stab if you wish. I also would like to revisit the 'unbalanced' math, as it is just so contorted now with back & forth references across tabs. And then stabilize. Ok?

Post by **LadyGeek** » Sun Dec 27, 2015 10:32 pm

siamond wrote:=> I can take a stab if you wish. I also would like to revisit the 'unbalanced' math, as it is just so contorted now with back & forth references across tabs. And then stabilize.

Those are array functions, which can be rather nasty to detangle.

You may be thinking to put the array functions on the same tab to improve performance. That's not the issue. It's the use of array formulas and SUMPRODUCTS() - both are used quite a bit in this spreadsheet.

This will help put things in perspective: spreadsheet array formula speed - Google Search

Speed, complexity, cost*. Pick any 2.

*In this case, the "cost" is the size of the spreadsheet.

siamond · Post by **siamond** » Sun Dec 27, 2015 10:38 pm

LadyGeek wrote:
siamond wrote:=> I can take a stab if you wish. I also would like to revisit the 'unbalanced' math, as it is just so contorted now with back & forth references across tabs. And then stabilize.
Those are array functions, which can be rather nasty to detangle.
You may be thinking to put the array functions on the same tab to improve performance. [...]
*In this case, the "cost" is the size of the spreadsheet.

I don't see any performance issue with this spreadsheet, but my Macbook is relatively new, so maybe I'm missing something.

I was coming from a totally different angle, the cost of maintenance and the ease of understanding. It is just a bad practice of have formulas that go back & forth tabs. Most of the spreadsheet is fairly hierarchical (data_xx feeds in returns_xx, which feeds in portfolio tabs), while this unbalanced math pushes portfolio-specific math down to data_xx.

Actually, it isn't hard to do an incremental fix. Just move the offending lines of computation from data_xx to the corresponding returns_xx tabs. Then the overall logic becomes much more consistent (far from fully hierarchical, but at least data_xx stays clean).

=> EDIT: done - this was easier than I expected. I checked, results remain the same. Ok, I'm done with my tweaking... For now!

siamond · Post by **siamond** » Sun Jan 03, 2016 1:42 pm

If somebody is itchy to update the Simba spreadsheet with the 2015 returns, please hold on for a couple of days, as I am working on an updated version with Serbeer.

As a side note, as I was running consistency tests, I found a couple of minor issues:
- Fixed inconsistency GLD/2007 between Data_72 and Data_85 => it is 30.56 (was 30.57 in Data_72)
- Fixed inconsistency BRSIX YOI between Data_72 and Data_85 => it is 1998 (was 1999 in Data_85)

EDIT: double-checked GLD price in 2007, and corrected accordingly.

edge · Post by **edge** » Sun Jan 03, 2016 9:59 pm

cool!

siamond · Post by **siamond** » Tue Jan 05, 2016 4:17 pm

For those of you who cannot wait any more, here is an updated *DRAFT* spreadsheet with the 2015 returns in Excel format. Note that a couple of data points (e.g. inflation) will not be known until end of January, so I used the best approximation I could find.

Again, this is a DRAFT, serbeer didn't review it yet, so use it to your own risk! And if you find mistakes, please tell me.

(temporary) link 2015: https://drive.google.com/open?id=0B0svR ... VBfWF8wUWc

Note that a couple of things are pending, will do after review as those changes would make a 1:1 comparison with the 2014 version harder:
- the Fama-French adjustment I identified in an earlier post
- review all ERs, as I noticed that at least a couple of those are outdated
- seems that a couple of GLD numbers aren't quite right (2005, 2006)

siamond · Post by **siamond** » Sat Jan 09, 2016 3:57 pm

I was preparing to do the changes described in the previous post (numerous ERs do require an update!), and I stumbled upon an issue...

For 5-yrs Treasury Bonds, VFITX is used since 1992. Before that, it appears that somebody named 'tamasset' developed an Excel spreadsheet which WAS located here: http://www.tamasset.com/other/AC2705.xls, but this URL is now obsolete. We have a similar issue for LT bonds (LTGB). I tried to contact tamasset by PM, but this user name no longer exists.

For 5-yrs treasuries, we have a simple solution, just use the model longinvest developed for pre-1972 numbers, and extend its use till 1991. This might actually improve the accuracy of the numbers. For LT treasuries, maybe just leave the tamasset numbers as is, even if the reference is not available.

Would this make sense? Does anybody know if 'tamasset' is reachable by other means, and/or if his spreadsheet is available somewhere else?

EDIT: I tried to contact the Equius fellows, where we're redirected when trying to follow the link, and it seems that I identified 'tamasset', although I don't have (yet) an answer to my question.

Kevin M · Post by **Kevin M** » Sat Jan 09, 2016 10:02 pm

I don't know what the solution is, but referencing/using data sources that are not currently available or verifiable bugs me.

Kevin

longinvest · Post by **longinvest** » Sat Jan 09, 2016 11:24 pm

The more I think about it, the more I'm inclined to recommend adding the bond return calculations of my bond fund simulator spreadsheet directly into Simba's, if only because it would avoid adding another dependency on an external spreadsheet. The additional dependency, for including the calculations, would be limited to robustly available FRED data.

siamond · Post by **siamond** » Sat Jan 09, 2016 11:43 pm

Kevin M wrote:I don't know what the solution is, but referencing/using data sources that are not currently available or verifiable bugs me.

Yup, this is exactly why I raised the point.

longinvest wrote:The more I think about it, the more I'm inclined to recommend adding the bond return calculations of my bond fund simulator spreadsheet directly into Simba's, if only because it would avoid adding another dependency on an external spreadsheet. The additional dependency, for including the calculations, would be limited to robustly available FRED data.

Well, copying by value or including the whole logic is a bit of an orthogonal topic to my current concern. Irrespective of the way we arrive at such data series, we can use it from 1972+, and with the way I restructured the latest spreadsheet, this would be very consistent because pre-1972 returns resulting from your simulator would be exactly at the same level (same tab, same column) as any 1972+ returns we could derive from your simulator (that is, until the year where VFITX was created).

Now what to do about LT Gov Bonds? Do you think you could take a stab at generalizing your simulator to deal with those too? At least, starting from the availability of FRED long-term interest rates?

(fact is the more I understand this spreadsheet, the more I realize that numerous data series used some form of 'synthetic' tool to reconstruct some missing numbers. None of them are included in the spreadsheet itself - could be viewed as good, or not good...)

longinvest · Post by **longinvest** » Sun Jan 10, 2016 12:05 am

siamond wrote: Now what to do about LT Gov Bonds? Do you think you could take a stab at generalizing your simulator to deal with those too? At least, starting from the availability of FRED long-term interest rates?

I would have to find out how "LT Gov Bonds" are defined. I guess that they are Treasuries of 10 to 30 years maturity. Could be simulated using the spreadsheet, as long as FRED contains 30-year yields. This would involve something like a 20-rungs ladder.

siamond wrote:(fact is the more I understand this spreadsheet, the more I realize that numerous data series used some form of 'synthetic' tool to reconstruct some missing numbers. None of them are included in the spreadsheet itself - could be viewed as good, or not good...)

I definitely prefer for synthetic calculations to be explicitly provided, along with the source data (such as interest rates). This way, if a user doubts the calculations or has better ones, he can redo the calculations.

siamond · Post by **siamond** » Sun Jan 10, 2016 9:53 pm

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).

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.

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

****************************************************************************************************************************************
In Excel .xlsx format:
Backtest-Portfolio-returns-rev17e.xlsx
****************************************************************************************************************************************
Check the README tab in the spreadsheet for more details about the revision history
****************************************************************************************************************************************

Kevin M · Post by **Kevin M** » Tue Jan 12, 2016 6:26 pm

siamond wrote:Reminder: Once Google Drive loads a Web page with images of tabs of spreadsheet, you have to click on the Download (arrow pointing downwards) button to get the entire spreadsheet in .xls format which you can open locally on your PC if you have MS Excel 2000 or older installed (or LibreOffice or OpenOffice -- not supported though works for many). I use Google Drive for storage only, this is not a Google spreadsheet, this is an Excel spreadsheet, so please ignore all Google-level errors.

Notes to Google Sheets users.

Although this is not a Google spreadsheet, it can be loaded and converted to Google Sheets; although the converted spreadsheet is not fully functional, as range names or macros are not preserved in the conversion, the data is accessible, which is what is of main interest to me. 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.

Here is the way it works for me on a Chromebook.

I click on siamond's link, and I see a preview of the spreadsheet. At this point I can click on each "tab" and see the preview of the contents of that sheet. But at this point it's not actually usable.

Rather than click the download icon, I click the "Open" down-arrow (to right of Open), which is the Open with Google Sheets function. After about 15-30 seconds, the xls file is opened in Google Sheets in a new Chrome tab, with all sheet tabs visible. At this point you can open each sheet, click in cells to see formulas, etc., but it is a read-only file.

Finally, I click File -> Save as Google Sheets. A new Chrome tab is opened, and in about 20 seconds the Google Sheets version is available. Again, many of the formulas don't work, but the data is available for your own use (e.g., the sheets with sheet names beginning with "Data".).

Kevin

siamond · Post by **siamond** » Tue Jan 12, 2016 8:36 pm

Kevin M wrote:Although this is not a Google spreadsheet, it can be loaded and converted to Google Sheets; although the converted spreadsheet is not fully functional, as range names or macros are not preserved in the conversion, the data is accessible, which is what is of main interest to me.

Cool. I'm glad this works for you. Thanks for sharing the tips.

One note for people who like to develop their own spreadsheets based on the raw data. The "Data_All" tab now includes returns for *ALL* funds, pre-1972, 1972+, 1985+, whatever. And I removed dependencies on other tabs from this sheet. So it should be easy enough to copy and use for your own goals. Which I actually do myself for some of my personal modeling stuff!

siamond · Post by **siamond** » Tue Jan 12, 2016 8:40 pm

siamond wrote:For 5-yrs Treasury Bonds, VFITX is used since 1992. Before that, it appears that somebody named 'tamasset' developed an Excel spreadsheet which WAS located here: http://www.tamasset.com/other/AC2705.xls, but this URL is now obsolete. We have a similar issue for LT bonds (LTGB). I tried to contact tamasset by PM, but this user name no longer exists.

For 5-yrs treasuries, we have a simple solution, just use the model longinvest developed for pre-1972 numbers, and extend its use till 1991. This might actually improve the accuracy of the numbers. For LT treasuries, maybe just leave the tamasset numbers as is, even if the reference is not available.

A quick update on this, I did contact the fellow from Equius who used to be known as 'tamasset', and this old spreadsheet of his is no longer available. So I'll proceed with the easy fix for the IT bonds in the next spreadsheet update, and we can take more time to ponder about the LT bonds. In any case, the numbers shouldn't change very much (I hope!).

Post by **LadyGeek** » Sun Jan 17, 2016 10:02 pm

I just tripped over a data source that might be helpful - the Russell Index Performance Calculator | FTSE Russell.

This tool allows you to view total returns for any of Russell's US equity indexes over customizable date ranges.

* Define return periods
* Select indexes individually or by capitalization size
* Download or print the results of your query
* Data beginning January 1, 1995

The operative phrase here is total return. Also note that Russell is now part of FTSE.

The spreadsheet sources tab shows Vanguard funds used as proxies for the Russell 2000 indices. Perhaps this data might be useful, perhaps not. However, it's worth a look - all of the Russell US indices are listed.

(Found while updating sources in the wiki's Stock market indexing page.)

siamond · Post by **siamond** » Mon Jan 18, 2016 3:38 pm

LadyGeek wrote:I just tripped over a data source that might be helpful [...] The spreadsheet sources tab shows Vanguard funds used as proxies for the Russell 2000 indices. Perhaps this data might be useful, perhaps not. However, it's worth a look - all of the Russell US indices are listed.

Actually, this is the reverse way around, the Simba spreadsheet uses various indexes as proxies in the early days, before the appropriate Vanguard funds were created. Point being to use funds accessible to regular investors, as much as possible, while providing solid history for backtesting. The Russell Web site only provides data for 1995+, so as far as I can see, this doesn't really help for the goals of the Simba spreadsheet, most relevant Vanguard funds were in existence by then.

This being said, thanks for the pointer, I didn't know that one. For folks eager to compare the performance of Vanguard funds against various indexes, this could indeed be useful.

PS. this being said, you made me realize that the description of some data sources in the Simba spreadsheet was incomplete. Will be fixed in next version.

siamond · Post by **siamond** » Wed Jan 27, 2016 2:31 pm

I posted a rev15b, mostly to provide the latest inflation numbers, check the link in this post:
viewtopic.php?f=10&t=2520&start=700#p2753812

Feedback welcome for suggested improvements or mistakes identified...

siamond · Post by **siamond** » Fri Feb 19, 2016 5:59 pm

I posted a rev15c, check the link in this post:
viewtopic.php?f=10&t=2520&start=700#p2753812

Two main changes:
- added support to generate telltale charts to compare portfolios' trajectories
- changed some of the historical returns for bonds, as the 'tamasset' source isn't available anymore (plus it was rather approximate), and longinvest expanded his 'bond fund' model to address IT bonds as well as LT bonds (many thanks!)

Tyler9000 · Post by **Tyler9000** » Sat Feb 20, 2016 5:01 pm

siamond wrote: changed some of the historical returns for bonds, as the 'tamasset' source isn't available anymore (plus it was rather approximate), and longinvest expanded his 'bond fund' model to address IT bonds as well as LT bonds (many thanks!)

Thank you very much for the work you put into this! I have two questions:

1) VUSTX has a weighted average maturity of 25 years, and according to the Vanguard website 88% of the held treasuries are between 20-30 years maturity. Comparable long bond funds like FLBIX and TLT are similar. In the new Longinvest calculations, the long bonds appear to be between 10 and 30 years maturity, which I'm not sure accurately represents the category. Am I reading this correctly? If so, why the shift?

2) When I open the file in Win10, I get an error that says "Office has detected a problem with this file. Editing it may harm your computer." This was never an issue in rev 14 or earlier and I don't have the same problem downloading Longinvest's spreadsheet. I'm curious if anyone else is having a similar issue.

BigFoot48 · Post by **BigFoot48** » Sat Feb 20, 2016 5:19 pm

Tyler9000 wrote:2) When I open the file in Win10, I get an error that says "Office has detected a problem with this file. Editing it may harm your computer." This was never an issue in rev 14 or earlier and I don't have the same problem downloading Longinvest's spreadsheet. I'm curious if anyone else is having a similar issue.

My Win10 computer using Excel 2003 or 2013 produces the same error message when I add new or revised internet links to my Retiree model. It's very frustrating and I finally just gave up on finding how to turn that false-positive message off and just provide website links in text, not in hyperlink, format. I suspect the same thing is happening in this model.

siamond · Post by **siamond** » Sat Feb 20, 2016 5:43 pm

Tyler9000 wrote:1) VUSTX has a weighted average maturity of 25 years, and according to the Vanguard website 88% of the held treasuries are between 20-30 years maturity. Comparable long bond funds like FLBIX and TLT are similar. In the new Longinvest calculations, the long bonds appear to be between 10 and 30 years maturity, which I'm not sure accurately represents the category. Am I reading this correctly? If so, why the shift

Let me post your comment on longinvest's thread, as you're essentially asking a question about his LT bond fund model (I just copied the outcome in the Simba spreadsheet, for the years where we don't have VUSTX actual returns). I'll just say two quick things here:
- I have no idea what was the assumption made in the 'tamasset' math that was previously used, as the corresponding Excel file is lost in history. I succeeded to track the author, but he has no copy of his model, nor much recollection of how he proceeded.
- yes, we did assume an average maturity of 20 years, to be consistent with the SBBI/Ibbotson definition of LT bonds, and also to be practical with the interest rate historical data available in FRED. Still, I see your point.

Tyler9000 wrote:2) When I open the file in Win10, I get an error that says "Office has detected a problem with this file. Editing it may harm your computer." This was never an issue in rev 14 or earlier and I don't have the same problem downloading Longinvest's spreadsheet. I'm curious if anyone else is having a similar issue.

Hm. This is weird. I just gave it a quick try on my son's Windows computer, and there was a 'protected view' warning for the v14 version, but only the usual warning "source originated from the Internet". Now with the v15 versions I created on my macbook, this leads indeed to a new type of 'protected view' warning, more nebulous. Ok, I'll try to investigate, although I'm not too sure how to proceed.

Thanks for the feedback in any case, appreciated.

siamond · Post by **siamond** » Sat Feb 20, 2016 6:35 pm

Tyler9000 wrote:2) When I open the file in Win10, I get an error that says "Office has detected a problem with this file. Editing it may harm your computer." This was never an issue in rev 14 or earlier and I don't have the same problem downloading Longinvest's spreadsheet. I'm curious if anyone else is having a similar issue.

Ok, here is a solid workaround... I saved the file in XLSX format, and both my son and Tyler confirmed that the issue doesn't appear any more (well, except for the usual 'Internet-originated' warning). So those of you with a not overly old version of Excel can use this file for now:
https://drive.google.com/open?id=0B0svR ... FozQURIN2M

I then tried to save this XLSX file in the XLS format, and the issue re-appeared. Darn. Will investigate more. Ideas welcome.

Note 1: longinvest's bond model spreadsheet is posted in the XLSX format.

Note 2: I took a v14 version without the annoying warning, I opened it on my macbook, I saved it in XLS format, put it on Google Drive, and I reproduced the issue on my son's Windows laptop...

longinvest · Post by **longinvest** » Sat Feb 20, 2016 8:35 pm

siamond wrote: Note 1: longinvest's bond model spreadsheet is posted in the XLSX format.

Actually, it is developed in ODS format (LibreOffice). It is then exported into XLS format*. The XLS is then imported into Google Sheet. The imported document is then exported from Sheet into XLSX format (and tested on the free http://www.office.com). I make the ODS, Sheet, and XLSX versions available.

* The export from LibreOffice to XLSX doesn't import correctly into Google Sheets.

siamond · Post by **siamond** » Sat Feb 20, 2016 10:15 pm

Ok, it sounds that the Simba spreadsheet was actually posted in XLSX format for a while, then a compatibility issue with OpenOffice was discovered, and the age-old XLS format was adopted. And now this age-old format appears to be a source of concerns. Oh joy.

I just tried the XLSX format with LibreOffice and it seems to be fine (Ladygeek, if you read this, mind giving it a quick go too?). Hopefully, the old issue with OpenOffice was fixed too. Let's see how it goes with the XLSX format for now (I modified the post of reference to point to such a file). Feedback welcome.

Post by **LadyGeek** » Sun Feb 21, 2016 12:39 pm

siamond wrote:I just tried the XLSX format with LibreOffice and it seems to be fine (Ladygeek, if you read this, mind giving it a quick go too?). Hopefully, the old issue with OpenOffice was fixed too. Let's see how it goes with the XLSX format for now (I modified the post of reference to point to such a file). Feedback welcome.

I gave it a quick look with LibreOffice Calc, which is to check functionality.

In Compare_Portfolios tab, control Telltale P3 (left-side chart) has no linked cell - the check box does not work.

Post by **LadyGeek** » Sun Feb 21, 2016 1:17 pm

Based on ideas to improve the spreadsheet in www.portfoliocharts.com ... "The Coolest Portfolio Tool on the Web"?, I did a quick test to see about implementing the pixel charts.

Instead of "pixel charts", I would describe this effect as a "heat map" which is created using conditional formatting. excel heat map - Google Search

With LibreOffice Calc:

In the Compare_Portfolios tab, select the cell range for an Annual Returns table. Format --> Conditional formatting --> Color Scale. Select the conditions as Value, with Min = (light red), Mid = (sort of yellow), Max = (light green). Instant cool colors

. Use the format painter tool to apply the changes where this effect looks useful.

How does one interpret the colors? No differently than a chart. However... everyone has a different perspective. Perhaps using colors would be more effective to make your point to some users, charts would be more effective for others.

To consider: This spreadsheet is already very resource hungry. Adding conditional formatting will slow it down further.

siamond · Post by **siamond** » Sun Feb 21, 2016 4:42 pm

LadyGeek wrote:Based on ideas to improve the spreadsheet in www.portfoliocharts.com ... "The Coolest Portfolio Tool on the Web"?, I did a quick test to see about implementing the pixel charts.
[...]
To consider: This spreadsheet is already very resource hungry. Adding conditional formatting will slow it down further.

Yes, agreed, the current spreadsheet is already quite heavy-duty. Works pretty fast on Excel, but getting slow on LibreOffice. I made another suggestion on Tyler's thread, to develop a separate spreadsheet, with the same source data.

(as a side note, I cleaned up a lot of the conditional formatting in v15c. Now it's much simpler, and... it works better).

siamond · Post by **siamond** » Sun Feb 21, 2016 5:37 pm

LadyGeek wrote:In Compare_Portfolios tab, control Telltale P3 (left-side chart) has no linked cell - the check box does not work.

Er, that was weird. Worked fine in Excel. I reproduced the issue on LibreOffice, the control was linked to a cell, but in the first tab (huh?). I deleted the checkbox on Excel, re-created it, and this appears to work now. Go figure. I copied the update to Google Drive without changing the version number (not worth it). Thanks for the feedback, Lady Eagle-Eye.

Post by **LadyGeek** » Sun Feb 21, 2016 6:29 pm

OK, so the same problem is now on the Telltale P2 control on the right-side chart. It's pointing to the first tab.

One other quirk is that opening the spreadsheet in LibreOffice Calc has all of the chart series turned on (both the Portfolio and Compare_Portfolios tabs). Once you toggle the checkbox states, they behave as expected.

This "quirk" is related to compatibility between LibreOffice and Microsoft. It's more of an annoyance than anything.

siamond wrote:Yes, agreed, the current spreadsheet is already quite heavy-duty. Works pretty fast on Excel, but getting slow on LibreOffice. I made another suggestion on Tyler's thread, to develop a separate spreadsheet, with the same source data.

I agree, it's very easy to get distracted on graphics. The first order of business is to ensure the data and formulas are correct.

Post by **LadyGeek** » Sun Feb 21, 2016 8:07 pm

Now for a quick look with Microsoft Excel 2010:

In the Portfolio and Compare_Portfolios tabs, the chart check box states were saved as enabled ("checked" state) -which matches the chart, but appear unchecked in LibreOffice Calc (does not match the chart display). This is definitely a compatibility issue.

The Telltale P2 control is now working.
==================

The chart in Lazy_Portfolios_85 tab is titled "Portfolios Return vs. Risk". Can the title be modified to clarify this chart is also known as the Efficient Frontier, e.g. "Efficient Frontier (Return vs. Risk)"?

I don't want to stir any controversy here, but the Harry Brown Permanent portfolio (P23) appears to be an outlier. Is this correct?

====================
In Data_Sources cell A8, I believe End of day index data search - MSCI can be used as a substitute for the dead MSCI link. The bottom of the table contains a link to a downloadable spreadsheet.

siamond · Post by **siamond** » Sun Feb 21, 2016 11:38 pm

LadyGeek wrote:OK, so the same problem is now on the Telltale P2 control on the right-side chart. It's pointing to the first tab.

LadyGeek wrote:Now for a quick look with Microsoft Excel 2010 [...] The Telltale P2 control is now working.

Yep, LibreOffice seems to have somewhat random hiccups with such checkboxes, and occasionally decides to relocate the linked cells in the first tab... The Excel formulas are perfectly fine. I am a little stumped. Will do more trial and error tomorrow to try to find a better way.

LadyGeek wrote:The chart in Lazy_Portfolios_85 tab is titled "Portfolios Return vs. Risk". Can the title be modified to clarify this chart is also known as the Efficient Frontier, e.g. "Efficient Frontier (Return vs. Risk)"?

An efficient frontier chart isn't exactly like that, as it typically shows a continuum of possible AAs following the same pattern, and a corresponding curved line (the 'frontier'). Here we have discrete values corresponding to very diverse portfolios. I think the title should stay as is. Or be changed to "Portfolio Returns vs. Volatility", which would be more neutral (one's definition of 'risk' isn't necessarily volatility).

LadyGeek wrote:I don't want to stir any controversy here, but the Harry Brown Permanent portfolio (P23) appears to be an outlier. Is this correct?

It is a VERY peculiar portfolio indeed. Quite a low return, but remarkably low volatility. I checked the formulas, they seem ok. The same portfolio is parameterized by default in the Compare_Portfolio tab, and the corresponding math is completely independent, and the outcome is (relief!) the same. So yes, this is a strange outlier, but it is what it is...

LadyGeek wrote:In Data_Sources cell A8, I believe End of day index data search - MSCI can be used as a substitute for the dead MSCI link. The bottom of the table contains a link to a downloadable spreadsheet.

Yes, I have more work to do with the dead links. Thanks for the proposal, will look it up.

siamond · Post by **siamond** » Mon Feb 22, 2016 11:59 am

siamond wrote:
LadyGeek wrote:OK, so the same problem is now on the Telltale P2 control on the right-side chart. It's pointing to the first tab.

LadyGeek wrote:Now for a quick look with Microsoft Excel 2010 [...] The Telltale P2 control is now working.
Yep, LibreOffice seems to have somewhat random hiccups with such checkboxes, and occasionally decides to relocate the linked cells in the first tab... The Excel formulas are perfectly fine. I am a little stumped. Will do more trial and error tomorrow to try to find a better way.

I think I fixed the issue... I used an absolute reference (including the sheet's name) for linked cells, and this seems to make LibreOffice less confused... Let me know if this is indeed working well for everybody. Same link, no change.

siamond · Post by **siamond** » Mon Feb 22, 2016 10:21 pm

siamond wrote:
LadyGeek wrote:In Data_Sources cell A8, I believe End of day index data search - MSCI can be used as a substitute for the dead MSCI link. The bottom of the table contains a link to a downloadable spreadsheet.
Yes, I have more work to do with the dead links. Thanks for the proposal, will look it up.

Took me a while to figure out how to proceed to get the old historical returns, but yes, you're right, this link is the proper entry point. THANK YOU!

Pick a recent date for DM or EM, search, then click on appropriate MCSI broad index, tune chart display, download Excel spreadsheet, do the math Dec 31st to Dec 31st.

Then when selecting net returns, we do find values very close to what we currently have in the Simba spreadsheet. Now why were net returns (e.g. including taxed dividends reinvestments) used instead of gross returns (untaxed), is a little bit puzzling.

Tyler9000 · Post by **Tyler9000** » Sun Mar 20, 2016 5:49 pm

So I have a data question. My apologies if this was addressed earlier in the thread somewhere.

What's the limiting factor in getting mid cap growth and mid cap value data not just to 1985 but also to 1972? It would be pretty cool to finish out the 9-box grid as options.

siamond · Post by **siamond** » Mon Mar 21, 2016 10:36 am

Tyler9000 wrote:What's the limiting factor in getting mid cap growth and mid cap value data not just to 1985 but also to 1972? It would be pretty cool to finish out the 9-box grid as options.

Indeed, this would be great. But. The corresponding Vanguard funds didn't exist by then. I am not quite sure the corresponding Russell indexes did exist pre-1985 (actually, I can't even find where the original authors of this spreadsheet found the 1986-1995 historical data!). And we can't use the Fama-French data in such a public spreadsheet, plus we don't even have such mid-cap tiers in the FF data library (I had to request a 3x3 matrix to Pr French a while ago to satisfy my own curiosity - see this thread).

In other words, I just don't know where to find such information (that we can use). Ideas welcome.

siamond · Post by **siamond** » Sun Jun 12, 2016 2:59 pm

Folks, I am going to update the Simba spreadsheet mid-year, mostly because I know that numerous ERs have changed for various Vanguard funds we track.

I have a few cosmetic things in the works, but the only other significant change I have on my TODO list is to switch from the 10-1 model to the 10-4 model for the pre-Vanguard 5YrT and IT bonds synthetic returns -- using longinvest's bonds fund model v1.7, as was discussed in the corresponding thread (then the average maturity will get closer to the Vanguard fund). This mostly affects pre-1972 historical returns (and a few years afterwards, but this will barely make the needle move). Sorry for the back and forth on this one, took us a while to settle on it.

I also have a pending issue with some old IFA numbers (will provide more details in a separate post or thread).

Any other update somebody might have in mind?