Mean Variance Optimization in Excel (With Easy Steps)

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.

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)

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.

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.

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

=I6

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)

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.

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)

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)

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)

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.

Then, select Options from the left panel.

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

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

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.

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.

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.

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.

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.

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.

Mean Variance Optimization.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!