Spreadsheet for backtesting (includes TrevH's data)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

Postby stratton » Thu Jan 07, 2010 11:58 pm

Paul Douglas Boyer wrote:
simba wrote:If you build on this spreadsheet, you may wish to double check the Vanguard returns and especially the Non-Vanguard returns.
And I flipped a couple of 2008 to 2009 switches, but not sure if I got them all.

What did you do for inflation?

Paul
User avatar
stratton
 
Posts: 10789
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

Postby Paul Douglas Boyer » Fri Jan 08, 2010 12:48 am

stratton wrote:What did you do for inflation?

Paul


Sorry, I deferred to Simba who took the initiative to add inflation when available.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Postby simba » Fri Jan 08, 2010 1:20 am

Paul Douglas Boyer wrote:I have already found an error: I used Average instead of CAGR in the charts.

Here is a link to a fixed version. I have also gone through Vanguard's site to add in their 2009 returns. If you build on this spreadsheet, you may wish to double check the Vanguard returns and especially the Non-Vanguard returns.
And I flipped a couple of 2008 to 2009 switches, but not sure if I got them all.

http://MadMoneyMachine.com/podcasts/Backtest-Portfolio-returns-rev8j.xls

I put a chart in the Lazy_Portfolios_85 tab, feel free to delete it since it does not include all of the portfolios in the table.


Paul - I did not use your spreadsheet as such. I had already made a few changes to the latest (local - unreleased) version rev8j with the 2009 returns. I did notice that you had an error in your version, but in my version I was using the CAGR. I meant to post it later tonight but I guess you caught the error.

Cheers,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby dandan14 » Fri Jan 08, 2010 12:25 pm

This is unbelievably cool!
User avatar
dandan14
 
Posts: 895
Joined: Wed Feb 11, 2009 8:32 am

Backtesting spreadsheet - rev9a

Postby simba » Fri Jan 15, 2010 4:21 pm

I've updated the spreadsheet with the 2009 returns and for those interested, You can download the Excel Spreadsheet [rev9a] or the OpenOffice version [rev9a]

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby Chuck T » Fri Jan 15, 2010 4:42 pm

Simba

Thanks again. This spreadsheet is a wonderful resource. Chuck
Chuck | Past Performance Is Just That - bob | For info on the SC LowCountry & Savannah GA Area Bogleheads contact me at chucktanner46@gmail.com
Chuck T
 
Posts: 1062
Joined: Wed Sep 03, 2008 4:23 pm
Location: Lowcountry of South Carolina

Postby Anagoge » Sun Jan 17, 2010 3:03 am

Thanks for the updated spreadsheet. It is clear a lot of work went into this. I took a look at the OpenOffice version and it seems to be missing the 1985-2009 data for a few mutual funds: BRSIX (J80), PCRIX (R80), and PIGLX (AM80).

I assume the goal is to track total returns for the calendar year, which would include dividends/splits, etc., as Morningstar does. If so, one 2008 number might be wrong as well:

BRSIX (see the performance tab for the fund at Morningstar for this data):
2008: -39.5 (not -41.74?)
2009: 26.0 (missing)

PCRIX:
2008: -43.3 (correct)
2009: 39.9 (missing)

PIGLX:
2008: -2.7 (corrrect)
2009: 17.2 (missing)

The 1972-2009 data should be updated similarly, I assume. Minor aside: Lazy Portfolios cell C1 references 2008.
Anagoge
 
Posts: 3
Joined: Thu Feb 26, 2009 1:59 pm

Postby RTR2006 » Sun Jan 17, 2010 2:13 pm

Just adding a nod here that I've been playing with this spreadsheet during its many incarnations and it continues to improve. My portfolio is rather uncomplicated but it allows the freedom of many wonderful 'what-if' scenarios.

Now, if we could only download the 'crystal ball forward-gazing' spreadsheet!

Thanks for a wonderful, flexible tool.

RTR
RTR2006
 
Posts: 755
Joined: Thu May 24, 2007 6:38 pm
Location: Near SF...

Re: Backtesting spreadsheet - rev9a

Postby jamacq » Sun Jan 17, 2010 2:20 pm

simba wrote:I've updated the spreadsheet with the 2009 returns and for those interested, You can download the Excel Spreadsheet [rev9a] or the OpenOffice version [rev9a]


Simba:

I noted that the return you show for VBMFX for 2009 is 6.79% in your two data sheets. However, the correct return I believe is 5.93%. VBIIX return for 2009 was 6.79%. Is it your intention to change the ITB category to use VBIIX?

Jeff
Best Regards,
Simba
User avatar
jamacq
 
Posts: 141
Joined: Sun Mar 04, 2007 10:28 am
Location: Virginia

Postby Fear and Loathing » Sun Jan 17, 2010 3:20 pm

This is excellent - and very easy to use.

Unfortunately, I only have a bond index fund available (DBMIX) that tracts the Lehman Brother's Aggregate Bond Index....what would be the appropriate equivalent?
Fear and Loathing
 
Posts: 1025
Joined: Mon May 26, 2008 11:55 pm

Postby waitforit » Sun Jan 17, 2010 5:04 pm

I apologize if this has already been answered in the 7 pages of replies - is there any plan to get international small value data into the spreadsheet?

I understand that prior to VINEX this class was somewhat un-investable and the various ETFs have but a few years of data. Are there any index return data for this class out there? Perhaps one could subtract 0.5% ER from index returns and use that as a proxy for the class.
waitforit
 
Posts: 320
Joined: Wed May 23, 2007 9:28 am

Postby simba » Mon Jan 18, 2010 4:56 pm

Chuck T wrote:Simba

Thanks again. This spreadsheet is a wonderful resource. Chuck
Thanks. Glad you like it.

RTR2006 wrote:Just adding a nod here that I've been playing with this spreadsheet during its many incarnations and it continues to improve. My portfolio is rather uncomplicated but it allows the freedom of many wonderful 'what-if' scenarios.

Now, if we could only download the 'crystal ball forward-gazing' spreadsheet!

Thanks for a wonderful, flexible tool.

RTR
RTR - Thank you for the kind words. Good luck in finding the 'crystal ball forward-gazing spreadsheet' ;)

jamacq wrote:I noted that the return you show for VBMFX for 2009 is 6.79% in your two data sheets. However, the correct return I believe is 5.93%. VBIIX return for 2009 was 6.79%. Is it your intention to change the ITB category to use VBIIX?
Jeff - Thanks for catching it. The error was an oversight and unintentional. I'll correct it in the next revision.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Mon Jan 18, 2010 5:00 pm

kb0fhp wrote:This is excellent - and very easy to use.

Unfortunately, I only have a bond index fund available (DBMIX) that tracts the Lehman Brother's Aggregate Bond Index....what would be the appropriate equivalent?


Both DBMIX(Dreyfus Bond Market Idx) and VBMFX (VG Total Bond Mkt Idx) track the Barclays US Aggregate Bond Index (formerly Lehman Brothers Aggregate Bond Index) so you can use the VBMFX. Keep in mind the ER for DBMIX is .4 where as that for VBMFX is .2
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Mon Jan 18, 2010 5:08 pm

Anagoge wrote:Thanks for the updated spreadsheet. It is clear a lot of work went into this. I took a look at the OpenOffice version and it seems to be missing the 1985-2009 data for a few mutual funds: BRSIX (J80), PCRIX (R80), and PIGLX (AM80).

I assume the goal is to track total returns for the calendar year, which would include dividends/splits, etc., as Morningstar does. If so, one 2008 number might be wrong as well:

BRSIX (see the performance tab for the fund at Morningstar for this data):
2008: -39.5 (not -41.74?)
2009: 26.0 (missing)

PCRIX:
2008: -43.3 (correct)
2009: 39.9 (missing)

PIGLX:
2008: -2.7 (corrrect)
2009: 17.2 (missing)

The 1972-2009 data should be updated similarly, I assume. Minor aside: Lazy Portfolios cell C1 references 2008.


I thought I added the PCRIX and PGLIX but looks like I inadvertently missed it in the final revision. I have added them to the next revision.

Where did you get the 2009 BRSIX returns? There was a mismatch for the 2008 returns for BRSIX between Bridgeway and Morningstar websites. I used the returns listed on the Bridgeway website.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby Anagoge » Fri Feb 05, 2010 8:01 pm

simba wrote:Where did you get the 2009 BRSIX returns? There was a mismatch for the 2008 returns for BRSIX between Bridgeway and Morningstar websites. I used the returns listed on the Bridgeway website.


I was referencing the Morningstar data. The latest Bridgeway audited prospectus (click "Get a Prospectus" in your link above) also agrees with Morningstar (page 21/23).

It isn't a big deal either way, but I suspect the Bridgeway web site just has an error.
Anagoge
 
Posts: 3
Joined: Thu Feb 26, 2009 1:59 pm

Postby DP » Fri May 28, 2010 10:49 am

Hi,
Each time a new backtest spreadsheet comes out I add a row to show the returns for the worst year. Closest that I can get to a maximum drawdown given annual data. I find this to be a very important and useful piece of data when evaluating a portfolio. Perhaps it could be added to the next version?

Thanks to all who contributed to this spreadsheet, It is invaluable.

Don
DP
 
Posts: 659
Joined: Thu Apr 17, 2008 6:19 pm

Postby GammaPoint » Fri May 28, 2010 11:20 am

I wasn't around when this was first posted, but it's very cool. Thanks for all those who were involved in making and debugging it!
GammaPoint
 
Posts: 2552
Joined: Sun Aug 02, 2009 11:25 am
Location: California

Postby kyounge1956 » Sat Jun 12, 2010 9:29 pm

FYI for any Mac users: I downloaded the Excel version of the spreadsheet. I don't have Excel on my computer, but was able to open and use the spreadsheet in "NeoOffice", an Open Office based suite. The calculators work, but the graphs don't seem to. I have NeoOffice 2.2.4
My Mac is an Intel-based Mac Mini running OS 10.5.8

here's a wikipedia article on NeoOffice: http://en.wikipedia.org/wiki/NeoOffice
and here's a link to the home page: http://www.neooffice.org/neojava/en/index.php

Interestingly, NeoOffice could not open the Open Office version of the spreadsheet. There is a newer version available than the one I have installed. Maybe the spreadsheet would work with that.
kyounge1956
 
Posts: 333
Joined: Mon May 31, 2010 6:39 pm

Postby re@51.5 » Thu Sep 16, 2010 8:14 pm

deleted
Last edited by re@51.5 on Sun Sep 19, 2010 9:55 pm, edited 1 time in total.
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby re@51.5 » Sun Sep 19, 2010 9:54 pm

I am having difficulties adding a new fund to the Backtest-Portfolio-returns-rev9b.xls spreadsheet.

The README tab says...
To add returns for additional funds, use the appropriate worksheet (Data_85_09 to add returns for the years 1985-2009. You only need to modify the cells with -
This will prepopulate the Fund Name and ticket symbol in the rest of the worksheets and then add the yearly returns.

I am adding VFICX (Vanguard Interm-Term Investment-Grade) to the Data_85_09 tab column AQ.

I added this info...
AQ1 = Interm-Term Invest-Grade
AQ2 = VFICX
AQ3 = 0.24
AQ4 = 1993

I then added the Annual Returns...
AQ64 = 0.21
AQ65 = -4.20
AQ66 = 21.39
AQ67 = 2.78
AQ68 = 8.93
AQ69 = 8.30
AQ70 = -1.53
AQ71 = 10.70
AQ72 = 9.42
AQ73 = 10.28
AQ74 = 6.29
AQ75 = 4.75
AQ76 = 1.97
AQ77 = 4.43
AQ78 = 6.14
AQ79 = -6.16
AQ80 = 17.73

AQ64 thru AQ80 is for years 1993 thru 2009.

I notice all funds have entries for all years 1985 thru 2009 no matter they started! :shock:

How/where/why did the before inception data come from????

Mike
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby re@51.5 » Thu Sep 23, 2010 10:12 pm

I really hate to be a PITA...

Can anyone please provide me a bread crumb of a clue on how/where did the data before inception come from?

Mike
Last edited by re@51.5 on Fri Sep 24, 2010 6:21 am, edited 1 time in total.
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby Cb » Thu Sep 23, 2010 10:57 pm

re@51.5 wrote:I really had to be a PITA...

Can anyone please provide me a bread crumb of a clue on how/where did the data before inception come from?

Mike


One of the tabs in the spreadsheet lists the data sources for each asset class.
User avatar
Cb
 
Posts: 268
Joined: Wed Feb 21, 2007 1:39 am

Postby Noobvestor » Sun Sep 26, 2010 3:47 am

I got excited, then I questioned my recency bias, when I realized my own simple buy-and-hold, slice-and-dice play off of the UB&H with some modifications ... beat every single portfolio in the spreedsheet :S
User avatar
Noobvestor
 
Posts: 3919
Joined: Mon Aug 23, 2010 2:09 am

Postby DP » Sun Sep 26, 2010 4:33 pm

Hi,
It's suprisingly easy to curve fit to the past, but backtesting is far from useless. I use backtesting for active trading and the way I look at is I am already a step ahead of the many many people that either have no strategy or use a strategy that never worked. At least I know the strategies I use worked at some point in time. Next I make sure that my strategy or allocation makes basic sense and is not relying on some unusual performance (eg. gold in the 70's, t-bonds in the 80's, emerging markets in the 70's thru 90's at a time when access to this class was generally not available or would have been much more expensive). The correlation values in the spreadsheet are useful. While they will break down in a bear market it is still useful to invest in uncorrelated assets.

Don
DP
 
Posts: 659
Joined: Thu Apr 17, 2008 6:19 pm

Postby stratton » Sun Sep 26, 2010 5:17 pm

DP wrote:Hi,
It's suprisingly easy to curve fit to the past, but backtesting is far from useless. I use backtesting for active trading and the way I look at is I am already a step ahead of the many many people that either have no strategy or use a strategy that never worked. At least I know the strategies I use worked at some point in time. Next I make sure that my strategy or allocation makes basic sense and is not relying on some unusual performance (eg. gold in the 70's, t-bonds in the 80's, emerging markets in the 70's thru 90's at a time when access to this class was generally not available or would have been much more expensive). The correlation values in the spreadsheet are useful. While they will break down in a bear market it is still useful to invest in uncorrelated assets.

Annual returns are a bit coarse. To see peak to trough issues you really need something like monthly data or a chart. Don't fall in love with the spreadsheet.

Paul
...and then Buffy staked Edward. The end.
User avatar
stratton
 
Posts: 10789
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

Postby SDBoggled » Wed Oct 27, 2010 5:58 pm

Thanks so much for making this spreadsheet available. I just found it and have been slow to understand the importance of international and emerging markets, so this helps a lot with seeing their effect.

I have been playing with changing the weights of combinations of TSM/VISVX/VGSIX/VEIEX/VTRIX and I think my "ideal" is 15%/30%/15%/15%/25%. Just one observation which surprised me, I think the Total un-rebalanced nominal almost always exceeds the rebalanced value ie While I see plenty portfolio reduction in Std Dev. I don't think I see any balancing benefit to return despite some individual correlations < 0.5... I also tried up to 5% VUSTX.

I was hoping that someone could explain my mistake in expecting a balancing boost.

Thanks
SDBoggled
 
Posts: 264
Joined: Wed Oct 27, 2010 5:35 pm

Postby Paul Douglas Boyer » Wed Oct 27, 2010 11:26 pm

SDBoggled wrote:Thanks so much for making this spreadsheet available. I just found it and have been slow to understand the importance of international and emerging markets, so this helps a lot with seeing their effect.
Thanks


Here's to helping speed things up for you: try adding in some gold also.
You will find that it increased the returns and decreased the risk.

Where are the bonds? Cash? I like the Harry Browne Permanent Portfolio for money you can't afford to lose.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Backtest-Portfolio-returns-rev9b.xls typo

Postby re@51.5 » Sat Nov 13, 2010 5:20 am

On the Compare_Portfolios tab, cell P68...

=STDEV(P$118:P$141) <<-- typo

=STDEV(P$118:P$143) <<-- fixed

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby Leif » Sun Nov 14, 2010 3:35 pm

SDBoggled wrote:
I was hoping that someone could explain my mistake in expecting a balancing boost.

Thanks


It depends on the market. 2/3 of the time it is going up, and 1/3 time it is going down. If the market is going up, and stocks have increased ,then cutting back on stocks can hurt future returns, if the market continues its raise.

As a result, not reblancing means the returns will increase. That is because your AA will be higher for equities, over time.

However, you are increasing your risk. While rebalancing does enforce a sell high buy low strategy. As you point out SD is lowered. I would expect that the Sharpe ratio should be higher, due to the buy low,sell high, while controling risk.
Investors should diversify across many asset-classes so that whatever happens, we will not have all our investments in underperforming asset classes and thereby fail to meet our goals-Taylor Larimore
User avatar
Leif
 
Posts: 1074
Joined: Wed Sep 19, 2007 5:15 pm

Postby SDBoggled » Mon Nov 15, 2010 2:01 pm

Leif,

Thanks for taking the time, your explanation makes a lot of sense and does seem obvious once you spelled it out. I am shocked and disappointed how often I have trouble with such basics... makes me doubt that I "really know what I am doing".

So I revisited the model and saw that Emerging markets had gone from 15% to just over 50%... I had totally overlooked the "ending allocation if not rebalanced column".

Thanks again.
SDBoggled
 
Posts: 264
Joined: Wed Oct 27, 2010 5:35 pm

Backtest-Portfolio-returns-rev9b.xls typo

Postby re@51.5 » Wed Nov 17, 2010 4:24 pm

On the Returns_72_09 tab, cell AS100 is blank, it should be...

=IF(AND($A100>=SYear72,$A100<=EYear72),1-(AQ100/AR100),"")

Just drag-copy the AS99 cell down to the AS100 cell.

This will fix the bottom left graph on the Portfolio tab. If you'll compare the bottom-right and the bottom-left graphs you will see that the bottom-left graph stops at 2008 instead of 2009.

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby Noobvestor » Wed Nov 17, 2010 5:11 pm

SDBoggled wrote:Leif,

Thanks for taking the time, your explanation makes a lot of sense and does seem obvious once you spelled it out. I am shocked and disappointed how often I have trouble with such basics... makes me doubt that I "really know what I am doing".

So I revisited the model and saw that Emerging markets had gone from 15% to just over 50%... I had totally overlooked the "ending allocation if not rebalanced column".

Thanks again.


It's a common misunderstanding, I think, in part because of the language used. We think of 'bonuses' as being financial in nature - i.e. a 'bonus' is something extra we get paid. In this case, it has a very different meaning - the 'bonus' is reduced volatility, which in general reduces yields. The key of course is that most people want to reduce, not increase, risk over time, hence rebalancing makes sense for most portfolios.
"In the absence of clarity, diversification is the only logical strategy" -= Larry Swedroe
User avatar
Noobvestor
 
Posts: 3919
Joined: Mon Aug 23, 2010 2:09 am

Postby DP » Sat Nov 27, 2010 1:21 pm

Hi,
I was looking at the correlations on the data_85_09 tab and noticed some discrepancies. I wanted to see the name of the fund so I added in the list of fund names and symbols from the Portfolio tab in front of the existing fund symbol. In the list I see some duplicates and some mismatches, and I am not clear how the data is pulled in so I'm not sure which is correct.

See for example VWNDX, VWNFX listed multiple times, mismatches in the health care fund and the energy fund seems to be missing.

Code: Select all
         VTSMX
Total US Market - TSM   VTSMX   VTSMX   1.00
Large Cap Value - LCV   VIVAX   VIVAX   0.95
Large Cap Blend - LCB   VFINX   VFINX   0.99
Large Cap Growth - LCG   VIGRX   VIGRX   0.96
Mid-Cap Blend - MCB   VIMSX   VIMSX   0.91
Small Cap Value - SCV   VISVX   VISVX   0.72
Small Cap Blend - SCB   NAESX   NAESX   0.88
Small Cap Growth - SCG   VISGX   VISGX   0.87
Micro Cap    BRSIX   BRSIX   0.63
REIT   VGSIX   VGSIX   0.55
Intl Developed - EAFE   VDMIX   VDMIX   0.67
Emerging Mkt - EM   VEIEX   VEIEX   0.61
Total_Intl - EAFE85/EM15   VGTSX   EAFE/EM   0.69
Intl Pacific   VPACX   VPACX   0.44
Intl Europe   VEURX   VEURX   0.82
Intl.Value   VTRIX   VTRIX   0.63
Commodities   PCRIX   PCRIX   0.17
Long Term Govt Bond - LTGB   VUSTX   VUSTX   0.00
5 Yr T-Bills   VFITX   VFITX   -0.01
Total Bond   VBMFX   VBMFX   0.32
Tbills/Treasury Money Mkt   VMPXX   VMPXX   0.23
Synthetic TIPS   VIPSX   S-TIPS   0.16
Wellington - Balanced   VWELX   VWELX   0.91
Wellesley - Balanced   VWINX   VWINX   0.68
Energy Fund   VGENX   VWNDX   0.48
Health Care Fund   VGHCX   VWNFX   0.63
Precious Metals Fund   VGPMX   VGHCX   0.22
Windsor   VWNDX   VWNDX   0.85
Windsor II   VWNFX   VWNFX   0.85
Short term Tax Exempt   VWSTX   VWSTX   0.18
Inter Tem Tax Exempt   VWITX   VWITX   0.43
Long Term Tax Exempt   VWLTX   VWLTX   0.46
High Yield Corp   VWEHX   VWEHX   0.79
2 Year ST Treasury   VFISX   VFISX   0.05
Mid-Cap Growth Index   VMGIX   VMGIX   0.90
Mid-Cap Value Index   VMVIX   VMVIX   0.81
VG Extended Market   VEXMX   VEXMX   0.96
Global Bond Fund   PIGLX   PIGLX   0.21
ST Investment Grade   VFSTX   VFSTX   0.58
GOLD   GOLD   GOLD   -0.19
   



Don
DP
 
Posts: 659
Joined: Thu Apr 17, 2008 6:19 pm

2010 data?

Postby jlhod1 » Tue Jan 04, 2011 11:29 am

Is anyone working on a revision of this wonderful tool that includes 2010 data?
jlhod1
 
Posts: 32
Joined: Wed Aug 05, 2009 10:11 pm

Postby Cb » Tue Jan 04, 2011 5:55 pm

It takes a few weeks to gather all the final #'s...my guess is Simba will have it updated before the month is out.

Cb
User avatar
Cb
 
Posts: 268
Joined: Wed Feb 21, 2007 1:39 am

Backtest-Portfolio-returns-rev9b.xls typo

Postby re@51.5 » Fri Jan 07, 2011 3:11 pm

Image

Image

I changed the left side compare years from 1972-2009 to 1985-2009 so that the right and left are the same so I can compare two portfolio graphs.

But they have different table info, see high-lighted yellow items.

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby BigFoot48 » Sun Jan 09, 2011 12:19 pm

This is a remarkable tool, and I hope Simba comes back to update it, but even as is it is very valuable for backtesting portfolio alternatives.

What I've learned from this is that a few funds, like in Taylor's 3 or 4 fund portfolios, will perform as well or nearly as well as portfolios with slicing and dicing in every which way. I'm now rethinking my decision to go with the Coffeehouse portfolio and will just go to the 3/4 fund portfolio and forget about worrying if value stocks are getting hammered or not.

Thanks Simba for your significant efforts on this.
Retired | Two-time Top-10 Diehard S&P500 Picker; Nine-Time Loser
User avatar
BigFoot48
 
Posts: 2074
Joined: Tue Feb 20, 2007 11:47 am
Location: Arizona

Postby simba » Sun Jan 09, 2011 2:24 pm

BigFoot/Mike (re@51.5)/Jlhod1,

I have the updated the spreadsheet with the 2010 returns. As Cb said i am waiting for a couple more funds to announce the returns in addition to the CPI numbers to be announced. Once I have them, I'll post the latest revision.

Mike/Don (DP) - the latest version fixes the issues you raised. Thanks for bringing them up.

Best Regards & Happy New Year
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby re@51.5 » Sun Jan 09, 2011 8:45 pm

Simba,

We all very much appreciate all your hard work!

I "asume" you will also include the two typos I reported above...

Nov 13, 2010
Compare_Portfolios tab, cell P68

Nov 17, 2010
Returns_72_09 tab, cell AS100

Here is another one...

Today, Jan 09, 2011
Compare_Portfolios tab
cell L67 =AVERAGE(L$118:L$143)
cell M67 =AVERAGE(M$118:M$143)
cell N67 =AVERAGE(N$118:N$143)
cell O67 =AVERAGE(O$118:O$143)
cell P67 =AVERAGE(OFFSET(P$118,P$62,0,P$61,1):OFFSET(P$118,P$62,0,P$61,1))

I think the formula in P67 should be =AVERAGE(P$118:P$143)

I have search this forum many times trying to find another typo reported about this spreadsheet... I can not find it. I think the typo was if year 1 had a negative return? Do you see this typo?

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Postby Lbill » Sun Jan 09, 2011 10:05 pm

Simba - much anticipated. Your spreadsheet is an absolute must-have in my investment toolchest. Thanks for all you've done.
"Life can only be understood backward; but it must be lived forward." ~ Søren Kierkegaard | | "You can't connect the dots looking forward; but only by looking backwards." ~ Steve Jobs
User avatar
Lbill
 
Posts: 4997
Joined: Fri Mar 14, 2008 12:25 am
Location: Somewhere between Up and Down

The spreadsheet

Postby yolk8422 » Sun Jan 09, 2011 11:50 pm

Just found this spreadsheet. It is amazing what has been put together, but the Fundadvice is slightly off from what is currently put into the Lazy Portfolio sheet. to be accurate, it looks like (unfortunately 2 more columns of data needed to be added:

International Small Cap
Intl Small Cap Value

See: Fundadvice dot com Ultimate Buy and Hold Strategy
Portfolio 6.

Anyway of adding those 2 columns since the update for 2010 is probably being worked on as we speak?
yolk8422
 
Posts: 1
Joined: Sun Jan 09, 2011 11:36 pm

Simba's Spreadsheet

Postby wtoner » Mon Jan 10, 2011 3:44 pm

Anxiously awaiting Simba's update with 2010 returns, also...

and just let me add my thanks and compliments on this creation.
This is a wonderful contribution, a powerful tool, and an obvious labor of love.
wtoner
 
Posts: 6
Joined: Mon Nov 29, 2010 1:07 pm

Postby Chuck T » Tue Jan 25, 2011 4:24 pm

Thanks again Simba. Your hard work is appreciated. The spreadsheet is a tool I use often and value very much. Chuck t
Chuck | Past Performance Is Just That - bob | For info on the SC LowCountry & Savannah GA Area Bogleheads contact me at chucktanner46@gmail.com
Chuck T
 
Posts: 1062
Joined: Wed Sep 03, 2008 4:23 pm
Location: Lowcountry of South Carolina

Postby phositadc » Wed Jan 26, 2011 11:05 am

Wow. Just discovered this spreadsheet. Absolutely amazing! Many, many thanks for developing this.
phositadc
 
Posts: 379
Joined: Mon Jul 26, 2010 7:17 pm

Postby simba » Wed Jan 26, 2011 12:29 pm

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

You can download the Excel Spreadsheet [rev10b] or the OpenOffice version [rev10b]

A couple of extra charts have been added for rolling returns for both nominal and real returns.

Have fun with the tool :)

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Wed Jan 26, 2011 12:34 pm

Mike/Lbill/yolk/wtoner/chrikenn - Thanks for you wonderful comments.

Have fun with the new version.

Best Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby Rager1 » Wed Jan 26, 2011 5:47 pm

Simba,

I echo many of the other Bogleheads who appreciate your work and sharing it with the larger community.

Ed
User avatar
Rager1
 
Posts: 802
Joined: Fri Jun 01, 2007 1:03 pm

Postby re@51.5 » Wed Jan 26, 2011 6:07 pm

Again, thanks Simba!

From Bill Bernstein's book, The Four Pillars of Investing (2010)
The study of financial history is an essential part of every investor's education. It is not possible to precisely predict the future, but knowledge of the past often allows us to identify financial risk in the here and now. Returns are uncertain. But risks, at least, can be controlled.

George Santayana, a Spanish American philosopher
Those who cannot remember the past are condemned to repeat it

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
re@51.5
 
Posts: 196
Joined: Thu Sep 02, 2010 8:51 am
Location: West Michigan

Minor labeling error: VG Int'l Small symbol

Postby stoub » Thu Jan 27, 2011 8:02 pm

Hi Simba--

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

--SET
User avatar
stoub
 
Posts: 67
Joined: Thu Jan 27, 2011 7:58 pm
Location: Berkeley, CA

Postby rmelv » Sat Jan 29, 2011 6:56 am

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
 
Posts: 106
Joined: Wed Jan 26, 2011 8:35 pm

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: Baidu [Spider], baw703916, bertilak, douginct, Emeralds, Epsilon Delta, EyeDee, FAST Enterprise [Crawler], jrh, linenfort, nirvines88, Tanelorn, Yahoo [Bot] and 54 guests