The article will show you how to create a Minimum Variance Portfolio in Excel. A properly organized portfolio with individual risky properties or assets that are combined when traded together creates a Minimum Variance Portfolio. This portfolio gives an efficient idea to an investor how much money he can invest to a company with the lowest possible risk. We also need the rate of change in stock for that. Let’s stick to the following sections of this article to find out how we can create a Minimum Variance Portfolio using Excel.

## What Is Minimum Variance Portfolio?

The **Minimum Variance Portfolio** hedges the total portfolio risk for the level of risk allowed in relation to the anticipated rate of return on the portfolio by leveraging the risk of each individual asset with an offsetting investment. Simply said, each investment in a minimum variance portfolio is dangerous if traded separately, but the risk is compensated when exchanged as a portfolio. The Markowitz Portfolio Theory, which contends that volatility can be used to substitute risk and that lower volatility variance is correlated with lower investment risk, is where the word first appeared.

## How to Create Minimum Variance Portfolio in Excel: 2 Practical Examples

In this article, I’ll show you two examples of how to create a **Minimum Variance Portfolio **in Excel. We will use two different datasets. Here, I’ll present to you a set of **Stock Return **data of five large companies for 10 months. This data refers to the rate of change in stock each month. Following this data, an investor can have an idea of what percentage of investment in any of these companies will be risk free. Let’s have a look at the data.

**1. Using Matrix to Create Minimum Variance Portfolio (Multi Assets)**

As you can see, we have five different companies’ stock return data in our Excel sheet, so it will be good for us to use a matrix to model the **Minimum Variance Portfolio**. This approach is really useful if you have stock return data of more than two companies or assets. Let’s go through the procedure below.

**Steps:**

- First, we need to calculate the
**Excess Returns**of these companies. For that reason, we go to a new sheet and use a formula to calculate it. - After that, type the following formula in
**B6**and press**ENTER**. This will show you the**Excess Return**of**Microsoft**for the first month.

`=dataset!B5-AVERAGE(dataset!B$5:B$14)`

The formula uses **the AVERAGE function** to calculate the average of total stock returns **Microsoft**. Then it is subtracted from each stock return to get the **Excess Return **data. The **dataset **term in the formula means that the cell reference is coming from the **dataset **sheet.

- Next, drag the
**Fill Handle**to the right to**AutoFill**the cells to**F6**.

- Thereafter, use the
**Fill Handle**again to**AutoFill**all the cells. This command will return all the**Excess Returns**for 10 months.

- Next, we will create a matrix by multiplying the
**transpose**of the array**B6:F15**with the**B6:F15**We will use this matrix to determine the**variance**in the future. For that purpose, create some columns to store the data of the matrix, select the**5×5**(multiplication between**5×10**and**10×5**matrices will result in a**5×5**matrix) range (**H6:L10**), and type the following formula in**H6**.

`=MMULT(TRANSPOSE(B6:F15),B6:F15)`

The formula uses **the TRANSPOSE function** to transpose the array **B6:F15**. **The MMULT function** returns the matrix multiplication result between this transposed array and **B6:F15**.

- After that, hold
**CTRL+SHIFT**and press**ENTER**. Although it won’t give you any errors in the latest version of Excel, you get errors in the older version of Excel. You will see the output of the**Matrix Multiplication**in the sheet.

- We will go to another new sheet for our convenience. Create necessary columns in it as well.
- After that, type the following formula in
**B5**.

`=xtx!H6/10`

- Later, drag the
**Fill Icon**to the right upto**F5**. After that, also drag down the**Fill Icon**to**AutoFill**the lower cells. This will create a**Portfolio Return Matrix**.

- Thereafter, we will use some dummy data to solve our
**Portfolio**. Select five decimal numbers that total to**1**. If you have 6 companies, you should select 6 decimal numbers. Here, I just chose**2**as**0.2**times**5**equals**1**. This is the hypothetical investment percentage for an investor.

- After that, type the following formula in
**C11**to determine the**Weighting Matrix**. Make sure you select the array**C11:G11**and hold**CTRL+SHIFT**before pressing**ENTER**. Although it won’t give you any errors in the latest version of Excel, you get errors in the older version of Excel.

`=MMULT(TRANSPOSE(C4:C8),variance!B5:F9)`

- Similarly, use the following formula to determine the
**Variance**of our data.

`=MMULT(C11#,C4:C8)`

- We are almost there. Just we need to use the
**Solver Toolpak**to finish our job. If you don’t have this in your**Data Tab**, you need to go to the**File Menu**>>**Options**>>**Add-ins**>>**Manage**>>**Excel Add-ins**>>**Go…**

- After that, the
**Add-ins**window will appear. Check**Solver Add-in**and click**OK**.

- The
**Solver Add-in**will appear in the**Data Tab**. Click on it to open it.

- Next, you need to insert
**Solver Parameters**. Here, we need to minimize the risk by minimizing the**variance**. So our**Objective**cell will be**C12**which stores the value of**Variance**. Also, select**Min**. - After that, select
**C4:C8**for**Changing Variable Cells**. We will get the percentages of sustainable investment in these cells once we launch the**Solver**. - In addition, we will add some
**Constraints**to get more accurate results.

- After clicking on
**Add**, the**Add Constraint dialog box**will show up. Here I set the value of cell**C4**to**greater or equal to zero**.

- Similarly, I added another
**constraint**which sets the value in**C9**to**1**. - After that, let’s click on
**Solve**.

- The
**Solver Results**window will appear. Just click**OK**.

- Next, you will see the minimized
**Variance**and the risk-free investment percentage.

- Let’s convert these decimals to percentages. The output illustrates that
**9110626%**of investment in**Microsoft**,**24.5315518%**of investment in**Twitter**, and so on will be risk-free for an investor.

Thus you can create a **Minimum Variance Portfolio **in Excel.

**Read More:** How to Calculate Portfolio Variance in Excel

### 2. Minimum Variance Portfolio Comparing Two Assets

Here, I’ll show you how to calculate **Minimum Variance Portfolio **when you have two companies or assets in your consideration. This is a less complex method than the previous one. Let’s stick to the process for a better understanding.

**Steps:**

- First, insert the stock return data and select some cells to store the necessary data like
**Standard Deviation**or**Variance**. Set an initial investment percentage. Say, we want to invest**67%**in**Twitter**. The rest will be invested in**Tesla**. - For that reason, type the following formula in
**G5**.

`=1-G4`

- Thereafter, type the following formula in cell
**D5**which will return the**Portfolio Return**

`=B5*$G$4+C5*$G$5`

- Later, use the
**Fill Handle**to**AutoFill**the lower cells.

- Again, we use another formula to calculate
**Expected Returns**for**Twitter**,**Tesla**, and**Portfolio Return**.

`=AVERAGE(B5:B14)`

- Next, we will use the
**VAR.P**function to calculate the**variance**of stock returns of**Twitter**. We want to ignore any logical values and text in the data, so we used the**P function**.

`=VAR.P(B5:B14)`

- Thereafter, type the following formula to calculate
**Standard Deviation**using the**STDEV.P**function.

`=STDEV.P(B5:B14)`

- Drag the
**Fill Icon**to the right to determine the**Expected Return**,**Variance**and**Standard Deviation**for other data.

- Again, we want to
**Minimize**the**Variance**or**Standard Deviation**(as**Standard Deviation**is simply the square root of**Variance**, we can use any of them for the**Minimum Variance Portfolio**). - So, we insert the cell reference
**I10**where the**Portfolio Return Standard Deviation**is stored and set the**Objective**to**Min**. - We will see the change by varying the investment percentage of
**Twitter**. So we insert the**G4**cell reference in the ‘**By Changing Variable Cells**’ section. - Later, just click on
**Solve**.

- After that, we will get the optimum value for the investment percentage in both
**Twitter**and**Tesla**. Also, we minimize the**Variance**from**85%**to**3.61%**.

This is another efficient and easy method to create a **Minimum Variance Portfolio **in Excel.

**Read More: **Budget vs Actual Variance Formula in Excel

**Download Practice Workbook**

## Conclusion

In the end, we can conclude that you will learn how to create a **Minimum Variance Portfolio **in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

**Related Articles**

- How to Calculate Variance Inflation Factor in Excel
- How to Calculate Semi Variance in Excel
- How to Calculate Schedule Variance Using Excel Formula
- How to Calculate Budget Variance in Excel
- How to Calculate Variance of Stock Returns in Excel
- How to Do Price Volume Variance Analysis in Excel

**<< Go Back to Calculate Variance in Excel | Excel for Statistics**** | Learn Excel**

=dataset!B5-AVERAGE(dataset!$B$5:$B$14)

should be

=dataset!B5-AVERAGE(dataset!B$5:B$14)

otherwise when you copy across to the other stocks, you are still using the Average for Microsoft to calculate the excess returns of the other stocks