Technology Product Manager Harness the power of the Internet

22Jan/1010

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.

Video to show you how to calulate Std deviation

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.

Comments (10) Trackbacks (0)
  1. 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.

  2. THANKYOUUU SO MUCH! PHEEWW

  3. 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.

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

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

  6. the best guidance i ever have thank u

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

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

    Thanks!

    André Koch, Amsterdam

  9. Great tutorial, really simplified things for me!

  10. 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


Leave a comment


No trackbacks yet.