Thursday, June 20, 2013

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,, 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.

No comments:

Post a Comment

Please send us comments here. Thank you.