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

When it comes to business, understanding and managing risks is crucial. One aspect of risk management involves analyzing how the actual returns of a portfolio fluctuate over time. Portfolio variance provides valuable insights into this variability.

What Is Portfolio Variance?

Portfolio variance is a statistical measure in modern investment theory. It quantifies the dispersion of actual returns within a portfolio relative to its mean return. To calculate portfolio variance, we consider both the standard deviation of each security in the portfolio and the correlation between those securities.

The Portfolio Variance Formula

We can compute portfolio variance using the following formula:

Portfolio Variance = W1^2 * σ1^2 + W2^2 * σ2^2 + 2 * ϼ1,2 * W1 * W2 * σ1 * σ2

Where,

W = represents the portfolio weight, calculated by dividing the dollar value of a security by the total dollar value of the portfolio.

σ^2 = denotes the variance of an asset.

ϼ = represents the correlation between two assets.

Method 1 – Using the Conventional Formula

We’ll use a Dataset for Stock 1 and Stock 2 with the values of Stock Value, Standard Deviation and Correlation 1 & 2.

Step 1 – Calculation of Stock Weight in Portfolio

Select a cell (Cell C8) to measure Stock weight.
Enter the following formula:

=C5/(C5+D5)

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

Now, press ENTER.

To measure the Stock Weight in Portfolio for Stock 2.
Enter in following formula in D8:

=D5/(C5+D5)

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

Press ENTER.

Step 2 – Portfolio Variance Calculation

Enter the following formula in Cell C9:

=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

Press ENTER to calculate the portfolio variance using the conventional formula.

We calculated the Portfolio Variance using the conventional formula.

Method 2 – Using the MMULT Function

The MMULT function provides an interesting approach to compute portfolio variance. It leverages matrix multiplication to achieve this. Here’s how you can apply it:

We’ll use a dataset of portfolio returns for the companies GOOGLE, TESLA, and Microsoft.

Step 1 – Collect Portfolio Returns Data

Gather data on portfolio returns for the companies (e.g., Google, Tesla, Microsoft).
Create a dataset with returns for each company.

Step 2 – Calculate Covariance

Go to the Data tab in Excel.
Select Data Analysis.
Choose Covariance from the available options.

A Covariance box will appear.

Enter your data range (e.g., C5:E13) and select a cell for the covariance output (e.g., C15).
Click OK to obtain the covariances.

We will have the Covariances on the selected cell.

Step 3 – Modify the Dataset

Add company names both horizontally and vertically.
Express stock weights as percentages in the dataset.

Step 4 – Fill in Empty Cells

Place the relevant covariances in the empty cells.

Step 4 – Calculate Portfolio Variance

Enter the following formula to calculate the portfolio variance:

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

Where,

- - The first matrix multiplication is between the arrays D16:F16 and D17:F19.
  - The second matrix multiplication involves the result from the first multiplication and the array C17:C19.