How Do I Calculate the Expected Return of My Portfolio in Excel?

If your expected return on the individual investments in your portfolio is known or can be anticipated, you can calculate the portfolio’s overall rate of return using Microsoft Excel.

If you don’t use Excel, you can use a basic formula to calculate the expected return of the portfolio.

Calculating Total Expected Return in Excel

First, enter the following data labels into cells A1 through F1: Portfolio Value, Investment Name, Investment Value, Investment Return Rate, Investment Weight, and Total Expected Return.

Key Takeaways

• Enter the current value and expected rate of return for each investment.
• Indicate the weight of each investment.
• Calculate the overall portfolio rate of return.

In cell A2, enter the value of your portfolio. In column B, list the names of each investment in your portfolio. In column C, enter the total current value of each of your respective investments. In column D, enter the expected return rates of each investment.

In cell E2, enter the formula = (C2 / A2) to render the weight of the first investment. Enter this same formula in subsequent cells to calculate the portfolio weight of each investment, always dividing by the value in cell A2. In cell F2, enter the formula = ([D2*E2] + [D3*E3] + …) to render the total expected return.

Example

In the example above, assume that the three investments total $100,000 and are government-issued bonds that carry annual coupon rates of 3.5%, 4.6%, and 7%.

After labeling all your data in the first row, enter the total portfolio value of $100,000 into cell A2. Then, enter the names of the three investments in cells B2 through B4. In cells C2 through C4, enter the values $45,000, $30,000, and $25,000, respectively. In cells D2 through D4, enter the respective coupon rates referenced above.

Next, in cells E2 through E4, enter the formulas = (C2 / A2), = (C3 / A2) and = (C4 / A2) to render the investment weights of 0.45, 0.3, and 0.25, respectively.

Finally, in cell F2, enter the formula = ([D2*E2] + [D3*E3] + [D4*E4]) to find the annual expected return of your portfolio. In this example, the expected return is:

= ([0.45 * 0.035] + [0.3 * 0.046] + [0.25 * 0.07])
= 0.01575 + 0.0138 + 0.0175
= .04705, or 4.7%

Why Calculate Expected Return?

In the example above, expected return is a predictable figure. Most bonds by definition have a predictable rate of return.

For many other investments, the expected rate of return is a long-term weighted average of historical price data. As the standard disclosure says, past performance is no guarantee of future results.

It’s an imperfect reference point, but it’s the only one you get for stocks.

Calculating the expected rate of return on your investments, and your portfolio as a whole, at least gives you numbers to use in comparing various options for investing and considering buying and selling decisions.

Read the original article on Investopedia.