### Simba's backtesting spreadsheet [a Bogleheads community project]

Posted:

**Sun May 13, 2007 3:45 pm**[As of 27-Jan-2016, support for this spreadsheet is maintained by siamond in this post--admin LadyGeek]

[22-Jan-2017: Modified title, was "Spreadsheet for backtesting (includes TrevH's data)" --admin LadyGeek]

The backtesting tool is a spreadsheet to backtest portfolio returns from 1972 to present [1985 to present if you want to include certain Sector/Tax-Exempt funds that started after 1985] of various Vanguard Mutual Funds. This tool is customizable and you can add the returns for any mutual fund.

This tool originated from TrevH's data that was listed in numerous topics on both the forums.

The spreadsheet calculates various metrics such as CAGR/Std Dev/Sharpe Ratio/Sortino Ratio. The tools also provides rolling returns [both nominal and real] for 3,5,10 and 15 years. It lets you compare upto 5 different portfolios [both 1972-present and 1985-present].

Please see the README for more instructions/revision history. The data sources can be found in the Data_Sources worksheet.

Hope you find the tool useful. Do keep in mind that the past returns do not guarantee future results and use this tool only for learning purposes.

You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a] [see above, admin LadyGeek]

Past returns are not indicative of future returns.

I'd like to thank TrevH and all the other diehards/bogleheads who continue to assist folks like me on a daily basis.

Regards,

Simba

1 - Chas - Fix Sharpe Ratio to reflect the T-Bill Avg for the period backtesting (Currently it uses the avg for the entire 1972-2008 or 1985-2008 time period) -

2 - acr123 - Fix the formula for 2008 returns in Returns_85_08 -

3 - Chas - Fix correlations for CHP and SBFS SP-72-08 and SP-85-08 worksheets

4 - simba - Fix formula for Total-Unrebalanced in compare portfolio section

5 - MrMatt2532 - Sortino Ratio formula needs to be updated

6 - acr123 - Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula)

Edit: Corrected the link

Edit: Added data for Balanced Funds VWELX (wellington) & VWINX (Wellesley). Also corrected the data from EAFE.

Edit: Updated the spreadsheet with link to rev4.

Edit: rev4a - Changed the CAGR formula to reflect the change in initial investment. Also fixed a flaw in the forumula to calculate total.

Edit: rev5a - Improved the usability. The only changes permitted are in the worksheet portfolio.

Edit: rev5b - Changed the background color of cells that can be changed

Edit: rev5c - User inputs starting/ending year rather than # of years.

Edit: rev5d - corrected the columns/data in Returns_85_06 to match Data_85_06.

Edit: rev5f - corrected some typos and some other formula/data. Included chart.

Edit: rev5g - added CHP portfolio and Spreadsheet now compares the growth of your portfolio with CHP

Edit: rev5h - spreadsheet now includes both nominal/real returns. Also included High Yield Corp.

Edit: rev5i - corrected the formula for the CHP in 85-06 returns.

Edit: rev5j - Added ST (2 year) treasury. Corrected the returns for Pacific/Europe funds

Edit: rev5k - Added gummy's changes regarding probability estimates (charts/macro)

Edit: rev5l - Incorrect formula for ST treasury when I added gummy's changes

Edit: rev5m - Added Sortino Ratio

Edit : rev6a - Compare 5 different portfolios. SS now returns rebalanced and un-rebalanced total/returns etc. Chart shows the selected period.

Edit: rev6b - corrected the formula for real returns in portfolio comparisons.

Edit: rev6c - Changed the Intl value data returns

Edit: rev6d - Added Mid-cap Growth & Mid-Cap Value Index.

Edit: rev6e - Added VG Extended Mkt Index

Edit: rev6j - Added drawdown plots

Edit: rev6g - Added rev6g for OO

Edit: rev7a - Added 2007 returns

Edit: rev7c - Updated the CPI-U, BRSIX and PCRIX returns.

Edit: rev7d - Corrected the errors that were caused by adding 2007 returns

Edit: rev7e - Added a new parameter MAR(Minimum Acceptable Return) for Sortino Ratios; Change the method for calculating the un-rebalanced portfolios; Deduct ER for synthetic/index returns (modifiable)

Edit: rev7f - Fixed the Returns so ER is deducted for the comparisons as well.

Edit: rev7h - Added 2008 returns and some minor updates

Edit: rev8c - Added Gold returns, Now using month-month CPI instead of avg-avg. New worksheet for comparing Lazy Portfolios.

Edit: rev8d - bug fixing ; updated sources ; updated references to non-existing 07 worksheet

Edit: rev8e - Fixed formula for 2008 returns in Returns_85_09 and Fixed Sharpe Ratio to reflect the T-Bill Avg for the time period being tested.

Edit: rev8f - Fixed Correlations issues; Updated Sortino Ratio formula ; Added the ability to change the time period for comparing portfolios ; Added the ability to change the time period for comparing Lazy Portfolios.

Edit: rev8g - Fixed the formula for sortino in Returns_72_08 and Returns_85_08

See the Revision History in the spreadsheet for complete list of changes

[22-Jan-2017: Modified title, was "Spreadsheet for backtesting (includes TrevH's data)" --admin LadyGeek]

The backtesting tool is a spreadsheet to backtest portfolio returns from 1972 to present [1985 to present if you want to include certain Sector/Tax-Exempt funds that started after 1985] of various Vanguard Mutual Funds. This tool is customizable and you can add the returns for any mutual fund.

This tool originated from TrevH's data that was listed in numerous topics on both the forums.

The spreadsheet calculates various metrics such as CAGR/Std Dev/Sharpe Ratio/Sortino Ratio. The tools also provides rolling returns [both nominal and real] for 3,5,10 and 15 years. It lets you compare upto 5 different portfolios [both 1972-present and 1985-present].

Please see the README for more instructions/revision history. The data sources can be found in the Data_Sources worksheet.

Hope you find the tool useful. Do keep in mind that the past returns do not guarantee future results and use this tool only for learning purposes.

**Download Location:**You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a] [see above, admin LadyGeek]

**DISCLAIMER:**Please use this spreadsheet only for learning purposes and as a need for diversification. Do not change your Asset Allocation purely based on historical returns.Past returns are not indicative of future returns.

I'd like to thank TrevH and all the other diehards/bogleheads who continue to assist folks like me on a daily basis.

Regards,

Simba

**Disclaimer/Caution/Caveat Emptor**: Use these only for Entertainment purposes. Please do not change your asset allocation purely based on historical performance. This was a fun project and please treat it such.**Audit/Request log:**1 - Chas - Fix Sharpe Ratio to reflect the T-Bill Avg for the period backtesting (Currently it uses the avg for the entire 1972-2008 or 1985-2008 time period) -

**Fixed in rev8e**2 - acr123 - Fix the formula for 2008 returns in Returns_85_08 -

**Fixed in rev8e**3 - Chas - Fix correlations for CHP and SBFS SP-72-08 and SP-85-08 worksheets

**Fixed in rev8f**4 - simba - Fix formula for Total-Unrebalanced in compare portfolio section

**Fixed in rev8f**5 - MrMatt2532 - Sortino Ratio formula needs to be updated

**Fixed in rev8f**6 - acr123 - Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula)

**Fixed in rev8g**Edit: Corrected the link

Edit: Added data for Balanced Funds VWELX (wellington) & VWINX (Wellesley). Also corrected the data from EAFE.

Edit: Updated the spreadsheet with link to rev4.

Edit: rev4a - Changed the CAGR formula to reflect the change in initial investment. Also fixed a flaw in the forumula to calculate total.

Edit: rev5a - Improved the usability. The only changes permitted are in the worksheet portfolio.

Edit: rev5b - Changed the background color of cells that can be changed

Edit: rev5c - User inputs starting/ending year rather than # of years.

Edit: rev5d - corrected the columns/data in Returns_85_06 to match Data_85_06.

Edit: rev5f - corrected some typos and some other formula/data. Included chart.

Edit: rev5g - added CHP portfolio and Spreadsheet now compares the growth of your portfolio with CHP

Edit: rev5h - spreadsheet now includes both nominal/real returns. Also included High Yield Corp.

Edit: rev5i - corrected the formula for the CHP in 85-06 returns.

Edit: rev5j - Added ST (2 year) treasury. Corrected the returns for Pacific/Europe funds

Edit: rev5k - Added gummy's changes regarding probability estimates (charts/macro)

Edit: rev5l - Incorrect formula for ST treasury when I added gummy's changes

Edit: rev5m - Added Sortino Ratio

Edit : rev6a - Compare 5 different portfolios. SS now returns rebalanced and un-rebalanced total/returns etc. Chart shows the selected period.

Edit: rev6b - corrected the formula for real returns in portfolio comparisons.

Edit: rev6c - Changed the Intl value data returns

Edit: rev6d - Added Mid-cap Growth & Mid-Cap Value Index.

Edit: rev6e - Added VG Extended Mkt Index

Edit: rev6j - Added drawdown plots

Edit: rev6g - Added rev6g for OO

Edit: rev7a - Added 2007 returns

Edit: rev7c - Updated the CPI-U, BRSIX and PCRIX returns.

Edit: rev7d - Corrected the errors that were caused by adding 2007 returns

Edit: rev7e - Added a new parameter MAR(Minimum Acceptable Return) for Sortino Ratios; Change the method for calculating the un-rebalanced portfolios; Deduct ER for synthetic/index returns (modifiable)

Edit: rev7f - Fixed the Returns so ER is deducted for the comparisons as well.

Edit: rev7h - Added 2008 returns and some minor updates

Edit: rev8c - Added Gold returns, Now using month-month CPI instead of avg-avg. New worksheet for comparing Lazy Portfolios.

Edit: rev8d - bug fixing ; updated sources ; updated references to non-existing 07 worksheet

Edit: rev8e - Fixed formula for 2008 returns in Returns_85_09 and Fixed Sharpe Ratio to reflect the T-Bill Avg for the time period being tested.

Edit: rev8f - Fixed Correlations issues; Updated Sortino Ratio formula ; Added the ability to change the time period for comparing portfolios ; Added the ability to change the time period for comparing Lazy Portfolios.

Edit: rev8g - Fixed the formula for sortino in Returns_72_08 and Returns_85_08

See the Revision History in the spreadsheet for complete list of changes