Portfolio returns versus risk, a spreadsheet model to calculate portfolio beta

The punch line: Weigh your portfolio's performance, that is your returns, against the risk that you are taking.

---

Beta is not an ideal measure of risk but when looking at how your portfolio fluctuates from day to day it isn't bad. It certainly measures how much your stock moves relative to the market -- and you can use this fact to gauge what the market thinks of your stock.

---

This is especially important if you have an aggressive portfolio and the market is weak. You don't want to panic and sell foolishly. For example, suppose your aggressive portfolio's beta is 1.5. Suppose the market falls seven percent. (It can be a daily fall or a multi-period fall.) Then your portfolio should be expected to fall 1.5 * 7 or 10.5 percent. Now, if during a market fall, your portfolio falls "only" (say) 5 percent, that's not bad. You are outperforming the market relative to the risk that you are taking. If, on the other hand, your portfolio falls (say) 15 percent, that's bad. You are underperforming the market relative to the risk that you are taking.

You might think that a fall of 5 percent is bad. You lost money. And in that sense it is. But you cannot be expected to never lose money and always earn money! What you are hoping for instead is that you are more than compensated for the risk that you are taking. What you are hoping for is that the smaller fall in your portfolio value relative to the market during downturns implies that you've got good individual stories. Holders in your stocks believe in these stories and are not willing to sell during market downturns. Over the longer term, these better-than-expected stories should lead to better-than-average returns.

---

Here's a spreadsheet that shows how to calculate your portfolio's beta using Excel:

The spreadsheet is straightforward. It uses the result from here, http://www.neocadence.com/2013/06/calculating-beta-of-portfolio.html, that is, the beta of a portfolio is the sum of its weighted betas.

Column C lists your positions.
Column D lists how much you have in dollars in each position.
Cell D24 is the total value of your portfolio, the sum of the individual values.
Column E are the percentages, or weights, equal to the values in Column D divided by D24.
Column G lists the betas. You can get these from a site such as Yahoo! Finance.
Column H are the weighted betas, equal to Column E times Column G.
Cell H24 is your portfolio's beta, the sum of the weighted betas.

(The beta for cash is zero. You can include mutual funds and ETFs here as well.)

---

If your portfolio falls less than what is expected, you've got individual stories that are trumping the market's weakness. That's good -- you've got so-called positive alpha. If your portfolio falls more than what is expected, you've got individual stories that are playing out worse than the market's weakness. That's bad -- you've got so-called negative alpha.

Calculating the beta of a portfolio

The punch line: The beta of a portfolio is the sum of its weighted betas.

---

It is not difficult to see why.

Beta, as originally defined by I believe Sharpe sometime around 1962, is the slope of a linear regression line when the market's excess return is plotted on the x-axis and the stock's excess return is plotted on the y-axis.*

---

Accordingly, per the formula for the slope of a linear regression line:

\[ \beta=\frac{Cov\left(r,R\right)}{Var(R)} \]

Here, Cov stands for covariance and Var for variance. The lower-case r is for the stock and the upper-case R is for the market.

---

For a portfolio of two stocks, A and B, because the return of a portfolio is the sum of its weighted returns, the formula becomes:

\[ \beta=\frac{Cov\left(w_{a}r_{a}+w_{b}r_{b},R\right)}{Var(R)} \]

Here the w's represent the weights of each stock in the portfolio. For example, if a portfolio is 70% IBM and 30% JNJ, the weights are 0.7 for IBM and 0.3 for JNJ.

---

Conveniently, because the covariance function is linear in its arguments, in particular its first argument:

\[ \beta=\frac{w_{a}Cov\left(r_{a},R\right)+w_{b}Cov\left(r_{b},R\right)}{Var(R)} \]

Or:

\[ \beta=\frac{w_{a}Cov\left(r_{a},R\right)}{Var(R)}+\frac{w_{b}Cov\left(r_{b},R\right)}{Var(R)} \]


Or:

\[
\beta=w_{a}\beta_{a}+w_{b}\beta_{b}

\]

---

In other words, the beta of a portfolio of two stocks is the sum of its weighted betas. The result extends to three or more stocks and, indeed, to not just stocks but to any assets -- as long as the definition of beta makes sense.

---

To see how this works in real life, suppose you own a portfolio, 70% of which is in IBM, with a beta of 0.7, and 30% of which is in JNJ, with a beta of 0.5. The weighted beta for IBM is 0.7 * 0.7 or 0.49. The weighted beta for JNJ is 0.3 * 0.5 or 0.15. The beta for the portfolio is the sum of the weighted betas, that is, 0.49 + 0.15, or 0.64. Piece of cake, right?

---

* Excess return is return over and above the return of a risk-free asset. In other words, excess return of an asset is the return of the asset minus the return of a risk-free asset. If a stock's return is 5% and a risk-free asset's return is 1%, the stock's excess return is 4%.