The expected return of your portfolio can be calculated using Microsoft Excel if you know the expected return rates of all the investments in the portfolio. Using the total value of your portfolio, the value of each investment, and its respective return rate, your total expected return can be calculated.
You can also calculate the expected return of a portfolio outside of Excel by using a basic formula.
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.
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.
In the example above, assume that the three investments are government-issued bonds that carry annual coupon rates of 3.5%, 4.6%, and 7%, respectively.
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%
(Further Reading: Improve Your Investing With Excel)