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.


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.

How to Calculate Portfolio Variance in Excel

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.

How to Calculate Portfolio Variance in Excel

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

How to Calculate Portfolio Variance in Excel

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.

How to Calculate Portfolio Variance in Excel

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

Read More: How to Calculate Variance in Excel (Easy Guide)


Similar Readings


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.

How to Calculate Portfolio Variance in Excel

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

How to Calculate Portfolio Variance in Excel

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.

How to Calculate Portfolio Variance in Excel

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

How to Calculate Portfolio Variance in Excel

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

How to Calculate Portfolio Variance in Excel

  • Next, press ENTER.

How to Calculate Portfolio Variance in Excel

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

  • Finally, press ENTER.

How to Calculate Portfolio Variance in Excel

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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo