How to Calculate Portfolio Variance in Excel (3 Smart Approaches)

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.

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.

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.

Practice Section

Practice here for further expertise.

Download Practice Workbook

Portfolio Variance Calculation.xlsx

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.