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

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

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

Read More: How to Calculate Semi Variance in Excel

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

• Press ENTER to obtain the Portfolio Variance.

### Method 3 – Using SUMPRODUCT and SUM Functions

Step 1 – Compute Variance

• Follow the same procedure as before to find the variances.

Step 2 – Calculate Portfolio Variance

• Select a cell and enter the following formula:
`=D17*SUMPRODUCT(\$C\$18:\$C\$20,D18:D20)`

Where,

• The SUMPRODUCT function multiplies the arrays C18:C20 and D18:D20 element-wise.

• Press ENTER.

• Use the Fill Handle to AutoFill the remaining cells (i.e. E21 & E22).

• Apply the SUM Function to calculate the summation of the output.

• Press ENTER.

## Practice Section

Use the workbook to practice these methods.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF