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

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.

In this Excel guide, I’ll explain the two easy steps to calculate the expected return of a portfolio investment in detail:

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

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).

Calculate Expected Return in Excel

  • Use the following formula:

=SUMPRODUCT(C6:C8,D6:D8)
Calculate Expected Return in Excel using SUMPRODUCT function

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

Calculate Expected Return in Excel


Step 2: Calculate the Expected Return of the Portfolio

Weights of Investment in 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).

Investment Returs and Their Weights

Calculate Portfolio Expected Return:

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

Calculate Expected Return of A Portfolio

  • Use the following formula:

 =SUMPRODUCT(D15:D17,E15:E17)

Using SUMPRODUCT Function to calculate expected return in Excel

  • 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:
      • Use 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, use 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, use 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

Get FREE Advanced Excel Exercises with Solutions!
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo