Attendees of our recent Web seminar, “How Much Alpha Is There In Beta?” learned the link between alpha and beta in today’s investment environment and the importance of obtaining accurate data to monitor portfolio risks and rewards.
Here, we present the Excel arguments necessary to calculate customized portfolio metrics that track risk exposure and performance.
The requisite for these calculations is a string of prices for an investment -- a stock, a fund, a bond or a portfolio -- and a benchmark, such as the S&P 500 or the Barclays U.S. Aggregate Bond Index. You can easily get historical prices from online resources such as Yahoo! Finance.
Let’s assume your investment’s data points are listed in Column B of your Excel worksheet, from Cell 2 to Cell 253, and your benchmark prices are displayed in the corresponding cells of Column D. Column C should be used to display the point-to-point changes, i.e., day-to-day, week-to-week or month-to-month, of your investment. The change, for example, from Cell 2 to Cell 3 can be calculated with the argument “=(B3-B2)/B2”. Copy that argument into each cell of Column C down to Cell 253. Repeat this procedure in Column E to determine the point-to-point changes in your benchmark’s value, i.e, “=(D3-D2)/D2” etc.
You can then calculate your investment’s period return in Cell 254 of Column B with the argument “=(B253-B2)/B2”. Repeat this for Column D. If you were using daily prices, you now have an annual return (assuming 252 trading days in a year). If you used another data frequency say, weekly or monthly prices, you’ll need to adjust your argument. If, for example, you used weekly data, your argument in Cell B254 would be “=((B253-B2)/B2)/(252/52)”.
Before you move on to calculating your metrics, you’ll need a yield for a risk-free investment over the period. Here, you use Treasury securities. You can obtain historical data from a source such as Yahoo! Finance or from the U.S. Treasury Web site (www.treasury.gov/resource-center/data-chart-center/interest-rates/). Let’s suppose you enter the yield, expressed as a percentage, in Cell F254.
Now, on to those calculations.
In an open cell (let’s say you choose Cell C254), you can write the argument to calculate the r2 coefficient. This metric represents the degree to which changes in the benchmark value explain those of the investment. Values will range from .00 to 1.00. At the extremes, a coefficient of 1.00 means the investment’s value moves in lockstep with the benchmark; an r2 of .00 indicates no correlation between the investment and its comparator.
Excel makes the r2 calculation easy. Simply write your argument as “=RSQ(C3:C253,E3:E253)”.
The higher the r2 coefficient, the greater confidence you’ll have in the other portfolio metrics. The idea is to find the benchmark with the “best fit” to keep the coefficient as high as possible.
Next, you’ll calculate beta (β). Beta compares the risk of investment to that of the market (“systematic risk”). Values can be positive or negative. A beta of 1.00 represents “market risk”. Values greater than 1.00 signify greater-than-market risk; negative beta coefficients indicate compensating risk, i.e., hedges.
Let’s use Cell C254 for our beta argument. You’ll use two Excel functions in your calculation -- variance (“VAR”) and covariance (“COVAR”). You don’t need to be a statistician to put this together. You’ll simply divide the covariance of the investment and the benchmark by the benchmark’s variance. In any open cell, write: “COVAR(C2:C253,E2:E253)/VAR(E2:E252)”.
Last, you’ll determine the alpha (α) coefficient. Alpha measures an investment’s return compared to that of the risk-adjusted benchmark. Positive alpha indicates an investment outperforms the market while a negative coefficient bespeaks a lagging investment. A positive alpha of .01 signifies outperformance of one percent. An opposite. i.e., negative, alpha ( -.01) indicates under-performance of one percent.
To calculate alpha, you’ll need to use your risk-free (Treasury) yield, along with the annualized returns for the investment and its benchmark as well as the investment’s beta. In any open cell, write your argument as: “=(B254-F254)-C254*(D254-F254)”
That’s it. You now have the means to calculate timely and customized risk metrics without relying upon outside vendors.
Next month, we’ll explore EXCEL arguments that identify the real cost of active management inside an investment.
