The expected return of the portfolio can be calculated using Microsoft Excel if you know the expected rate of return of all investments in the portfolio. Using the total value of your portfolio, the value of each individual investment, and the corresponding yield, your total return can be computed simply.
You can also calculate the expected return of the portfolio outside of Excel using basic formulas.
Calculate the total expected return in Excel
First, enter the following data labels in cells A1: F1: the value of the portfolio, the name of the investment, Investment cost, investment returns, investment weight, and total yield.
In cell A2 enter the value of your portfolio. In column B, list the names of all the investments in your portfolio. In the column, enter the total current value of each of your respective investment. In column D, enter the expected rate of return of each investment.
In cell E2 enter the formula = (C2 / A2) to display the weight of the first investment. Enter the same formula in subsequent cells, to calculate the weight of the portfolio each investment, always dividing by the value in cell A2. In cell F2 and enter the formula = ([D2*E2] + [D3*E3]+ …) to provide total return.
In the above example, assume that the three investments, the government issued bonds, which carry an annual coupon rate of 3.5%, 4.6% and 7% respectively.
After marking all of your data in the first row, enter the total value of the portfolio in the amount of $100,000 in cell A2. Then enter the names of three of the investment in cells B2 through B4. In cells C2 to C4, enter a value of $45,000,$ 30,000, and $25,000, respectively. In cells D2 through D4, enter the relevant coupon rate as specified above.
Then in cells E2 in E4, enter the formula = (C2 / A2) = (C3 / A2) and = (C4 / A2) to provide investment weight 0.45, 0.3 and 0.25, respectively.
Finally, in cell F2 and enter the formula = ([D2*E2] + [D3*E3] + [D4*E4]) to find the annual expected rate of return of your portfolio. In this example, the expected yield is:
= ([0.45 * 0.035] + [0.3 * 0.046] + [0.25 * 0.07])
= 0.01575 + 0.0138 + 0.0175
= .04705, or 4.7%