In terms of doing business, we face the necessity to measure all kinds of risks. It is also needed to take into consideration how the aggregate of actual returns works for a set of securities making up a portfolio fluctuate over time. Portfolio Variance gives exactly what we are talking about. We are going to explain 3 smart approaches on how to calculate Portfolio Variance in Excel.

## What Is Portfolio Variance?

**Portfolio Variance **actually refers to a statistical value of modern investment theory. It measures the dispersion of actual returns of a portfolio from its actual mean. It is measured using the standard deviation of each security in the same portfolio and correlation of the securities.

## Formula of Portfolio Variance

We can calculate the **Portfolio Variance **applying the following formula:

`Portfolio Variance = W1^2 * Ïƒ1^2 + W2^2 * Ïƒ2^2 + 2 * Ï¼1,2 * W1 * W2 * Ïƒ1 * Ïƒ2`

Where,

** W** Â = Portfolio Weight which is calculated dividing the dollar value of a security by the total dollar value of the portfolio

** Ïƒ^2** = Variance of an asset

** Ï¼**Â Â = Correlation between two assets

## How to Calculate Portfolio Variance in Excel: 3 Smart Approaches

### 1.Â Â Using Conventional Formula to Calculate Portfolio Variance

In this method, we simply input the value in the equation and calculate **portfolio Variance**.We have taken a **Dataset **for **Stock 1 **and **Stock 2 **with the values of **Stock Value**, **Standard Deviation **and **Â Correlation 1 & 2**.

Letâ€™s start to calculate the desired Portfolio Variance.

__Calculation of Stock Weight in Portfolio__

- Select a cell to measure
**Stock weight**. I selected cell**C8**in**Stock 1** - Input the following formula:

`=C5/(C5+D5)`

Here, the stock value of **Stock 1 **is divided by the total stock value.

- Now, press
**ENTER**.

- Similarly, measure the
**Stock Weight in Portfolio**for**Stock 2**.

In this case, the formula is:

`=D5/(C5+D5)`

Where, the stock value of **Stock 2 **is divided by the total stock value.

- Hit the
**ENTER**button.

__Portfolio Variance Calculation__

- Apply the following formula:

`=C8^2 *(C6^2) +D8^2*(D6^2)+2*C7*C8*D8*C6*D6`

Where,

**C8 **= Portfolio Weight of Stock 1

**C6 **= Standard Deviation of Stock 1

**Â Â Â Â Â Â D8 **= Portfolio Weight of Stock 2

**D6 **= Standard Deviation of Stock 2

**C7 **= Correlation between Stock 1 and Stock 2

- Finally, press
**ENTER**.

Thus, we can calculate the **Portfolio Variance **using the conventional formula.

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

### 2. Application of MMULT Function to Calculate Portfolio Variance

Another quite fascinating way to calculate **Portfolio Variance **is to apply **the** **MMULT Function**. The **MMULT Function **gives the output of the matrix product of two arrays.

You need to collect a set of portfolio returns for the investments. Here, I have created a dataset of portfolio returns for the companies **GOOGLE**, **TESLA, **and **Microsoft**.

**Steps**:

- Gather the data as Iâ€™ve done here.
- Now, go to the
**Data** - Select
**Data Analysis**.

- Choose
**Covariance**from the**Data Analysis** - Press
**OK**.

A **Covariance **box will appear.

- Input your data range in the
**Input Range (i.e. C5:E13)**. - Pick a cell to have the
**Covariance**output (i.e.**C15**). - Next, click on
**OK**.

We will have the **Covariances **on the selected cell.

- Modify your dataset. I have added the companysâ€™ names horizontally and vertically.
- I have added the stock weight in percentage in both horizontal and vertical fashion.

- Now, fill up the empty cells. I have placed the related
**Covariance**in the empty cells.

- Now, apply the following formula to calculate the portfolio variance:

`=MMULT(MMULT(D16:F16,D17:F19),C17:C19)`

Where, 1st matrix multiplication is done between **D16:F16 **and **D17:F19** arrays. Then, 2nd matrix multiplication is done with the 1st matrix product and **C17:C19 **arrays.

- Finally, press
**ENTER**to have the**Portfolio Variance**.

**Read More: **How to Create Minimum Variance Portfolio in Excel

### 3.Â Calculate Portfolio Variance Using SUMPRODUCT and SUM Functions

We can also use a formula combining the** SUMPRODUCT **and **SUM **functions to calculate **Portfolio Variance**.

**Steps**:

- Follow the same procedure from the above to find out the
**Variances**. - Now, select a cell and input the following formula:

`=D17*SUMPRODUCT($C$18:$C$20,D18:D20)`

Where, the SUMPRODUCT Function is applied to multiply between the arrays **C18:C20 **and **D18:D20**.

- Next, press
**ENTER**.

- Use
**Fill Handle**to**AutoFill**the rest cells (**i.****e. E21 & E22**)**.**

- Sequentially, apply the
**SUM Function**to calculate the summation of the output.

- Finally, press
**ENTER**.

This is another way how we can also calculate **Portfolio Variance**.

**Read More: **How to Calculate Variance Inflation Factor in Excel

## Practice Section

Practice here for further expertise.

**Download Practice Workbook**

## Conclusion

I have tried to explain 3 smart approaches **how to calculate Portfolio Variance in Excel **in this article. I hope everyone will be able to understand it quite easily. For further queries, comment below.

## Related Articles

- How to Calculate Budget Variance in Excel
- Budget vs Actual Variance Formula in Excel
- How to Calculate Variance of Stock Returns in Excel
- How to Do Price Volume Variance Analysis in Excel
- How to Calculate Schedule Variance Using Excel Formula

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