Spreadsheet for backtesting (includes TrevH's data)

[staythecourse]

Like many I would like thank simba for tirelessly putting all this together.

I feel like this app. Does need a few cautionary notes as a reminder not to data mine:
1. For the same reasons the sec does it: past performance does not predict future returns.
2. Remember that many of the indexs/ funds had no investible produc for much of the time points. This makes some of the more dated data purely theoritical. Keep in mind the real starting dates for the specific funds of interest.

Thanks again simba.

[jlhod1]

That is about right. Here is the return of the ST T-Bill index from Vanguard:

https://personal.vanguard.com/us/funds/ ... st=tab%3A1


2.64% in 2010

[simba]

A minor labeling error for the next revision: the symbol for "VG Int'l Small" should be VFSVX, not VFWIX.

SET - Thanks for pointing the typo. I have fixed this for the next version.

Btw - Welcome to the forum.

Best Regards,
Simba

[simba]

Hey I'm a little bit confused. I made a hypothetical portfolio that is 100% t-bills/treasury money market and I'm showing a 2.76% percent return for 2010.

That's not right, is it?

Thanks for your wonderful tool. It has really helped me analyze my portfolio

rmelv - There was a typo in the returns for VUSXX (Admiral Treasury Money Mkt). The actual returns for this fund is 0.01% instead of 2.76%. I have corrected this in the next revision. I have also corrected the symbol I was using in the Portfolio worksheet for TBills from VMPXX to VUSXX in the next version.

Best Regards,
Simba

[simba]

Like many I would like thank simba for tirelessly putting all this together.

I feel like this app. Does need a few cautionary notes as a reminder not to data mine:
1. For the same reasons the sec does it: past performance does not predict future returns.
2. Remember that many of the indexs/ funds had no investible produc for much of the time points. This makes some of the more dated data purely theoritical. Keep in mind the real starting dates for the specific funds of interest.

Thanks again simba.

Staythecourse - I currently have this in the README worksheet

Please use this spreadsheet only for learning purposes and as a need for diversification. Don't change your AA purely based on historical returns.
Past returns are not indicative of future returns.

I have added a header for DISCLAIMER in the next revision.

I started this spreadsheet as a learning tool and have always stressed to use it for its entertainment value, rather than to decide/change one's Asset Allocation based purely on historical returns.

rmelv/Staythecourse - Thank you for your compliments.

Best Regards,
Simba

[staythecourse]

Simba, please don't take offense at my comments.

I use your spreadsheet often myself and commend your work.

I just want to make sure other less experienced investor don't get "carried away".

Thanks again.

[simba]

Simba, please don't take offense at my comments.

I use your spreadsheet often myself and commend your work.

I just want to make sure other less experienced investor don't get "carried away".

Thanks again.

STC - None taken. I appreciate your comments and definitely agree with you about the disclaimer.

Best Regards,
Simba

[SteveK]

Hi Simba,

You've provided a tremendous tool. The additition of the rolling period charts is very helpful. In addition to the graph, can you have the data for 3,5,7,10 and 15 years displayed to the side or bottom with Standard Deviation and comparision to Total Market or any other index or portfolio desired for comparision? Here's the way I do it.

Rolling 3 Year Returns

Total Mkt Total Mkt My My
Return St. Dev. Return St. Dev.
1987 1989 15.46% 13.69% 9.52% 5.36%
1988 1990 12.46% 17.86% 9.02% 6.09%
1989 1991 17.55% 22.02% 12.07% 8.59%
1990 1992 11.42% 20.52% 9.82% 8.47%
1991 1993 17.64% 14.15% 13.85% 5.64%
1992 1994 6.51% 5.90% 6.53% 8.43%
1993 1995 14.39% 18.48% 10.03% 11.22%
1994 1996 17.92% 18.07% 7.70% 10.68%
1995 1997 29.10% 7.57% 11.98% 6.18%
1996 1998 25.00% 5.25% 8.26% 1.41%
1997 1999 25.97% 4.31% 6.88% 3.36%
1998 2000 10.92% 19.69% 7.04% 3.57%
1999 2001 -0.48% 19.97% 6.50% 3.58%
2000 2002 -14.30% 5.89% 7.52% 2.45%
2001 2003 -2.59% 27.77% 8.50% 4.20%
2002 2004 5.32% 26.49% 9.33% 3.65%
2003 2005 16.13% 13.17% 9.27% 3.73%
2004 2006 11.26% 4.87% 7.89% 1.59%
2005 2007 8.90% 5.65% 8.02% 1.67%
2006 2008 -8.46% 27.90% 4.59% 7.32%
2007 2009 -5.10% 33.34% 5.85% 9.42%
10.24% 15.84% 8.58% 5.55%

[staythecourse]

Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.

[simba]

Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.

STC - You got sharp eyes The chart was pulling up incorrect years. This has been fixed in the next revision (rev10c)

Edited to add the next revision version.

Best Regards,
Simba

[staythecourse]

Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.

STC - You got sharp eyes The chart was pulling up incorrect years. This has been fixed in the next revision.

Best Regards,
Simba

Thanks.

[Bongleur]

>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?

Adding the date of the current rev to Post 1 would be helpful.

[re@51.5]

>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?

No.

Post #1: "Last edited by simba on Wed Jan 26, 2011 11:21 am"

Post #1,000,0000: quote from Simba, "Posted: Mon Feb 07, 2011 4:52 pm ... This has been fixed in the next revision."

Adding the date of the current rev to Post 1 would be helpful.

Yes.

Mike

[simba]

>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?

Adding the date of the current rev to Post 1 would be helpful.

Post 1 will always have the latest version.

[simba]

The latest version of the spreadsheet (rev10c) is now available.

You can download the latest version of Excel Spreadsheet [rev10c] or the OpenOffice version [rev10c]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba

[phositadc]

Thanks dude. You are a spreadsheet wizard!

[rmelv]

Past returns may not predict future returns, but you are still a god send! Thanks for all of your hard work. I wonder how many portfolios were influenced by your spreadsheet... Funny to think about

[staythecourse]

Past returns may not predict future returns, but you are still a god send! Thanks for all of your hard work. I wonder how many portfolios were influenced by your spreadsheet... Funny to think about

I am not implying this Towards Simba's work, but I've actually thought if everyone is using your spreadsheet there may be a new risk named the "Simba risk". This would be the risk if Simba made a mistake in data entry and everyone used it incorrect to play with their portfolios.

I guess the diversification of having thousands use the spreadsheet would likely find out and eliminate mistakes such as data entry. Sort of a Efficient Spreadsheet Hypothesis.



Thanks again.

[jlhod1]

Simba Risk, I love it!

I actually think the bigger risk is for the user who creates a portfolio that ends up so curve fitted that they fail to remember that rule #1.

Namely, an a priori assumption that you can never know what will do best in the future, so you must own a lot of asset classes.

For me the primary value of the spreadsheet is seeing the power of annual rebalancing and to see what happens when I make different choices regarding *how* I achieve ownership of asset classes. For example, Total bond vs T-Bills. What was the long term affect on risk/return.

I also love playing with the years that the portfolio is held to see how it performed in different economic cycles (ie stagflation).

That is why I would never create a portfolio like Larry Swedroe's hypothetical "Minimize Fat Tails", even though it looks great when looking backwards. If it doesn't contain most asset classes (Large, Small, Dom, Int'l, Value, and ideally Real Estate) I would be extremely cautious.

[wtoner]

Correlation to me (and I know little) seems to be directional only and does not include amplitude? Asset A goes down 10 %, Asset B goes up .1% and correlation is -1 but B is not hedging A signficantly. Does anyone have info, research, or thoughts on this concept.

Also, not sure what I want to ask.. but MPT seems closely tied to standard deviations, so what is the significance of the VIX? Say stocks normally have a StdDev of 15% but at a market bottom, the VIX can reflect (and perhaps accurately) a StdDev of 70-80%. Can this be factored into a model? Should it be? Further, the implied volatility can be imputed (and used) from any asset with an option, obviously.
Does anyone have info, research, or thoughts on this concept.

[stratton]

Amplitude doesn't matter with correlation. Two items can be 100% correlatied and one item can be 10x more volatile.

[stratton]

Where are the international small cap numbers sourced from? There is no source for 20 years of it.

In the spreadsheet the data source is listed as:

Intl Small IFA website (added back expenses):1972-1974
Intl Small 1975-1996
VG Intl Explorer - 1997 - 2009
FTSE All-World ex-US Small Cap Idx (VFSVX) 2010 -

What is the source of data for the bolded ones?

TRP Intl Discovery (PRIDX) which contains EM goes back to 1989 for perf numbers.

Templeton Global Smaller Comp A (TEMGX) goes back to 1982 with perf numbers and is about 25% US and has EM according to a 1994 SEC filing I was looking at.

Paul

[Bongleur]

Could it be tweaked to show the dollar amount & % amount involved in rebalancing whenever rebalancing is needed?

Here's an idea to account for physical gold as a store of emergency value:

At portfolio creation, any chosen $$ of gold is put to the side. Its not part of the 100%

When rebalancing is required and the loss above a threshold (and if desired, a loss in both equity and bond classes) (indicating some sort of market disaster) then the gold is used as if it were outside cash, except that its value is increased by some factor.

So if $10,000 was required to rebalance, only say $5000 of gold would be deducted (factor of 2).

The factor is larger when the magnitude of rebalancing is larger (the theory that gold multiplies quickly in value during such times).

The gold is replenished to its starting value (or up to the $$ available) only when a year's gain is above some threshold; the amount above the threshold is cashed in as gold. Don't know how to estimate the current price though.

[kyounge1956]

The latest version of the spreadsheet (rev10c) is now available.

You can download the latest version of Excel Spreadsheet [rev10c] or the OpenOffice version [rev10c]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba

Hi Simba,
thanks for all the work you do on these spreadsheets. I was looking at some sample asset allocations the other day and I think I've discovered a slight correction that needs to be made. On both of the "Returns" tabs, I don't think the conditional formatting has been applied to all the cells it should have. When I entered enter a 1972-2010 portfolio of 5% each Small-cap Value, REIT, EAFE & commodities, 40% 5-year Treasuries and 40% TIPS, the worst drawdown is -3.51% (in Column AM, line 43) but the orange cell is on line 29, which is the next worst return (+0.21%). I can't tell whether the Real Return columns are affected because the worst real return is up toward the top of the spreadsheet. Using the same AA and the 1985-2010 data, the worst return is -4.07% (Column AZ, line 30), but the orange cell is on line 16, the next worst return, and the same glitch occurs in the Real Return.

I don't recall which version I downloaded—I think it was the Excel one, but I don't have Excel on my computer so I'm not completely certain. The spreadsheet I have opens fine in NeoOffice on a Mac Mini using OS 10.5.8, if that tells you anything.

[mikewitteman]

Your backtested portfolio spreadsheet is a gem. Thanks so much for creating it and posting it. It is exactly what I was looking for!

[Houston101]

Simba this is indeed a gem, thank you very much.

Will it be correct to assume that the gains are calculated without taking into account dividends?

[Cb]

Simba this is indeed a gem, thank you very much.

Will it be correct to assume that the gains are calculated without taking into account dividends?

No...the returns include dividends, and have fund ER's deducted.

[muddyglass]

hello simba (and all others who contributed to the spreadsheet), thanks for the mother of all spreadsheets! your work is really appreciated. i had a lot of fun messing around with this thing!

[floydtime]

Hello Simba, your hard work (and especially your sharing of it!) is very much appreciated.

I find it interesting, under the Lazy_Portfolios_85 tab, that the Annualized returns of all of the lazy portfolios, save one, are surprisingly close.

I'm also quite surprised to see Wellesley holding its own with the lower volatility.

I know, I know...past performance. I only said it is interesting.

[johan851]

Thanks for building this! I was wishing over the last couple of days that there was something exactly like this to sort of visualize how different allocations behaved in the past. It was really, really helpful for someone getting started.

[adlerps]

Simba thanks for all your hard work.

I was reading Scott Burns article called "Has Your Portfolio Recovered Yet?", that lead me to ask you the question.

http://assetbuilder.com/blogs/scott_bur ... d-yet.aspx

Is it possible to add an option that would allow us to enter a percent for withdrawal monthly. Example:

If you invested $1 million in a Portfolio or total of all Portfolios at the start of 2007 and reinvested all dividends and capital gains distributions and then withdraw $3,333.33 (4% a year) a month (start of 2007) what would the Portfolios have at the end of 2010?

Thanks

[John Z]

Hi Simba,
I downloaded your spreadsheet several days ago and am amazed at the amount of info here for lazy portfolios. I do have one question:
On the lazy portfolio tab I entered start year 2007 because I wanted to see how the coffeehouse did in the last 4 years. I went to the portfolio vs return risk chart and noticed that P20, Scott Burns Four Square shows std deviation a bit over 10% but the figure in column D shows 19.28%. Am I missing something? I have rev10c.
Thank you,
John

MY MISTAKE. Did the same thing (above) on another PC with a better display and see that I was looking at the wrong symbol. Very sorry.

[sschullo]

Simba and Trev,
Thanks for all of your hard work.

Quick question: are the returns of the different portfolios from 1985 to 2010 listed under tab Lazy Portfolio 85, lines 116-141?
Looks like it to me, but just verifying.

Thanks so much,
Steve

[sschullo]

Hello Simba, your hard work (and especially your sharing of it!) is very much appreciated.

I find it interesting, under the Lazy_Portfolios_85 tab, that the Annualized returns of all of the lazy portfolios, save one, are surprisingly close.

I'm also quite surprised to see Wellesley holding its own with the lower volatility.

I know, I know...past performance. I only said it is interesting.

Ditto.

[paper200]

Hi Simba,

I love your spreadsheet. Great work in putting together something like this for everyone.

I have a request - currently the way the portfolio "predictions" are designed it takes into account only a lumpsum allocation. Is it possible for you to design an additional feature that will take into account someone allocating on a "yearly" basis to see how a portfolio will evolve given a set of past perfomance of the market? You are an expert with spreadsheets and the problem to design one may be of interest to you, and if you publish, to folks in the forum.

Regards
Nathan

[staythecourse]

Hi Simba,

I love your spreadsheet. Great work in putting together something like this for everyone.

I have a request - currently the way the portfolio "predictions" are designed it takes into account only a lumpsum allocation. Is it possible for you to design an additional feature that will take into account someone allocating on a "yearly" basis to see how a portfolio will evolve given a set of past perfomance of the market? You are an expert with spreadsheets and the problem to design one may be of interest to you, and if you publish, to folks in the forum.

Regards
Nathan

Must say Simba if you do accomplish the above you will have done something quite remarkable and should start charging for it (of course not us here on Bogleheads )

[sschullo]

I have been comparing 17 years from 1994 - 2010 with my own portfolio and the Wellington fund performance has been the best so far. Ill post results when I am done.

[Lbill]

Simba-

Just wanted to verify that the OpenOffice version 10c is not able to display the returns charts on the Porfolio tab, as they are in the Excel version, correct? Is this a limitation of OpenOffice?

[Clive]

Simba - great spreadsheet - love it

Query on the 5 year treasury VFITX for 1995 though that shows a 20.44% gain.

From http://www.federalreserve.gov/releases/h15/data.htm it looks like the 5 year T yields were

1994 6.69
1995 6.38
1996 6.18

So I can't see how a 20.44% gain in 1995 occurred.

Yahoo shows that value http://au.finance.yahoo.com/q/pm?s=VFITX - but just seems ???

[rwwoods]

Where is Simba? I have not seen a post from him since Feb.

[sschullo]

Simba and TrevH,
Will you have data for 2011 early next year?
Thanks for your outstanding work.
Steve

[wtoner]

Yes .....where is Simba? Concern grows daily...

[edge]

I wonder if this will be updated?

[stratton]

I wonder if this will be updated?

Previously the update took place after the CPI-U is issued for year which will probably be in a few weeks.

Paul

[Lbill]

I'd die without my Simba fix!!!!!

[sschullo]

I'd die without my Simba fix!!!!!

Yea, I want to find out if my portfolio has beaten more lazy portfolios. I only beat one, 2nd Grader. I need more!

[staythecourse]

Since we are already begging for requests for Simba...

Can you please add a feature that allows to find the value of a portfolio by regular additions to the portfolio over a period of time, i.e. regular monthly additions. It is unusual this is the most common way most folks invest (monthly contributions through a 401k for example) yet there is NO spreadsheet that analyzes returns that way.

Thanks.

[phositadc]

It would be a TON of work to turn this spreadsheet into something that can track the growth of monthly additions. If you look at the data tables that the spreadsheet uses, it just uses yearly returns. To find data for monthly returns (i.e., what would a portfolio grow to if you make a monthly contribution of X) would be quite a task.

That being said, if you are satisfied tracking yearly contributions as opposed to monthly ones, you can easily create such a table yourself. Say you have a current portfolio of $100,000 and you contribute $10,000 per year to it. And say that Simba's spreadsheet shows backtesting returns for your portfolio of 7% per year. After 1 year, you have [($100,000*1.07)+10,000]. On a spreadsheet it is a relatively simple matter to extend this calculation for dozens of iterations, using the result from the previous calculation, so that you can see what value your portfolio would theoretically be sitting at in any given year. For example, for year 2 you would have [((year 1 result)*1.07)+10,000]. And so on and so forth.

[sschullo]

It would be a TON of work to turn this spreadsheet into something that can track the growth of monthly additions. If you look at the data tables that the spreadsheet uses, it just uses yearly returns. To find data for monthly returns (i.e., what would a portfolio grow to if you make a monthly contribution of X) would be quite a task.

That being said, if you are satisfied tracking yearly contributions as opposed to monthly ones, you can easily create such a table yourself. Say you have a current portfolio of $100,000 and you contribute $10,000 per year to it. And say that Simba's spreadsheet shows backtesting returns for your portfolio of 7% per year. After 1 year, you have [($100,000*1.07)+10,000]. On a spreadsheet it is a relatively simple matter to extend this calculation for dozens of iterations, using the result from the previous calculation, so that you can see what value your portfolio would theoretically be sitting at in any given year. For example, for year 2 you would have [((year 1 result)*1.07)+10,000]. And so on and so forth.

I agree. Forget the monthly contribution calculation. Simba you have done enough already. Thanks.

[texas_archer]

tag for the update