A Faster Way to Calculate Portfolio Risk, and Remember It Too

Learn how porfolio risk calculations are performed at scale

Beginner

Video

Videos are available at one organized Quant 101 Playlist on YouTube.

A faster way to calculate portfolio risk, and remember it too (16:50)

Video Script

Welcome. Today's goal is to not only learn to calculate portfolio risk but to internalize the concepts so we don't have to struggle with formula notation.

I'm Paul, and I'll admit, to pass tests five times in my career I've resorted to memorizing the calculation for portfolio variance, only to flush it from my memory once the test was over. It wasn't until teaching did I discover an easier way.

So here I will share that with you using the simpleist of scenarios, a two-stock portfolio. We will calculate variance first, then its cousin standard deviation, focusing on application because it has been my experience that understanding portfolio risk is a stumbling block for many, so mastering it is a way for you to differentiate yourself during your career.

In the end, for the series of financial modeling tutorials called Quant 101, we will use the resulting standard deviation to chart 11 portfolios, which is the objective of Chapter 3. If you want to play along with a free data set head back to the System Setup tutorial.

Our tutorials are designed to be watched and read in tandem. The video provides the audio and visual and a web page has the whole transcript and the tricky Excel cell formulas when you need to slow it all down.

Okay, let's look at our agenda for today.

Outline

First, we will state the problem and discuss how we're going to tackle it.

Second, we walk through the calculation of portfolio variance.

Third, we translate that to standard deviation and interpret units and give this calculation a more descriptive and accurate name.

Fourth, we return to the risk and return scatter plot finishing with practical considerations for portfolios that are allowed to drift.

And in our next episode we move to 4 stocks and create a covariance matrix, and interpretable correlation matrix.

Step 1 - The Problem with Portfolio Risk

Let's start of with why the concepts associated with risk are confusing and are often career differentiators in the field of investment portfolio management.

a. Math is difficult

I'm surely not the first person to tell you that people with degrees in science, technology, engineering and math, or STEM subjects are in the highest demand, right? And why is that the case?

Well, first of all, those who succeed in these subjects need a strong background in math, and math is difficult.

Second, based on my experience, a common complaint with so many people is the way math is taught, with a lack of practical examples and reliance on formula notation.

Third, everyone is under a time crunch, and in the end, students need to pass tests, so many resort to memorizing formulas. They plug in the numbers, hope for the best and vow that when high school is over they're through with math.

I've done it the wrong way too. For the portfolio variance calculation, I memorized it at the undergrad level, then again during a Masters and again three times for the CFA program.

Even with a career in portfolio management at the Institutional level I've been surprised by how few people understand portfolio risk. To be frank those who do get it are often in the highest paying jobs. Simple supply and demand here, and that's why I'm so passionate about this topic and why we will focus on the concept here instead of memorizing formula notation.

In fact, if there's a thread running through all of our financial modeling tutorials, it is that, a focus on concepts.

Now, with that rant over, let's calculate portfolio variance.

Step 2 - Calculate Portfolio Variance

Let's start with Excercise 1.

a. Our scope

First off, I should mention our scope here is on portfolios that hold one or more stocks.

Like with all of our financial modeling tutorials in Quant 101, we use one data set imported to the Returns data tab sitting in cells B6:H66 with download instructions detailed in the System Setup tutorial, so let's start by pulling the data forward.

The easiest way is to get the first cell, in E8 and using the =Returns!B6 reference which pulls the first of our required 21 cells for this tutorial.

Copy that over two columns and down six rows and you're all set.

	E	F	G
7		Stock Returns
8	Date	MSFT	EBAY
9	4/30/03	5.62%	8.91%
10	5/30/03	-3.75%	9.41%
11	6/30/03	4.19%	2.31%
12	7/31/03	3.00%	3.17%
13	8/29/03	0.42%	3.28%
14	9/30/03	4.83%	-3.19%

If you are following along, take a moment to format the returns in percent and dates suitable for a presentation.

b. Portfolio weights

Next, we need to bring portfolio weights back to the discussion. Weights determine how much of each stock's risk is incorporated into the portfolio. As you alter the weights between two securities with different risk profiles, the portfolio risk changes.

We covered individual stock risk two tutorials prior and many of the concepts are the same, but here we add the wrinkle that portfolios with multiple holdings have weights associated to each stock on each date.

Here create table headings for Microsoft and eBay and in cell H9 input 0.50 and copy that across one column and down five rows.

	E	H	I
7		Portfolio Weights
8	Date	MSFT	EBAY
9	4/30/03	0.50	0.50
10	5/30/03	0.50	0.50
11	6/30/03	0.50	0.50
12	7/31/03	0.50	0.50
13	8/29/03	0.50	0.50
14	9/30/03	0.50	0.50

This is the only data we need for this tutorial.

c. The portfolio variance formula - 2 versions

Let's move on to the formula for portfolio variance that can be visualized two different ways.

There is the memorize-the-formula-notation approach I mentioned earlier, or an easier approach.

For a 2-stock portfolio there are four total terms we need to sum up, and it looks like this.

Portfolio variance (for 2 stocks) =
(MSFT weight^2 * MSFT variance) +
(EBAY weight^2 * EBAY variance) +
2 * MSFT weight * EBAY weight * covariance(MSFT|EBAY)

The first is the weight of the portfolio allocated to stock 1, or Microsoft, squared times the variance for Microsoft.

The second is the weight of the portfolio allocated to stock 2, or eBay, squared times the variance for eBay.

The third and fourth terms are identical, which is why we put a multiplication of 2 out front. The logic here is that the covariance between Microsoft and eBay is the same as the covariance between eBay and Microsoft. Here I am using the pipe symbol between MSFT and EBAY (MSFT|EBAY) to mean the relationship between the pair of stocks. So this simplification turns four terms into three terms.

Adding these terms together gives us portfolio variance.

And keep in mind, for a three stock portfolio there would be nine terms total, but simplified down to six, which is what makes this formula approach confusing, in my view.

A bordered covariance matrix

A better way is with what is called a bordered covariance matrix.

	K	L	M	N
14			MSFT	EBAY
15		Weights	0.50	0.50
16	MSFT	0.50	Var(1)	Cov(1\|2)
17	EBAY	0.50	Cov(1\|2)	Var(2)

Let's walk through it.

In cell M16 we have the variance for stock 1 which is Microsoft. The table is bordered with the weights, 50% to each stock. So imagine you were taking four calculations using these bordered weights. So in cell M16 we have the weight of 50% from the left times the 50% from the above, which is the same as weights squared from our formula. Then we multiply that times the variance of Microsoft in the cell. This matches the first term in the formula.

Next, let's do cell N17. Here it is the weights on the borders squared, or multiplied together, so N15 times L17 times the variance for eBay in cell N17. That is the second term.

Now for the last one, let's head back two tutorials so everyone is clear on why terms 3 and 4 are the same.

If you haven't done so, I suggest walking through the tutorial called Calculate Stock Risk. There we used demeaned returns in columns J and K for all four measures of risk: variance, standard deviation, covariance and correlation.

Variance we said was an absolute measure, meaning it is a measure for each stock alone. So we can think of the variance for Microsoft as the column of demeaned returns for Microsoft times the same set of demeaned returns for Microsoft, essentially squaring them. Next we add them up and divide by the number of observations. This gives us variance.

We also said that the relative measure, or the relationship between pairs of stocks, so multiplying the demeaned returns for Microsoft and those for eBay is, in the end, covariance. So whether you mutiply Microsoft by eBay or the other way around, multiplying eBay by Microsoft, the order doesn't matter. You get the same result.

Just like how multiplying 2 times 1 gives you the same result as multiplying 1 times 2.

This explains why we are doubling the product of the weights and the resulting covariance. The covariance between Microsoft and eBay is the same as the covariance between eBay and Microsoft. This allows us to reduce the final two terms to one and just double it.

Portfolio variance for two stocks

Next, let's collect the data we need to find portfolio variance using functions in Excel for our 6 month timeframe. For two stocks we only require two variances and one covariance.

	E	F	G	H	I
16	Variance		Weights		Covariance
17	MSFT	EBAY	MSFT	EBAY	MSFT\|EBAY
18	0.0010	0.0018	0.50	0.50	-0.0006

In cell E18 collect the variance of Microsoft using =VAR.P(F9:F14), and since eBay is right next to it you can copy E18 to F18.

For weights, I pulled those down from the period 9/30/03.

For the last piece, we need the covariance between the pair of stocks. So here in cell I18 we use =COVARIANCE.P(F9:F14,G9:G14). This produces negative -0.0006 result for covariance.

It is negative because as one stock moved up the other stock moved down, which was the tendency during this short period.

To quantify this, let's visualize our bordered covariance matrix and the formula, breaking it out into all four elements in a new table.

	E	J	K	L
20	Terms in the formula	Weight^2		Result
21	MSFT weight^2 * MSFT variance:	25%		0.00026
22	EBAY weight^2 * EBAY variance:	25%	+	0.00045
23	MSFT weight * EBAY weight * cov(MSFT\|EBAY):	25%	+	-0.00015
24	MSFT weight * EBAY weight * cov(EBAY\|MSFT):	25%	+	-0.00015
25	Portfolio variance	100%	+	0.0004

First let's talk about weights in column J. Here we take the weight of 0.50, or 50%, and square it, which when you think about is 25%. So each term makes up 25% of the total variance calculation. All of the component pieces add up to 100%.

So row 21 shows that 25% percent of our total portfolio variance comes from the variance of Microsoft.

Let's move on to column L to see the details of each component. In cell L21 we have the formula =G18^2*E18 for the Microsoft weight squared times its variance.

Next, in cell L22 take the weight squared for eBay times its variance. That's the second twenty-five percent of the risk calculation.

The last piece in the equation is 2 times the weights times the covariance between the pair. Instead of combining them, in our table we break out terms 3 and 4.

The third piece comes from the weights times the covariance of Microsoft to eBay, and the fourth piece, is the covariance going the other way, from eBay to Microsoft. So cells L23 and L24 are identical with =G18*H18*I18.

Now we have the whole 100% of the portfolio accounted for. All that is left is adding the terms using the =SUM() function and we arrive at 0.0004, the portfolio variance.

Did you notice that the last two pieces were negative? This means that portfolio variance is not the sum of the variances; it is the allocation to the weights times the variances and covariances. So when the stocks don't move in perfect tandem, the total portfolio variance will be lower than the weights of the variances. Here we subtract the co-movement piece and this quantifies the magic of diversification - free risk reduction.

If this is frustrating, don't worry, later we will simplify all of this. Going through it once the long way will pay dividends down the road.

Step 3 - Derive Portfolio Standard Deviation

Just as we learned in the tutorial called Calculate Stock Risk we won't bother trying to interpret variance. Instead we will turn it into a standard deviation.

Portfolio standard deviation is the square root of portfolio variance, so in cell K29 we can use the shortcut =L25^0.5, raising it to the 1/2 power which is the same as taking the square root. I also formatted this in percent.

An alternative way would be to use the =SQRT() function.

a. Units of measure

I have a note next to the 0.0004 portfolio variance as a reminder that the units are in returns-squared, which means it is not interpretable.

Once we take the square root of variance the units become percent, which makes portfolio standard deviation interpretable.

b. Timeframes

Also, as another reminder, in the last several tutorials we discussed timeframes and we should be careful to indicate that this measure is for a historical timeframe.

So formally we could call this the historical portfolio standard deviation meaning data was collected for periods in the past.

Step 4 - Portfolio Risk and Rebalancing

Next, for Step 4, let's interpret all of this.

a. Interpret standard deviation

Much like when we calculated standard deviation for stocks, using this sample, roughly two-thirds of observations fall within 2.01% of the average for the portfolio.

We calculated the average in the previous tutorial, so let's have a look at that.

b. What do we use it for?

At the outset of Chapter 3, we set out with the goal of creating a risk and return plot with 11 portfolios with average historical portfolio return on the y-axis and historial portfolio risk, using standard deviation, on the x-axis.

So now that we have our answer for the risk of this 50/50 portfolio of 2.01% we can now plot the point right near the tip of the parabola with the average portfolio return of 3.18%.

While return was the midpoint between the returns of each stock individually, but the risk of the 50/50 diversified portfolio was much less, at 2.01%.

With respect to risk on the individual stocks, we calculated those earlier, so a portfolio that owned only Microsoft had a standard deviation of 3.20% and one allocated exclusively to eBay was 4.26%.

Granted this is a short time frame of only 6 months and with only two stocks it isn't very applicable, but this whole exercise was designed to simplify the example. The benefit is that the size of the formula for portfolio variance is manageable. The number of terms for portfolio variance starts to balloon, as you move on to three, four or one thousand stocks.

c. Portfolio drift and rebalancing

Before we go, for those who went through the previous tutorial called Calculate Portfolio Returns, let's do Exercise 3 as a mental exercise.

It asks "How would the portfolio variance calculation differ if you allowed the portfolio to drift?" So above we rebalanced the portfolio back to 50/50 at the end of each period, just so the numbers all tied out.

Because this is an interesting question and it happens in the real world this way, give it some thought. We won't actually go through the math here, but this should help you understand how complex portfolio risk measurement and accounting programs can get, and why Institutional asset management firms rely so heavily on their software tools. It also illustrates why spreadsheets like Excel are not up for the task, especially when you analyze thousands of stocks.

Summary

By way of summary, we walked through portfolio risk. You have seen how imperfectly correlated stocks offer lower portfolio risk. That's what diversification is about after all.

Another concept we reinforced is demeaned returns. We use them in two ways, for each stock we square them, sum them up and divide by the observations, deriving variance. Second, for each pair of stocks, we multiply the two demeaned returns together, sum them up and divide by the number of observations to get covariance.

Last, since the formula for portfolio variance past two stocks gets a little scary and more difficult to remember, we need an easier method. Breaking it out as I have here, in four separate pieces is helpful, but there is even a better method.

Step 5 - Next: Covariance Matrix

In the next episode we will take what we learned here and see how Institutional asset management firms scale these calculations using a covariance matrix, plus how you can create a more interpretable correlation matrix, this time using a four stock case.

With this we just passed a third of the way through our journey here in Quant 101. You are welcome to join us any time. Have a nice day.