Learning outcomes. After watching this video, you will be able to, explain how to identify the mean-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 a standard deviation of returns of 7%. And Y has an expected return of 20% and a 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, this free means no risk, and so it's standard deviation of returns is zero. And it's correlation coefficients with both the risky assets is zero. We have the original mean radius frontier for X and Y. That is a black curl 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 risks 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 its deeper 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 grey 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 to top left as part of being non-associated. How are 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 that 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 a 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 portfolios it does. Such that, the expected return of the portfolio equals w times the expected return of X plus 1 minus w times the expected return of Y. And the standard deviation of the portfolios return is the square root of w squared times the variance of excess returns plus 1 minus w the whole squared times the variance of Ys returns plus 2 times w times 1 minus w times the covariance between X and Ys returns. W here is the weight of X in the portfolio, and 1 minus w is the weight of Y in the portfolio. We can solve for the rates using Excel. Enter the expected return for X in cell C2. It's standard deviation of returns in cell D2. The expected return for Y in cell C3. And it's 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 portfolios expected return in cell C5. And the formula for it's 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. Under bi 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 rate 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% interest. 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.