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.
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:
- 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. - Select a cell to calculate expected return.
Here, we selected cell C9. - Insert the following formula in the selected cell:
=SUMPRODUCT(C6:C8,D6:D8)
- Use the Fill Handle or copy the formula to get other investments expected return.
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. Below, a simple guide is presented on how to calculate expected return of a portfolio in Excel:
- 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. - Select a cell to calculate expected return of the portfolio.
Here, we selected cell D18. - Insert the following formula:
=SUMPRODUCT(D15:D17,E15:E17)
- 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