投资组合管理是每个企业财务部门都必须进行的一项重要活动。在投资组合管理中，计算预期回报对投资者至关重要。预期回报是投资者可以预期从投资中获得的利润或亏损金额。预期回报是通过将潜在结果乘以它们发生的概率，然后将这些结果相加来计算的。
在本Excel指南中，详细解释了在Excel中计算投资组合投资预期回报的两个简单步骤。首先，我们必须使用SUMPRODUCT函数计算个别投资选择的预期回报。接下来，我们必须使用投资选择的个别预期回报及其相应的权重来计算我们投资组合的预期回报。
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.
在我们的上述样本数据中，我们有一些投资（投资1、投资2和投资3），它们在三种不同的市场或经济条件下的回报，以及这些回报在不同市场条件下发生的概率。
步骤1：计算个别投资的预期回报
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:
下面，提供了一个逐步指南，说明如何在Excel中使用SUMPRODUCT函数计算个别投资的预期回报：确定您的回报和概率所需的数据。
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.
在这里，我们在单元格C6:C8、E6:E8和G6:G8中有回报率，在单元格D6:D8、F6:F8和H6:H8有它们的概率。
Select a cell to calculate expected return.Here, we selected cell C9.
选择一个单元格来计算预期回报。在这里，我们选择了单元格C9。在Excel中计算预期回报
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.
使用SUMPRODUCT函数在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.
使用填充句柄或复制公式以获取其他投资的预期回报。在Excel中计算预期回报 现在，您将获得个别投资的预期回报。但是，要计算这三个股票的投资组合预期回报，我们必须从这一点继续前进
步骤2：计算投资组合的预期回报
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.
要在Excel中计算投资组合的预期回报，我们需要投资的个别回报及其在投资组合中的相应权重。假设，例如，我们在投资1中有35%的资金，在投资2中有25%，在投资3中有40%。投资在投资组合中的权重如下所示：
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.
在这里，我们在单元格D15:D17有个别投资回报的数据，在单元格E15:E17有它们在投资组合中的权重。投资回报及其权重
Select a cell to calculate expected return of the portfolio.Here, we selected cell D18.
选择一个单元格来计算投资组合的预期回报。在这里，我们选择了单元格D18。计算投资组合的预期回报
Insert the following formula:
使用SUMPRODUCT函数计算Excel中的预期回报
=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.