Portfolio variance formula excel mmult
Hem / Ekonomi & Finanser / Portfolio variance formula excel mmult
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.
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.
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.
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.
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. To maximize the benefit of diversification more securities are needed, making the textbook method of calculation tedious and prone to 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.
➤ You’ll get the transpose matrix. But, have you ever wondered how diverisfication works on investments? 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%. There is a correlation of 0.85 between the two stocks. However, some accounts only have a couple of products.
This method works well if you want to lower portfolio risk without depending on projected returns. 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. 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.
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. Learn how in seven minutes!
The short tutorial below implements the standard matrix multiplication algorithm for determining expected portfolio variance and standard deviation (volatility).
Determine the variance.
Given,
- The standard deviation of stock A, ơA = 15%
- The standard deviation of stock B, ơB = 10%
Correlation, ρA,B = 0.85
Below is data for the calculation of the portfolio variance of two stocks.
Weightage of Stock A, wA = $60,000 / ($60,000 + $90,000) * 100%
Weightage of Stock A = 40% or 0.40
Weightage of Stock B, wB = $90,000 / ($60,000 + $90,000) * 100%
Weightage of Stock B= 60% or 0.60
Therefore, the portfolio variance calculation will be as follows,
Variance = wA2 * ơA2 + wB2 * ơB2 + 2 * ρA,B * wA * wB * ơA * ơB
= 0.4^2* (0.15)2 + 0.6 ^2* (0.10)2 + 2 * 0.85 * 0.4 * 0.6 * 0.15 * 0.10
Therefore, the variance is 1.33%.
Hi guys,
Need your help.
I have a portfolio with various accounts each of which has some products (called risk_curve_month_name in my datasets).
My goal is to be able to calculate portfolio variance based on what accounts were selected (one or a few).
Portfolio variance is calculated through the multiplication of the transposed vector of asset weights by the variance-covariance matrix and multiplied by the vector of asset weights again (this time not transposed).
The weights of assets are already calculated in my power bi: quantity (called exposure in my dataset) * price * variance.
The tricky part is that the variance-covariance matrix consists of all possible combinations of the products.
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 minimum variance portfolio offers the least possible risk for a certain level of expected return. 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.