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

**Table of Contents**hide

## Download Practice Workbook

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

## 3 Smart Approaches to Calculate Portfolio Variance in Excel

### 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 Variance in Excel (Easy Guide)**

**Similar Readings**

**How to Calculate Pooled Variance in Excel (with Easy Steps)****Calculate Coefficient of Variance in Excel (3 Methods)****How to Calculate Variance Percentage in Excel (3 Easy Methods)**

### 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 Do Variance Analysis in Excel (With Quick Steps)**

### 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 Using Pivot Table in Excel (with Easy Steps)**

## Practice Section

Practice here for further expertise.

## Conclusion

I have tried to explain 3 smart approaches of **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.