Minimum Variance Portfolio (the risk return trade off with two risky assets portfolios)

[yordankasexcel]

So I've been trying to figure out how to display the minimum variance of a portfolio, but I don't know how to graph it using Solver (or any Excel tool).
I already added Solver and I have calculated the mean and standard deviation of the minimum variance portfolio in the end, but I also want to have a nice graphical display.

Also, how can one draw a tangent line from the risk free-rate (let's say 6%) to the opportunity set?

* The "s" abbreviation stands for standard deviation, I need to fix it.

Thanks a lot to anyone who is willing to share some advise.

[Brendan_Floyde]

https://www.youtube.com/watch?v=S5VYM0lMb2Y

This is a really good explanation of almost exactly what you are doing. Essentially you need to plot a graph of expected return against std deviation.

Post your workbook if you need more help.

[yordankasexcel]

The video is very informative, but it doesn't relate to what I'm trying to do. The video uses historical data and the weights of the stocks are fixed, whereas I don't have historical data and the weights of my stocks vary in relation to each other. Therefore, my "set objective" and the "by changing variable cells" criteria on Solver don't work as shown on the video.
I have all the data figured out and I can plot it by hand, but I wanted to know how can I enter it in Solver.

[Brendan_Floyde]

Can you post your file so we can have a play?

[yordankasexcel]

Here I have attached the file:

[yordankasexcel]

I have figured out the minimum variance portfolio (% in stocks, % in bonds, exp. return, std. dev., and the sharpe's ration), but I haven't figured out the tangency portfolio (% in bonds, % in stocks, Exp. return, Std. dev. and the Sharpe's ratio).

How can I create a covariance matrix ? Cov(rs,rb)=pQsQb ?

Bonds Stocks
Bonds ____ _____
Stocks____ _____

[Brendan_Floyde]

To use solver set B5 as =1 - a5

Then in solver obj min E5 by changing A5

this returns the values you've already listed.

[Brendan_Floyde]

http://economistatlarge.com/portfoli...cient-frontier

This any help? Pushing the limits of my corporate finance knowledge!

[yordankasexcel]

Yes, that's very helpful, however, I am still not sure how to set up the covariance matrix. I have 6 weights ( from 0% to 100% with 20% increments) for each asset, so the logical approach would be to set up a 6x6 matrix, however the answer from my textbook is 2x2.
Once I find out how to set up this Excel sheet right and have the time to finish it I'll post back on here the solution.

The right answer of the covariance matrix is:

_________Bonds______Stocks
Bonds____529.9______110.4
Stocks___110.4______1024.0

[Crooza]

The covariance martrix is as follows (for a 2 weighted portfolio)

rho(1)^2 ________________rho(1,2) x sig (1) x sig (2)
rho(1,2) x sig (1) x sig (2) __________________ rho(2)^2


if rho(1) = 23 (you've already worked this out in your spreadsheet then rho(1)^2 = 529
rho(2) = 32 (again you worked this out so rho(2)^2 = 1024
and the E(r) = 0.15 so
rho(1,2) x sig (1) x sig (2) = 0.15 x 23 x 32 = 110.4