Building A Bulletproof 2020 Portfolio: Part III
Part II Recap
In Part II of Building a Bulletproof 2020 Portfolio, I listed some key data sources you might find useful when gathering information such as monthly returns, return on equity, free cash flow, and other fundamental metrics. I went through the pros and cons of using these free sources and some of the things you should watch out for, as you want to make sure data integrity isn’t a big issue before you begin.
In Part III, we’re finally going to go through the process of building a basic equity model in Excel. Before we get started, download this sample template and follow along!
Template Overview
The attached workbook has five worksheets:
Equity Fund: this is the worksheet where you will be doing the actual portfolio optimization
Portfolio Visualizer: this is a linked worksheet which shows you the historical annual performance of the portfolio developed in the Equity Fund worksheet
Covariances: this is a covariance matrix of all stocks in the sample stock universe, derived using the Data Analysis Add-In which comes built-in with Excel
Monthly Returns: I sampled 25 random stocks from each of the 11 GICS sectors in the S&P/TSX Composite Index and provided their monthly returns dating back to 2008
Annual Returns: this is a linked worksheet which simply calculates the annual returns for each stock based on the values entered in the Monthly Returns worksheet
Once you have done the legwork of actually getting all the monthly returns neatly organized, you will need to build a covariance matrix. So let’s show you how to do that in Excel right now.
Building a Covariance Matrix
Microsoft Excel comes pre-built with a Data Analysis Add-In, but you do have to load it first. It should appear in Data - Analyze - Data Analysis, but if you don’t see it, follow these steps to enable it:
File
Options (at the bottom)
Click Add-Ins on the left-pane
Click Go under Manage: Excel Add-Ins at the bottom
Check off Analysis Toolpak, Analysis Toolpak - VBA and Solver Add-In.
Check Data - Analyze - Data Analysis again to see if they are there. If they are not, repeat the process by first unchecking the three add-ins, and then re-check them
Now that the data analysis add-in is enabled, go to the Monthly Returns worksheet and select Data Analysis under Data - Analyze. Click Covariance and select the data range, including the tickers (I.e. labels) but not the column headers. Finally, ensure you have “Labels in first column” checked off and that your data is grouped by rows. Before clicking OK, your screen should look like this
Once you click OK, the entire operation should only take a couple of seconds. You will be left with a covariance matrix of sorts, but you’ll have to fill in the gaps. I have already done this in the sample template, but if adding additional tickers or using different symbols, you’ll of course need to know how to do this part. In order to fill in the whole matrix, you can use the Transpose function to quickly copy/paste until the table is filled out.
Using Excel’s Transpose and Array Functions
In the resulting covariance worksheet, select the range C2:Z2 (the blank cells) and then click on the formula bar. Enter: =transpose(B3:B26) and then press CTRL+SHIFT+ENTER to enter the formula as an array. The before and after should look like this:
Before clicking, press CTRL+C to copy the formula. Move down to cell D3 and press CTRL+V to copy. Continue to E4, F5, G6, etc. until you get to the end at Z25. Yours should look like this:
Once you’ve completed, select the entire worksheet and Copy/Paste Values. Delete the zeroes past column Z, add a header to A1, such as Symbol, and convert the data to percentage format to make it a little nicer to look at. Congratulations, you’ve just built a covariance matrix and are ready to do some modeling!
I should note that all of this could be done without the Data Analysis add-in by using the Covariance.S (sample) or Covariance.P (population) formula. If you want sample covariance then this is your only method, as the above method uses population covariance. Since we have a lot of data points though, it shouldn’t make much of a difference.
Setting Up Your Model
Jump back to the Equity Fund worksheet and we will take a look at how this is set up. The key pieces of data we will be using on this worksheet is the weight of each security and the expected monthly return. For simplicity sake, we are assuming that the expected return is the historical return. By clicking on cell F7, for example, you’ll see that I have used an Index-Match formula to return column EQ in the Monthly Returns worksheet based on the value entered in cell C7 (Symbol). Don’t worry about what is entered in the weight’s column - this is what Excel will calculate for you based on the constraints you enter.
Under Portfolio Statistics, Risk is calculated by using the matrix multiplier function, which multiplies by the weights of each security by the covariance matrix. For more information on this quite useful function, Microsoft has a page dedicated to explaining this function here. It should be noted that this calculates the portfolio variance - we need the standard deviation as our measure of risk though, so we take the square root of this value, all entered as an array by pressing CTRL+SHIFT+ENTER.
Return is much easier - it is simply the weighted average return. I chose to use the Matrix Multiplier function again, but you could have just as easily entered a SUMPRODUCT formula for the two arrays (weights and average return) and gotten the same result.
The Sharpe ratio is the excess portfolio return divided by the portfolio risk. In order to calculate excess return, we need to subtract the one-year T-bill rate. According to the Bank of Canada, the yield is 1.74%. Per month, this works out to be about 0.145% (0.144% if compounded). Keep note of this Sharpe Ratio, as this is the cell we are going to get Excel to maximize for us.
Using Solver
If you followed the instructions above, Solver should already be enabled in Data - Data Analysis. First ensure you are on the Equity Fund worksheet and click on Solver. A dialogue box will open. To get started, fill out these basic parameters and constraints:
Set objective cell to maximize the Sharpe Ratio (Cell I10). In other words, we are trying to maximize the return/risk ratio of our portfolio.
By changing variable cells of the weights column (Cells E7:E31)
Subject to the constraint that the sum of all weights equals 1, or 100%
Assuming you don’t want a portfolio where shorting stocks is possible, leave the box checked for “Make Unconstrained Variables Non-Negative”
Keep your Solving Method to GRG Nonlinear. This will work most times assuming you do not add too many constraints. If one of your constraints, or objective cell for that matter, is linear, Solver will not find a solution.
You can add in other constraints as you see fit. I would recommend adding them one at a time, such as limiting the maximum weight of a single stock or a single sector. You can also limit the number of stocks, set a fixed weight for a particular stock you wish to own, and a whole host of other easy constraints.
Some users may want to maximize the Sharpe Ratio but want to maintain an acceptable risk value. To do this, they can simply add in a constraint which says that risk must equal a particular value.
Interpreting The Outputs
As you can see, using Solver isn’t too complicated and it aids in making your portfolio more objective. Some people may also find use in it for testing their own portfolio for excess risk by simply entering in the weights of their current portfolio. Personally, I have been finding that this method, like most statistical methods out there, understates risk and overstates return, but not to the extreme and not frequently either. My 2019 portfolio, for example, has an expected monthly return of 1.77% and an expected monthly risk of 2.17%. Using the basic assumption that returns are normally distributed, this would mean that I could expect, 19 times out of 20, to have monthly returns fall between +/- two standard deviations of the average return. This would put the expected return for a 95% confidence interval between -2.58% and 6.12%.
Looking back to the 143 months between January, 2008 and November, 2019, the portfolio’s worst month was October, 2008 (-4.14%) compared to the benchmark index ETF (XIC.TO) return of -17.16%. In September, 2008, it returned -3.20% compared to the benchmark’s -13.20%. As far as best months go, the best month was November, 2014 when it returned 6.05%, while the best month for the index ETF was May, 2009 when it returned 10.85%. Still, a portfolio worth $10,000 would turn into $83,185 by now, while the index ETF would only be worth $12,494.
Now of course this isn’t a fair comparison, because I did not have this data available in December, 2007 and therefore could not use it to develop a portfolio. I’m only noting this as validation that the model does give an output that’s pretty representative of the past. The only question now is, how much weight do you give a stock’s past performance in predicting future results?
Everyone’s answer will naturally vary. I personally believe in, at most, the weak form of the efficient market hypothesis, which allows for some technical and historical data to be used to outperform the market. This may even be a stretch, as there appear to be many examples of market inefficiency. All I can state though is my past experience, and it continues to be positive. Take 2019 for example. This year, the range of monthly returns has been between -0.23% and 6.00% compared to the Index ETF’s -2.95% and 8.64% and the portfolio outperformed the benchmark by nearly 7%. This all comes back to my investment philosophy: lower volatility equity portfolios achieve higher returns.
Up Next
Now that we have a workable template, we must refine it in order to conform to our investment philosophy. That is what Part IV will deal with. The model in its current state makes no adjustments for return on equity or free cash flow. It hasn’t yet to put any limits on weights for any stocks or sectors, “punished” investments for not having a lengthy trading record dating back to the last recession, or even taken into consideration the life cycle of the stocks it has chosen. You could conceivably have stocks in your portfolio which were in the growth stage a decade ago but now have much lower expected returns after transitioning to the mature stage. We’ll go through a number of these things you may want to constrain your model by, and get you on your way to building a portfolio you can be happy with during any time period.
Stay tuned!