- 1). Estimate the returns an investment may generate in a best-case and worst-case scenario and the percent probability of each scenario occurring. A probability is a number between 0 and 1, and the probability of both scenarios must add up to 1. For example, determine that an investment has a 75 percent chance of returning 10 percent in a best-case scenario and a 25 percent chance of losing 2 percent in a worst case scenario.
- 2). Open an Excel worksheet and type "Best" in cell A1 and "Worst" in cell A2. Press "Enter" after typing in each cell.
- 3). Click in cell B1, type the percentage probability of your best-case scenario as a decimal and press "Enter." For example, click in cell B1, type "0.75" and press "Enter."
- 4). Click in cell B2, type the percentage probability of your worst-case scenario as a decimal and press "Enter." For example, click in cell B2, type "0.25" and press "Enter."
- 5). Click in cell C1, type the percentage return of your best-case scenario as a decimal and press "Enter." For example, click in cell C1, type "0.1" and press "Enter."
- 6). Click in cell C2, type the percentage return of your worst-case scenario as a decimal and press "Enter." For example, click in cell C2, type "-0.02" and press "Enter."
- 7). Click in cell D1, type "=B1*C1" and press "Enter." This tells Excel to multiply the value in cell B1 by the value in cell C1, which results in a weighted return based on the probability of that scenario. A weighted return is a return based on its amount of probability. A higher probability scenario will influence the expected return more. Excel shows "0.075" in cell D1.
- 8). Click in cell D2, type "=B2*C2" and press "Enter." Excel shows the weighted return of "-0.005" in cell D2.
- 9). Click in cell D3, type "=SUM(D1:D2)" and press "Enter" to calculate the expected return. This tells Excel to calculate the sum of the weighted returns in cells D1 and D2 to get the expected return. Excel shows an expected return of "0.07" in cell D3, which represents a 7 percent expected return on the investment based on the two possible scenarios.
SHARE
