Portfolio Optimization in Excel Using Solver

This video shows how to use Solver in Excel to generate optimal portfolios (mean - variance optimization). It also shows how to utilize matrix notation to make your life easier.

[@yashjain8200]

You're the best man. Just needed the exact thing for my assignment :)))))))

[@user-yj6jv8ef1x]

Thank you! That was really helpful!

[@kamranashraf3921]

Wonderful Explanation ♥️

[@arrowb3408]

LOLOLOLLL...great to see this pro management sheet which enables me to understand from here as well as the others.
The key point here is that I need to scoop the historical data before constructing this pro style sheet. Man.
Still, got it and well demonstrate here 🙂

[@asganawayaway]

Great video! I'll use this for my thesis on futures contract optimization :)

[@eoeo1382]

thank you, your explanation really helped me

[@fatinnoor2422]

Thank you for the Video, Ric.

[@yuxuanzhang5366]

Thank you for the video! It is really informative and helpful!

[@connorg2447]

In order for solver to output negative weights the box that says “Make unconstrained variables non-negative” needs to be unchecked

[@sailedship6530]

Thanks for the great share . Would save users time by explaining the basics early on. The µ divided by σ in first table D col is difficult to tell at first so one can follow along. was wondering what that 0.23 for as you went over the first two cols but not that one. It took me up to 5:02 to figure out it's the ratio of weighted average. Hope it helps someone...

[@carloscosta6400]

Hi, any chance you could go on and talk about how we get the efficient frontier and how could we graph these analysis in excel?
Much appreciated!

[@antsos]

Excuse me but where did you get the 7% in cell J5? Also, you use the valu"information ratio" but not the sharpe ratio what I understand from the video, can you please explain why?

[@tuongmy3796]

can u help me graph efficient frontier constructed from the index model
and the full covariance matrix ?

[@fatinnoor2422]

It would be really helpful if you could share the excel sheet online 😀

[@allykolesnik5222]

How would I go about using solver to find optimal weights for a portfolio with a specific return, say 2%?

[@ricthomas6436]

In that case, you would open solver, at the top you want to MINIMIZE volatility, and then one of the constraints would be that the solved return would equal 2 percent. This is similar to the video where I am MAXIMIZING return, and constraining volatility to be a fixed level (like 6 percent).

[@salamanlibya2006]

Transposed ? Please explain why we transpose. Thank you.

[@ricthomas6436]

When doing matrix math, you have to have everything expressed in the right order. So a row vector is 1 row by N elements. It can be multiplied by a matrix which is N by N because the inner dimensions (N) are the same. When starting with an N by N matrix, we now have to change the vector (1 by N) into a column vector (which is N by 1). Now you can multiply the N by N matrix by the N by 1 vector since the inner dimensions (N) are the same. I know this is a bit much but it is just following the rules of matrix multiplication - making sure everything is in the right order. I hope this helps.

[@salamanlibya2006]

@@ricthomas6436 thanks Ric.

[@zahidislam9829]

can u giv the file?

[@HelloThere-lo3qi]

i dont understand why my return is only 1% and even if I use solver, absolutely nothing change, this is depressing :D

[@ricthomas6436]

Hi Sorry to hear this. A low return can happen if you are constraining volatility to be a low number. If you constrain it to be too low of a number, then it can only buy low vol stocks (it wont buy any Tesla in this example). Often, some mistakes are made if you are referencing different columns in solver. So, if you want to maximize cell H14 in this example, everything in solver needs to reference cells in column H. Sorry, it’s hard for me to know what’s gong on without more information.

[@HelloThere-lo3qi]

@@ricthomas6436 hiii thanks a lot for the reply, cant belive you still check your vids XD, i will try the refferencing part you advice, hope it works!

[@arrowb3408]

LOLOLOLLL...great to see this pro management sheet which enables me to understand from here as well as the others.
The key point here is that I need to scoop the historical data before constructing this pro style sheet. Man.
Still, got it and well demonstrate here 🙂

[@tuongmy3796]

can u help me graph efficient frontier constructed from the index model
and the full covariance matrix ?