Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Zapped
Posts: 216
Joined: Wed May 30, 2007 3:00 pm
Location: Austin, TX

Re: OpenOffice issues

Post by Zapped »

CyberBob wrote: 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.
- Jim in Austin, TX
User avatar
CyberBob
Posts: 3387
Joined: Tue Feb 20, 2007 1:53 pm

Re: OpenOffice issues

Post by CyberBob »

Zapped wrote: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
User avatar
LH
Posts: 5490
Joined: Wed Mar 14, 2007 2:54 am

Post by LH »

Image

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
User avatar
gummy
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by 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. :D

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).
User avatar
LH
Posts: 5490
Joined: Wed Mar 14, 2007 2:54 am

Post by LH »

As always, thanks gummy : )

Thanks to Simba et. al. as well for making the spreadsheet.
kaesler
Posts: 241
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Post by kaesler »

Is enough historical data on VINEX (Vanguard Ingternational Explorer) available so that it could be added to this terrific spreadsheet?
dormouse99
Posts: 1
Joined: Sun May 27, 2007 9:38 am

Post by 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!
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

kaesler wrote: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.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

dormouse99 wrote: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
Posts: 61
Joined: Sun Mar 04, 2007 5:23 pm
Location: Portland, OR

Post by 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.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

baldeagle wrote: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.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Updated Spreadsheet 6j

Post by 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
Posts: 126
Joined: Thu Mar 01, 2007 2:54 pm

Post by 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?
User avatar
Random Musings
Posts: 6756
Joined: Thu Feb 22, 2007 3:24 pm
Location: Pennsylvania

Post by 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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

validity

Post by simba »

mwgr5 wrote: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

Simba, Help ????

Post by 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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: Simba, Help ????

Post by simba »

avstan@cableone.net wrote: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
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Post by 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 
Last edited by sterjs on Thu Jun 28, 2007 5:09 pm, edited 5 times in total.
sterjs
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Post by 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.
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

What gives?

Post by 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
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

Post by 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
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Re: What gives?

Post by sterjs »

gbs wrote: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 ... %20(2).pdf
Last edited by sterjs on Sat Jun 30, 2007 1:57 am, edited 5 times in total.
sterjs
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Post by 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
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Post by 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
Posts: 18
Joined: Mon Aug 27, 2007 1:52 pm

Post by 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
Posts: 40
Joined: Fri Apr 06, 2007 9:59 pm

Post by MDIndexer »

richrf wrote: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).
User avatar
Vig Oren
Posts: 818
Joined: Wed May 09, 2007 8:38 am
Location: NC (near Raleigh)

Why backtesting?

Post by Vig Oren »

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

BTW, where is Trev H?
richrf
Posts: 18
Joined: Mon Aug 27, 2007 1:52 pm

Post by richrf »

MDIndexer wrote:
richrf wrote: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
User avatar
SoonerSunDevil
Posts: 2000
Joined: Mon Feb 19, 2007 9:32 pm
Location: The desert

Re: Why backtesting?

Post by SoonerSunDevil »

Vig Oren wrote:What good is backtesting if most likely it will not repeat? :roll:

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
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Re: Why backtesting?

Post by johnb »

Vig Oren wrote:What good is backtesting if most likely it will not repeat? :roll:
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
User avatar
Vig Oren
Posts: 818
Joined: Wed May 09, 2007 8:38 am
Location: NC (near Raleigh)

Predicting the Past?

Post by 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
User avatar
oneleaf
Posts: 2562
Joined: Mon Feb 19, 2007 4:48 pm

Post by oneleaf »

Thank you for all the work on this spreadsheet. Very useful and very appreciated!
User avatar
gatorman
Posts: 2493
Joined: Wed Oct 31, 2007 9:35 am
Location: The Swamp

Post by gatorman »

simba wrote:
investorperson wrote: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
Posts: 14235
Joined: Mon Oct 08, 2007 4:35 pm

Post by 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.
User avatar
DaleMaley
Posts: 1592
Joined: Thu Mar 01, 2007 7:04 pm
Location: Fairbury, Illinois
Contact:

Post by DaleMaley »

Great spreadsheet :lol: :lol: :lol:

Thanks for all the work that went into putting it together :D
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
User avatar
gatorman
Posts: 2493
Joined: Wed Oct 31, 2007 9:35 am
Location: The Swamp

Post by gatorman »

Simba- Wow! What a nice tool. Thank you.
Gatorman
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by 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.

:idea: There should be a permanent link to this thread so it is easy to find. :idea:
User avatar
DaleMaley
Posts: 1592
Joined: Thu Mar 01, 2007 7:04 pm
Location: Fairbury, Illinois
Contact:

Post by 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:

Image

Image

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

:lol:
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
Malachi
Posts: 189
Joined: Tue Apr 03, 2007 5:12 pm

Post by 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
Posts: 25
Joined: Wed May 09, 2007 4:28 pm

#VALUE! error

Post by 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
Posts: 14235
Joined: Mon Oct 08, 2007 4:35 pm

Re: #VALUE! error

Post by stan1 »

iad wrote: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
Posts: 25
Joined: Wed May 09, 2007 4:28 pm

Yes, the columns add to 100%

Post by 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.
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Re: Yes, the columns add to 100%

Post by stratton »

iad wrote: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
Posts: 25
Joined: Wed May 09, 2007 4:28 pm

Well said, Paul

Post by 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
Posts: 61
Joined: Sun Mar 04, 2007 5:23 pm
Location: Portland, OR

Post by baldeagle »

Malachi -- "Trev's awesome spreadsheet"? You mean Simba's?
Malachi
Posts: 189
Joined: Tue Apr 03, 2007 5:12 pm

Post by Malachi »

baldeagle wrote:Malachi -- "Trev's awesome spreadsheet"? You mean Simba's?
Oops! Yep, I meant Simba.

Thank you for correcting that.
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

Post by edge »

Simba,

Are you planning to update with 2007 YE data?
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by 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.
Last edited by simba on Thu Jan 10, 2008 10:14 pm, edited 1 time in total.
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

Post by edge »

Hi Simba,

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

http://passive-investor.googlegroups.com/web/
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

edge,

I updated the links. Can you try it again?
Post Reply