Mean Variance Optimization in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Mean variance optimization is an important factor during the process of investment. Any investment company goes through this analysis process before making the right decision. Microsoft Excel is a very powerful tool to perform the analysis of mean variance optimization. In this article, we will guide you through the procedure of mean variance optimization in excel with some easy steps. But before that let us have a clear concept of mean variance optimization.


What Is Mean Variance Optimization?

Before jumping into the calculation, we need to understand mean variance optimization. It is an analysis tool in modern portfolio theory. With the assumptions through mean variance optimization, investors make rational investment decisions based on complete information. Their assumptions always seek low risk and high reward in the optimization process.

Mean variance optimization is determined by the following two components:

  • Variance- it represents how varied or spread out the numbers are in a set through numerical values based on a period of time.
  • Expected Return- it is the probability of expressing the estimated return of the investment.

When two securities have a similar return, then one with the lower variance is preferable for investment. Otherwise, investors will pick the security with the higher return while having a similar variance.

But in modern portfolio theory, an investor may differentiate his/her choice in securities with different levels of variance and expected return. The aim of this is to reduce the risk of catastrophic loss in rapidly changing market conditions.


How to Do Mean Variance Optimization in Excel: Step-by-Step Process

Now let us hop into the main section. Here we will go through the steps below for the analysis of mean variance optimization in excel.

Step 1: Prepare Dataset

To begin the process, we need to set up a dataset.

  • Here, we prepared a sample dataset with the information on Stock Returns of 3 companies- Apple, Samsung and Microsoft in cell range B6:E17. Each of their stock returns is given within a time period of one year.

Step-by-Step Process of Mean Variance Optimization in Excel

  • Along with it, determine the Assumption value in cell C20.


Step 2: Calculate Expected Return

At this stage, we will find out the Expected Return for each company. The expected return is the amount of returns in the portfolio that defines the mean or average of the possible return distribution.

  • First, select cell H5 and insert this formula.
=AVERAGE(C6:C17)

Calculate Expected Return

  • Then, press Enter > Autofill.
  • That’s it, you will get the percentage of Expected Returns in the cell range H5:H7.

In this formula, the AVERAGE function returns the cumulative average of stock returns of each product through the selected cell range.
  • Don’t forget to format the values as percentages with the Home > Numbers > Percentage command.

Read More: How to Make Price Optimization Models in Excel


Step 3: Calculate Variance-Covariance Matrix

Now, we will calculate the variance-covariance matrix for the stock returns. It is a square matrix that is associated with variances and covariances with several variables. Let’s see the calculation process below.

  • First, we need to determine a generic value of weights for each company. It is the percentage of investment of the companies.
  • Therefore, insert the values as shown in the image in the cell range I6:K6.

Calculate Variance-Covariance Matrix

  • Then, insert this formula as the vertical cell reference of the Weight of the company Apple and press Enter.
=I6

Calculate Variance-Covariance Matrix

  • Following, apply the same method to determine all the weight references in cells H8 and H9.

  • Now, select cell I7 and insert this formula to calculate the percentage of variance.
=VAR.S(C6:C17)

Calculate Variance-Covariance Matrix

  • Then, press Enter.
  • As the variances are placed in diagonal cells, therefore apply the similar formula for each co-existing company to see this output.

Here, we used the VAR.S function to estimate the variance based on the sample that we gave before in the Stock Return of each company.
  • Next, we will calculate the covariance in the rest of the cells.
  • For this, insert this formula in cell J7.
=COVARIANCE.S(C6:C17,D6:D17)
  • Hit Enter.

Calculate Variance-Covariance Matrix

  • Following, apply the same process for each blank cell to determine the covariance of each co-existing company.

In this formula, the COVARIANCE.S function returns the sample covariance and the average of the standard deviation of each pair of datasets.
  • Lastly, type this formula in cell I10 to get the sum of variance by each company as the contribution.
=I6*SUMPRODUCT($H$7:$H$9,I7:I9)

Calculate Variance-Covariance Matrix

  • Press Enter.
  • Finally, apply the similar formula for each company and you will get this final output.

Here, the SUMPRODUCT function returns the sum of the variance and covariance values based on the given cell range.

Step 4: Create Inputs for Optimization

At this stage, we need to create some inputs before doing the mean variance optimization. To do this, follow the process below.

  • First, calculate the Sum of Weights in cell C7 with this formula.
=SUM(G6:I6)

Create Inputs for Optimization

Here, the SUM function calculates the total amount of weights of the 3 companies.
  • Then, calculate the Sum of Expected Return with this formula in cell C8.
=SUMPRODUCT(G13:I13,G6:I6)

Create Inputs for Optimization

  • Next, apply this formula in cell C9 to get the total Standard Deviation.
=SUM(G10:I10)^(1/2)

  • Finally, type this formula in cell C10 to get the expected Sharpe Ratio which compares the return of an investment with its risk.
=(C8-C5)/C9

  • That’s it, we have got all the inputs that will help us to perform the optimization.


Step 5: Enable Solver in Workbook

Before doing the mean variance optimization, we need to install Solver in our workbook. It is a Microsoft Excel add-in program that is used for what-if analysis. Let’s see the process to install it.

  • In the beginning, go to the File tab on your workbook.

Enable Solver in Workbook

  • Then, select Options from the left panel.

  • After that, select Add-ins in the Excel Options dialogue box.

Enable Solver in Workbook

  • Now, select Solver Add-in from the Add-ins list.
  • Then, press Go.

Enable Solver in Workbook

  • Following, select Solver Add-in from the Add-ins available list.

  • Lastly, press OK to complete the process.

Step 6: Perform Mean Variance Optimization

Finally, we are at the stage to perform the optimization. For this, carefully go through the process below.

  • First, go to the Data tab and select Solver under the Analyze group.

Perform Mean Variance Optimization

  • Then, you will see the Solver Parameters dialogue box.
  • Here, insert cell C10 as an Absolute Cell Reference in the Set Objective box.

  • Then, type the cell reference in the By Changing Variable Cells box like this.

Perform Mean Variance Optimization

  • Now, click on Add in the dialogue box.

  • Following, you will see the Add Constraint dialogue box.
  • In this dialogue box, insert the Cell Reference and Constraint with a condition as shown in the image.

Perform Mean Variance Optimization

  • After this, press Add > Cancel.
  • Therefore, you will see that the constraint is added in the Subject to the Constraints box.

  • Similarly, follow the same procedure for each reference cell and click on Solve.

Perform Mean Variance Optimization

Note: The Sum of Weights will always be 1. This is why we put the constraint for cell C7 as $C$7 = 1.
  • Lastly, mark checked the Keep Solver Solution box in the Solver Results window.
  • Then, hit OK.

  • That’s it, we have successfully done the mean variance optimization with the changed values of weights and inputs.

Mean Variance Optimization in Excel

  • With this process, any investor will determine the risk based on the value of the expected Sharpe Ratio. It will continuously change when the Weights are changed according to market conditions.

Read More: Schedule Optimization in Excel


Limitations of Mean Variance Optimization

  • The calculation of standard deviation or variance for risk is only valid for normally distributed returns. It is mostly true for traditional stocks, bonds and derivatives.
  • The assumption of the theory refers that investors will not alter their asset distribution after the mean variance optimization.

Download Practice Workbook

Download this practice file and try it by yourself.


Conclusion

Concluding the article, I hope you have got a detailed understanding of mean variance optimization in excel with the easy steps. Let us know for any kind of suggestions.


Related Articles


<< Go Back to Optimization in Excel | Solver in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

2 Comments

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo