Portfolio Standard Deviation and Correlation Matrix Calculator in Google Sheets
For many investors, a diversified allocation of assets is a key part of their strategic long-term investment strategy. Diversification is a critical tactic in building a portfolio that can balance out risk and reward. In this article I’ll take you through a Google Sheet I created that automates many of the calculations to help you create a diversified portfolio.
A diversified portfolio is created by holding a variety of different assets that can give you a high enough long term return and when held together lower your risk. The mix of different assets is the key. It’s not just about holding many assets.
True diversification mixes assets that respond differently to market conditions, even in opposite directions. For example, holding bonds and stocks in a portfolio. Since bonds typically respond in the opposite direction of stocks this can balance out wide market swings. This way, even if part of your portfolio is loosing ground your other components are gaining. Diversification doesn’t ensure gains, but it can lower your overall risk.
But how exactly do you actually measure risk in a portfolio of assets? How do you know you have a truly diversified portfolio?
A portfolio correlation matrix can help you see quickly what assets rise and fall together or which assets rise when others fall. The correlation matrix is derived from the underlying covariance matrix of asset returns, which is used to calculate portfolio risk or volatility. Together these matrices can give you a statistical measure of your portfolios risk and diversification.
While there is no shortage of videos and downloadable spreadsheets that can show you how to build these matrices by hand from a portfolio in Excel, I found none that could simply do it for you. So, I created one.
The link below is a Google Sheet I created that can do all of the analysis and calculations for you automatically. Just enter your ticker symbols and the correlation matrix is automatically calculated along with a measure of risk (the portfolio standard deviation).
The sheet is integrated with the Google Finance API to automatically pull down stock price data as you enter the ticker. With some cell formulas, the sheet calculates expected returns, portfolio risk, sharpe ratios, and finally the portfolio correlation matrix. Just enter the ticker symbols and your portfolio allocations! The sheet supports up to 10 ticker symbols entered from the top to the bottom.
https://docs.google.com/spreadsheets/d/1UQHMyGbQcNdQdrH4nJmk7uQG0gIJ8NIjIMe2bi9FKfw
To open this link you will need a free Google account. Click the link above and when the sheet opens choose “File -> Make a Copy” from the Sheets main menu. This will copy it to your own Google Sheets account where you can edit your own copy and you are ready to go.
Here’s how it all works.
Asset Returns
Asset returns are ups and downs of price fluctuations for a set time range and frequency. For example 1 year of daily close price returns. Google Sheets includes a very handy API function for Google Finance to retrieve stock ticker OHLC (open, high, low, close) prices for any date range. For our purposes, we’ll retrieve the last 300 days of price close data automatically when a ticker is entered into the target cell. We’ll place this data in the sheet “Returns”.
The cell function below will extract out only the “Close” price for the ticker for the last 300 trading days. In order to get 300 actual trading days, we need to over fetch the date range in order to account for weekends and holidays. I’m using 500 “real” days to be sure. This formula retrieves all of the ticker performance data we will need and formats the result in a single column.
=index( sort(C7 array_constrain( sort( GOOGLEFINANCE(A2,"price",TODAY()-500, TODAY()), 1, FALSE) , 300 + 2, 2)), 0,2)Returns are the plus or minus differences of the fluctuations and are sometimes expressed as percent change. However, a more common way of modeling the change is to use the natural logarithm of the price today divided by the price yesterday:
Returns = ln( price(n)/price(n-1) )
This is calculated by the spreadsheet automatically on the “Returns” tab. With the daily return data in hand, we can turn our attention to calculating our standard deviation and correlation measures. We calculate this for each asset in the portfolio as well as for the portfolio as a whole.
Asset Volatility and Standard Deviation
The risk of an individual asset is also referred to as volatility. What we are looking to quantify is how much the asset price swings from day to day. As a statistical measure, this is usually expressed as the variance. The volatility is the square root of the variance which is simply the standard deviation. This gives a statistical measure of how much the price moves from its mean return value for the entire period on a day to day basis. Fortunately, Google Sheets has a nice built-in function for this: STDDEV().
Sharpe Ratio
The Sharpe Ratio is a measure of the overall expected performance of an asset versus how much the asset varies. As a metric, it shows the balance of the reward versus the risk. It’s calculated from the following formula:
Where u is the expected return of the asset (or the mean) and Rf is the risk free rate of return. The risk free rate of return is typically the US Treasury bills, which as of this writing is a 1.5% return rate. So the Sharpe Ratio gives you a good measure of how much return versus risk value you are getting minus the return value if you took no risk. The higher the Sharpe Ratio the better. A value over 1.0 can be regarded as pretty good.
Portfolio Returns
The expected return of the overall asset is based upon the expected returns of each asset and the allocation or weighting amount of that asset in the portfolio. Using w as the assets portfolio allocation percentage and the mean return of the asset as u, we can calculate the expected portfolio return as:
The cell formula for this is given in cell D17 on the Portfolio tab by:
=mmult(transpose(array_constrain(F4:F13, B17, 1)), array_constrain(G4:G13,B17,1))The array_constrain() function is a function that reduces a cell range to a height and width. Here, we want to reduce the height of the range to the number of assets in the portfolio (calculated in B17) and a width of 1. This gives use the proper sized vectors. You’ll see the array_constrain() in numerous places.
Portfolio Volatility and Standard Deviation
However, in order to calculate the volatility of the entire portfolio, we will need to calculate the covariance matrix . The covariance matrix is a measure of how much each each asset varies with each other. If there are N assets in the portfolio, the covariance matrix is a symmetric NxN matrix.
In order to calculate the covariance matrix for a sample distribution of returns we will need to initially compute the Excess Returns or mean centered returns. This is computed by simply taking the difference of each assets returns with the mean of the return for that asset.
Each entry of the covariance matrix can then be calculated in matrix form as:
=mmult(transpose( array_constrain(Returns!W4:AF303,299, Portfolio!B17) ), array_constrain(Returns!W4:AF303, 299, Portfolio!B17) )We then divide each element in the resulting range by n -1, n being the number of days of returns. Lastly, since we started with daily return values we can annualize these calculations by multiplying by 252 (approximate number of trading days in a year). All of these calculations are performed on the “Cov” tab in the Google Sheet.
The finalized covariance matrix is automatically calculated and formatted nicely with some color coding.
However, as we will see later it’s easier to use the correlation matrix to interpret the connections of the assets as it standardizes the ranges from -1 to +1.
Given the covariance matrix and , we can now compute the expected volatility (or standard deviation) of the overall portfolio using the weighted allocations (w) of the assets in the portfolio.
The portfolio allocation weights are obviously the critical factors that influence the overall portfolio standard deviation. The daily standard deviation can be annualized by multiplying by the square root of 252.
= sqrt( mmult(transpose( array_constrain(F4:F13,B17,1)), mmult(array_constrain(Cov!B15:K24, B17, B17), ARRAY_CONSTRAIN(F4:F13, B17, 1))) ) * sqrt(252)The Sharpe Ratio of the overall portfolio is calculated by subtracting the risk free rate of return from the expected return of the overall portfolio.
Portfolio Correlation Matrix
Lastly, the final portfolio correlation matrix is calculated from the covariance matrix entries using this equation:
This is the Pearson Coefficient value and standardizes the covariance matrix to values between -1 and +1. A correlation value of -1 between 2 assets means they are perfectly negatively correlated. When one moves . Where each σ value is the standard deviation of the asset. This is a bit tricky to do in Google Sheets, but we can create a temporary matrix of the asset standard deviations by using this equation:
We can then just use some simple spreadsheet formula to divide the covariance matrix with each of the (i,j) values. This gives us the final correlation matrix.
The correlation matrix shows how each asset moves with each other. Each entry in the table is the Pearson Correlation value for the corresponding assets along left and right. The matrix is symmetric and the diagonal values should all be equal to 1 (or very close allowing for precision).
The correlation values range from -1 to +1. A value of 0 means there is no correlation at all. A value of +1 indicates perfect correlation, meaning that when the asset rises or falls the corresponding asset matches that activity perfectly. A -1 value means the assets are perfectly negatively correlated. When one rises the other falls and vise versa.
I’ve color coded the matrix to show these correlation values much more easily. Green means higher positive correlation, yellow is zero, and red means a negative correlation.
In future articles I will continue to expand the Google Sheet with more functionality. We can see how certain assets may be related with principal component analysis, add more metrics, and see how to calculate the optimal asset allocation for a certain risk tolerance.
- Principal Component Analysis (PCA)
- Optimization of the risk/reward tradeoff for the portfolio
- Measuring systematic risk with the Capital Asset Pricing Model (CAPM)
- Additional portfolio metrics including value at rick (VAR) and maximum drawdowns