Risk-adjusted Returns In Action: The Excel Spreadsheet (PDF)

**Most clients wouldn't** question the superiority of a 10-percent return over a 5 percent one. Or would they? For some accounts, it's not so much where they end up, but how they got there. Reporting gross returns — even when they're not so gross — gives a narrow-angle snapshot of a portfolio's performance: You can tell how much you've won or lost, but not how much risk was taken to produce the result.

That's why risk-adjusted measures of return were developed. Many metrics to describe how much risk was taken to win a particular return have been devised over the years, some attracting more adherents than others. Each represents a stage in the evolution of risk management, and comes with its own set of advantages and drawbacks.

But how do you know which risk-adjusted return you should use? It can be a complicated business, but you can learn how to calculate the various different metrics in real time using Microsoft Excel. With these tools in hand, you won't have to rely upon stale, vendor-supplied data when you're evaluating investment options. You won't need expensive data feeds either. With Excel, you can perform sophisticated portfolio analysis using the historical prices from free online sources like Yahoo! (http://finance.yahoo.com).

### Treynor Ratio

The modern concept of risk-adjusted returns traces back to 1966 when Jack Treynor, then the editor of the *Financial Analysts Journal*, and a contributor to the development of the Capital Asset Pricing Model (CAPM), introduced his eponymous yardstick. Treynor's ratio may have since been eclipsed by other measures, but it's still used in Morningstar evaluations.

Two terms very familiar to financial advisors are needed to calculate a Treynor ratio: beta and excess return. The excess return employed by Treynor is simply the difference — plus or minus — between a particular portfolio's return, and that of a riskless investment.

Figure 1 (on page 96) features a three-year string of month-end mutual fund NAVs in Column B. Column G contains the contemporaneous yields for 13-week Treasury bills. You can find a proxy for T-bill yields on Yahoo! finance using the ticker symbol ^IRX. The average yield over the 36-month sampling period is computed in Excel as =AVERAGE(G3:G38) in Cell B43.

Beta, of course, is a measure of relative volatility, and can be found by comparing an investment's return variance with that of its benchmark. Again, using Excel, Column C lists the fund's monthly returns, while Column J contains those of the fund's benchmark index. The argument =COVAR(C4:C38, G4:G38)/VAR(G4:G38), renders the beta coefficient in Cell B45. Alternatively, a simple, one-term argument can be used to derive beta: =SLOPE(C4:C38, G4:G38).

The fund's compound annual growth rate is calculated in Cell B41.

To derive a Treynor ratio, a fund's return in excess of the risk-free rate is divided by its beta. To make the ratio consistent with Morningstar's presentation, use the expression =(B41-B43)/B44*100.

The higher the ratio, the better a portfolio's performance. A reading greater than 1.00 indicates more units of return produced by an investment than units of risk. A cautionary note: The Treynor ratio's reliance on beta implies an assumption that non-systematic risk can be mitigated by diversification. For that reason, the ratio has limited utility in evaluating non-diversified portfolios, or in comparisons of portfolios with different benchmarks.

### Sharpe Ratio

The Sharpe Ratio was introduced in 1966, not long after Treynor's ratio, and has since become one of the most commonly quoted measures of risk-adjusted return. Derived by Nobel Laureate William Sharpe, the ratio, like Treynor's, gauges portfolio gains per unit of risk, but uses an investment's standard deviation of returns instead of beta as the risk denominator. Using an investment's price volatility removes market influence from the risk evaluation, allowing comparisons of undiversified portfolios and those tracking different benchmarks.

To figure a portfolio's volatility, use Excel's STDEV function on the range of monthly returns in Column C. You'll need to annualize volatility to match it to the annualized return found in Cell B41. =STDEVP(C4:C38) gives you the standard deviation for the three-year survey period. To arrive at an annualized figure when using monthly data points, multiply the standard deviation by the square root of 12 (for the 12 months in a year). The Excel argument then becomes =STDEVP(C4:C38)*SQRT(12). If we were using daily data, the adjustment would have been the square root of 252 (since there are typically 252 trading days per year). For weekly data points, the square root of 52 is factored.

The Sharpe ratio is calculated like Treynor's: The “reward” — the excess return over the T-bill yield — is divided by the “risk”: in this instance, the fund's volatility. The Excel expression looks like this: =(B41-B43)/B42.

So, what's a good Sharpe ratio? Generally speaking, a ratio of 1.00 represents “good” compensation for risk, while a ratio of 2.00 earns a “very good” rating, and 3.00 or better is “outstanding.”

### Alpha

In 1968, Michael Jensen, a professor at the University of Rochester, published a risk measurement that grew to be even more popular than the Sharpe Ratio. Jensen's alpha measures an investment's over- or under-performance in relation to its expected return. Expected return is the beta-adjusted margin above or below the risk-free rate by which a portfolio beats, or lags, its benchmark.

To calculate alpha, start with the fund's annualized return. Subtract the Treasury bill yield, then multiply the result by the fund's beta. The result is then subtracted from the benchmark index's excess return. Alpha, again presented in the format favored by Morningstar, is computed in Cell B46 using the argument =((B41-B43)-B45*(B44-B43))*100. In regression terms, a simple alternative is =INTERCEPT(C4:C38, G4:G38).

Alpha, though expressed like a ratio in the Morningstar world, is actually a percentage. In our case, the manager has been able to wrest a 3.85 percent gain above the portfolio's expected annual return. Obviously, the higher, or more positive, the alpha reading, the greater the manager's skill in producing outsized returns and/or controlling risk.

Like Treynor's Ratio, alpha measures portfolio performance in relation to the market. Since the metric takes only systematic risk into account, portfolio comparisons are only fair if the investments have the same market exposure. An alpha comparison of an equity portfolio benchmarked to the S&P 500 against a bond fund vying to outperform the Lehman Aggregate Bond Index is, statistically speaking, nonsense.

### Metrics Can't Be Applied Universally

The measures we've examined aren't suitable in every instance. Illiquidity, and lack of transparency, can lull users of these ratios into a sense of undeserved security. As many investors painfully learned during the recent mortgage-backed security meltdown, an investment that isn't repriced frequently can appear much less volatile (less risky) than it really is. In addition, Treynor ratio, and Jensen's alpha in particular, don't lend themselves to the evaluation of hedge funds.

Another fundamental problem with these measures is that they're designed to track investments exhibiting normal- or symmetrical-return distributions. There are portfolios, however, that produce decidedly asymmetrical returns. Consider an option-writing portfolio as an example. If the portfolio manager writes deep out-of-the-money options, a string of modestly positive returns would likely be earned as premiums are collected, punctuated with occasional large drawdowns whenever shorted options move into the money. Reward-to-risk ratios are likely to run deceptively high for this portfolio in the premium collection cycle until a leveraged loss is sustained.

Autocorrelation — the tendency of an investment's return to correlate with itself over successive time intervals — can also tip a risk-adjusted return ratio in the “reward” side's favor. Highly positive autocorrelation is the ne plus ultra for momentum investors, as leading time-series trends are expected to match those of lagging series, but it skews the return distribution enough to cheapen the utility of these comparators.

For these ills, other metrics, such as maximum drawdown and the Calmar ratio are useful. The examination of hedge fund metrics will have to wait for another time, but we can address a fairly constant complaint about the Sharpe ratio.

Some critics say the Sharpe ratio's reliance on standard deviation as a measure of risk is misleading, claiming it counts upside volatility as equally deleterious as downside volatility.

Some investors view volatility as simply the uncertainty of returns. The all-inclusiveness of a Sharpe ratio appeals to them. Others welcome volatility of the upside kind. It was for the latter reason that the Sortino ratio was developed in 1994.

### Sortino Ratio

Instead of using standard deviation in the denominator, the Sortino ratio uses downside semi-variance, separating the “good” volatility from the “bad.” By penalizing only an investment's harmful volatility, the ratio expresses returns per unit of downside risk. Unlike the other metrics we've examined, the Sortino calculation also incorporates a user-defined threshold — a target — as a performance gauge. The target could be the risk-free rate used in the Sharpe calculation, or it could be some other benchmark, such as the return of a fund's peer group or a particular index. The Sortino ratio can thus be made to be goal-oriented in that it can gauge portfolio management relative to an objective the investor is trying to achieve. It's therefore possible to remove market — or systematic — risk from an evaluation.

By definition, Sortino ratios rely upon logarithmic or continuously compounded returns. We can use the Excel expression =LOG(B4/B3) to render our first monthly data point in Cell D4. The monthly Sortino value, using the monthly logarithmic return of the benchmark index as the target, can then be computed in Cell E4 as =IF(D4K4,(D4-(K4)^2,0).

Finally, the overall ratio is derived in Cell B50 as =(LN(B38/B3)-(B44)*36/12)*SUM(E4:E38)^(-0.5). A higher Sortino ratio indicates a lower risk of large losses. Sortino ratios are also useful as cross-checks on Sharpe ratio readings. The degree to which a fund's Sortino ratio exceeds its Sharpe ratio indicates the manager's ability to control risk. Conversely, if the Sortino ratio is lower than the Sharpe ratio, the portfolio may be riskier than the Sharpe ratio lets on.

### Information Ratio

Active portfolio management raises the cost of investing. To be justified, it must produce value in excess of that cost. Failing that, passive or index-based investing is a more cost-effective proposition.

The information ratio, introduced in 1995, assesses a manager's skill in deviating from a benchmark to produce excess returns. In its most stringent form, the ratio is the quotient of alpha divided by tracking error, and describes the value per unit of extra risk assumed, the manager's decisions have added to — or subtracted from — what the market would have delivered.

Cell B51, containing the Excel argument =(B47/B45)/100, generates a 1.02 information ratio for our portfolio. Higher information ratios indicate better active- management skills. Ratios in the neighborhood of 0.50 are generally considered “good,” ratios of 0.75 as “very good,” and ratios of 1.00 as “exceptional.”

Because the alpha numerator is derived using beta, the information ratio is market influenced. That said, information ratios are useful only when comparing funds benchmarked to the same index, and especially when the portfolios exhibit similar management styles and asset-allocation policies. You can also use an information ratio as a tie-breaker when assessing funds with near-identical alphas. The portfolio with the higher information ratio identifies a manager who is more skillful in betting on securities that deviated from the benchmark.

A portfolio with a lower information ratio may still be more suitable, depending upon an investor's aversion to risk. If a large tracking error — as calculated in Cell B45 — must be absorbed, the fund with the higher ratio may be reasonably shunned.

### Final Notes

Alpha, the Treynor ratio, and the information ratio, are all built upon the premises of the Capital Asset Pricing Model. If you have a problem with CAPM, you'll probably not be thrilled about using these measures.

The Sharpe ratio doesn't have this problem, though. When comparing undiversified portfolios or investments benchmarked to different market indexes, the Sharpe ratio is your best bet.

Still, you may believe that Sharpe's reliance on standard deviation unduly penalizes upside volatility. That makes you a potential user of the Sortino ratio.

As we've seen, these measures can be used synergistically. No one metric is ideal for all users or applications. Luckily, you have several at your disposal. Just keep in mind the words of British churchman and historian Thomas Fuller: “No garden is without its weeds.”

Risk-adjusted Returns In Action: The Excel Spreadsheet (PDF)