ab-logo

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.

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!

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