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.

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 FundsI know it's time to update it but just wanted to confirm that the data in the spreadsheet is correct.

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.

"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

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.

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.

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).

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]

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.

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.

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.

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.

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

Last edited by stratton on Fri Jan 27, 2012 4:48 pm, edited 1 time in total.

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%

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 5000Starting Year (>=1985) 2011Ending Year (<= 2010) 2011 MAR Nominal 5%Offset #N/A# of Years of Backtesting 1

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.

Last edited by sschullo on Sat Jan 28, 2012 5:42 pm, edited 1 time in total.

Public School K-12 Educators: "Ask NOT what your annuity sales person can do for you, ask what you can do to be a Do-It-Yourselfer (DIY)."

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.

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.

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.

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.