Portfolio variance formula excel mmult

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.

This method works well if you want to lower portfolio risk without depending on projected returns. 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.

So please ask me a question if anything is unclear.

Many thanks for your time and help. This can be accomplished in Excel with MMULT and TRANSPOSE array functions.This tutorial makes use of a COVARIANCE matrix.

Portfolio Variance Formula

Let us take the example of a portfolio that consists of two stocks.

But, have you ever wondered how diverisfication works on investments?

➤ You’ll get the transpose matrix. 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 risk in SML is beta or systematic risk.

What is the minimum variance curve of a portfolio?

The minimum variance curve or minimum variance frontier represents a set of portfolios with the least variance (risk) for each level of expected return.

How to calculate returns from the adjusted close price?

Returns:

---

Wrapping Up

In this tutorial, we’ve learned about the minimum variance portfolio and the steps to calculate minimum variance portfolio in Excel using the functions and the Solver add-in.

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. However, some accounts only have a couple of products. It uses the covariance matrix of asset returns to assign weights that minimize the variance of the entire portfolio.

---

Why Use a Minimum Variance Portfolio?

➤ Uses diversification to lower portfolio risk.
➤ Only the historical return data is needed.
➤ A typical place to start in the Modern Portfolio Theory (MPT)

---

Making a Minimum Variance Portfolio in Excel

In this dataset, we have the returns from 1st January 2024 to 30th June 2024 for five stocks:

➤ Toyota Motor Corporation (TM)
➤ Walmart Inc.

(WMT)
➤ Chevron Corporation (CVX)
➤ Nvidia Corporation (NVDA)
➤ Microsoft Corporation (MSFT)

There are 123 observations and the returns have been calculated from Yahoo Finance based on the adjusted close price.

Suppose you are in charge of a portfolio of these five assets and want to reduce the total volatility.

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.

Excess return is calculated by subtracting the average returns from the firm’s return.

Note: Press the  F4  key twice to lock only the rows.

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

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. Thanks to Excel's covariance matrix and array functionality, we can easily calculate the variance and standard deviation for a portfolio of as many securities as we want.

Alternatively a portfolio variance can be calculated using a CORRELATION matrix, but using the COVARIANCE may be more intuitive.

You may also be interested in:

Portfolio Optimization with Excel

Value at Risk with Excel

You can download the Excel file used in the video here.

Portfolio Standard Deviation in Excel

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.

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.

Learn how in seven minutes!

The short tutorial below implements the standard matrix multiplication algorithm for determining expected portfolio variance and standard deviation (volatility). 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.

This tutorial covers how to quickly and accurately calculate portfolio volitility for any number of securities.The tutorial is useful for anyone trying to understand diversification of risk, and is a must know for any aspiring Professional Risk Manager(PRM).

Easily Calculate Portfolio Volatility (Standard Deviation) Using Excel

Finance textbooks demonstrate how to calculate variance of a portfolio with two securities, a fairly complex algorithm meant to demonstrate the idea of diversification, however not very realistic.

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.