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.
Download Practice Workbook
Download this practice file and try it by yourself.
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.
Step-by-Step Process of Mean Variance Optimization in Excel
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.
Read More: How to Calculate Mean Variance and Standard Deviation in Excel
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.
- Don’t forget to format the values as percentages with the Home > Numbers > Percentage command.
Read More: How to Calculate Percentage Variance between Two Numbers in Excel
Similar Readings
- Find the Variance of a Probability Distribution in Excel
- How to Solve Network Optimization Model in Excel (3 Cases)
- Calculate Semi Variance in Excel (3 Simple Methods)
- Schedule Optimization in Excel (Detailed Analysis)
- How to Calculate Variance Inflation Factor 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.
- 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.
- 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.
Read More: How to Calculate Variance in Excel (3 Handy Approaches)
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)
- 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.
Read More: How to Create Minimum Variance Portfolio in Excel (2 Examples)
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.
Read More: How to Do Variance Analysis in Excel (With Quick Steps)
Similar Readings
- How to Calculate Coefficient of Variance in Excel (3 Methods)
- Solve Linear Optimization Model in Excel
- How to Calculate Portfolio Variance in Excel (3 Smart Approaches)
- Calculate Variance Using Pivot Table in Excel (with Easy Steps)
- How to Calculate Variance Percentage in Excel (3 Easy Methods)
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.
- 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: Excel Optimization with Constraints (3 Case Scenarios)
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.
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. Follow ExcelDemy for more tutorials.
Related ArticlesÂ
- How to Perform Route Optimization in Excel (2 Easy Ways)
- Budget vs Actual Variance Formula in Excel (with Example)
- How to Make Price Optimization Models in Excel (with Easy Steps)
- Calculate Pooled Variance in Excel (with Easy Steps)
- How to Do Portfolio Optimization Using Excel Solver
- Find Population Variance in Excel (2 Easy Ways)
- How to Calculate Schedule Variance Using Excel Formula