How to Calculate Expected Return Using Excel

When considering a stock investment, it's important to do your homework. Among the things to consider, such as quality of management, earnings, business outlook and past financial performance, you need to set an expected return for your investment. While this doesn't necessarily guarantee that the stock will perform as expected, it sets a bar that helps you determine if an investment is worth holding.

The expected return is the average probability distribution of possible returns. Investors, even in the same stock, assign different expected returns based on individual assumptions about risk.

Open a new spreadsheet and label Cell A1 "Probabilities." These are the various probabilities you'll assign under different investment scenarios. Assign probabilities beginning in column B in cell B1. The number of probabilities you assign will correspond to subsequent columns. For example, if you assign two additional probabilities, you'll have to enter those in cells C1 and D1.

No matter how many probabilities you assign to your investment, they should add up to 100 percent. For example, if you assign four probabilities of 40 percent, 30 percent, 25 percent and 5 percent, these percentages should appear in cells B1 through E1.

Enter the label "Outcome" in cell A2 to reflect expected return outcome. Enter in cell B2 and subsequent cells the expected outcomes that correspond to the probabilities you assign to the investment. For example, if you're expecting the stock to have a 40-percent chance of generating a 20-percent return, enter 20 percent under the 40-percent column in cell B2.

Enter the expected returns for the other probabilities. For example, if you expect a 30-percent chance of a 15-percent return, enter 15 percent in cell C2 in the 30-percent column. To continue with the example, enter expected returns of 10 percent in Cell D2 and negative-10 percent in Cell E2.

Create another label in A3 called "Expected Return." Create a formula that multiplies B1 by B2 to derive the expected return under the different return outcomes in cell B3. You can do this in one of two ways: The first way is to type in the formula directly in cell B3 by entering "=B1*B2." The second method is to enter the equal sign and highlight cell B1, then enter the multiplication sign and highlight cell B2.

Continuing with the example, the result in cell B3 is 8 percent, or 40 percent multiplied by 20 percent. Drag the formula from cell B3 across to cells C3 through E3 to calculate the expected return for the other return scenarios.

Add the expected returns under the different outcomes to derive the total expected return for the investment. Continuing with the example, add cells B3 to E3 in cell F3. The result is an expected return of 14.5 percent.