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.
March 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.
April 5th, 2011 - 10:07
THANKYOUUU SO MUCH! PHEEWW
May 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.
May 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
July 16th, 2011 - 22:39
Thanks, the video is very helpful and the instructions too.Good learning.
September 11th, 2011 - 06:07
the best guidance i ever have thank u
November 11th, 2011 - 19:00
Here’s a spreadsheet for mean-variance optimization: http://investexcel.net/215/mean-variance-portfolio-optimization-with-excel/
November 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
March 2nd, 2012 - 04:06
Great tutorial, really simplified things for me!