Simba's backtesting spreadsheet
Simba's backtesting spreadsheet describes a spreadsheet originally developed by forum member Simba for the purpose of acting as a reference for historical returns, and analyzing a portfolio based on such historical data. The spreadsheet is no longer maintained by Simba, but other forum members continue to support it and to expand functionality, as a Bogleheads community project.
|This spreadsheet and the information it contains is intended for your personal, non-commercial, use only (e.g. for educational purposes). This is a learning, researching and teaching tool, nothing more.|
Backtesting refers to testing a model using historical data to predict future performance.
Backtesting seeks to estimate the performance of a strategy if it had been employed during a past period. This requires simulating past conditions with sufficient detail, making one limitation of backtesting the need for detailed historical data. A second limitation is the inability to model strategies that would affect historic prices.
Finally, backtesting, like other modeling, is limited by potential overfitting. That is, it is often possible to find a strategy that would have worked well in the past, but will not work well in the future.
Despite these limitations, backtesting provides valuable information not available when models and strategies are tested on synthetic data.
The spreadsheet is discussed in this Bogleheads® forum topic:.
The latest version and download instructions are in this post, which links to Google Drive. To download:
- Hover your mouse near the top of the page and click on the icon to download the file.
- Ignore the "Whoops! There was a problem loading more pages." message if it appears.
Detailed instructions and revision history are in the "README" tab. Here is a brief overview of the individual worksheets:
- Analyze_Portfolio is a simple way to change the allocations of various funds for a single portfolio. It provides the CAGR (Compound Annual Growth Rate), Sharpe ratio, Sortino ratio, etc. for a given time period. It also compares your portfolio to a benchmark portfolio like the 60/40 classic allocation and draws charts to compare growth, drawdowns, etc.
- Compare_Portfolios allows to compare two sets of 5 different portfolios for a given time period (e.g. 1970+ or 1985+) with the ability to change the starting and ending years.
- Lazy_Portfolios compares more than 20 lazy portfolios and shows corresponding charts and statistics.
- Data_TR_USD includes the returns of ALL the funds being tracked, as well as some historical data series (e.g. from Prof. Shiller)
- Data_Sources documents the sources used for the various historical returns.
- Data_Misc provides some additional data series, including long-lived funds, Canadian funds, etc.
The spreadsheet also includes statistics and charts for a portfolio that is rebalanced annually (default) and one that is not rebalanced (un-rebalanced).
The spreadsheet provides an extensive set of historical returns for various types of index (and non-index) funds. Most funds are from Vanguard, and all corresponding historical returns have been validated with Vanguard. All returns are expressed as total returns, i.e. including dividends. The perspective of a U.S. investor is assumed, with returns expressed in USD.
When actual fund returns are not available (e.g. early years), attempts were made to provide credible numbers to extend the fund's history, using returns from corresponding indices, and in some cases, using a synthetic model.
In general, fairly good quality data is available starting in 1927 for U.S. stocks and bonds. Most International returns are available starting in 1970. Most sector returns aren't available before 1985.
The Data_TR_USD worksheet is the main repository for such historical data, and can be easily copied in another spreadsheet, allowing to perform other types of backtesting analysis than the fairly basic tools provided by the Simba spreadsheet.
John Bogle stated:
A telltale chart is devised simply by dividing the cumulative returns of one data series into another (the benchmark).
In the Simba spreadsheet, such a chart compares the trajectory of historical returns for the various portfolios of interest to a Telltale benchmark (itself a portfolio), in a relative manner.
Using Telltale charts can be very informative, truly 'telling the tale' of what happened over time to portfolio trajectories, illustrating return to the mean properties, or lack thereof.
It is interesting to observe that growth charts are actually a special form of telltale charts, and can be generated by the same tool.
A telltale/growth chart is provided in the Compare_Portfolios worksheet and can be used in multiple ways:
- If the benchmark is defined as 100% cash, the telltale chart becomes a simple growth chart (i.e. showing the growth of an initial investment over time, in nominal terms).
- If the benchmark is defined as 100% inflation, the telltale chart becomes an inflation-adjusted growth chart (i.e. showing the growth of an initial investment over time, in real terms).
- If a more concrete benchmark is used (e.g. US Total Market, aka TSM), the telltale chart shows the relative growth over time of the portfolios of interest compared to the benchmark.
The spreadsheet is maintained using Microsoft Excel 2011, and provided in XLSX format.
It should work fairly well with recent versions of LibreOffice Calc, with some minor limitations. Once the file has been downloaded from Google Drive, open the file and save it in the suggested "ODF" default format. Otherwise, the charts will not be preserved in the correct format.
There is some level of incompatibility with Google Sheets. The calculations and statistics appear to work well, while the charts do not.
- Backtesting, on Wikipedia
- The Telltale Chart, John C. Bogle, Vanguard.com (Bogle_site), June 26, 2002.
- Spreadsheet for backtesting (includes TrevH's data), forum thread containing Simba's spreadsheet.
- Backtesting, on Wikipedia
- Issues Related to Back Testing, from FinancialTrading.com
- Backtesting, from Investopedia