Page 9 of 23

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 06, 2012 7:41 pm
by Fear and Loathing
How do I add results for the missing years?

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 09, 2012 5:47 pm
by 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 09, 2012 5:50 pm
by sschullo
Thanks Simba!
Steve

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 13, 2012 10:04 am
by wtoner
At last, the triumphant return of Simba....

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Tue Jan 24, 2012 6:37 pm
by bob90245
Don't hibernate too long...

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 26, 2012 4:43 pm
by 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

Re:

Posted: Thu Jan 26, 2012 5:05 pm
by simba
kyounge1956 wrote: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.
Lbill wrote: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

Re:

Posted: Thu Jan 26, 2012 5:17 pm
by simba
Clive wrote: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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 26, 2012 6:10 pm
by 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. :beer

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 26, 2012 6:21 pm
by Clearly_Irrational
Lbill wrote: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. :beer
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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 26, 2012 6:23 pm
by Clive
Thanks yet again Simba. Great stuff. :D

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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 26, 2012 7:18 pm
by edge
Thanks Simba.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 11:54 am
by 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?

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 3:43 pm
by simba
Cyclone wrote: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]

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 3:49 pm
by simba
Clearly_Irrational wrote: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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 3:53 pm
by Clearly_Irrational
simba wrote: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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 4:01 pm
by Paul Douglas Boyer
Clearly_Irrational wrote:
simba wrote: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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 4:38 pm
by simba
Paul Douglas Boyer wrote: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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 4:46 pm
by 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 4:46 pm
by Paul Douglas Boyer
simba wrote: 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 6:31 pm
by 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%

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Jan 27, 2012 11:16 pm
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Sat Jan 28, 2012 1:31 pm
by grok87
simba wrote: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,

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Wed Feb 01, 2012 4:22 pm
by JasonF
Simba,

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

Thanks! Jason

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Feb 02, 2012 10:59 am
by Liquid
Thanks Simba, this is awesome! :beer

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Feb 03, 2012 6:28 pm
by 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Feb 09, 2012 1:07 pm
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Feb 10, 2012 2:51 pm
by Clive
Worksheet Data_72_11 Cell R47 missing 2011 data for PCRIX (shown as -7.56% on the Data_85_11 worksheet)

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Feb 13, 2012 1:33 pm
by roberts1001
Thank you, Simba, for sharing your work so generously.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Feb 17, 2012 4:26 pm
by wtoner
Simba,

Thanks once again for this outstanding contribution.

Walter :D

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Sun Feb 26, 2012 11:04 am
by Annonymous
Just found this post/spreadsheet. All I can say is: WOW. Thx for your hard work.

dj

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Mar 19, 2012 1:36 pm
by rntevans
Simba,

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

--Russ

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Mar 26, 2012 12:07 pm
by 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?

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Apr 12, 2012 3:43 pm
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Apr 12, 2012 3:52 pm
by Clearly_Irrational
hudson4351 wrote: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".

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Tue Jun 26, 2012 11:02 am
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Wed Nov 14, 2012 4:23 pm
by vset
:) :) :)


thank you for sharing


:beer :beer :beer :beer

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Sat Nov 24, 2012 10:15 am
by CWRadio
Is there a way to show a 4% a year withdrawal and how it effects the portfolios? Thanks

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Wed Jan 02, 2013 12:02 pm
by 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! :sharebeer

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 21, 2013 1:58 pm
by 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!

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 21, 2013 3:30 pm
by sschullo
serbeer wrote: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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 21, 2013 5:27 pm
by vset
+2
:D
:)

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 21, 2013 5:49 pm
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Jan 21, 2013 11:31 pm
by gc527
+3

:happy

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Wed Jan 23, 2013 3:10 am
by 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Wed Jan 23, 2013 2:13 pm
by serbeer
Thank you mtbomb.
Looks like Rolling returns on both Returns_72_11 and Returns_85_11 tabs are not extended properly for 2012?

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Thu Jan 24, 2013 4:13 pm
by 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

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Fri Feb 01, 2013 4:36 pm
by 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.

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Mon Feb 04, 2013 9:33 pm
by Yipee-Ki-O
Anyone know if Simba will be updating his spreadsheet this year?

Re: Spreadsheet for backtesting (includes TrevH's data)

Posted: Tue Feb 05, 2013 6:13 pm
by 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)