Simba's backtesting spreadsheet

 describes a spreadsheet originally developed by forum member Simba for the purpose of comparing a portfolio against historical data. The spreadsheet is no longer maintained by Simba, but other Bogleheads forum members are continuing to support it.

Backtesting
Backtesting refers to testing a model using historic 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 information not available when models and strategies are tested on synthetic data.

Simba's spreadsheet
The spreadsheet is obtained from this forum thread: Spreadsheet for backtesting (includes TrevH's data)

Detailed instructions are in the "README" tab. Here is a brief overview of the individual worksheets:


 * Portfolio is a simple way to change the allocations of various funds. It provides the CAGR (Compound Annual Growth Rate)/ total return / Sharpe Ratio, Sortino ratio, etc. It also compares your portfolio to sample portfolios like the Coffeehouse and draws charts to compare the growth.
 * Compare_Portfolios allows you to compare up to 5 different portfolios for 1972-2013 and 1985-2013 with the abilty to change the starting and ending years.
 * Returns_72_13 and Returns_85_13 calculate portfolio returns (Total / CAGR / Sharpe ratio / Correlation, etc.) for funds from 1972-2013 and 1985-2013 respectively.
 * Data_72_13 includes the returns of various funds from 1972-2013.
 * Data_85_13 includes the returns of various funds from 1985-2013 (to include many of the funds that were started in 1984)
 * Data_Sources lists the sources of the various fund returns.
 * Compare_Portfolios allows you to compare up to 5 different portfolios for 1972-2013 and 1985-2013 with the abilty to change the starting and ending years.

The spreadsheet also includes the total return / CAGR, etc. for a portfolio that's rebalanced annually (default) and one that is not rebalanced (un-rebalanced) and the charts reflect both the returns.