Follow these steps to calculate a stock’s expected rate of return in Excel:
1. In the first row, enter column labels:
• A1: Investment
• B1:Gain A
• C1: Probability of Gain A
• D1: Gain B
• E1:Probability of Gain B
• F1: Expected Rate of Return
2. In the second row, enter your investment name in B2, followed by its potential gains and probability of each gain in columns C2 – E2*
• Note that the probabilities in C2 and E2 must add up to 100%
3. In F2, enter the formula =B2*C2+D2*E2
4. Press enter, and your expected rate of return should now be in F2
If you’re working with more than two probabilities, simply extend your columns to include Gain C, Probability of Gain C, Gain D, Probability of Gain D, etc.
If there’s a possibility for loss, that would be negative gain, represented as a negative number in cells B2 and/or D2.