## Using Excel to determine a portfolio’s Mean, Variance and Standard Deviation

9/23/10 Update. I've uploaded an image to show that there is a video to show you how to do this.**You will still probably want to see the text version of these instructions below**.

Okay, so you probably don't really want to know how to do this but this is also a reference for me when I need to do it in the future.

Here's the scenario: You have several stocks and you want to know what their combined returns and risk are. This is how you can figure it out.

Step 1: Set up the percentages

Step 2: Calculate the sample mean (average return for the individual stocks)

Step 3: Label the percentage as "p". This represents the proportion of the portfolio that is in a particular stock. This is how you name a range of cells. You highlight them and then give it a name in the top right in the "Name box".

Step 4: Label the sample mean as "mu".

Step 5: Use Excel to create a Covariance matrix. You'll need to install the data analysis tools in excel in order to do this. See below for a video on how to do this.

Step 6: Populate the rest of the covariance matrix by using "paste special".

Step 7: Name the covariance matrix results "sigma".

Step 8: Use this formula to calculate the "mean": =MMULT(TRANSPOSE(p),mu). After you've entered that formula, you have to hit "shift, ctrl, and enter" at the same time because it's a special function in Excel.

Step 9: Use this formula to calculate the "Variance": =MMULT(MMULT(TRANSPOSE(p),sigma),p). Again, after you've entered that formula, you have to hit "shift, ctrl, and enter" at the same time because it's a special function in Excel.

Step 10: Use the sqrt function in excel on the Variance to get the Standard deviation for the portfolio.

I really don't think you would be able to follow that based on my instructions so I've included a short video to show you how. Video: Using Excel to calculate a portfolio's mean, variance and standard deviation.

Here is a video showing you how to turn on Data Analysis tools in Excel.

Andy MolerMarch 25th, 2011 - 14:11

This is something i have been trying to figure out for years. Thanks for helping me out. Do you know how to do a correlation on an entire portfolio as well?

I was thinking you just have to weight the assets times returns but that would be time consuming and I thought there may be an easier way you may know of.

AAAApril 5th, 2011 - 10:07

THANKYOUUU SO MUCH! PHEEWW

DeanMay 27th, 2011 - 05:49

Have tested this with original algebra and it doesn’t appear to factor in the standard deviation of the assets themselves. Interested to hear of any modifications you’ve made.

Sim ConMay 28th, 2011 - 22:05

Here’s an Excel spreadsheet to calculate the investment weighting for a portfolio with the maximum Sharpe Ratio: http://bit.ly/lL6GJe

AkashJuly 16th, 2011 - 22:39

Thanks, the video is very helpful and the instructions too.Good learning.

sadiSeptember 11th, 2011 - 06:07

the best guidance i ever have thank u

Sol LedermanNovember 11th, 2011 - 19:00

Here’s a spreadsheet for mean-variance optimization: http://investexcel.net/215/mean-variance-portfolio-optimization-with-excel/

André KochNovember 23rd, 2011 - 14:42

Excellent job. I´ve been struggling with this for a while, but this is the simplest method I ve seen.

Thanks!

André Koch, Amsterdam

andreMarch 2nd, 2012 - 04:06

Great tutorial, really simplified things for me!

sabahatMay 26th, 2012 - 07:47

thank you ssssssssssssooooooooooooooooo much……i wanted to know about this…. i was having problems to calculate the risk for several stocks……. great work……. thanks thanks thanks