Learning outcomes. After watching this video, you will be able to explain how to identify the main-variance efficient portfolio, calculate the Sharpe ratio for the mean-variance efficient portfolio, calculate the weights of the risky assets in the mean-variance efficient portfolio using Excel. Let's go back to our two risky assets X and Y. X has an expected return of 10% and the standard deviation of returns of 7%. And Y has an expected return of 20% and the standard deviation of returns of 10%. The correlation coefficient of their expected returns is 0.10. Now, we add a risk-free asset with a return of 5% to the mix. By definition, risk-free means no risk, and so its standard deviation of returns is zero and its correlation coefficient with both the risky assets are zero. We have the original mean-variance frontier for X and Y, that is the black curve that runs through X and Y. Where will the risk-free asset lie on this picture? It has zero risk, and so it will lie on the vertical axis. Now, how do we include the risk-free asset in our portfolio? We could form a portfolio with X and the risk-free asset. All combinations of these two lie on the red capital allocation line. Can we do better than this in terms of risk and returns? The answer is yes. Why? Remember, non-satiation. We can always get higher utility by moving towards the top left. So what gives us a higher utility? A portfolio of Y and the risk-free asset, the blue line, has a steeper capital allocation line, which means higher utility. Can we do even better than this? The answer is yes. Make the capital allocation line from the risk-free asset steeper until it is tangential to the mean-variance frontier between X and Y. This is the gray line from the risk-free asset through the point MVE which lies on the mean-variance frontier. MVE stands for the mean-variance efficient portfolio. Now, can we do even better than this? Remember, we want to keep moving towards the top left as part of being non-satiated. However, the answer now is no, as the capital allocation line would no longer pass through or touch any point on the mean-variance frontier. Increasing the slope any further would simply lead us to risk-return combinations that are infeasible given X, Y, and the risk-free asset. The mean-variance efficient portfolio is at the point of tangency of the capital allocation line from the risk-free asset to the mean-variance frontier. It provides the maximum reward-to-risk ratio, which is also called the Sharpe ratio. Now how do we calculate the weights of X and Y in the mean-variance efficient portfolio? We want to identify a portfolio that maximizes the Sharpe ratio. We want to calculate the pair of weights that maximizes the difference between the expected return of the portfolio and the risk-free rate of return divided by the standard deviation of the portfolio's returns such that the expected return of the portfolio equals w times the expected return of X plus one minus w times the expected return of Y. And the standard deviation of the portfolio's return is the square root of w-squared times the variance of X's returns plus one minus w the whole squared times the variance of Y's returns plus two times w times 1 minus w times the covariance between X and Y's returns. w here is the weight of X in the portfolio, and one minus w is the weight of Y in the portfolio. We can solve for the weights using Excel. Enter the expected return for X in cell C2, its standard deviation of returns in cell D2, the expected return for Y in cell C3, and its standard deviation of returns in cell D3. Also, enter the correlation coefficient between the returns in cell E3 and the risk-free return in cell C4. Enter the formula for the MVE portfolio's expected return in cell C5 and the formula for its standard deviation in cell D5. Enter the formula for the Sharpe ratio in cell C6. Now, open Solver in Excel. Set Objective should be linked to cell C6 since we want to maximize the Sharpe ratio. Click on the button to the left of Max. On the By-Changing Variable Cells, select cell B2 as we want to maximize the Sharpe ratio by changing the weight of X in the portfolio. Then click on Solve. You will be able to see that B2 now shows a value of 0.3607. To calculate the weight of Y in the portfolio, in cell B3, enter the formula "=1-B2". Cell B3 will now display a value of 0.6393. So the weight of X in the MVE portfolio is 36.07% and that of y is 63.93%. The Sharpe ratio of the MVE portfolio is 1.6%. That is, for every 1% in risk, the MVE portfolio gives an excess return of 1.60%. Next time, we will introduce a third risky asset Z and see how the investment opportunity set changes.
