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.

**Table of Contents**Expand

## 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**.

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

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

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

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

**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)`

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

## 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**

- How to Solve Linear Optimization Model in Excel
- Excel Optimization with ConstraintsÂ
- Perform Multi-Objective Optimization with Excel Solver
- How to Optimize Multiple Variables in Excel
- How to Calculate Optimal Product Mix in Excel
- How to Solve Network Optimization Model in Excel
- How to Perform Route Optimization in Excel

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

well done!

Dear

Marc,You are welcome.

Regards

ExcelDemy