In the previous article, we identified the assets that will comprise our original portfolio and calculated simple statistics to describe the expected returns and the variability of those returns.
The next step in building an optimal portfolio is using the return statistics to construct a covariance table. Since our objective is to create an easy-to-apply guide for modern portfolio theory, we will not go into detail on the math behind the covariance table.
This article will show you how to use Excel to calculate covariance. For an alternative (and we consider more powerful and flexible) approach, consider using R instead of Excel. See the page here for guidance on using this great platform to analyze covariance and correlation.
Let’s begin with an example to illustrate the concept and application of :covariance.
Variables can be positively related; when the price of stock a rises, stock b tends to rise. They can also be negatively related; when the price of stock a rises; stock b tends to fall.
Simple Example – Covariance of Two Assets
A simple example can help illustrate how we can use covariance to describe the relation between two investments. Consider a portfolio with the following two assets.
|Month||Asset A||Asset B|
At first glance, we can see that Asset A has more variability. Asset A has an overall higher average return, but the variability of the return is also much greater. Asset B could be described as slow and steady.
Let’s use Excel to calculate the covariance of this simple example. Either open Excel and enter the returns of these two assets as they are shown above, or click here to open up a sample Excel that already has the data entered. In a new cell, use the COVAR function to calculate the covariance. After you are done, check you answer below.
What can we summarize from the covariance analysis?
- The assets are positively correlated (they tend to move together, either up or down)
- The covariance is small and so the relationship, while positive, is very weak
Remember that a major premise of the Markowitz modern portfolio theory is that risk can be reduced by selecting assets that are not 100% correlated. If two assets were 100% correlated, there would be no benefit of diversification–a more down by one asset would be mirrored perfectly by the other.
As we defined earlier in this series, risk is the variance of the price of the asset. In modern portfolio theory, unrelated movements in asset prices can have the net effect of lowering overall risk.
For example, if one asset falls in value at the same time another asset rises, the overall effect is lower variance–or lower risk. The goal is to select the right mix, or allocation, of assets where the overall portfolio rises regardless of individual asset movements. Don’t worry if this isn’t immediately apparent; we’ll use real data to demonstrate this phenomenon later in the series.
Applying Covariance to Our Portfolio
Let’s extend using covariance from the simple two asset table above to our full portfolio mix. Remember that our portfolio currently consists of six possible assets plus a risk-free investment.
First, we aggregate the returns of each of the six assets. The we used the built-in Excel data analysis tool to create a covariance table, pictured below.
We applied conditional formatting to the covariance table. The darker the cell, the higher the covariance (worse for diversification). Lighter cells highlight low covariance–potential opportunities to benefit from diversification.
The covariance table is simply an aggregation of the the covariance of each pair. Notice that the covariance of a same-asset pair (for example, SPY-SPY) is simply the variance of the asset. The table can be split along the diagonal.
What can we observe from the covariance table? A number of important characteristics, including:
- LQD has a low covariance with all other assets. This asset may be an excellent asset in the optimal portfolio.
- IWM has a high covariance with all assets, except for LQD. This asset may not provide as much diversification benefit as other assets.
You can download the file by clicking here. We encourage you to use Excel tools and replicate the table above.
In the next article in this series, we will use this covariance table to actually construct the optimal portfolio based on the Markowitz selection technique.