How to Calculate Expected Return in Excel (2 Easy Steps)

Dataset Overview

Portfolio management is a crucial activity for corporate finance, and understanding expected returns is essential for investors. The expected return represents the anticipated profit or loss an investor can expect from an investment. It is calculated by multiplying potential outcomes by their respective probabilities and then summing up these results.

The following image presents our sample data and our calculation process of portfolio expected return:

In our above sample data, we have some investments (Investment 1, Investment 2 and Investment 3), their returns on three different market or economic conditions, and the probabilities of those returns on different market conditions.

Step 1 – Calculate the Expected Return of Individual Investments

Identify Your Data:

• Gather the rates of return for each investment. These values should be in separate cells. For example:
  • Returns for Investment 1: C6:C8
  • Returns for Investment 2: E6:E8
  • Returns for Investment 3: G6:G8

• Also, collect the probabilities associated with each return. These probabilities should be in separate cells as well:
  • Probabilities for Investment 1: D6:D8
  • Probabilities for Investment 2: F6:F8
  • Probabilities for Investment 3: H6:H8

Calculate Expected Return for Each Investment:

• Select a cell where you want to calculate the expected return (e.g., C9).

• Enter the following formula:

=SUMPRODUCT(C6:C8,D6:D8)

• Drag the formula down or use the Fill Handle to calculate the expected returns for other investments.

Step 2 – Calculate the Expected Return of the Portfolio

Collect Data on Investment Returns and Weights:

• Gather the individual investment returns (e.g., D15:D17) and their corresponding weights in the portfolio (e.g., E15:E17).

Calculate Portfolio Expected Return:

• Select a cell where you want to calculate the portfolio’s expected return (e.g., D18).

• Enter the following formula:

 =SUMPRODUCT(D15:D17,E15:E17)

• Press Enter to get the expected return of the portfolio.

Now you have the expected return for your portfolio of three stocks.

Download Practice Workbook

You can download the practice workbook from here:

Frequently Asked Questions

1. Calculating Expected Return Using a Probability Distribution:

• • Yes, you can calculate the expected return in Excel using a probability distribution. To do so, follow these steps:
    • Enter the formula:

      =SUMPRODUCT(returns, probability)
      

      Replace “returns” with the range of possible returns and “probability” with the range of probabilities associated with each return.

2. Calculating Expected Return for a Mutual Fund:

• • To calculate the expected return for a mutual fund in Excel, enter the following formula:

    =AVERAGE(returns)
    

    Replace “returns” with the range of historical returns for the mutual fund.

3. Calculating Expected Return for a Bond:

• • If you want to calculate the expected return for a bond in Excel, enter this formula:

    =RATE(nper, pmt, pv, fv) * nper
    

    Here:

    • “nper” represents the number of periods.
    • “pmt” is the periodic payment.
    • “pv” is the present value.
    • “fv” is the future value.
    • The result will give you the expected return as a percentage.

<< Go Back to ROI Calculation in Excel | Excel for Finance | Learn Excel