In this issue’s Technically Speaking column, we introduce Peter Martin’s Ulcer Index, a measure of investment volatility similar to standard deviation. However, by only focusing on downside volatility, the Ulcer Index overcomes the common criticism of standard deviation that it places upside and downside volatility on equal footing. As long-only investors, we are not concerned with volatility to the upside; in fact, we embrace it.
Beyond allowing us to compare the relative downside risk of investments, as we show in the Technically Speaking column, we can also use the Ulcer Index to calculate the excess return of an investment per unit of downside risk, similar to the way standard deviation is used in the Sharpe ratio. In this installment of Spreadsheet Corner, we show you how to use a spreadsheet to calculate the Ulcer Index of an investment, as well as the Ulcer Performance Index, or Martin ratio, to determine excess return. Using the monthly return data available for the AAII stock screens, we walk through the calculation of the Ulcer Index for a single methodology and then compare the downside risk of all the screens AAII tracks.
Our collection of stock screening methodologies is one of the most popular member benefits of AAII. Each month we calculate the performance of hypothetical portfolios of the stocks invested in these various strategies as well as provide lists of the companies passing these screens. We report this information at the Stock Screens area of AAII.com (www.aaii.com/stock-screens).
We also provide downloadable monthly performance data for all of the screens we track at www.aaii.com/files/spreadsheets/stockscreens/monthlyperformance.xls. This is the data used for our analysis here.
Calculating the Ulcer Index
Figure 1 is a partial representation of the monthly performance data for the Est. Rev. Up 5% screen. This data is available at the Stock Screens area of AAII.com for all of the 60+ screens AAII tracks. For the majority of screens, monthly performance data is available from January 1998. More frequent data is not available, as we rebalance the hypothetical portfolios only once a month. Martin points out that weekly data is ideal for calculating the Ulcer Index, but daily data can be used as well. As the interval is extended beyond a week, there is an increasing danger of missing significant intra-period drawdown-and-recovery events. The use of quarterly or longer intervals is strongly discouraged for this reason.
Column B of the spreadsheet contains the percentage monthly price gain or loss for the screen through the end of April 2013 (row 199). The monthly performance of the screen is the average price change of all the stocks held in the hypothetical portfolio. At the end of each month we run the screen and “buy” all of the stocks that pass the screen, investing equal dollar amounts in each stock and buying at the month-end price. We assume we hold all of these stocks through the next month-end, at which point we “sell” all of the stocks at the month-end price and “reinvest” all the proceeds in the new collection of passing companies, again investing equal dollar amounts in all the passing companies. This hypothetical performance does not include commissions, bid/ask spread, slippage or dividends. For a more comprehensive explanation of our backtesting methodology, see the FAQs section of the Stock Screens area of AAII.com.
Column C is the running cumulative or total performance of the screen. This is a geometric return, which we arrive at by stringing together the monthly returns. Through the end of April 2013, the Est. Rev. Up 5% screen had generated a total price gain return of 4,534.8% since January 1998. To calculate the geometric return of an investment, you multiply the returns. Looking at Figure 1, we see that the screen lost 0.51% in January 1998 (cell B16), gained 12.16% in February and gained 6.04% in March. Therefore, the total gain of 18.33% at the end of March is based on the following calculation: [(1 + (–0.0051)) × (1 + 0.1216) × (1 + 0.0604)] – 1] = [(0.9949 × 1.1216 × 1.0604) – 1] = 0.1833. Note that we converted the monthly performance figures into decimal form. To calculate this return in the spreadsheet, we entered this formula into cell C15:
Cell B16 is the monthly price gain/loss for January 1998 while cell C15 is the cumulative price gain/loss as of the previous month. We then simply copied this formula down for all of the monthly data through cell C199.
The Ulcer Index is based on the maximum drawdown in the dollar value an investment or portfolio experiences over the analysis period. Therefore, we must convert the monthly percentage gain/loss data for the Est. Rev. Up 5% screen into a dollar value for the hypothetical portfolio. To accomplish this, we entered a starting value of $1,000 at the end of December 1997 and then multiplied the previous month’s portfolio value by the monthly performance. So, the portfolio value of $994.89 in cell D16 is based on the previous month’s portfolio value of $1,000 and the monthly loss of 0.51%, such that [($1,000 + 1) × (1 + (–0.0051)) – 1]. So, in cell D16 we entered the following formula:
Again, we copy this formula down for all the months in the analysis period down to cell D199, where we see that the ending portfolio value for the Est. Rev. Up 5% screen is $46,393.54 as of April 30, 2013. (If you are calculating the Ulcer Index for a single stock or mutual fund, you can skip to this step and simply copy in the daily or weekly closing prices from Google Finance, Yahoo! Finance, etc.)
The drawdown of an investment or portfolio is the percentage loss from the maximum value of the investment or portfolio to that point. So the value in each cell of column E is the maximum value from column D to that point. For example, the value in cell E19 in Figure 1, $1,220.65, is the highest value of the portfolio to that point in the backtesting history. In cell E16, the maximum value of the portfolio to that point was $1,000 because the screen lost 0.51% in January 1998, lowering the portfolio value to $994.89. To calculate the maximum values in column E, we entered this formula into cell E16:
This “if, then” statement looks at the values in cell D16, the current portfolio value, and cell E15, the maximum value of the portfolio as of the previous month. If the current portfolio value (D16) is greater than the maximum value from the previous month (E15), the maximum value for that month is also the portfolio value for that month. However, if the current month’s portfolio value is less than the previous month’s maximum value, the maximum value remains as it was from the previous month. Looking at cell E199 in Figure 1, we see that maximum portfolio value of the portfolio over the backtesting period is $46,744.13 to that point. This value was first achieved at the end of March of this year. Then, in April, the screen lost 0.75%, so the maximum value remained at $46,744.13 as of the end of April.
After we have the maximum portfolio value for each month, the next step is to calculate the percentage drawdown for each month of the backtesting period. This is simply the percentage difference between the current month’s portfolio value and the maximum value of the portfolio to that point. In the months where the portfolio value and the maximum value are the same, there is no drawdown for that period. So, in cell F16, we entered this formula and copied down to cell F199:
In the condensed spreadsheet in Figure 1, there are only two months where the current portfolio value is below the maximum portfolio value to that point: January 31, 1998 (cell F16), and April 30, 2013 (cell F199).
While this value isn’t used for the Ulcer Index, the Est. Rev Up 5% had a maximum drawdown of 46.9% over the backtesting period. We found this value by entering this formula in cell E5:
While it may be counterintuitive, the maximum drawdown is the lowest (MINimum) drawdown value over the backtest period. The Est. Rev. Up 5% screen reached its maximum drawdown point at the end of September 2000.
As shown in the Technically Speaking column in this issue, the Ulcer Index is the quadratic mean (root mean square) of all periodic retracement values (drawdowns):
- R is the retracement (drawdown) value for each period in the look-back (backtesting) period, and
- N is the number of periods in the look-back (backtesting) period.
So the next step is to square the drawdown or retracement values for each month of the backtesting period. By squaring each retracement, it doesn’t matter that the values are negative, as squaring a negative value results in a positive value.
Column G is merely the value of the squared drawdown values from column F, such that we used this formula in cell G15 and copied it through cell G199:
When using spreadsheets the “^” symbol, or caret, is used to raise a number to a certain power, in this case the second power. For those months where the portfolio value and maximum value are the same, the drawdown or retracement value is zero (0), as is the squared value.
Lastly, as we stated earlier, the Ulcer Index is the root mean square of all retracement, or drawdown, values. In other words, it is the square root of the average retracement value over the look-back, or backtesting, period. Before we can calculate the average and then take its square root, we must add up all of the squared drawdown values. So, in column H, we keep a running total of the squared drawdown values using the following formula, which is entered into cell H16 and then copied down to cell H199:
This calculation takes the previous month’s sum of squared drawdowns (H15) and adds to it the current month’s drawdown squared (G16). The cumulative value over the backtesting period, in cell H199, is 47,315.82.
Now that we have the sum of the squared drawdowns, all that remains is to calculate the average and then the square root of the average. To calculate the average, we need the number of periods in the look-back (backtesting) period, which is what we total in cell E3 with the formula:
The COUNT function counts the number of cells that contain numbers, in this case the number of monthly price gain/loss values entered in Column B. The backtesting period of January 1998 through April 2013 encompasses 184 months. So, the average squared drawdown value of the backtesting period is 47,315.82 ÷ 184, or 257.15. The square root of this is 16.04. Cell E6 combines these calculations into a single formula:
It takes the total sum of the squared drawdowns (H199) and divides it by the number of months in the backtesting period (E3) and, lastly, takes the square root of the average by raising the quotient to the one-half power. So, the Ulcer Index of the Est. Rev. Up 5% screen is 16.04%.
Ulcer Performance Index (UPI)
Once we have the Ulcer Index, we can use it to calculate the amount of excess return a portfolio or investment generates for each unit of downside risk. The Ulcer Performance Index, or Martin ratio, is similar to the Sharpe ratio, which measures the amount of excess return an investment generates for each unit of total risk, as measured by standard deviation.
The formula for the UPI is:
- Return is the annualized return for the portfolio or investment;
- RiskFreeReturn is the annualized return for the risk-free asset, typically T-bills; and
- Ulcer Index is the Ulcer Index value for the investment or portfolio over the same period used to calculate the annualized return.
We begin by annualizing the cumulative price gain return for the Est. Rev. Up 5% screen, which we do in cell E4 with the formula:
The formula converts the cumulative price gain in cell C199 to a percentage and annualizes it by raising it to the 15.33 root (184 months in the backtesting period, cell E3, divided by 12). Between January 1998 and April 2013, the Est. Rev. Up 5% screen has averaged an annual price gain return of 28.43%.
As a proxy for the risk-free return we use three-month T-bills. Between January 1998 and April 2013, T-bills have averaged an annual return of 2.53% (this data is taken from the monthly performance spreadsheet at the Stock Screens area of AAII.com).
So, in order to calculate the UPI for the Est. Rev. Up 5% screen, we deduct the annualized return for T-bills (E7) from the annualized return for the hypothetical portfolio (E4) and divide this by the screen’s Ulcer Index (E6), such that: (28.43% – 2.53%) ÷ 16.04% = 1.62 (cell E8). The formula we used in cell E8 is:
This means that the Est. Rev. Up 5% screen generated 1.62 units of return for every unit of downside risk, as measured by its Ulcer Index, between January 1998 and April 2013.
To say that the Est. Rev. Up 5% screen has an Ulcer Index of 16.04% or a Martin ratio of 1.62 is meaningless without putting the values in context. To do this, we calculated Ulcer Index values for each of the AAII stock screens, along with other statistics such as standard deviation and Sharpe ratio. Figure 2 lists the 10 screens of the 64 that AAII currently tracks that have the highest Martin ratio and provide rankings based on a number of risk and return data points.
Interpreting the Ranks
For anyone who tracks the AAII stock screens, the list in Figure 2 undoubtedly contains some names that turn up frequently when it comes to top-performing strategies. The Piotroski: High F-Score screen had the highest Martin ratio as of April 30, 2013, at 1.82. This means the screen generates the highest excess return per unit of downside risk. The screen combines several factors of financial strength into a single variable: the F-score. The screen ranks in the top third of AAII screens with its Ulcer Index, and it ranks at the top in terms of annualized return.
Recall that the Ulcer Index attempts to overcome one shortfall of standard deviation by ignoring upside volatility and focusing exclusively on downward volatility. The Piotroski: High F-Score screen ranks near the bottom of all AAII screens in terms of its price volatility, as measured by annual standard deviation. However, the screen’s strong overall price performance has allowed it to rank third in terms of the Sharpe ratio.
Remember, too, that the Ulcer Index measures the depth and duration of percentage drawdowns. Therefore, the greater the drawdown in value, and the longer it takes to recover to earlier highs, the higher the Ulcer Index. Since the index is the square root of the mean of the squared percentage drawdowns in value, the squaring effect penalizes large drawdowns proportionately more than small drawdowns. The Piotroski: High F-Score screen ranks in the bottom third of all AAII stock screens in terms of maximum drawdown. The methodology had a maximum peak-to-trough decline of 59% from July 2007 to February 2009. However, it only took the screen seven months from that trough to reach a new all-time high. So, while it had one of the largest percentage drawdowns, the Piotroski: High F-Score screen was able to rebound quickly, which led to its relatively low Ulcer Index value (and relatively high Martin ratio).
Sometimes, however, the data isn’t as complementary. The P/E Relative screen ranks third in terms of the Martin ratio, but ranks 20th in terms of the Sharpe ratio. The screen has the lowest Ulcer Index, aided by the fourth-smallest maximum drawdown of –36.1%. Using the Ulcer Index instead of standard deviation may lead you to make different risk conclusions for different investments.
The Ulcer Index is a useful risk measure, especially for long-only investors, as it focuses on downside volatility (specifically, drawdowns). The index hovers near zero when prices regularly record higher highs and advance. The index rises when prices move lower and extend from their recent high.
This tool is often used in conjunction with other risk measurements such as standard deviation, but as our examples show, you can sometimes get conflicting values. However, as a general rule, investors who are more risk-averse may decide to avoid investments or portfolios with high Ulcer Index values because, if these securities experience a severe decline, they are less likely to recover in the near future.
Using RANK Function in Excel
The RANK function in Excel returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. This is more efficient than sorting individual columns in a spreadsheet.
For example, Column AP in Figure 2 contains the rankings of each AAII stock screen based on its annualized price gain since inception. To calculate this data, we entered this formula in cell AP6 and copied it down to cell AP69:
E6 is the number whose rank we wish to find. In this case, the average annual price gain since inception for the Piotroski: High F-Score screen; and
$E$6:$E$69 is the array of numbers we are ranking cell E6 against.
If we wanted to rank the values in ascending order, we would have used this formula: =RANK(E6, $E$6:$E$69,1). But since we want to rank the screens where “#1” has the highest annualized price gain since inception, we omitted it. We used the ascending rank order in columns AR (annual standard deviation) and AS (Ulcer Index), as lower values are preferred for these data items.