Efficient Portfolio Frontier explained: Solver (Excel)

How to optimise your portfolio if you have more than two stocks? Here, the numerical optimisation in Excel Solver can help a lot. Today we are considering optimal diversification for a portfolio of five stocks and, as a bonus, go through some important concepts such as expected returns and CAPM.
Don't forget to subscribe to NEDL and give this video a thumbs up if you want more videos in Finance!
Please consider supporting NEDL on Patreon: / nedleducation

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7 Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@surangasa]

Can you mention from where do you get your price data downloaded? Thank you.

[@norwayusasummit5357]

I will support. One more question, when clicking on the link for the materials its says that I don't have access and I'm not able to download anything.

[@NEDLeducation]

@@norwayusasummit5357 Could you drop me an email to [e-mail] and tell what spreadsheets you're interested in?

[@sjsphotog]

@@surangasa Yahoo Finance Historical Data is a great place. Just be sure to use the ADJUSTED CLOSE price and not just the CLOSE price

[@user-ub4ey1sj9j]

Good afternoon! I faced the problem of completing this case at the University. I want to say a huge thank you as I've really understood this topic only thanks to you!

[@NEDLeducation]

Анна, спасибо! Рад, что видео пригодилось :)

[@SuperTsogo]

incredible. this guy should be a professor.

[@jaycom9373]

amazing video ! Great explanations, thanks a lot !!

[@paultennis9414]

Very interesting and very useful. Thank you.

[@qiguosun129]

Great lecture, thanks!

[@igormonteiro2289]

Great video!

[@AlexanderKonig2577]

Hi! great and very good videos on such interesting topics. Quick and no-nonse. CONGRATS!!! A quick tip: For you to fix rows or columns (you do it a lot in your formulas) is to place the cursor in the col/row in the formula bar and just pulse F4. The first time is both, col and rows, the second just col and a third click on F4 only the rows. I hope it helps.

[@NEDLeducation]

Hi Alexander, and many thanks for such kind words! A pretty funny story about F4 - back at undergrad I used to have a laptop which had F4 as a hotkey that closed the current window (without prompting a chance to save). You imagine how many spreadsheets I lost this way! :) This made me quite psychologically averse to using F4, but I am over this now and am using quite a bit of F4 in the more recent videos. Thanks for noticing! :)

[@AlexanderKonig2577]

@@NEDLeducation fair enough!!! a question, what method do you use in order to speak as confident as you do. Do you memorize the whole content of each video, do you read? amazing how fast every video is. Congrats!!!

[@aliassaid6398]

I just started studying about asset allocation and your tutorial has provided me with very useful insights. Keep up the great work!

[@sjsphotog]

fantastic video. Ive seen alot of others do their own Efficient Frontier Portfolio Optimization but NONE of them show that last step to adjust for the EXPECTED return to get a more realistic result instead of the optimal result as if your data has a bunch if UP years and less DOWN years then it will be skewed alot by that data. Which spreadsheet on your Good drive is this one specifically named as there are alot of them to try and find it. Thanks

[@aarthikannadoss1229]

Thanks for the uploading such videos. could u pls clarify when we are calculating for the covariance matrix why should we not multiply the formula with *sqrt(252)

[@ahmedsuleiman7027]

Thanks

[@gonzalonogues802]

Great video!! Wouldn't be possible to use instead of CAPM to calculate the expected return, a factor model like the Five Factor Fama French model or another more robust model incorporating Alpha? I think that might be another very interest video combining other financial concepts. Thanks!

[@chopper081]

Great video, I was wondering were beta comes into play here if you want to beta hedge the portfolio? I am doing an assignment for college were I have to construct a variance covariance matrix and then an efficient frontier. I wanted to construct an equity Long short portfolio using 5 longs and 5 shorts but as spread trades, for example AAPL/IBM being one, so 5 different spreads.. I was told to do this all I need to do is change the weights on the portfolio from positive to negative but that doesn't take into account the spreads, only the individual stocks. Can you advise me?

[@NEDLeducation]

Hi, and many thanks for the question! It seems that here the logic of efficient portfolio frontier weight optimisation is slightly conflated with statistical arbitrage/pair trading/hedging. I have got a video solely dedicated to pair trading (like AAPL/IBM), check this out if you are interested: kzbin.info/www/bejne/pZWuiaawl7R6ipY. Returning to EPF, however, you can interpret some EPF results as implicit hedging, for example, if two assets are almost perfectly correlated, but have different expected returns, the model will tend to long the one with the higher return and short the one with the lower return to obtain a low-risk profit. You can implement this logic deliberately within the EPF framework by including 5 pairs of stocks with very high (close to 1) pair-wise correlation, with the long stock in the pair having higher expected return than the short stock (however, this is quite an unconventional use of EPF). Hope it helps!

[@norwayusasummit5357]

Do you have time for a Teams meeting at your convenience by any chance? I'd be interested in discussing Arbitrage and the calculations that cover as many instruments and prices (including spreads and derivatives as well as fees) to maximize and be alerted for those opportunities if we have the API's and trading houses/banks in place with execution possibilities from a server/cloud based solution.

[@i.preck22]

Thanks for the clear explanation. Using the CAPM to calculate the estimate of expected return does not imply you change something on the calculation of the risk? since you used the historical returns.

[@NEDLeducation]

Hi, and glad you liked the video! As for your question, no, you can quite reasonably estimate the EPF with CAPM expected returns and historical covariance matrix. The EPF model is quite flexible in accommodating various assumptions: for example, you could use historical returns and historical risks, or expected returns and historical risks, or even plug in option-implied volatilities if you wish to do so. Hope it helps!

[@OMT980]

These videos are simply amazing! Once quick question...if you were considering 3 fixed income funds for a portfolio, could you use the CAPM based on the market risk premium derived from the Bloomberg Aggregate to get the respective funds CAPM derived expected return, or would you still use an estimate of equity markets like the S&P 500 or the MSCI ACWI (for global)? Thanks again for the excellent videos!

[@NEDLeducation]

Hi, and glad you are enjoying the videos! Yes, you can use CAPM this way to derive fund expected returns.

[@tutranangcam5972]

thank you for the video. But how can I calculate monthly return and risk for 3 stocks in 5 years? It could be =PRODUCT(xxxx)^(1/5)-1 for return and =STDEV.S(xxxx)*SQRT(12) for risk? I hope you could answer my question

[@NEDLeducation]

Hi, and thanks for the comment! As for your question, the formulae you present would calculate annualised return and risk. To get monthly return and risk, use =PRODUCT(xxxx)^(1/60)-1 for return and =STDEV.S(xxxx) for risk. Hope it helps!

[@norwayusasummit5357]

Curious, is it possible to get the excel spread sheet that you are using for these calculations? If so, please let us know where it could be downloaded (willing to pay). What you are doing is excellent in my opinion!

[@NEDLeducation]

Hi, all spreadsheets are available for free on our Google drive, please check the pinned comment for the link. Hope it helps!

[@salardelavarqashqai]

Hi. Please have an example about Modelling of portfolio optimization with mixing technical and fundamental datas. God bless you

[@Goragoch]

I have some question. I’ve tried to calculate and find that my Beta is minus therefore expected return is also minus. What does it mean by this?

[@NEDLeducation]

Hi, and thanks for the question! In case of negative beta, you can use the absolute value of the beta to derive the expected return. Alternatively, you could estimate the beta on a higher frequency and/or larger sample, as sometimes negative betas result from small samples or low data frequency. Hope it helps!

[@furkaansen]

Good afternoon sir, why didn't we find the covariance value directly through data analysis tools? The value I found there is different, am I doing a mistake?

[@furkaansen]

By the way, thank you very much for your valuable information.

[@ludogeris8820]

Very interesting , but what about dividends, shouldn't they be added to the historic return ?

[@NEDLeducation]

Hi Ludo, and thanks for the question! To take into account dividends, the same analysis can be performed on total return indices or, as they are called in Yahoo Finance, adjusted closes.

[@user-cz5yw2ez4v]

Ok, but if the period less than 10 Years, Say 4 year, how to calculate Risk free rate?

[@NEDLeducation]

Привет, Валентин, и спасибо за вопрос! Безрисковая ставка обычно определяется как доходность к погашению релевантной (по стране риска и валюте) государственной облигации с срочностью, наиболее близкой к горизонту инвестирования портфеля. Если это четырехлетний портфель, то есть смысл взять либо трехлетнюю, либо пятилетнюю ставку по гособлигациям.

[@carlos.eesperanzate2792]

how do i calculate the monthly return in 1 year

[@carlos.eesperanzate2792]

=product(1+xxx)^(365/30)-1 is this correct i used 365 days instead 1 to 1 year in order to get the monthly return

[@NEDLeducation]

Hi Carlos, yes, this is correct.

[@qiguosun129]

Really great courses! I would say it maybe more friendly for begingers using Python languages

[@NEDLeducation]

Hi Qiguo, and glad you are enjoying the channel! I have got a Python playlist as well check it out if you are interested: kzbin.info/www/bejne/e3LSnGOhdq2hebc

[@qiguosun129]

@@NEDLeducation Thanks for the reply, I found the python playlist in the channel which is fabulous!