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.
