Simba's backtesting spreadsheet [a Bogleheads community project]

[Zapped]

Just select Tools --> Protect Document and uncheck Sheet...

Thanks kaeser & CyberBob. I unprotected the entire sheet.

Now I've just got the blank graphs I mentioned and I also notice #MACRO? warnings across rows 144 & 145 on the Portfolio sheet.

[CyberBob]

Now I've just got the blank graphs I mentioned...

I'm looking at it right now and it appears that the data-range for those two graphs is incorrect and is showing the data for the graphs to be located at README$A$1.

I'll look at it later to see exactly what's up, but to just get the graphs working was an easy fix. PM me an email address and I'll shoot you a .ods file with working graphs.

Bob

[LH]

Can someone explain what these two graphs represent?

Also, my Portfolio Growth without Rebalancing (Nominal) $1,257,487
is GREATER than my Portfolio Growth - Nominal $1,088,232.

Does that happen often, that rebalancing hurts a portfolio?

Thanks,

LH

[gummy]

Can someone explain what these two graphs represent?

There's an explanation (of sorts) here:
http://www.gummy-stuff.org/return-distributions.htm

If you look here:
http://www.gummy-stuff.org/Simba.htm
you'll get an example of how one might use the (leftmost) distribution.

Does that happen often, that rebalancing hurts a portfolio?

Rebalancing replaces good performers with poorer performers
... so (maybe) it does happen often.

P.S.
I've had lots of email asking how to run my spreadsheets with (for example) google software.
I ain't found out how ... yet.

P.S.2
Once upon a time I had started a macro that did Monte Carlo, but have since removed it.
Hence the warning 'bout macros (on earlier versions of Simba's spreadsheet).

[LH]

As always, thanks gummy : )

Thanks to Simba et. al. as well for making the spreadsheet.

[kaesler]

Is enough historical data on VINEX (Vanguard Ingternational Explorer) available so that it could be added to this terrific spreadsheet?

[dormouse99]

Simba,

First, thanks so much for the excellent spreadsheet. It's a brilliant piece of work.

Pardon me if this has already been covered, but I'd like to ask a question about a possible error in the Sortino ratio calculation in rev6e. I noticed that if I input a Coffeehouse Portfolio on the Portfolio worksheet, I end up with a Sortino ratio of 1.90 (Portfolio Growth-Nominal) vs. 2.90 calculated in the SP-85-06 sheet. I checked the Sortino ratio formula (SP-85-06 worksheet cell F29) and found that it references the worksheet Data_72_06 rather than referencing worksheet Data_85_06. I tried correcting the sheet reference (and the cell range referenced in the formula) but ended up with a #VALUE error.

Any thoughts on a simple fix? Thanks once again for taking the time to make this tool available to us all!

Cheers!

Mike

First post!

[simba]

Is enough historical data on VINEX (Vanguard Ingternational Explorer) available so that it could be added to this terrific spreadsheet?

VINEX was started in 1996 so it would be difficult to add it to the spreadsheet.
However you are welcome to play around with the SS by adding it yourself.

The latest version of SS (rev 6g) that can be downloaded here makes it easier to add additional funds (you may have to change the Start year to include funds that were started in the recent years).

FYI - I had compiled the historical returns for Vanguard funds that I posted in this Thread

You can download the historical returns of Vanguard funds here.

I also compiled the returns for Dodge & Cox. You can download them here.

[simba]

Simba,

First, thanks so much for the excellent spreadsheet. It's a brilliant piece of work.

Pardon me if this has already been covered, but I'd like to ask a question about a possible error in the Sortino ratio calculation in rev6e. I noticed that if I input a Coffeehouse Portfolio on the Portfolio worksheet, I end up with a Sortino ratio of 1.90 (Portfolio Growth-Nominal) vs. 2.90 calculated in the SP-85-06 sheet. I checked the Sortino ratio formula (SP-85-06 worksheet cell F29) and found that it references the worksheet Data_72_06 rather than referencing worksheet Data_85_06. I tried correcting the sheet reference (and the cell range referenced in the formula) but ended up with a #VALUE error.

Any thoughts on a simple fix? Thanks once again for taking the time to make this tool available to us all!

Cheers!

Mike

First post!

Mike - Welcome to the forum. Thanks for the compliment.

I've corrected the sortino ratio and made a few other changes. The latest version rev6g can be downloaded here.

After confirming with gummy, I've removed the Ito's calculation from the SS for now.

I am also looking into porting it to OO.

Regards,
Simba

[baldeagle]

Hi Simba,

Something happened to the 35 year run for CHP on the Portfolio tab in rev-6g. The table still shows the $481k ending value, but the graph below it tops out around $100K. Graph looks just like the run to its right for 22 years.

[simba]

Hi Simba,

Something happened to the 35 year run for CHP on the Portfolio tab in rev-6g. The table still shows the $481k ending value, but the graph below it tops out around $100K. Graph looks just like the run to its right for 22 years.

The graph shows the real value for CHP while showing the nominal values for rebalanced & un-rebalanced. Cyberbob pointed me to that error and this will be corrected in the next revision.

[simba]

With Gummy's help - I added the drawdown plots to the spreadsheet. Also corrected the graph to show the nominal returns of CHP.

Latest version rev6j can be downloaded here.

Regards,
Simba

[mwgr5]

Thanks for all of the work on these spreadsheets. They are very interesting and fun to test different portfolios with.

I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?

[Random Musings]

Matt said:

I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?

With respect to the bond portion of the portfolio, no. IMO, best way to treat bonds (and TIPS) are to use current yields (or fixed component rate). In the case of TIPS, the future inflation number is still a guess. So for traditional bonds, you still have the unknown of price volatility, and for TIPS the unknown of inflation volatility (that's where MC simulation can come in handy).

For the equity portion, this model provides food for thought on the importance of diversifying in various asset classes - but don't use it as the holy grail - just understand that it is beneficial to diversify in various asset classes (and that historically pushing out the efficient frontier with the nth asset class may or may not work in the future), be tax-efficient, rebalance and make sure your portfolio risk matches your need to take risk.

This spreadsheet is a very good tool - but to solely determine portfolio asset allocation on this model was probably not the intention of the developers of this spreadsheet (since there are other factors involved).

That being said, I still like the overall body of work - and it's fun to play with. However, I leave you with this thought - if we had today's knowledge (and computers) in the early 1940's, what sort of conclusions would we come up with using this type of model then?

RM

[simba]

Thanks for all of the work on these spreadsheets. They are very interesting and fun to test different portfolios with.

I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?

My whole intent for developing this tool was to use it as a learning tool. I DO NOT intend to change my allocation based on data mining or historical returns. As RM and others have said, historical returns are well historical. There is no guarantee that we'll get those returns.

I wouldn't base my allocation purely based on returns/valuations etc. You have to take into consideration individual circumstances/goals/risks etc and remember "There is no such thing as a perfect portfolio, but only the one that works for you and helps you meet your goals".

Jack/Larry/Rick and Bill B agree that the next 20-30 years will not result in the same kind of returns that we saw in the previous 20 years.

Rick has posted the valuations for the next 30 years based on his research here.

Larry also gave his take on the valuations in his book The only guide to a winning investment strategy.

Speaking of valuations this is what I have so far:

Code: Select all

YEAR   Fund   "Nominal Valuations"   "Real Valuations" (Inflation =   3)
MKT-TSM   VTSMX   8   5
LCV   VIVAX   9   6
500 Idx   VFINX   8   5
LCG   VIGRX   8   5
SCV   VISVX   10   7
SCB   NAESX   9   6
SCG   VISGX   9   6
D10   BRSIX   9   6
REIT   VGSIX   8   5
EAFED   VDMIX   8   5
EM   VEIEX   10   7
EAFE85   EM15   8.5   5.5
Pacific   VPACX   8   5
Europe   VEURX   8   5
Intl Value   VTRIX   9   6
LTGB   VUSTX   5   2
5 Yr T   VFITX   4.5   1.5
ITB   VBMFX   5   2
TBILL   VMPXX   3.5   0.5
S-TIPS   VIPSX   5.5   2.5
V ST TE   VWSTX   3.5   0.5
V Int TE   VWITX   4   1
Hi-Yld C   VWEHX   7   4
ST Treasury   VFISX   4.5   1.5

Can anyone help me fill the gaps for the following asset classes/funds?

Code: Select all

MCB   VIMSX
COMM   PCRIX
Wellington   VWELX
Wellesley   VWINX
Energy   VGENX
Health   VGHCX
P.Metals   VGPMX
Windsor   VWNDX
Windsor II   VWNFX
V LT TE   VWLTX
MidCap Growth   VMGIX
MidCap Val   VMVIX
V Ext Mkt   VEXMX

Both Windsor and Windsor II are LV funds - Would it be okay to use the valuations for LV here?
Wellington (60/40) - Wellesley (40/60) are balanced funds. Would it be okay to use the valuations for LC and Inter Bond for these?

Regards,
Simba

[avstan@cableone.net]

Hi
I downloaded your spreadsheet OK. I also entered values OK but the software did not respond to the input percentages. Does it automatically update on data entry or do you have to initiate the update after data entry and if so how is it done . ? You did a fantastic job and I am anxious to try it out if I can get to work. Thanks for your help. aurel

[simba]

Hi
I downloaded your spreadsheet OK. I also entered values OK but the software did not respond to the input percentages. Does it automatically update on data entry or do you have to initiate the update after data entry and if so how is it done . ? You did a fantastic job and I am anxious to try it out if I can get to work. Thanks for your help. aurel

Aurel - Welcome to the forum. Once you enter your allocation % (as long as it equals 100%), you don't need to do anything else.

Take a look at Gummy's site to see if you entered the values correctly [rev6j does not have Ito's probability stuff but you should still be able to check if you are doing anything wrong].

Cyberbob helped me out me with a workaround to get the charts to show up in OO. If you are using OO, please download rev6g for OO .

Regards,
Simba

[sterjs]

FWIW, I created a synthetic MSCI 1750 Value Index(the index that VISVX/VBR follow). I used MSCI's data from 1996-2006 and from 1972-1995 I used ( 1.0 * Market + .40 * SmB + .80 * HmL - .18 ) = return as per Robert T's regression and the Kenneth French Annual Factor Returns.

Edit: I think there is something fishy with the result--MSCI SV is superior to FF SV using that methodology.

Code: Select all

13.39%
-20.12%
-20.04%
51.68%
51.62%
11.46%
10.08%
29.39%
15.10%
19.01%
34.88%
42.81%
15.88%
32.58%
19.91%
-4.80%
31.73%
19.23%
-20.67%
29.07%
31.48%
27.23%
-0.16%
29.96%
23.52%
34.73%
-5.12%
-2.17%
21.22%
12.95%
-6.63%
44.43%
23.72%
6.28%
19.44% 

I also assembled data on U.S. Rm-Rf (Market), SmB (Size), Mom (Momentum) and HmL (Value) factors from the French Library:

Code: Select all

YEAR   Rm-Rf   Mom   SmB   HmL            
            
1972   12.97   15.3   -12.24   2.09
1973   -24.69   29.4   -23.94   17.98
1974   -35.7   8.0   -0.61   9.36
1975   32.35   -18.7   15.04   8.63
1976   21.9   7.5   13.8   24.01
1977   -8.19   18.2   23.24   7.79
1978   1.04   12.0   14.16   0.47
1979   13.33   26.3   20.4   -2.21
1980   22.1   37.2   5.59   -24.56
1981   -18.05   -8.1   7.3   24.57
1982   10.78   34.5   8.79   13.16
1983   14.41   -9.9   13.94   18.86
1984   -4.76   9.3   -8.61   18.63
1985   24.63   14.7   -0.92   1.16
1986   10.41   8.3   -10   9.99
1987   -3.51   -3.9   -10.39   -2.54
1988   11.55   -5.5   6.72   13.78
1989   20.51   27.9   -12.01   -5.64
1990   -13.84   17.6   -14.4   -10.6
1991   29.1   14.6   16.51   -15.08
1992   6.41   3.2   7.78   23.05
1993   8.37   23.4   7.47   16.95
1994   -4.11   3.1   0.39   -0.08
1995   31.04   17.9   -6.94   -3.46
1996   16.25   6.4   -1.86   0.22
1997   26.07   11.9   -3.73   11.14
1998   19.41   23.5   -23.29   -15.03
1999   20.21   34.6   11.66   -39.4
2000   -16.71   14.9   -5.69   21.39
2001   -14.78   4.4   28.41   27.24
2002   -22.91   25.9   4.35   3.71
2003   30.74   -24.6   28.08   15.14
2004   10.69   -0.4   6.32   13.21
2005   3.21   15.0   -2.7   3.71
2006   10.58   -7.7   1.04   11.91 

[sterjs]

Another dataset I think is useful:

Decile 9-10 Stocks (1972-2005 + DFA Micro for 2006)

Code: Select all

-1.0
-40.7
-29.3
69.9
54.5
22.1
21.8
44.2
34.7
7.8
27.6
34.3
-13.9
28.4
3.2
-13.8
21.8
8.2
-27.4
50.3
27.7
20.2
-3.2
33.3
19.1
24.1
-7.9
32.2
-13.4
34.2
-14.1
78.4
16.8
3.5
16.2

Probably closer to the microcap funds out there than the D10 that is used currently. I believe D10 is around 150 million market cap at the moment.

[gbs]

sterjs, thanks for the data!

I added this data (see end of post) were FFLV and FFSV are benchmarks with 2006 returns of the DFA funds and used your D9-10 and MSCI-SCV.

For 1972-2006:

25% each S&P500, D9-10, FFLV, FFSV - CAGR 14.03% STDEV 20.01%.
25% each S&P500, NAESX, VIVAX, SCV - CAGR 13.75% STDEV 17.32%

If I were to chose between those two I chose the second. Still that does not seem right as the difference is the opposite of what I expected what gives?

Maybe some of our experts will care to chime in.

gbs

Data:

MSCI CRSP9-10 FFLV FFSV
SCV D9-10 FFLV FFSV


13.39 -1 19.4 5
-20.12 -40.7 -4 -32
-20.04 -29.3 -17.1 -20.2
51.68 69.9 47.6 66.9
51.62 54.5 50.8 57.5
11.46 22.1 -5.7 21.6
10.08 21.8 3.5 22.2
29.39 44.2 23.3 33.9
15.1 34.7 16.4 22.6
19.01 7.8 16.5 16.6
34.88 27.6 20.6 37.8
42.81 34.3 35 49.3
15.88 -13.9 9.4 3.7
32.58 28.4 31 27.9
19.91 3.2 20.3 5.1
-4.8 -13.8 3.9 -4.9
31.73 21.8 24.1 30.8
19.23 8.2 27.5 11.5
-20.67 -27.4 -22.5 -26.1
29.07 50.3 34.8 43
31.48 27.7 16 35
27.23 20.2 24.5 28.6
-0.16 -3.2 -0.3 3.4
29.96 33.3 40.1 26.7
23.52 19.1 20 22.4
34.73 24.1 33.7 36.9
-5.12 -7.9 11.9 -10
-2.17 32.2 7 4.4
21.22 -13.4 -6.4 -3.1
12.95 34.2 -2.7 40.6
-6.63 -14.1 -30.3 -11.7
44.43 78.4 36.4 74.5
23.72 16.8 17.7 27.3
6.28 3.5 9.7 4.5
19.44 16.2 20.18 21.55

[gbs]

One other thing that I tried and was not able to do was to come up with an un-rebalanced combination of VPACX and VEURX that will have the VDMIX risk/return.

1972-2006

VDMIX CAGR 11.31% STDEV 21.91%

Can someone find the matching combination and if not why not?

gbs

[sterjs]

sterjs, thanks for the data!

I added this data (see end of post) were FFLV and FFSV are benchmarks with 2006 returns of the DFA funds and used your D9-10 and MSCI-SCV.

For 1972-2006:

25% each S&P500, D9-10, FFLV, FFSV - CAGR 14.03% STDEV 20.01%.
25% each S&P500, NAESX, VIVAX, SCV - CAGR 13.75% STDEV 17.32%

If I were to chose between those two I chose the second. Still that does not seem right as the difference is the opposite of what I expected what gives?

Two reasons:

1. CRSP Decile 9 was a dog from 1972-2006. D6-8 outperformed D9-10 over that period with lower variance:

D6-8
CAGR: 13.48%
SD: 22.36%

D9-10
CAGR: 12.92%
SD: 26.65%

From a portfolio perspective, D9-10 weren't quite that bad because they had lower correlations with both U.S. Market and International Market.

The first portfolio is overexposed to D9-10 relative to the 2nd.

2. 0.8 HmL for MSCI might not be a reasonable approximation for 1972-2006. That was the factor regression for 1996-2006 only.

An IFA.com pamphlet showed HmL loading in the .4 range for FF SV, S&P 600 Barra Value and Russell 2000 Value from 1979-2003, whereas all of those were .65-.8 range from 96-06.

Index Regression:

http://www.ifa.com/Media/Images/PDF%20f ... FvsDFA.pdf

Edit:

Also found 1995-2004 regression and they show much lower HmL loading than Robert T:

http://www.ifa.com/Media/images/PDF%20f ... _msci%20(2).pdf

[sterjs]

IFA Small Value 1987-2006

Source: http://www.ifa.com/pdf/IFA_periodic_Table_shuffle.pdf

Use International Value for 85-86 I guess.

Code: Select all

.528
.336
.372
-.177
.045
-.219
.446
.211
.012
.009
-.227
.053
.190
-.031
-.046
.058
.665
.348
.232
.284

I'm loathe to use their data(their Emerging Markets construction is completely bogus, for example), but it's the only source I know of for this asset class.

[sterjs]

DJ-AIG Total Return (1993-2006):

Code: Select all

-.017
.166
.152
.232
-.034
-.270
.260
.301
-.195
.259
.239
.125
.177
.021

Total Return includes T-Bill rate.

Source: http://www.powershares.com/pdf/p-dbc-pc-1.pdf

Source has GSCI Total Return and DB Commodity Index Total Return.

[richrf]

Hi all,

This is my first post.

Can someone briefly explain to me the differences in how Nominal and Real Growth are calculated in this spreadsheet model? Appreciate it.

Rich

[MDIndexer]

Hi all,

This is my first post.

Can someone briefly explain to me the differences in how Nominal and Real Growth are calculated in this spreadsheet model? Appreciate it.

Rich

Nominal would be the value of your Asset Allocation at the time specified in the spreadsheet.

Real Value specifies the value of the dollar amount in today’s time (accounting for inflation).

[Vig Oren]

What good is backtesting if most likely it will not repeat?

BTW, where is Trev H?

[richrf]

Hi all,

This is my first post.

Can someone briefly explain to me the differences in how Nominal and Real Growth are calculated in this spreadsheet model? Appreciate it.

Rich

Nominal would be the value of your Asset Allocation at the time specified in the spreadsheet.

Real Value specifies the value of the dollar amount in today’s time (accounting for inflation).

Hi,

Thanks for the reply. This is what I thought, but it seems that when I compare a test portfolio with the Coffeehouse portfolio, that there is barely a difference between the Coffeehouse nominal and real returns while there is a significant one with the tested portfolio? Am I misreading something? Thanks again for the help.

Rich

[SoonerSunDevil]

What good is backtesting if most likely it will not repeat?

BTW, where is Trev H?

I'm not so sure that the past results of asset classes won't continue to prevail over the next few decades. I too am wondering where TrevH has been.

[johnb]

What good is backtesting if most likely it will not repeat?

Backtesting is useful for measuring results in the context of risk, plus it's useful for discovering the correlation of asset classes under different market conditions. These are things that do tend to repeat btw.

Best regards,
John

[Vig Oren]

John and Boomer,

I love to see Backtesting spreadsheets and hope that they would really help my portfolio's performance, especially now while I am retired and depend on withdrawals for a living.

BUT

If Backtesting has any merit in it why was I criticized by Ozark542 on M* discussion board in APR 2003? See it here:

Ozark542 = Ozzy

Vig = Viggy

Ozzy's post:

Predicting the past


Actually, Viggy, I'm wide awake. Or at least I was until I tried reading the stuff at Dr. Sortino's web site.

Here's the deal, Viggy. If you feel you can improve your portfolio's asset allocation by running the portfolio through various computer programs, measuring and grading various risk/reward relationships, feel free. It's okay with me. Honest. For myself, I'm not interested.

I'm also not interested in running reams of data through a computer program in order to discover how much I can withdraw yearly from my portfolio and never go broke.

Without having studied it, I'm willing to assume the Risk Grades deal is similar to the well known Efficient Frontier concept: Invest in a mix of assets that will give the best return for the least risk.

Wonderful. The problem in execution is this; both these approaches would seem to be limited to looking at PAST risk/return relationships, in order to predict FUTURE such relationships.

This approach hasn't worked very well and it never will.

There's lots of stuff we can learn by studying the past. One thing we can't learn, though, is how much the future will resemble the past.

There really is an Efficient Frontier. There really is a withdrawal rate that will allow my wife and I to spend all our money during our life times, but never go broke.

But Viggy? These things are unknown and unknowable, going forward. Such things are only knowable looking backward.

Given that such things are only knowable looking backward, academics with more letters after their names than I have money in the bank, have spent unconscionable amounts of time goobering through the past. They thus invented Modern Portfolio Theory---Beta, Alpha, R-Squared, and the crowning achievement, Sharpe Ratio. These accomplishments were celebrated and awards were given. Yes.

And then...a funny thing happened on the way to the bank. These numbers turned out to have little or no predictive value, regarding returns. And since they couldn't predict returns, they also failed to predict risk/return ratios.

Joining in the fun, M* invented their first Star Rating system, a system that graded...yep...risk- adjusted, past performance.

I wish I had 10 bucks for every post I've read where the poster said, essentially, "I have a balanced portfolio, made up entirely of 4 and 5 star funds." Too late, these jokers discovered what M* eventually discovered; past risk-adjusted performance doesn't predict future risk-adjusted performance.

I don't want to discover the Sharpe Ratio of my portfolio, Vig. I don't want to discover its Beta. I don't want to discover its Risk Grade. I have absolutely no confidence that adjusting the portfolio so that these numbers become more favorable will improve future risk/reward.

If others do want to do that, that's okay with me. I seriously doubt, though, that many successful mutual fund managers select securities in that manner. If any do, or if any money managers set their asset allocations in that manner, I'd be interested in their long-term results---results over periods of, say, 10 years, or more.

In short, Vig, computers are wondrous tools, but that's all they are. Every computer on Earth, all linked up and working 24/7, from now on, won't tell me my survivable withdrawal rate. Neither will they tell me what asset allocation would give me the best risk/reward ratio.

In my opinion, these things can't be calculated. We have to forge ahead without knowing these things. Deal with it.

Best regards.


----------------------------------------------------------------


My post to Ozzy:

Thanks Ozzy for the reply. You still need to explain why you go at all for mutual funds in which managers "waste" days and nights on Computerized Optimization, Indexing, Sortino's returns, paying huge amounts (your money!) to Mr. William Sharpe's company, etc.



-----------------------------------------------------------------------

Ozzy’s reply:

Nature abhors a vacuum


Humor me a minute here, Vig. List all the mutual funds in which I invest, and tell me how many managers of those funds employ the techniques you describe, in selecting individual stocks and bonds for their portfolios. Also list all my managers who pay huge amounts to Bill Sharpe or similar worthies.

You might also list for us 5 money managers who use the techniques you talk about. Don't forget to supply their total returns over the last 1, 3, 5, and 10 years.

Meanwhile, I'm busy calculating the Reynold's Number for my portfolio. Assuming it to still be liquid, of course.

Best regards.

***************************************************

p.s. the remark about the Reynold's Number was that Ozzy as an ex Jumbo jet pilot never bothered checking the the plane's turbo engines which the technicians had to do on his behalf by using computers.

Any comments?


Vig

[oneleaf]

Thank you for all the work on this spreadsheet. Very useful and very appreciated!

[gatorman]

Simba,

I may have caught an error.

Returns_85_06 Columns Z and AA (Energy, Health) feed off of Data_85_06 Columns Z and AA. However these data are for S-Tips and Wellington, not Energy, Health.

If I'm right in thinking that this is an error,
I believe it's the Energy, Health, P.Metals columns that don't match between Returns_85_06 and Data_85_06 that are causing things to shift.

Yes, No, Maybe So?

One of these days I have to learn all those formulas you use in Excel.

investorperson,

Thanks for pointing out the error. I verified the links and corrected the spreadsheet. Latest version rev5d can be downloaded here

I tried clicking on the blue "here" and got a message that the page does not exist. What am I doing incorrectly?
Gatorman

[stan1]

gatorman,

Just go to the first post in the thread for a link to the latest version, maintained by simba.

http://www.diehards.org/forum/viewtopic ... 1195903123

Current version is rev 6j.

[DaleMaley]

Great spreadsheet

Thanks for all the work that went into putting it together

[gatorman]

Simba- Wow! What a nice tool. Thank you.
Gatorman

[grayfox]

This spreadsheet is a great work. Many thanks to simba for creating and maintaining it.

The most useful thing for me is the chart showing Max Drawdown. That is the first thing I look at. My main concern is preservation of principle so I always want to know how much my portfolio can drop. I will get a lot of use out of this. It will help answer many questions that pop up.

There should be a permanent link to this thread so it is easy to find.

[DaleMaley]

I'm doing some portfolio analysis today......and decided to plot selected asset classes return versus risk......using the 1972-2006 data in the rev6j.xls version of the spreadsheet posted above. Later I will add some portfolio mixes to compare them to the asset class returns.

Investments I selected were:





And of course.....past performance is no guarantee of future results.

[Malachi]

I recently downloaded the most recent version of Trev's awesome spreadsheet. Fantastic job!

I'd like to make a suggestion. I think the "Portfolio Growth" charts should be set to use a logarithmic scale for the y-axis. Otherwise, the linear scale exaggerates the returns of recent years.

Great spreadsheet though!

[iad]

The spreadsheet looks like a terrific tool. Unfortunately, when I put my numbers in, I get nothing but #VALUE! errors at the bottom, in rows 57-58 (Portfolio Growth - Nominal and Portfolio Growth - Real).

Does anyone have any idea what the problem is?

[stan1]

The spreadsheet looks like a terrific tool. Unfortunately, when I put my numbers in, I get nothing but #VALUE! errors at the bottom, in rows 57-58 (Portfolio Growth - Nominal and Portfolio Growth - Real).

Does anyone have any idea what the problem is?

Are you sure the test portfolio you entered adds up to 100%?
Bad things happen if you are too low or too high.

There are two validation checkpoints built into the spreadsheet (Row 36/Row 49 in Rev 6j, depending upon whether you are using data back to 1972 or 1985). Make sure this checkpoint is exactly 100%.

[iad]

The problem with my getting #VALUE! errors doesn't stem from the columns not adding up to 100%. They DO add up to 100% exactly.

What's weird is that the "Compare 5 Portfolios" parts of the spreadsheet work fine for both 1972-2006 and 1985-2006 scenarios. It's just the top sections that fail in the results rows when I put my own values in.

[stratton]

The problem with my getting #VALUE! errors doesn't stem from the columns not adding up to 100%. They DO add up to 100% exactly.

What's weird is that the "Compare 5 Portfolios" parts of the spreadsheet work fine for both 1972-2006 and 1985-2006 scenarios. It's just the top sections that fail in the results rows when I put my own values in.

Go through and clear the cells with a <backspace> and make sure they are truly empty. I have funky stuff happen if you get a space or other spurious white space chars such as tabs.

Paul

[iad]

Yes, that was the problem. I had used spaces rather than backspaces. That's ok in Excel, but it wasn't here. The backspaces fixed the problem.

Thanks.

[baldeagle]

Malachi -- "Trev's awesome spreadsheet"? You mean Simba's?

[Malachi]

Malachi -- "Trev's awesome spreadsheet"? You mean Simba's?

Oops! Yep, I meant Simba.

Thank you for correcting that.

[edge]

Simba,

Are you planning to update with 2007 YE data?

[simba]

Thanks folks for all the wonderful comments.

I know most of the regular readers know that the idea behind the spreadsheet is not for optimization or to change asset allocation based on backtested results. For the newcomers to the site, this was a fun project and please treat it as such.

I've updated the SS with the 2007 returns (Yet to update data for BRSIX, PCRIX and the Inflation data[CPI-U])

You can download the Excel Spreadsheet [rev7b] and OpenOffice version [rev7b]

I have removed the charts/graphs from the OO version (for now). If anyone knows how to convert the Excel macros to be compatible with OO, I would really appreciate it.

Thanks and Best Regards,
Simba

Edit: My apologies, the file I uploaded was an older version.

[edge]

Hi Simba,

The links in your post do not work for me, but this URL does:

http://passive-investor.googlegroups.com/web/

[simba]

edge,

I updated the links. Can you try it again?