If you have followed my articles Dual-Beta - The Smart Investor's Most Valuable Tool or The Dual-Beta Portfolio, Part 1 - Consumer Defensive Sector, you are fully aware how powerful it can be to be able to dissect traditional beta into upside beta and downside beta, collectively called dual-beta. I highly recommend reading one or both of the above articles if you are unfamiliar with the metric. Here, I will show you exactly how you can calculate dual-beta, as well as several important considerations to make when performing your calculation.
A little known fact among novice investors is that traditional beta is calculated using vastly different time periods, depending on which source an investor is looking at. Yahoo uses 3 years of monthly returns, Google, Zacks, and Reuters use 5 years of monthly returns, while still others use as little as 1 year of monthly returns.
On one hand, as a measure of non-systematic risk, we would like beta to be static and permanent for each security it is characterizing. This line of logic leads us to prefer a lengthy time period like 5 or 10 years. However, if we accept that non-systematic risk is likely to change quite a bit, we obviously would like an up-to-date representation of this risk, which means we should prefer a shorter period like 1 or 3 years.
Frequency has very little relevance to traditional beta, as I have yet to come across a traditional beta calculation that does not use a monthly return frequency. With dual-beta, the frequency is much more important.
The Journal of Finance study which I have cited several times in my prior articles chose a daily frequency, which has caused most "practical" calculations of dual-beta elsewhere on the internet to choose a daily frequency. Remember, however, that their study was intended to measure a phenomenon known as loss-aversion, by most investors are far more sensitive to bad news than good news. Daily frequency allowed the study to effectively measure and test which stocks are most sensitized by their investors. While this may be somewhat useful information, it is almost guaranteed to change over time because investors change over time. Therefore, one is ruling out any sense of inherent risk, and totally accepting that they are seeing temporary risk.
The advantage to using monthly returns is that this loss aversion by investors is largely eliminated. The market has ample time to adjust to the irrationality of loss aversion. As a result, the monthly returns that are produced are representative more of the company and less of its investors. Indeed, this is likely why almost every financial data source uses a monthly return frequency to calculate traditional beta. There is no reason the same should not be the case for dual-beta.
For My Calculations, I Use...
- 10 Year Period
- Monthly Return Frequency
Calculating Dual-Beta in Excel
1. Choose your period and frequency
Think about your style and outlook as an investor. Keeping this in mind, decide which period to use (I recommend 10 years). Likewise, decide whether you would like to use daily returns or monthly returns (I highly recommend monthly returns).
2. Retrieve the S&P500's returns
A good site to use is Yahoo Finance. The SPY tracks the S&P500 so you can search for that. Select "Historical Prices." Then, select "Monthly Returns," and click "Get Prices." Finally, scroll to the bottom and click "Download to Spreadsheet." Copy and paste the entire "Close" column into whatever spreadsheet you are using to calculate dual-beta.
3. Retrieve your desired company's returns
Follow the same procedure as in Step 2 with your desired company. Afterward, you should have monthly closes for SPY and your company next to each other on a spreadsheet, with corresponding months aligned. For my example, I will use KMR.
4. Calculate monthly change
For both sets of returns, we must now calculate monthly percent change from the returns gathered. The simplest way to do this is to insert a new column after the SPY returns and after your desired company's returns. In these columns, follow the formula shown below and copy it to the bottom so that there is a percent change for each return (except the last one).
5. Delete excess data
For whatever period you have chosen, feel free to delete any historical returns/change that extend past the period you have chosen. For example, if you are using a 10 year period, delete any data for months prior to July 2003.
6. Format as a table
Formatting all 5 columns as a table will allow you to sort the data easily, which is exactly what we will need to do in the next few steps. Highlight all of the data and click "Insert Table." It is probably a good idea to have headers.
7. Separate upside data
Use your table to sort by only months in which SPY advanced. To do this, click on the header for SPY change, select "Number Filters," and click on "Greater than..." In the box next to "Is greater than," enter the number "0." After clicking "OK," some of the rows will disappear and you will be left with only months for which SPY advanced.
8. Create a new workbook for upside data
Select a new workbook at the bottom and copy the entire table into this workbook. When you are pasting the data, make sure you select "Values (V)" instead of "Paste (P)" so that the data is not dependent on formulas in this workbook.
9. Calculate upside beta
Using any cell, enter the formula to calculate upside beta. The formula consists of covariance between the S&P500 and your desired company divided by variance in the S&P500. The exact syntax for Excel is shown in the picture below. Ensure that the cell ranges match the number of data points you are using, which will change depending on the time period. For example, a 10 year time period gave me 77 upside data points, and therefore my cell ranges were 2 to 78 because I had headers. After you have entered the formula, the result will be upside beta for the chosen company.
10. Repeat for downside data
Follow the same process to separate downside data (use the table in your first workbook to sort by declines) and create a workbook for downside data. Use the same formula to calculate downside beta, but ensure that you have used the correct cell range, since it is very likely that there will not be the same number of upside data points as downside data points (for 10 years, you will have only 45 downside data points).
Since this is a complicated process, you are bound to make a mistake at some point. Always ask yourself whether the beta that you calculated makes sense. For the most part, you can use Google's traditional beta as a sanity check. Traditional beta usually falls somewhere between upside and downside beta, but this is not always the case. Try calculating dual-beta for a company whose dual-beta I have examined in one of my articles, and compare to the results found there. If they match, you have successfully emulated my calculation.
Finally, make sure you understand what dual-beta is telling you. This is a metric that explains where past alpha has come from. In other words, dual-beta tells you whether a company derived more of its growth from mitigating loss from bearish sentiment, or more of its growth from leveraging upside from bullish sentiment. The underlying goal of the metric is to get more out of being lucky than what you lose when you are unlucky.