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

Get FREE Advanced Excel Exercises with Solutions!

Portfolio management is a major activity that every corporate financials has to undergo. In portfolio management, the calculation of expected returns is essential for investors. The expected return is the amount of profit or loss an investor can anticipate receiving on an investment. An expected return is calculated by multiplying potential outcomes by the probabilities of them occurring and then totaling these results. 

In this Excel guide, 2 easy steps to calculate the expected return of a portfolio investment in Excel are explained in detail. First, we have to calculate the expected return for individual investment alternatives using SUMPODUCT function. Next, we have to use the individual expected returns of investment alternatives and their respective weights to calculate the expected return of our portfolio.

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

To find the expected return of each investment, we have to multiply the expected returns by their respective probabilities. Next, we have to sum up the products to find individual investments’ expected return. 

Below, a step-by-step guide is provided on how to calculate the expected return of individual investments in Excel using SUMPRODUCT function:

  1. Identify your required data on returns and probabilities.
    Here, we have rates of return in cells C6:C8, E6:E8, and G6:G8 and their probabilities in cells D6:D8, F6:F8, H6:H8.
  2. Select a cell to calculate expected return.
    Here, we selected cell C9.Calculate Expected Return in Excel
  3. Insert the following formula in the selected cell: =SUMPRODUCT(C6:C8,D6:D8)
    Calculate Expected Return in Excel using SUMPRODUCT function
  4. Use the Fill Handle or copy the formula to get other investments expected return.Calculate Expected Return in Excel

Now, you will get the expected returns of individual investments. But, to calculate portfolio expected return of these three stocks, we have to go further from this point.

Step 2: Calculate the Expected Return of A Portfolio

To calculate expected return of a portfolio in Excel, we need individual returns of the investments and their respective weight in the portfolio. Assume, for instance, we have 35% funds in Investment 1, 25% in Investment 2, and 40% in Investment 3.Weights of Investment in Portfolio Below, a simple guide is presented on how to calculate expected return of a portfolio in Excel:

  1. Collect data on investment return and their weight.
    Here, we have data on individual investment returns in cells D15:D17 and their weights in the portfolio in E15:E17 cells.Investment Returs and Their Weights
  2. Select a cell to calculate expected return of the portfolio.
    Here, we selected cell D18.Calculate Expected Return of A Portfolio
  3. Insert the following formula: =SUMPRODUCT(D15:D17,E15:E17)Using SUMPRODUCT Function to calculate expected return in Excel
  4. Press enter to get the expected return of the portfolio.

Now, we will get the expected return of a portfolio of three stocks.


Download Practice Workbook

You can download and practice this workbook.


Conclusion

So, we have shown you how to calculate expected returns in Excel using SUMPRODUCT function. We have shown 2 suitable steps for solving such problem. We hope you find the content of this article useful. If there are further queries or suggestions, make sure to mention them in the comment section.


Frequently Asked Questions

Can you calculate expected return in Excel using a probability distribution?

Ans: Yes, you can calculate the expected return in Excel. For that, you can use a probability distribution by using the formula.

=SUMPRODUCT(returns, probability) 

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

How do you calculate expected return in Excel for a mutual fund?

You can use the formula to calculate the expected return for a mutual fund in Excel

=AVERAGE(returns)

where “returns” is the range of historical returns for the mutual fund.

How do you calculate the expected return for a bond in Excel?

You can calculate the expected return for a bond in Excel. For that, you can use the following formula.

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

where “nper” is the number of periods, “pmt” is the periodic payment, “pv” is the present value, and “fv” is the future value. This will give you the expected return in percentage.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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