Spreadsheet for backtesting (includes TrevH's data)

[Fear and Loathing]

How do I add results for the missing years?

[simba]

Folks - Sorry but I have been hibernating from the forum

I'll be posting an update with 2011 returns shortly.

Wishing you all a very happy new year.

Best Regards,
Simba

[sschullo]

Thanks Simba!
Steve

[wtoner]

At last, the triumphant return of Simba....

[bob90245]

Don't hibernate too long...

[simba]

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

You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a]

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

Best Regards,
Simba

[simba]

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

kyounge1956 - This has been fixed in the latest version rev11a.

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

The spreadsheet uses some special conditions/formula that LibreOffice/OpenOffice does not currently support. Since there is no easy workaround I removed the charts in the Portfolio tab. The charts in the other tabs are fine so I left them there.

Best Regards,
Simba

[simba]

Simba - great spreadsheet - love it

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

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

1994 6.69
1995 6.38
1996 6.18

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

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

Clive - for VFITX here's the split for 1995
Capital Return - 12.98%
Income Return - 7.46%
Total - 20.44%

I posted the historical returns for Vanguard and also capital-income returns for all of the Vanguard Funds in this post - Historical Returns of Vanguard Funds
I know it's time to update it but just wanted to confirm that the data in the spreadsheet is correct.

As staythecourse said - "Beware the Simba Risk"

Best Regards,
Simba

[Lbill]

Simba - Many, many thanks for your continuing efforts to provide the updated spreadsheet. This tool has been of enormous help to me in increasing my investment knowledge, as it has been to legions of other individuals.

[Clearly_Irrational]

Simba - Many, many thanks for your continuing efforts to provide the updated spreadsheet. This tool has been of enormous help to me in increasing my investment knowledge, as it has been to legions of other individuals.

Completely agree, though I was somewhat chagrined to only find it after having done a significant amount of work to build something similar myself. However since yours was far superior to the product I had and so many more people are checking it for errors I was happy to switch. Now everyone can be sure that when they accuse me of curve fitting that at least the curves are the right ones!

I just wish I could get the same thing with quarterly data, but I doubt that's easily available.

[Clive]

Thanks yet again Simba. Great stuff.

One modification that you might like to consider for later revisions is to support negative allocations i.e. for the 1972 onwards data changing

Returns_72_11 cells C4 to AL4

=IF(Portfolio!$B$58*B$3<>0,Portfolio!$B$58*B$3,"")

Cells B7to AL50

=IF(AND($A7>=SYear72,$A7<=EYear72,B$3<>0),B$3*VLOOKUP($A7,Data_72_11!$A$8:$AM$51,MATCH(B$2,Data_72_11!$B$2:$AM$2,0)+1),"")

That way you can somewhat (very generally) emulate historic leveraged allocations (ETF's).

Only as a very broad guideline I know, but say I'm looking at what a 2x leveraged 60% TSM and 2x leveraged 40% bond allocation might have looked like historically, then allocating +120% to TSM, +80% to Total Bond and -100% to T-Bill provides a feel for what such a leveraged portfolio might have achieved on the assumption that the cost of borrowing (to leverage) compared to T-Bill yields.

In reality leveraged ETF's don't track the non leveraged (underline) due to daily rebalancing creating more unique price motions, but as a better than nowt indicator that relatively small change is a nice additional feature to support IMO. The figures would be more indicative where funds might have been borrowed to increase asset exposure. The modification might also provide a feel for where assets might have been sold short (deleting the <1 or >1 check in Returns_72_11 cells AM4 ensures the CAGR is shown in Portfolio Cell C53).

Best regards. Clive.

[edge]

Thanks Simba.

[Cyclone]

I love the spreadsheet, but I am using OpenOffice and the new versions (both) are not working for me. Nothing but errors. Am I doing something wrong?

[simba]

I love the spreadsheet, but I am using OpenOffice and the new versions (both) are not working for me. Nothing but errors. Am I doing something wrong?

Please use libreoffice. I noticed openoffice had some issues but I saw none with the libreoffice [I am using the current stable version 3.4.5] [FYI - libreoffice is a spinoff from openoffice after Oracle/SUN decided to stop supporting openoffice and is actively being maintained]

[simba]

Completely agree, though I was somewhat chagrined to only find it after having done a significant amount of work to build something similar myself. However since yours was far superior to the product I had and so many more people are checking it for errors I was happy to switch. Now everyone can be sure that when they accuse me of curve fitting that at least the curves are the right ones!

I just wish I could get the same thing with quarterly data, but I doubt that's easily available.

Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.

I do appreciate the kind words and a BIG thank you to everyone who enjoys using this spreadsheet.

Just as an FYI - the revision numbers follow the last year of returns and then any updates made for that revision. rev11a is the first revision that includes returns for 2011. Any subsequent updates would be rev11b, rev11c etc.

Best Regards,
Simba

[Clearly_Irrational]

Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.

That's what I figured, I didn't find it easily myself either. There is probably a paid database (or two) somewhere that has it but I doubt it would change the results enough to be worth it.

[Paul Douglas Boyer]

Thanks CI. I don't mind including the quarterly data but it's just too much work to capture the data for all the funds. Even if I venture out on this path, I won't be able to get the quarterly data for the funds back to 1972 or 1985 for that matter.

That's what I figured, I didn't find it easily myself either. There is probably a paid database (or two) somewhere that has it but I doubt it would change the results enough to be worth it.

I did a spreadsheet with MONTHLY data about a year ago. It does not noticeably change the results at all. I also computed Max Drawdown, and determined that you can estimate Max Drawdown by multiplying the Standard Deviation by 3.

So no, it is not worth your time to go quarterly, it wasn't even worth it going monthly.

[simba]

I did a spreadsheet with MONTHLY data about a year ago. It does not noticeably change the results at all. I also computed Max Drawdown, and determined that you can estimate Max Drawdown by multiplying the Standard Deviation by 3.

So no, it is not worth your time to go quarterly, it wasn't even worth it going monthly.

Paul - Thanks for the info. Btw did you get the monthly data from M* principia pro or somewhere else?

For the heck of it I looked up the prices for M* Principia Pro. I am absolutely sure they have a typo for the the Principia Suite. It is listed at $3.345 instead of $3,345.

I should maybe call them to see if they'll honor the listed price

-Simba

[stratton]

You can get quarterly data off Yahoo Finance. Use funds that have existed for many years.

See Wellington's performance page.

With a little judicious digging we can probably get quarterly data without much work because it's right there for the taking. As oposed to doing it manually from Yahoo historical prices or moving a slider on Morningstar's web site.

Paul

[Paul Douglas Boyer]

Paul - Thanks for the info. Btw did you get the monthly data from M* principia pro or somewhere else?

I used the RCH_Stock_Market_Functions.xla Excel Add-In from
http://finance.groups.yahoo.com/group/smf_addin/
to bring in data into Excel from Yahoo! Finance Historical Quotes (Adjusted for splits and dividends).

(Oh and I built a spreadsheet to bring in annual data into your spreadsheet too. You should try it,
it might save you some time.)

poor man's principia

Paul

[pinebarrens1]

thanks Simba, this spreadsheet has been very helpful to me. I've spent endless hours using it:) One thing (thought it makes my portfolio look much better than it actually was.....the Vanguard Small Cap Intl VFSVX return was -19.09 for 2011, not -4.00%

[sschullo]

One more thank you for your hard work Simba. I also noticed that when I inserted 2011 the output was "0" or "na" for the averages, SD and so on. But the other single years worked fine.
This is what I input:
Initial Investment 5000
Starting Year (>=1985) 2011
Ending Year (<= 2010) 2011
MAR Nominal 5%
Offset #N/A
# of Years of Backtesting 1

Output all the way across each portfolio:

Wellington
Average #DIV/0!
Std. Dev. #DIV/0!
Down SD #N/A
Up SD #N/A
CAGR #N/A
Sharpe #DIV/0!
Sortino #DIV/0!
US Mkt. Corr. #N/A
Intl. Corr. #N/A
Total - Rebalanced (N) #N/A
Total - Unbalanced (N) 0
Total - Rebalanced (Real) #N/A
$1 Portfolio = #N/A

If I input year 2010 or any other year, except 2011

I get this, which is expected:
Wellington
Average 10.94%
Std. Dev. #DIV/0!
Down SD 0.00%
Up SD #DIV/0!
CAGR 10.94%
Sharpe #DIV/0!
Sortino #DIV/0!
US Mkt. Corr. #DIV/0!
Intl. Corr. #DIV/0!
Total - Rebalanced (N) 5547
Total - Unbalanced (N) 5547
Total - Rebalanced (Real) 5465
$1 Portfolio = 1.11


Thanks again for your excellent work. I have been using it to see what I have had in my portfolio since 1994 had I used some of the lazy portfolios and compared to what actually happened. After the tech bubble disaster, I wasn't such a bad investor afterall.

[grok87]

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

You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a]

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

Best Regards,
Simba

Thanks Simba- great work as always!
cheers,

[JasonF]

Simba,

This is truly an outstanding piece of work, thank you for updating it.

Thanks! Jason

[Liquid]

Thanks Simba, this is awesome!

[pcola2234]

Random question,

If a financial advisor used this in his or her practice instead of the software one has to pay thousands a year for, would you think that right or wrong?

Do you think that person should have to pay a fee to bogleheads or at least a donation?

just curious

[RaleighStClaire]

Thanks for updating this stuff again simba, but I have found what seems like an error.

Under the compare portfolios tab, cell P77 does not update when you change the starting year in cell P59.

[Clive]

Worksheet Data_72_11 Cell R47 missing 2011 data for PCRIX (shown as -7.56% on the Data_85_11 worksheet)

[roberts1001]

Thank you, Simba, for sharing your work so generously.

[wtoner]

Simba,

Thanks once again for this outstanding contribution.

Walter

[Annonymous]

Just found this post/spreadsheet. All I can say is: WOW. Thx for your hard work.

dj

[rntevans]

Simba,

WOW! This is fantastic tool. Thanks for all your effort.

--Russ

[hudson4351]

I just checked this out for the first time today and am very impressed. Thanks for your contribution to the Bogleheads.

I just have one question: would it be possible to modify the spreadsheet to show actual after-tax historical returns, given the user's top marginal tax rate and LT capital gains rate?

[hudson4351]

I just thought of another question:

Where did the backtested data come from in cases where the fund was not in existence for the entire duration? For example, there is data for VFITX going back to 1985, yet when I look that fund up on Vanguard, the inception date is only 10/28/1991.

[Clearly_Irrational]

I just thought of another question:

Where did the backtested data come from in cases where the fund was not in existence for the entire duration? For example, there is data for VFITX going back to 1985, yet when I look that fund up on Vanguard, the inception date is only 10/28/1991.

Look at the tab on the spreadsheet named "Data Sources".

[Charybdis]

Where could I find the international or global REIT performance, from 1972 to 2011? Is there such a thing exist? I don't know where to download the global or international REIT returns.

[vset]

thank you for sharing

[adlerps]

Is there a way to show a 4% a year withdrawal and how it effects the portfolios? Thanks

[wtoner]

Ah January....always an exciting time of year as we await new data for the beloved Simba spreadsheet.

Couple of questions for interested parties..

Is there any long term return data for prefererred stocks that could be included?

Has anyone ever seen an attempt to simulate TIPS back prior to 1972?


Happy and prosperous New Year to all Bogleheads!

[serbeer]

Simba,
I realize that you rarely seen on the forum lately, but please consider updating the spreasheet with 2012 results and CPI data.

This is one of the more valuable tools of this forum!

Thank you!

[sschullo]

Simba,
I realize that you rarely seen on the forum lately, but please consider updating the spreasheet with 2012 results and CPI data.

This is one of the more valuable tools of this forum!

Thank you!

+1

[vset]

+2

[Dr. Gaius Baltar]

This spreadsheet is so amazingly helpful and interesting, I've grown so accustomed to having it around and use it very frequently. I would love to have one that's updated with 2012 data.

[gc527]

+3

[mtbomb]

I have updated this spreadsheet with the 2012 data. I am not simba, so there may be some errors. If you find any let me know and I'll make the changes until Simba updates the official one.

2012 backtest update XLS
You'll have to click download once you go to the google docs page.

Simba,
Hopefully this saves you some time from having to look up all the annual returns. I am not sure I did everything correctly, but I think it should be pretty close.



mtbomb

[serbeer]

Thank you mtbomb.
Looks like Rolling returns on both Returns_72_11 and Returns_85_11 tabs are not extended properly for 2012?

[wilson08]

I always look forward to Simba's update but thanks to you
mtbomb for an early preview. Looks like a great year all the
way around, only negative in the bunch was precious metals.

Wilson

[serbeer]

I figured Simba may not be getting to it any time soon this year. So I went ahead and fixed formulas, rolling returns, headings, etc completing mtbomb's 2012 update, relying on the 2012 data he entered. I also looked into errors reported in this thread since 2011a update was released and fixed those as well.

Backtest-Portfolio-returns-rev12b.xls

Enjoy.

Note: There are still some problems that are not related to this update, but instead to original formulas used that may not fit all year periods. For example, if year range for 1985 backtest is set to 2011-2012, Sortino Ratio in the table errors out, because there are not enough error checks and conditionals around Sortino formula used. But I don't use Sortino and not familar with it at all, so cannot fix that.

[Yipee-Ki-O]

Anyone know if Simba will be updating his spreadsheet this year?

[gbronc]

Thanks for the update, truly love this tool.

Did notice an error for the Average and Std Dev calculations on the Lazy_Portfolios_85 sheet. Rows 101 and 102 are not including 2011 and 2012 in the calculations. Should be =AVERAGE(C$116:C$143)