From Bogleheads

Simba’s backtesting spreadsheet describes a spreadsheet originally developed by forum member Simba. It is designed to act as a reference for historical returns, and a way to analyze a portfolio based on such historical data. Simba no longer maintains the spreadsheet, but other forum members continue to support it and to expand functionality, as a Bogleheads community project.

Backtesting

Backtesting is a way of testing a predictive model using existing historic data.[1] It is also often used to analyze the past for research purposes. It is particularly useful as a way to quantitatively measure the impact that asset allocation had (in known history) on possible investment strategies.

Backtesting tries to estimate the performance of a strategy by assuming that this strategy was used in the past. It requires enough detail to fully simulate the past conditions; lack of this detail is a limiting factor. It can also be limited by an 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 can give you valuable information that is otherwise unavailable when models and strategies are tested on synthetic data.

Spreadsheet overview and download instructions

For a discussion of the spreadsheet, see: Bogleheads forum topic: "Simba’s backtesting spreadsheet [a Bogleheads community project]".

You can find the latest version and download instructions here.

The README tab contains detailed instructions, a glossary, and a revision history. Below is a brief overview of the individual worksheets:

• Analyze_Portfolio gives you a simple way to customize the asset allocation of a single portfolio and study its historical performance over specific time periods. This worksheet compares a custom portfolio to a benchmark portfolio (for example, 60/40) and offers charts and statistics to illustrate respective growth, drawdowns and more.
• Compare_Portfolios allows you to compare two sets of 5 different portfolios, along with the ability to change the portfolios’ composition, the time period you are looking at, and to check the impact on various metrics and charts.
• Lazy_Portfolios compares 25 predefined portfolios, and provides risk/return statistics and charts for all portfolios.
• Portfolio_Math calculates portfolio returns for all combinations of interest and performs most of the ‘under the hood’ calculations.
• Analyze_Series investigates annual returns for all selected data series. It gives you a selection of various statistics, as well as a correlation matrix and rolling returns over various time periods.
• Data_Series provides a selection mechanism, letting you choose the data series to analyze and combine into portfolios in the rest of the spreadsheet.
• Raw_Data provides a dynamic splicing mechanism to combine historical raw annual returns (for example, funds, indices and synthetic models) into full data series, while applying expense ratio adjustments.
• Raw_ER provides a dynamic splicing mechanism to combine historical raw expense ratios into full series.
• Data_Sources identifies the various data sources used in this spreadsheet. More details on a per data series basis can be found in Raw_Data.

Historical returns

The spreadsheet shows an extensive set of historical returns for various types of passive index funds, and also a few active funds. Most funds are from Vanguard and all corresponding historical returns have been validated with Vanguard. All returns are expressed as total returns; that is, including dividends. It assumes that you are based in the US, and shows returns in USD.[note 1]

When actual fund returns are not available (for example, early years), the spreadsheet tries to provide credible numbers to extend the fund’s history. It does this 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 are not available before 1985.

The Raw_Data worksheet is the main repository for historical data. The Data_Series worksheet assembles a synthetic list of data series returns and can be easily copied in another spreadsheet. This means you can perform different types of backtesting analysis from the tools provided by the Simba spreadsheet.

Telltale chart

John Bogle wrote:[2]

A telltale chart is devised simply by dividing the cumulative returns of one data series into another (the benchmark).

— "The Telltale Chart"

The Simba spreadsheet can generate this type of chart, comparing 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 their lack.

It is interesting to observe that growth charts are actually a special form of telltale charts, and can be generated by the same tool.

The Compare_Portfolios worksheet provides a telltale/growth chart. You can be use this in multiple ways:

• as a regular (nominal) growth chart, showing the growth of an initial investment over time, in nominal terms,
• as an inflation-adjusted growth chart, showing the growth of an initial investment over time, in real terms,
• as a Telltale chart showing the relative growth over time of the portfolios of interest compared to the benchmark.

You can find some typical examples of Telltale charts generated with the Simba spreadsheet here: Telling Tales – 2017 update.[dead link]

Compatibility

The spreadsheet is maintained using Microsoft Excel 2021, and provided in XLSX format. It should be compatible with most versions of Microsoft Excel.

It should work fairly well with recent versions of LibreOffice Calc, with some minor limitations (for example, dynamic chart titles do not automatically update). Once you have downloaded the file from Google Drive, open the file and save it in the suggested "ODF" default format. Otherwise, this will not preserve the correct format for the charts.

Compatibility with Google Sheets is limited. The calculations and statistics appear to work well, while the charts do not work properly.

Inner workings

As any complex spreadsheet, the inner workings of the Simba spreadsheet may not be quite obvious by just looking at formulas. The following series of blog articles document various aspects of how it actually works.

• Simba backtesting spreadsheet: a layered structure, describes how the spreadsheet is constructed, and gives an overview of its layered structure.
• Simba backtesting spreadsheet: risk metrics, risk ratios: the details of calculations of risk metrics (for example, volatility, drawdowns, and so on) and risk ratios (such as Sharpe, Sortino, and similar).
• Simba backtesting spreadsheet: advanced topics: addresses on more advanced topics (for example, unbalancing, and safe withdrawal rate).
• Simba backtesting spreadsheet: miscellaneous topics: addresses a few miscellaneous topics (for example, end label on charts, compatibility issues, and spreadsheet analytics).

Notes

1. ↑ Canadian investors can uses special settings to view portfolios using Canadian passive funds, and with returns shown in CAD.

References

1. ↑ Backtesting, on Wikipedia
2. ↑ John Bogle (June 26, 2002). "The Telltale Chart" (PDF). Morningstar Investment Forum.

External links

• Spreadsheet for backtesting (includes TrevH’s data), forum thread containing Simba’s spreadsheet.
• Issues Related to Back Testing, from FinancialTrading.com
• Backtesting: Definition, How It Works, and Downsides, from Investopedia