Thanks for opening this question.

I have constructed some rules for a portfolio with annual rebalancing and am backtesting it for the period 1990-2014. I want to compare the risk-adjusted return to the risk-adjusted return of the S&P 500 index.

For every individual year I have calculated log returns of the constituents of the portfolio and calculate portfolio volatility for every year using the following formula in Excel:

First I compute the variance like this: =MMULT(MMULT(array1;matrix1);array2)
array1: array of weights of constituents of the portfolio*annual volatility of the constituent for every constituent in that year
array2: constituents return correlation matrix
array3: transpose of weights of constituents

And then I SQRT() the variance to get the annual portfolio volatility.

Now I am wondering if I can use the annual portfolio volatilies to compute volatilities for certain subperiods, say for example January 2013 - September 2014.

I could compute the portfolio volatily for the period Jan 2013 - Dec 2013, and for the period Jan 2014 - Sept 2014, but how do I then combine the result?

Thanks for helping me out.


You can use the Parallel Algorithm.

Your sample $X$ is divided in two sets of obsrvations $X_{A}$ and $X_B$. $$ \delta\! = \bar x_B - \bar x_A \\ \bar x_X = \bar x_A + \delta\cdot\frac{n_B}{n_X} \\ VAR_{X} = VAR_{A} + VAR_{B} + \delta^2\cdot\frac{n_A n_B}{n_X} $$ Where $n$ the number of observations and $\bar x$ the mean.

  • $\begingroup$ Thank you I will try this out and come back here to post results later today. $\endgroup$ – AltTabsen Jan 16 '15 at 10:05

Since you have the weights as well as the price series of your assets, the cleanest way I can think of is to calculate the portfolio price series for your given time frame, and then estimate the variance from that.

The problem you face is that volatility changes more often than yearly, and obviously the end of the year is not necessarily a 'clean cut' between two regimes. So while it's technically possible (See jaamor's answer), I don't think that will give you the desired result. In a backtest you want to understand how your trading strategy would have performed in the past, if you had implemented it, and one of the most important considerations will be how does a portfolio react to changes in market volatility. For example, if you want to use a low volatility strategy, the backtest will show you if the stocks you selected actually yielded you a portfolio with a lower volatility, or if correlations between the stocks actually reduced that effect.

So I would recommend to use real data wherever possible.

Since you work in excel, it should be quite straightforward to do. You will probably have the daily price series of your constituents. Start with the portfolio's initial value and distribute it according to your weights by buying the constituents. Track the prices of the positions until the next rebalancing, the sum will always be equal to the current portfolio value. Before the next rebalancing, calculate what your portfolio is wrorth now. Then redistribute that money according to your strategy's weights. Repeat. Use the resulting time series to calculate variance. Use the stdev-function over the period you want, and then annualize (e.g. from daily vola to annual vola by *sqrt(252), where 252 is the trading days.)

  • $\begingroup$ Thank you for your great answer @phi. However, you don't capture correlation of constituents returns like this, right? What you do here is you basically treat your portfolio as index, if I'm not mistaken. The variance caculated with this method differs from the variance as calculated using the method in my first post (modern-portfolio theory method). Can you elaborate? $\endgroup$ – AltTabsen Jan 16 '15 at 10:05
  • $\begingroup$ You're not mistaken, and of course you capture correlation: One asset goes down, another one goes up -> Portfolio NAV stays the same. That's correlation at work. You can actually trade effect this by selling a call on an index, while buying calls on the index' constituents. This is called dispersion trading. The variance of your portfolio is defined by its returns, and they will be the same. $\endgroup$ – zuiqo Jan 16 '15 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.