An investment's expected return is an estimate of a return it may generate. You can calculate an investment's expected return using Microsoft Excel based on possible scenarios that you predict may occur as a result of investing. Each scenario can have a different likelihood of occurring and each can result in a different return on your investment. Your investment's overall expected return is a single percentage number that is influenced by each scenario you predict. You can compare the investment's expected return to your investment criteria to help you make an investment decision.
Estimate the returns an investment may generate in a best-case and worst-case scenario and the per cent 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 per cent chance of returning 10 per cent in a best-case scenario and a 25 per cent chance of losing 2 per cent in a worst case scenario.
Open an Excel worksheet and type "Best" in cell A1 and "Worst" in cell A2. Press "Enter" after typing in each cell.
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."
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."
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."
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."
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.
Click in cell D2, type "=B2*C2" and press "Enter." Excel shows the weighted return of "-0.005" in cell D2.
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 per cent expected return on the investment based on the two possible scenarios.
You can add as many scenarios as you want as long as the total probability of the scenarios equals 1.