Portfolio Risk and Return Analysis with Array Math in Excel

Learn how to calculate portfolio risk and return from stock returns

Intermediate

Video

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

Portfolio Risk and Return Analysis with Array Math in Excel (15:00)

Video Script

Welcome. Today's goal is to calculate portfolio risk and return using array math in Excel following the rules of Linear Algebra.

I'm Paul, and it has been my experience that concepts of stock portfolio diversification, while easy to visualize on a risk-and-return plot, don't stick because of people's overreliance on memorizing formula notation. Often individuals seeking to pass tests for the CFA Program, PRM or FRM designations or college-level Finance courses, fail to truly understand risk calculations.

So here we will walk through a solution translating portfolio notation to formulas in Excel and then to Linear Algebra because that is the mathematical procedure used to scale risk management applications at Institutional asset management firms.

Our case here will be a simplified 2-stock example, but you can take away these formulas and apply them to thousands of stocks because they are written in array form for you.

Here we sit at the end of Chapter 5 in Quant 101, and we turn the difficulty level up a notch to the Intermediate level as I will assume you have knowledge of stock and portfolio risk and return calculations, portfolio theory and array math covered in previous tutorials.

If you would like to see the example formulas and transcript for this video on a web page the first link in the Description goes straight to it.

Okay, here is our plan for the day.

Outline

First, we will review our stock example here and how Linear Algebra simplifies portfolio math.

Second, we walk through the calculation of portfolio return.

Third, we create a covariance matrix using arrays in Excel and from that portfolio variance and standard deviation.

Fourth, we cover timeframes in financial modeling to make sure all of the theory can be made practical.

And in our next episode we start to see how CAPM helps us see what is baked-in to market expectations.

Step 1 - Portfolio Calculations using Arrays

For Step 1, let's see how easy it is to use array math for portfolios.

We have a table (see video) that summarizes portfolio return and portfolio variance calculations through three different lenses: formula notation, Excel calculations as we saw in previous tutorials and improvements made here with Linear Algebra.

a. From Excel functions to Excel arrays

From the start, we stated our preference to steer clear of formula notation because learning the concepts with real data will stay with us longer than memorizing formulas.

So we translated formula notation into Excel formulas and walked through three main calculations using a simple 2-stock case in Chapter 3.

First, we calculated Portfolio Return and then Portfolio Risk, starting with variance and then used portfolio standard deviation on a chart to create the visual of the Portfolio Possibilities Curve for these two stocks you saw at the outset.

We also saw how a Covariance Matrix is created with Excel functions but found that to be clunky, manual and prone to error.

So now with the operations of Linear Algebra, or Array Math like finding the transpose and using matrix multiplication we will simplify all of those same calculations here. Our answers here should match what we found there.

b. Why use Linear Algebra?

So you might be asking, if we already did it then why are we using Linear Algebra?

We gave four reasons; including, improved speed in building a spreadsheet, smaller file sizes and increased accuracy. Last, we can see how calculations are made at scale in statistical programming languages offering us the ability to scale our financial models when we want to work with portfolios of hundreds or thousands of stocks.

Here we will demonstrate just how easy it is.

c. Which array operations?

We will employ several time-savers from the last two tutorials.

First we will name ranges of data using the Name Box because this practice simplifies our formulas and all we would need to do is change the location of the data in the Name Manager and we wouldn't have to change cell references. That's convenient.

Second, to follow the rules of matrix multiplication we will have to transpose data using the =TRANSPOSE() function.

And third, we use the =MMULT() function first for the easy portfolio return calculation and second for portfolio variance. There we will create a covariance matrix using arrays first, then portfolio variance simplifies to a 1-by-1 array as the final answer.

As we saw earlier arrays are always communicated in row-by-column convention. Row first, then column. For matrix multiplication in particular the alignment of arrays in proper order is required, as covered in the tutorial Array Math.

Following those rules, the inner dimensions must match and the resulting array takes the shape of the outer dimensions.

Step 2 - Calculate Portfolio Return with Arrays

Let's get right to it now in Step 2.

Our first exercise asks us to calculate portfolio return using matrix multiplication.

a. Create and name weights and returns

Let's back up a minute and talk about our assumptions.

With respect to weights, a given for this exercise is that the weights to our two stocks will be 50% in each throughout. We also assume the portfolio is rebalanced back to the 50% weights at the end of each period.

For returns, we assume they are the average from the historical period, but will talk about this unrealistic assumption in Step 4.

	E	F	G	H	I	J	K
16	weights (2x1)			average_returns (2x1)			portfolio return (1x1)
17	50%	MSFT		2.38%	MSFT
18	50%	EBAY	*	3.98%	EBAY	=	3.18%

Given these assumptions, let's start by naming this range. Highlight cells E17:E18 and in the Name Box type weights followed by Enter.

Now for returns, our calculation involves returns for these two stocks over a very short 6-month period. We used this same subset earlier and using it again will help us verify that our formulas are correct.

These sit on the Returns data tab. If you would like to follow along, instructions to download this are found at the System Setup tutorial.

In cell H17 let's use =AVERAGE(Returns!C7:C12) for Microsoft returns and in cell H18 we have =AVERAGE(Returns!D7:D12).

Next, name the range in H17:H18 as average_returns.

b. Calculate portfolio return

Now we're ready to calculate portfolio return following the rules of matrix multiplication.

Our goal is to get a 1-by-1 answer in the cell K18 and to do that we need to line up the weights array as a 1-by-2 vector and multiply it times average_returns which is correctly lined up with 2-by-1 dimensions.

Transpose of weights (1x2) * average_returns (2x1) = Portfolio return (1x1)

Recall from the rules of Linear Algebra for matrix multiplication to work, the inner dimensions, so 2 and 2 here must be the same and the resulting array takes the shape of the outer dimensions, or 1-by-1.

We will use the =TRANSPOSE() function to adjust our arrays throughout this tutorial.

So in cell K18 input =MMULT(TRANSPOSE(weights),average_returns) followed by the Ctrl+Shift+Enter keystroke combination.

This result of 3.18% matches what we saw using functions in earlier tutorials for this 50/50 portfolio. Very good.

Step 3 - Calculate Portfolio Risk with Arrays

For Step 3 now, let's walk through portfolio risk using array. All that is missing is a covariance matrix.

While we could theoretically calculate portfolio variance without the intermediate step of creating the covariance matrix first, it will be easier to visualize if we do.

a. Create and name the covariance matrix

We saw a covariance matrix for two stocks using Excel functions in the Portfolio Risk tutorial.

It has individual stock variances down the diagonals and covariances for the pair of stocks in the off-diagonals.

We can create a covariance matrix using array math in Excel by squaring the array of demeaned returns for stocks then dividing by the number of observations. This is a bit trickier than it sounds so let's walk through it.

Demeaned returns

In the tutorial on Stock Risk we were introduced to demeaned returns when calculating variance for each individual stock, Microsoft and eBay, and the covariance between the pair.

Demeaned returns are monthly returns minus the average returns for the period. We have average_returns already named.

Let's go to the Returns tab and highlight the range from Returns!C7:D12 and in the Name Box give it the name stock_returns.

To square demeaned returns we need to think about how the arrays will line up and for this we will need, in a sense, two sets of demeaned returns. They're really the same thing, one is just the transpose of the other.

Here we won't create demeaned returns as an intermediate step as we have in the past, so the formula will be long, but you know where you can look it up.

Remember, our inputs are all set, we have one called stock_returns that is 6-by-2, six rows and two columns, and average_returns that is 2-by-1, or two rows and 1 column.

For our first demeaned returns array to have dimensions of 2-by-6 we will need stock_returns with dimensions of 2-by-6 minus average_returns with dimensions of 2-by-1.

For our second demeaned returns array to have dimensions of 6-by-2 we need stock_returns with 6-by-2 dimensions minus average_returns with dimensions of 1-by-2.

Covariance matrix = (First demeaned returns (2x6) * Second demeaned returns (6x2)) / Observations

So we will need to do a little transposing again.

Covariance matrix

After that, we use =MMULT() to multiply these properly aligned demeaned returns then divide that whole thing by the number of observations.

With that, let's calculate the covariance matrix with one array formula. It will appear tricky at first, but we will go through it.

Start by highlighting the output block of H27:I28 and input =(MMULT((TRANSPOSE(stock_returns)-average_returns),(stock_returns-TRANSPOSE(average_returns))/ROWS(stock_returns))) followed by the Ctrl+Shift+Enter keystroke combination.

That's a doozy. So let's break it apart.

Before we do that, let's check to see that these line up what we calculated earlier. The variance for Microsoft is 0.0010, and for eBay it is 0.0018. The covariance between the pair was -0.0006. So we did it correctly, now let's walk through it.

	E	F	G	H	I	J	K
26	weights vector (2x1)			covariance matrix (2x2)			portfolio variance (1x1)
27	50%	MSFT		0.0010	-0.0006
28	50%	EBAY	*	-0.0006	0.0018	=	0.0004

The first demeaned returns inside the =MMULT() function looks like TRANSPOSE(stock_returns)-average_returns. This changed the 6-by-2 stock_returns array to 2-by-6 and then we subtracted the 2-by-1 average_returns to produce a 2-by-6 matrix.

The second demeaned returns starts with the stock_returns array with 6-by-2 dimensions and subtracts average_returns. Here the dimensions must be 1-by-2, so we transposed the average_returns array.

Now when we multiply the resulting 2-by-6 matrix times a 6-by-2 matrix our result is a 2-by-2 matrix, and finally we divide by a count of rows using the =ROWS() function to divide by Obervations, as our formula says.

Very good. Before we go, name this covariance_matrix in the Name Box.

b. Calculate portfolio risk measures

Now we are ready to calculate portfolio variance in cell K28.

We haven't seen the calculation for portfolio variance using Linear Algebra yet. Our Excel calculation was weights squared times the variance of each stock, then we captured the covariance component by taking two times the weights of each stock multiplied together times the covariance between each stock.

On the Linear Algebra side, using matrix multiplication this is easier, we need to transpose the weights vector from 2-by-1 to 1-by-2, then multiply it times the covariance_matrix with dimensions of 2-by-2 and multiply that by the weights vector again, this time with 2-by-1 dimensions to produce a 1-by-1 result, from the outer dimensions.

Transpose of weights (1x2) * Covariance (2x2) * weights (2x1) = Portfolio variance (1x1)

So in cell K28 we have =MMULT(MMULT(TRANSPOSE(weights),covariance_matrix),weights).

Here we have two matrices multiplied together. If this gets confusing start with the inner parentheses first and move out from there.

The multiplication in Linear Algebra performs all of the calculations at once including all of the summations, and that follows the rules of matrix multiplication.

Well there you have it, the portfolio variance is 0.0004.

Because we rarely try to interpret variance, in cell K32 let's take its square root using =SQRT(K28) which gives us portfolio standard deviation.

We can annualize standard deviation by multiplying by the number of sub-periods in a year. We were using monthly periods and there are 12 months in a year, so we multiply by the square-root of 12, and cell K33 reads =K32*SQRT(12).

Great, there you have it, a 2.01% monthly portfolio standard deviation for the 50/50 portfolio to go along with a 3.18% portfolio return.

Step 4 - Where Are We Headed?

Now for Step 4, let's take a step back and close that open item mentioned earlier about our assumptions here and how we can take this small example and make it look like what you might see in the real world.

For that let's talk about the three Financial Modeling Timeframes used at Institutional asset management firms.

Financial Modeling Timeframes

We summarize backward-looking data as Historical, present calculations as Expected and Forecast as forward looking estimates. This applies to both return and risk.

Historical	Expected	Forecast
Past Returns Risk	Present Returns Risk	Future Returns Risk Active

If you take a step back and think about it, we used historical returns from average_returns to calculate portfolio return. We also used historical stock-by-stock variances and covariances in the covariance_matrix to calculate portfolio variance.

Technically the way it is spelled out in formula notation from textbooks, it to use expected returns and expected risk. Most of the times for classes, people get lazy, as we did here and use the historical average returns in this formula but that isn't technically correct.

To do this properly we should really calculate what is baked-in to market expectations for both return and risk for each stock and use those to calculate expected portfolio return and expected portfolio risk.

As you can imagine, this is not exactly easy and for that we need to move on to Chapter 6 and use CAPM as a way to establish expectations.

Rather than being precise about all of this here, the point was to lock away an understanding of how this all works. Also as a takeaway we tested our formulas so once we feel comfortable with our expectations for stocks we can return to these array calculations and analyze hundreds or thousands of stocks, which is why we introduced array math in the first place.

Summary

By way of summary, we completed our Chapter 5 objective of implementing Linear Algebra to calculate portfolio return and risk, verifying that we get the same answers as we did the long way back in Chapter 3.

We have seen three ways methods now, formula notation, using Excel formulas and now with Linear Algebra, which is the preferred way for large sets of data. If your goal is to learn how this is done in statistical programming languages like R, MatLab and Python, then this exploration of array math is a nice precursor to what you will find there.

The procedures are tricky at first but will become second nature with practice as we start to move from the Historical timeframe to Expected and then to Forecast.

Step 5 - Next: CAPM for Expected Returns

In the next episode we will move on to CAPM in Chapter 6 knowing that our goal is to set expectations. After that we will take that and use array math almost exclusively to optimize a portfolio using Excel's Solver Add-in.

After that we close out Quant 101 the next series will be about setting forecasts. On the return side of the equation this is the job of active portfolio management firms. On the risk side however it is normally third-party risk model providers who sell exotic covariance matrices.

For the next series after Quant 101, we may skip Excel and head straight to Python. Please submit your vote and leave a comment below if you have a preference to see the next Series in Python or Excel.

Also, if you haven't done so, please consider subscribing. There are a lot of messy and flat-out incorrect YouTube tutorials on portfolio measurement and we'd like to offer more of a college-level look at this material and that takes time and effort. The more subscribers we have the more we can justify time to make quality content. Subscribers will be notified of our new series when it comes out as well.

With that please feel free to join us any time and have a nice day.