From the course: Excel for Investment Professionals

Computing expected returns on a stock - Microsoft Excel Tutorial

From the course: Excel for Investment Professionals

Start my 1-month free trial

Computing expected returns on a stock

“

- [Instructor] Computing expected future returns is a key task but a difficult one. But investors can make this process a little bit easier using Excel. Let me show you what I mean. I'm in in the 03_01_Begin Excel file. Now, let's pretend you hold a single stock and you've gone through and analyzed the company's prospects, and you think there's five possible scenarios that could occur. Under scenario one, the firm loses 10 percent of its value over the next year. Under scenario two, the firm gains one percent in value. In scenario three, the firm gains 10 percent in value. Scenario four, the firm gains 20 percent. And scenario five, the firm gains 30 percent. Now these are just kind of made up numbers. Obviously, the rate of return would vary based on what's going on with the company itself. Some companies have really strong prospects, other companies don't. But we can go through and we can think about any stock on this basis. What could happen to the firm and what's the expected outcome if that does happen? Is the stock going to up or down and by how much? We can kind of ballpark it, and if our number is wrong, that's okay. It's probably in the right ballpark. From there, we have to determine the probability of any of these particular events. Now, in this case, I have assigned probabilities ranging from the most probable event, a 10 percent downturn in the stock, to the least probable event, a five percent chance of a 30 percent upside. The key here is we have to make sure that these probabilities all sum to one or 100 percent. Next, we're going to go through and figure out, based on these probabilities, what is our expected return? To do this we just multiply the rate of return in each scenario by the probability of that scenario. And we can do that for all five of our scenarios. Then, in order to figure out the expected return on the stock overall, we simply sum up these five different outcomes. So in this particular case, this stock has a total expected return, based on these scenarios, of 3.25 percent. The nice thing about doing this in Excel is that could go through and alter these. Let's pretend that the company comes out and reports better than expected earnings. Maybe that decreases the likelihood of that downside event and increases the likelihood of some of the upside events for the firm. Well now our expected total return has risen to 5.75 percent. So Excel lets us go through and do some pretty nice modeling when we're trying to think about what might happen to the firm and what's the expected returns overall. At this point you should be ready to start doing some basic scenario analysis for stocks that you hold.

Contents