Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Post by stratton »

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
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Post by Paul Douglas Boyer »

stratton wrote: What did you do for inflation?

Paul
Sorry, I deferred to Simba who took the initiative to add inflation when available.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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/Bac ... -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
dandan14
Posts: 972
Joined: Wed Feb 11, 2009 6:32 am

Post by dandan14 »

This is unbelievably cool!
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Backtesting spreadsheet - rev9a

Post by simba »

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
Chuck T
Posts: 1062
Joined: Wed Sep 03, 2008 3:23 pm
Location: Lowcountry of South Carolina

Post by Chuck T »

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
Anagoge
Posts: 5
Joined: Thu Feb 26, 2009 11:59 am

Post by Anagoge »

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.
RTR2006
Posts: 826
Joined: Thu May 24, 2007 5:38 pm
Location: Near SF...

Post by RTR2006 »

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
User avatar
jamacq
Posts: 176
Joined: Sun Mar 04, 2007 8:28 am
Location: Virginia

Re: Backtesting spreadsheet - rev9a

Post by jamacq »

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
Fear and Loathing
Posts: 1025
Joined: Mon May 26, 2008 10:55 pm

Post by Fear and Loathing »

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?
waitforit
Posts: 326
Joined: Wed May 23, 2007 8:28 am

Post by waitforit »

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

Post by simba »

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

Post by simba »

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

Post by simba »

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
Anagoge
Posts: 5
Joined: Thu Feb 26, 2009 11:59 am

Post by Anagoge »

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.
DP
Posts: 660
Joined: Thu Apr 17, 2008 5:19 pm

Post by DP »

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
GammaPoint
Posts: 2661
Joined: Sun Aug 02, 2009 10:25 am
Location: Washington

Post by GammaPoint »

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!
kyounge1956
Posts: 333
Joined: Mon May 31, 2010 5:39 pm

Post by kyounge1956 »

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.
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Post by re@51.5 »

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

Post by re@51.5 »

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 7:51 am
Location: West Michigan

Post by re@51.5 »

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 5:21 am, edited 1 time in total.
User avatar
Cb
Posts: 275
Joined: Tue Feb 20, 2007 11:39 pm

Post by Cb »

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
Noobvestor
Posts: 5944
Joined: Mon Aug 23, 2010 1:09 am

Post by Noobvestor »

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
DP
Posts: 660
Joined: Thu Apr 17, 2008 5:19 pm

Post by DP »

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

Post by stratton »

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.
SDBoggled
Posts: 370
Joined: Wed Oct 27, 2010 4:35 pm

Post by SDBoggled »

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
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Post by Paul Douglas Boyer »

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.
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Backtest-Portfolio-returns-rev9b.xls typo

Post by re@51.5 »

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."
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

Post by Leif »

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.
SDBoggled
Posts: 370
Joined: Wed Oct 27, 2010 4:35 pm

Post by SDBoggled »

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.
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Backtest-Portfolio-returns-rev9b.xls typo

Post by re@51.5 »

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."
User avatar
Noobvestor
Posts: 5944
Joined: Mon Aug 23, 2010 1:09 am

Post by Noobvestor »

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
DP
Posts: 660
Joined: Thu Apr 17, 2008 5:19 pm

Post by DP »

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
jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

2010 data?

Post by jlhod1 »

Is anyone working on a revision of this wonderful tool that includes 2010 data?
User avatar
Cb
Posts: 275
Joined: Tue Feb 20, 2007 11:39 pm

Post by Cb »

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
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Backtest-Portfolio-returns-rev9b.xls typo

Post by re@51.5 »

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."
User avatar
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Post by BigFoot48 »

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 in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Post by re@51.5 »

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."
User avatar
Lbill
Posts: 4997
Joined: Thu Mar 13, 2008 11:25 pm
Location: Somewhere between Up and Down

Post by Lbill »

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
yolk8422
Posts: 1
Joined: Sun Jan 09, 2011 9:36 pm

The spreadsheet

Post by yolk8422 »

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?
wtoner
Posts: 7
Joined: Mon Nov 29, 2010 11:07 am

Simba's Spreadsheet

Post by wtoner »

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.
Chuck T
Posts: 1062
Joined: Wed Sep 03, 2008 3:23 pm
Location: Lowcountry of South Carolina

Post by Chuck T »

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
phositadc
Posts: 443
Joined: Mon Jul 26, 2010 6:17 pm

Post by phositadc »

Wow. Just discovered this spreadsheet. Absolutely amazing! Many, many thanks for developing this.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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

Post by simba »

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

Have fun with the new version.

Best Regards,
Simba
User avatar
Rager1
Posts: 1000
Joined: Fri Jun 01, 2007 12:03 pm

Post by Rager1 »

Simba,

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

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

Post by re@51.5 »

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."
User avatar
stoub
Posts: 85
Joined: Thu Jan 27, 2011 5:58 pm

Minor labeling error: VG Int'l Small symbol

Post by stoub »

Hi Simba--

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

--SET
rmelv
Posts: 106
Joined: Wed Jan 26, 2011 6:35 pm

Post by rmelv »

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 :)
Post Reply