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

**Steps:**

- Need to calculate the
**Excess Returns**of these companies. Go to a new sheet and use a formula to calculate it. - 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.

- Drag the
**Fill Handle**to the right to**AutoFill**the cells to**F6**.

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

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

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

- Go to another new sheet for our convenience. Create necessary columns in it as well.
- Type the following formula in
**B5**.

`=xtx!H6/10`

- Drag the
**Fill Icon**to the right upto**F5**. Drag down the**Fill Icon**to**AutoFill**the lower cells. Create a**Portfolio Return Matrix**.

- 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. We chose**2**as**0.2**times**5**equals**1**. This is the hypothetical investment percentage for an investor.

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

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

- Use the following formula to determine the
**Variance**of our data.

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

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

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

- 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**. - Select
**C4:C8**for**Changing Variable Cells**. Get the percentages of sustainable investment in these cells once we launch the**Solver**. - Add some
**Constraints**to get more accurate results.

- After clicking on
**Add**, the**Add Constraint dialog box**will appear. Set the value of cell C4 to greater than**or equal to zero**.

- We added another
**constraint**which sets the value in**C9**to**1**. - Click on
**Solve**.

- The
**Solver Results**window will appear. Click**OK**.

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

Create a **Minimum Variance Portfolio **in Excel.

### Method 2 – Minimum Variance Portfolio Comparing Two Assets

**Steps:**

- Insert the stock return data and select some cells to store the necessary data, such as Standard Deviation or Variance. Set an initial investment percentage. We want to invest 67% in Twitter, and the rest in
**Tesla**. - Type the following formula in
**G5**.

`=1-G4`

- Type the following formula in cell
**D5**which will return the**Portfolio Return**

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

- Use the
**Fill Handle**to**AutoFill**the lower cells.

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

`=AVERAGE(B5:B14)`

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

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

**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**).- Insert the cell reference
**I10**where the**Portfolio Return Standard Deviation**is stored and set the**Objective**to**Min**. - See the change by varying the investment percentage of
**Twitter**. Insert the**G4**cell reference in the ‘**By Changing Variable Cells**’ section. - Cick
**Solve**.

- Get the optimum value for the investment percentage in both
**Twitter**and**Tesla**. Minimize the**Variance**from**85%**to**3.61%**.

**Download Practice Workbook**

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