Portfolio Optimizer in Excel

This video demonstrates how to make a simple portfolio optimizer in Excel. The goal of the optimization exercise is to choose portfolio weights that maximize the portfolio's Sharpe ratio and is part of the module on portfolio dynamics in my Investments course at Oregon State University.

[@AnthonyVlakancic]

This was one of the best videos I have ever watched for this subject.

[@senemsevercan3007]

Thank you. Great explanation. It was very helpful.

[@richardgordon]

Brilliant! Thank you!! Very clearly explained. 😎

[@KhoaTran-le1ot]

this is very helpful, thank u so muchhh!!

[@raulzevallos3399]

Thanks a lot.

[@aqibshafi6438]

You saved me 15% of my grade. Kuddos

[@Kig_Ama]

ty, can u provide a download link for your excel file?

[@luvynbays]

Thank you sir for this very informative video. however I’m stuck on the contribution to variance. Could you elaborate more on why it could be produced in this way ? thanks!

[@scottkellner2851]

This is a very old reply but here is a link to a thread on analystforum that explains the textbook math. We're just taking the textbook math and doing it in excel via sum product.

[@pureloor8634]

Hi Jonathan, I was wondering if there was any efficient way to do a project like this on a far larger scale - i.e. a 50+ stock portfolio. Obviously you can do it the same way you did it, but it just seems redundant at some point.

[@robinmjg]

Hi Jonathan, the portfolio optimizer is really a cool tool. Truly appreciate your time and efforts to share your professional insights indeed. One small request if you don't mind, since you mentioned that there is a better way to figure out the Expected Return for each of the stocks given in the example, I'm wondering if you may take a bit of time to illustrate how to get the Expected Return. Thanks & Best Wishes Robin

[@jonathankalodimosphd]

A common way is to use CAPM to estimate a the expected return. I don't have a complete video but in this video I show you how to estimate the "beta" in CAPM. kzbin.info/www/bejne/q5SufGhobaajedk Another way to get beta is just to grab it from Yahoo Finance or another website. After you've estimated the beta you use the CAPM formula.

[@LinoJaeWonLee]

wow~~~ amazing, can you leave the link how I can install the 'solve' function? I searched from your video list but a bit hard to find just by searching the title of your video.

[@ana_8696]

Great video!! I have one question: Great video!! I have 12 assets and their prices of 29 different weeks. I want to do portfolio optimization by minimizing the Mean Absolute Deviation. I have calculated r, E[r] , E[r-E[r]] and |E[r-E[r]]| using Excel . What do I have to do next?

[@istishota2110]

want to ask for the formula for calculating weight and standard deviation of the portfolio what is the formula, what formula is written using the formula, not the excel formula ?

[@jacksherman5324]

can you use this model for more than 3 stocks?

[@bballboyinthehood12]

Could you use CAPM to calculate the expected return?

[@WalterZelhofer]

By definition, the CAPM assumes the market portfolio to be mean-variance efficient (has the max Sharpe ratio), so the optimal weights correspond to their actual market capitalizations (price x shares) and no optimization is necessary at all. ;)

[@Kig_Ama]

3:40 Why do u use the + symbol and not =, do u have a reason for that or is it just your own preference?

[@jonathankalodimosphd]

It’s just convenience.

[@Kig_Ama]

@@jonathankalodimosphd Thank you for your reply!

[@OutofTheMoney69]

my solver keeps giving me an error message

[@alegurrola96]

If you have lots more stocks, how can you calculate the variance in the variance-covariance matrix? How to choose the diagonal of the matrix and applied the "var.s" formula?

[@easydrafting]

It's difficult in excel I believe. I use R programming for the same.

[@AlkaAT]

Hi. I get negative variance in some cells. Is it ok? Also my variance is in tens and ones. Is it ok?

[@ScoutBerks]

Variance cant be negative

[@kushagarwal2675]

How can we get historical data of returns downloaded as an excel file?

[@OfficialCANVAS]

Chrome has a table kopier extension you can copy to excel

[@Plakiwtis13]

Can you help me ? After i press solve all the weights turn to 0 expect one who takes 100% What am i doing wrong

[@jackdragon9597]

You could be setting your objective to maximize the "expected return" rather than the "expected sharp ratio'. For instance, I am creating a portfolio of renewable energy companies and when I set the objective to maximize the return of the portfolio, solver has me throw all my cash into one security. If I set me objective to maximize the sharp ratio, "solver" uses all securities in the portfolio. Another solution could be to change your restraints. Hopefully this is helpful!

[@Plakiwtis13]

@@jackdragon9597 thank you so much

[@I.U.1]

Is there another reason why this could happen? I also got a result from solver which gives 100% to one asset, but then i did some manual trial and error and found that shorting on one of the stocks increases the sharpe ratio. I did this by removing all the constraints, but the one which keeps the sum of weights to 1.

[@drek273]

what if you have like 35 stocks in your portfolio? how do you do this with more than 3 stocks?

[@jonathankalodimosphd]

Conceptually it’s the same but the method doesn’t scale well. I’d look into programming language based optimizations based on matrix algebra.

[@drek273]

@@jonathankalodimosphd for example, i can just look up portfolio optimization in R? also, what do you mean by it doesnt scale well

[@jonathankalodimosphd]

@@drek273 R would be a great choice. It doesn’t scale well because there are a lot of manual inputs. Also matrix operations can calculate things much more efficiently from a computational standpoint.

[@drek273]

@@jonathankalodimosphd ok thank you

[@mikeylejan8849]

I have 1,250 ETFs I need help to know the optimal portfolios, I got these from the New York Stock Exchange, I need help, I am using Cumulative Abnormal Return and Behavioral ETF

[@OfficialCANVAS]

ur not outperforming anything with 1,250 ETFs m8

[@mikeylejan8849]

@@OfficialCANVAS I have finished my study.It is titled “ Behavioral Portfolio Optimization using Behavioral Etfs” I am proud of this study because using these behavioral criterias, I was able to beat the S and P 500 markets and New York Stock Exchange Market. Out of 1245 Etfs only 245 Etfs showed Behavioral patterns. I have used these etfs for my testing years 2019 and 2020. After 1 year of work, I was able to finish this.

[@jonyal1650]

can you show how you calculate the HPR for each company ?

[@mitchstanley5004]

(End price+Dividends)/Beginning Price.

[@Cadu12345]

Have you ever used portfolio optimization for real? How was your experience ?

[@jeronimovargas9123]

What does it mean when the contribution to variance is 0 lol