Code. Models. Analysis. Decisions.
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. But, have you ever wondered how diverisfication works on investments? Do you have a vague idea of how to measure it but find the implementation daunting? 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. To maximize the benefit of diversification more securities are needed, making the textbook method of calculation tedious and prone to error. 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. 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 can be accomplished in Excel with MMULT and TRANSPOSE array functions.This tutorial makes use of a COVARIANCE matrix. 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