Portfolio variance formula excel mmult

Hem / Ekonomi & Finanser / Portfolio variance formula excel mmult

This blog post explores the minimum variance portfolio and explains how to make a minimum variance portfolio in Excel using simple steps.

➤ A minimum variance portfolio offers the lowest risk for a given expected return.
➤ Excess returns:
➤ Transpose matrix:
➤ Variance-covariance matrix:
➤ Set weights arbitrarily. The risk in CML is the standard deviation of returns or the total risk.
SML indicates the market’s risk and returns at a specific point in time and the expected returns of each asset.

You can also use the shortcut  Alt + F + T  to open Excel Options.

➤ Select Add-ins >> Choose Excel Add-ins from the dropdown >> Go.

➤ Check the Solver Add-in option >> OK.

The Solver add-in will be available in the Analysis group of the Data tab, whenever you open a new workbook.
➤ Go to the Data tab >> Analysis >> Click Solver.

➤ For Set Objective enter the portfolio variance (AB10).
➤ Enable the Min option.

The total weight adds up to 1.
➤ Based on past data, these weights will return the lowest portfolio standard deviation.
➤ Note how diversification among assets with weak or negative correlations helps to lower total volatility.
➤ The minimum variance portfolio configuration can be used as a standard against which other portfolios (such as equal weighted or max Sharpe) can be compared.

FAQ

What is portfolio variance?

Portfolio variance measures the risk of a portfolio based on the variance of each asset in that portfolio.

How to minimize portfolio variance?

Portfolio variance can be minimized by selecting stocks with negative or low correlation.

What is the difference between CML and SML?

CML shows the rate of return for a certain portfolio.

This can be accomplished in Excel with MMULT and TRANSPOSE array functions.This tutorial makes use of a COVARIANCE matrix. Feel free to download the practice file and share your thoughts and suggestions.

Post Views:921

How to Calculate Portfolio Variance in Excel

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

Diversification in Excel Using Multiple Securities

People have long known that diversification mitigates risk, not in just finacial markets, but in nearly every aspect of life.

➤ You’ll get the transpose matrix.

portfolio variance formula excel mmult

Do you have a vague idea of how to measure it but find the implementation daunting? All the weights must add up to one
➤ Weight transpose matrix:
➤ Portfolio variance:
➤ Enable Solver: File >> Options >> Excel Add-ins >> Solver Add-in.
➤ Solver: Set objective >> Min >> By Changing Variable Cells >> Constraints >> Solve.

In this article, we’ll learn to make a minimum variance portfolio in Excel using MMULT and TRANSPOSE functions and the Solver Add-in.

Download Practice Workbook

What is a Minimum Variance Portfolio?

A portfolio with the lowest volatility for a particular group of assets is known as the Minimum Variance Portfolio.

There is a correlation of 0.85 between the two stocks. The value of stock A is $60,000, and its standard deviation is 15%, while the value of stock B is $90,000, and its standard deviation is 10%. For example, we’ll add the constraints that the sum of all the weights must equal one and the weight for NVDA should be at least zero.
Other constraints include all the weights must be greater than zero or that some weights should be negative (shorting stocks).
➤ The sum of the weights equals one: Select AB7 cell >> Choose equal to >> enter 1 >> Add.

➤ The weight for NVDA should be at least zero: Select AB5 cell >> Choose greater than equal to >> Enter 0 >> OK.

➤ Check all the inputs and hit the Solve button.

➤ Check Keep Solver Solution >> OK.

Based on the Solver results our portfolio should have the following weights for each stock:

➤ Toyota Motor Corporation: 10%
➤ Walmart Inc.: 35%
➤ Chevron Corporation: 29%
➤ Nvidia Corporation: 2%
➤ Microsoft Corporation: 23%

➤ For each of the five assets, you will obtain a different weight.

The minimum variance portfolio offers the least possible risk for a certain level of expected return. To maximize the benefit of diversification more securities are needed, making the textbook method of calculation tedious and prone to error. Hope you can help me 🙂

 

So before doing the matrix multiplication, the variance-covariance matrix must be filtered first.
Below are snips of my power bi and my example calculation in excel using MMULT and Transpose functions (see the formula bar on the snip).

 

It's probably confusing.

However, the total must be equal to one.

➤ Check that the total weights equal one.

➤ Multiply the transpose of the weights with the variance-covariance matrix to get the weight transpose sigma array. But, have you ever wondered how diverisfication works on investments?

➤ Drag the Fill Handle tool to copy the formula for the rest of the columns and rows.

➤ Since there are 5 firms, select the cells for the 5 by 5 matrix >> Select all the excess returns >> Press  Ctrl + Shift + Enter .

Note: Remember to press the  Ctrl + Shift + Enter  keys, otherwise you’ll get an error.

We want to minimize the portfolio variance.
➤ For the By Changing Variable Cells field choose the weights (AB2:AB6).
➤ Click Add to add constraints.

You can add multiple constraints. For risk averse investors, it is particularly useful since it can be used as a benchmark before using strategies.

Steps:

➤ Select the output cell (G3) and enter the formula.

The curly braces in the formula bar indicate an array formula.

➤ To get the variance-covariance matrix divide each element of the transpose matrix with the total number of observations.

Or,

➤ Auto-fill the formula to the rest of the cells.

➤ Set the weights arbitrarily. Learn how in seven minutes!

The short tutorial below implements the standard matrix multiplication algorithm for determining expected portfolio variance and standard deviation (volatility).

To determine which weights in the portfolio produce the minimum variance, you can use Excel’s Solver Add-in to calculate the covariance matrix of returns.

Using 123 daily return observations, we’ll determine the minimum variance portfolio weights for the five assets. Select the 5 cells, enter the formula, and press  Ctrl + Shift + Enter .

➤ The weight transpose sigma matrix is shown below.

➤ To get the portfolio variance, multiply the weight transpose sigma with the weights.

This is just a dummy portfolio variance that needs to be optimized using Excel’s Solver add-in.

➤ Click on the File tab.

➤ Select Options.