Portfolio Volatility Spreadsheet

munk

Hi,

I've knocked together a spreadsheet to calculate the overall weighted volatility of a portfolio of funds. Could someone with half decent spreadsheet + investment knowledge have a look and tell me if the total I've got for the complete portfolio volatility is correct based on the data?

I've uploaded it to Google Docs here:

Portfolio Volatility Spreadsheet
(need a Google login / gmail account etc to view - for non-google: http://spreadsheets.google.com/pub?key=pPtxcWG31ilqAJdSjQ6kbSQ)

Cheers.

EDIT: Bah, you can't view the formulas in the sheet without being a collaborator. Not to worry...

MoneyTown

The figures in the spreadsheet appear to work to my untrained eye.

I guess the volatility calculation could be improved if you had some more data for it. Perhaps you could make the same spreadsheet for 3 and 5 years if the data is available?

Your portfolio seems generally quite cautious to me, but you've got alot of it tied up in one fund which could be risky: merrill lynch uk absolute alpha.

munk

Hey MoneyTown

The portfolio is really a 'momentum' one to be fair, very actively managed (ie I check fund progress daily (definition of masochism!) and generally modify/tweak once a month as sentiment changes in the market). I started out with good intentions of not timing the market and holding a well diversified portfolio, but with the recent market volatility I've taken a more defensive stance with this particular portfolio. As you say the portfolio is cautious for my mum actually who's 70 years old.

The Blackrock UK Abs Alpha has been a rock over recent months, more like a bank account than an investment fund in terms of the low volatility of the fund together with very good steady returns - basically does what it says on the tin, provides absolute returns. Have a squidge at the smoothness of this line!:

http://www.bloomberg.com/apps/cbuilder?ticker1=MLUKAAP:LN

All the same I'm keeping a very keen eye on it and if it does start to show signs of moving away from it's average daily volatility of around 0.1-0.5% then I'll be looking to move the money perhaps into cash. Also the fund is set up to benefit more from down or turbulent markets, so will be interesting to see how things pan out with the fund once the markets eventually take an upturn.

The general plan for the portfolio is to move back into around 40% (long) equities once the markets do return to some kind of normalcy hopefully at some point in the next year.

I should say if you or anyone else wants access to this spreadsheet just drop me a PM with your email address and I'll pass it on.

I'd considered allowing collaborators on this spreadsheet in Google (a collaborator has rights to modify a document on Google Docs) but it's probably not worth it for something so trivial.

Also you mentioned adding more data to the spreadsheet - I actually did create a more complex 'portfolio balancing' spreadsheet for calculating what weight the different asset classes, geographical locations and market caps held within a given portfolio. However this was a huge PITA to maintain due to the large quantities of data required for every single holding in a portfolio - end of the day places like morningstar and bestinvest provide this service free so ...

jon3001

The volatilty of the portfolio as whole isn't the same as the weighted sum of its components. It should be lower. You have to take into account the correlation between the components. Adding highly volatile assets to a portfolio can actually reduce the overall volatility of the portfolio providing they are uncorrelated.



[discussion]
Portfolio A is 100% Short Term bonds and had an SD of 5.69 and average return of 9.92% between 1975 and 1994.
Portfolio B is 100% stocks and had an SD 15.87 and average return of 21.97% between 1975 and 1994.

What does the conservative investor, who wants minimum volatility do? Oddly it's not 100% bonds. Adding 20% stocks actually reduces the volatility to 5.43 while increasing the return to 12.33%.

You can see a similar example here involving domestic and international stocks. [discussion]



If you want to read further about the 'efficient frontier' then a couple of books could be worthwhile examining.

William Bernstein 'The Intelligent Asset Allocator'
Examines mixes of stocks/bonds (of various maturities), domestic/international large stocks, domestic/international small stocks.

Roger Gibson 'Asset Allocation' (4th Ed)
Examines how to build high return, lower volatility portfolios using multiple uncorrelated asset classes: domestic stocks, international stocks, property and commodities futures. Yes - commodities are very volatile but because they have very low correlation with the other portfolio components they provide an extremely powerful diversification effect.

munk

Hi Jon,

I already bought (and read) the Bernstein book after your recommendation! I've had a look through his efficient frontier site as well which was interesting. (Reminds me I still have to have a look at the spreadsheets and tools on that site)

I found the book very insightful apart from the last few chapters - IIRC the first 5 chapters were very good and relevant but after that it trailed off slightly. I especially liked the chapter explaining how SD varies against return for different mixes of asset classes and how by adding a riskier asset class to a basic bond portfolio sends the 'curve' off like an upside down Nike curve so you get increased return with a paradoxical decrease in risk (like the last graph you pasted above).

I'll have a look at that book you link to re Inv Strats for 21st Century, thanks for that.

With the portfolio above that I linked to I've tried to keep a £4k allocation of the portfolio for higher risk funds (resources/commodities/emerging asset classes) which equates to roughly 5% of the total. Hopefully this will do the job in increasing reward without too much increase in risk overall.

Initially after reading the Gibson book I overdid the number of holdings in this portfolio and went slightly crazy on diversification - 21 holdings at most. I found this to be counter productive because it took so much effort to administer the portfolio and rebalance when things went awry (though it was a particularly bad time to start investing back last August!). Now I'm more comfortable with 8-12 different funds and find that's plenty of scope for decent diversification.

Thanks again!

jon3001

munk wrote: »

I found the book very insightful apart from the last few chapters - IIRC the first 5 chapters were very good and relevant but after that it trailed off slightly. I especially liked the chapter explaining how SD varies against return for different mixes of asset classes and how by adding a riskier asset class to a basic bond portfolio sends the 'curve' off like an upside down Nike curve so you get increased return with a paradoxical decrease in risk (like the last graph you pasted above).

Yeah - I think most of the value is in the earlier chapters. Later on it does go into the US tax system and some other bits that are less useful.

munk wrote: »

With the portfolio above that I linked to I've tried to keep a £4k allocation of the portfolio for higher risk funds (resources/commodities/emerging asset classes) which equates to roughly 5% of the total. Hopefully this will do the job in increasing reward without too much increase in risk overall.

Sounds good. It's somewhat more art than science since you can't really predict returns and correlation going forward. I think the important thing is to get a feel how different asset-class mixes impact on each other. But statistically it does seem that a little bit of diversification can go a long way.

munk wrote: »

Initially after reading the Gibson book I overdid the number of holdings in this portfolio and went slightly crazy on diversification - 21 holdings at most. I found this to be counter productive because it took so much effort to administer the portfolio and rebalance when things went awry (though it was a particularly bad time to start investing back last August!). Now I'm more comfortable with 8-12 different funds and find that's plenty of scope for decent diversification.

I think I'm in danger of becoming an asset-class junkie myself! :eek: Especially after reading Gibson. Gonna have to get my Excel skills up-to-speed to manage it properly before I take the plunge!

munk

Hehe @ asset class junkie. I've calmed it down a bit.

Whilst reading up since my last post I came across an interesting article that discusses SD and correlation between different asset classes (actually covers trading long/short equity pairs). Only flew over the article so far but has a spreadsheet included and looks like a good way to see how the 'experts' calculate what correlation two funds hold (or more importantly in long/short pair trading, what negative correlation they have) before making pair trades. Goes into some good stats math detail about the process.

http://www.bestwaytoinvest.com/statistical-analysis-in-trading

jon3001

Interesting. Naturally this seems more geared towards traders rather than long-term investors. But gives some insight into what these guys do and if in the long-run markets will become increasing efficient while these guys try to arbitrage out any price anomalies.

I'm guessing in practice he's looking to predict the point when two securities will diverge so he can make money on his long/short pair and will probably diversify over a range of securities.

cepheus

Munk

What is the Epic symbol of Blackrock UK abs Alpha, AAP? I cannot find it anywhere on my charting package, is it a UK unit trust?

jon3001

cepheus wrote: »

What is the Epic symbol of Blackrock UK abs Alpha, AAP? I cannot find it anywhere on my charting package, is it a UK unit trust?

Yes - it's a unit trust:
http://www.h-l.co.uk/fund_research/security_details/sedol/B11V7T6.hl

I does seem an interesting fund. Do the managers market it for cautious investors as HL seem to do?

I see they have a large cash position (79%) which presumably is collateral against their short positions and other derivates (otherwise they probably wouldn't be able to make such a high return: 16% this past year). Is there any risk of this going spectacularly wrong and the fund melting down into nothing!? :eek:

meester

jon3001 wrote: »

Yes - it's a unit trust:
http://www.h-l.co.uk/fund_research/security_details/sedol/B11V7T6.hl

I does seem an interesting fund. Do the managers market it for cautious investors as HL seem to do?

I see they have a large cash position (79%) which presumably is collateral against their short positions and other derivates (otherwise they probably wouldn't be able to make such a high return: 16% this past year). Is there any risk of this going spectacularly wrong and the fund melting down into nothing!? :eek:

It's really rather opaque.

They are selling shares short. This can obviously go very wrong. What happens if you sell something short, and then the price doubles? Major margin calls, and eventually potentially a loss.

That said, derivatives are rather more sophisticated than just long/short:

http://en.wikipedia.org/wiki/Template:Derivatives_market