Calculating the Optimal Portfolio in Excel | Portfolio Optimization

"Calculating the Optimal Portfolio in Excel | Portfolio Optimization" by Ryan O'Connell, CFA FRM. This video is based on the Modern Portfolio Theory (MPT) and the Efficient Frontier.
Chapters:
0:00 - Explanation of Assets
0:36 - Expected Return, Standard Deviation, and Weights
1:50 - Enable Data Analysis Toolpak and Solver Toolpak
2:25 - Get Historical Return Data from Yahoo Finance
3:31 - Create a Covariance Matrix
4:58 - Calculate Portfolio Standard Deviation
5:31 - Calculate Sharpe Ratio
6:39 - Find Optimal Portfolio Using Excel Solver
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfinance.com/produ...
👨‍💼 Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC
ALTERNATIVE TITLES:
Portfolio Optimization Made Easy with Excel
Optimal Portfolio Management: Excel Techniques Revealed
Building Your Ultimate Investment Portfolio in Excel
Excel Your Investments: A Guide to Optimal Portfolio Creation
Smarter Investing: Master Portfolio Optimization in Excel

[@RyanOConnellCFA]

💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/optimal-portfolio-calculation-excel-template/ 👨‍💼 Freelance Financial Modeling Services: ► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/

[@maximehardy3654]

Omg, jsut mind blowed that I didnt discover that earlier.

[@RyanOConnellCFA]

Glad you found it now!

[@aniruddhaganguly5699]

Thank you brother Ryan for this wonderful video . Please upload a video of Treynor ratio explaining its advantages

[@RyanOConnellCFA]

My pleasure! I have a video where I briefly discuss Treynor here from many years ago! I can make a newer version though in the future: kzbin.info/www/bejne/pZvQXnelqbqVb5Y

[@aniruddhaganguly5699]

Yes brother. I read it

[@aniruddhaganguly5699]

@@RyanOConnellCFA Thank you brother

[@Bubblegun14]

This is very fun! Thank you, I hope you keep sharing more content.

[@RyanOConnellCFA]

I'm glad you enjoyed it Michael! I appreciate the feedback and have no plans of stopping haha

[@williamrivera162]

Hi Ryan. Finally, I was able to download the file. Thank you so much, for writing.

[@RyanOConnellCFA]

You're welcome!

[@evangelineolaer8]

Interesting subject. It helps a lot. Thanks

[@mohamedmadkour6926]

Thank you so much Ryan, much appreciated

[@RyanOConnellCFA]

My pleasure!

[@leandrobaptista]

OMG! This is amazing. Thank you so much!

[@RyanOConnellCFA]

You're so welcome!

[@johncarroll6910]

Thank you. Great job!

[@RyanOConnellCFA]

Thank you too John!

[@saharabdullah48]

Very informative ! Thank you

[@RyanOConnellCFA]

My pleasure!

[@patrickkevin7064]

Very helpful

[@sunnypj1892]

Great videos. Thanks!

[@RyanOConnellCFA]

Thanks for watching!

[@yi-news]

wonderful lesson,thanks

[@RyanOConnellCFA]

You are welcome!

[@TheRocketmaster6]

lifesaver! thanks :)

[@RyanOConnellCFA]

You're welcome!

[@KalyanisMusicandMelody]

Thank you!🙏

[@RyanOConnellCFA]

My pleasure!

[@ceyhunozdemir6057]

Very useful... Thanks

[@RyanOConnellCFA]

My pleasure!

[@kr1tical41]

Great video! You have made a small error in calculating the variance-covariance matrix. Instead of dividing through by N-1, you divided by N. Only managed to spot this while working through the theory :)

[@noldy90]

NIce...

[@esraalpcoskun5936]

Hi Ryan, Thank you for the videos. Is there any video about CCAR and DFAST stress testing?

[@RyanOConnellCFA]

Hello Esra, thank you! I don't have any videos on those topics yet but I can certainly look into them in the future

[@user-wh5zi2io7u]

Hey, Ryan! Thanks for the interesting and well-explained video. Which gold (asset) did you use for this video and where is it being traded on?

[@RyanOConnellCFA]

It's my pleasure! I used the GLD ETF which tracks the price of gold. Basically, you are buying the rights to physical gold that is stored for you (typically an expense fee of 0.5% per year). It trades on the major stock exchanges like NYSE

[@charliegriffin3491]

Ryan - thanks for the video. Very simple explanation so appreciated that. In terms of assigning an expected return to a stock, would it be useful to use an analyst price target (and subsequent return) as the expected return? Also, do you have a parameter to determine max and min weights. It seems simple with a portfolio of 4 or 5 stocks but with say 16 I cannot really determine what a max or min should be? appreciate your help. Thanks

[@RyanOConnellCFA]

Charlie, the consensus (or median) analyst expectation would probably be your best bet for expected return. As for min weights, its hard to say, I haven't done enough research on that topic

[@RoniCleveland1122]

Hi Ryan, great video. What is impact of covariance matrix and how to understand covariance matrix ?

[@RyanOConnellCFA]

Hi there! Thank you for your comment. The covariance matrix represents the relationship between different assets in a portfolio, capturing how they move relative to each other. Understanding it is crucial because it helps in assessing the combined risk of the portfolio and optimizing asset allocation for the best risk-reward trade-off.

[@maksymnikulyak2580]

Hi, Ryan, thanks a lot for your very detailed solution! The only thing, I'm a bit confused with the behavior of the Expected Return, in cell C12, as it has actually *decreased* from 6.5% prior to the optimization, down to 6.3% thereafter. Please, if you could take a look and clarify?..

[@saidakbarkhasanov8370]

Hey, Ryan. Thank you for walking through the steps. Very clearly and well explained. 1) How can I set it to calculate optimal portfolio if going short is allowed too? 2) I can't find nor downloadable link neither link to your website where I can download file. Is there a chance that you post one of those in the description to video? Thanks.

[@RyanOConnellCFA]

Thank you Saidakbar! 1. I'm not entirely certain on how you would do that. 2. I re-posted the file with a Github link (in the description). Please try that and let me know if it works better for you

[@saidakbarkhasanov8370]

@@RyanOConnellCFA Thank you very much, it worked.

[@RyanOConnellCFA]

@@saidakbarkhasanov8370 You're welcome!

[@alejadroigoyanes]

u should put the standard deviation formula in the video description

[@tommypain]

Ryan, just surfing around and stumbled on you and this video. Very interesting. Thank you. I'll play with it some more. BTW, I see there is some bad data at the bottom of your Raw Data tab on Row 253. Is this causing some wild error in the calculations? Just curious. Hey, I'm just down the road in Arlington.

[@RyanOConnellCFA]

Hey Tommy, great to hear from someone so close by! What do you mean by bad data in row 253? All the data looks fine to me. The returns (which our analysis is based on) stop in row 252

[@tommypain]

@@RyanOConnellCFA Hmmm, watching your video again, I don't see it. I think when I grabbed the data (using your template) for some other tickers, it had one extra row (Row 253) and that's where the extra line of "bad data" was. Disregard.

[@RyanOConnellCFA]

@@tommypain No problem Tommy! Also, I just released a more updated portfolio optimization video in excel here if you are interested: kzbin.info/www/bejne/joK2YmqPp7-pe9U

[@tommypain]

@@RyanOConnellCFA Thanks, Ryan. I'll check it out.

[@evangelineolaer8]

What if the constraint is a 15% standard deviation? Can you please advise? Thank you.

[@Persian5life]

Ryan, what is the advantage of using Daily Data over Monthly Data?

[@jakekennedy6718]

Thanks for this. Really stupid question here but what hotkeys allow you to copy cells? I'm an excel luddite so I am just click and dragging the bottom right.

[@RyanOConnellCFA]

We all start that way Jake! Ctrl + C allows you to copy Then go to the last column that has values, you hit Ctrl + down arrow to go to the bottom row with values Then you can go to the empty column where want to paste the values and hold Ctrl + Shift + up arrow. This will highlight the whole range. Then Ctrl + V to paste over the whole range

[@user-is1xg7lq3k]

I like this youtube, can you tell be why you inserted the number 252 at the end of the standard deviation formula? Thank you

[@RyanOConnellCFA]

252 is the default number of trading days in the year after removing weekends and holidays when the market isnt open. This number is used to annualize returns and standard deviations

[@octavearmand4679]

Hi I dont understand how you found the st dev and exp return for each asset, can you please explain

[@RyanOConnellCFA]

You can get more precise and scientific in this step by following my 5 minute video on this topic here: kzbin.info/www/bejne/a2emmHmDpMaEjNE

[@hansen_song]

How do you determine what the min and max weights are for each security? Also if I wanted to include risk-free assets like US treasuries into the portfolio, would this still work?

[@Ghaith7702]

Nice video the real question is how to find the stocks that maximize the sharp ratio if only solver can do that . or can it ?

[@RyanOConnellCFA]

Hello! I have a newer and more sophisticated video on Portfolio Optimization here: kzbin.info/www/bejne/joK2YmqPp7-pe9U Please let me know if that helps you answer your question. It also uses the solver as portfolio optimization is an iterative math problem

[@murtalaa]

Hi, could you do a video on optimal portfolio optimisation with value at risk constraint

[@RyanOConnellCFA]

I can look into this in the future!

[@nickheyburn]

Hello, In my own data set when I try to use the covariance function, excel keeps blocking it because it says my input range contains non-numeric data. I don't see how that could be true because my data only consists of percentages after doing the steps you demonstrated in this video. Do you know what I could be doing wrong? Your videos are super helpful thank you!

[@RyanOConnellCFA]

In the column next to your returns, use the formula =isnumber() and inside that function reference the cell that has the return. Drag it all the way down for each stock. Find the cell(s) where it says FALSE and you'll have your non-numeric data. Thanks!

[@crazyKiLLAKid29D]

How do you go about finding the St Dev (Risk) values? Overall great video!

[@RyanOConnellCFA]

Thank you! Excel has an inbuilt standerd deviation formula. You just input the range of data into that formula

[@tagtrenton8659]

@@RyanOConnellCFA Ryan, beautiful video. Easier to follow and comprehend than professor instruction. I have a question regarding the standard deviations you can obtain from the covariance matrix. By going diagonally from the top left to bottom right of the covariance matrix and taking the square root of each output, you can calculate the standard deviation of that factor. For instance, if you take the square root of the output from SP500 and SP500 you’ll obtain the SP500 input’s standard deviation. How is this standard deviation different from say using the standard deviation function on the SP500 historical data used in the sheet?

[@RyanOConnellCFA]

@@tagtrenton8659 Hey there! Great question! The standard deviation you obtain by taking the square root of the diagonal elements in the covariance matrix is actually the same as the one calculated using the standard deviation function (STDEV or STDEV.P) on the historical data. In the covariance matrix, the diagonal elements represent the variance of each individual asset, and taking the square root of the variance gives you the standard deviation. Both methods should yield the same results, and any minor discrepancies could be due to rounding or calculation differences in Excel. So, you can use either approach to calculate the standard deviation for each asset in your portfolio.

[@tagtrenton8659]

@@RyanOConnellCFA Thank you for the fast reply! I was getting minor discrepancies like you said and could not figure out why. Finally, I’m at peace lol. Thanks again!

[@RyanOConnellCFA]

@@tagtrenton8659 Awesome, glad you could figure it out!

[@sairajshindexie7253]

How do we take max weight and min weights?

[@williamrivera162]

Ryan, very good video and explanation. I trying to download the file, but this message appear on the screen; You're seeing this error because you have DEBUG = True in your Django settings file. Change that to False, and Django will display a standard 404 page. I don't know if you can formatted the file in other form. Thank you

[@RyanOConnellCFA]

Please try now William, I have added a Github link to the description

[@GabrielaSanchez-qn9my]

@@RyanOConnellCFA Btw, I was wondering if you could share a link that is only uploaded to Google Drive? The direct download doesn't work for me because since I don't have the Excel program downloaded on my computer (only Excel online), it just downloads it directly onto "Pages" (i'm a macbook user) and the formatting comes out wrong. Thanks!

[@sandmantheendless7780]

Could you post the st. dev formula cell? I am having trouble access it

[@RyanOConnellCFA]

Here is the formula exactly as it was in the video: =SQRT(MMULT(MMULT(TRANSPOSE(G5:G8),J5:M8),G5:G8*252))

[@sandmantheendless7780]

@@RyanOConnellCFA Thank you! I really appreciate your videos.

[@donahuco]

@@RyanOConnellCFA Why "*252"?

[@gheorghebarbulescu2787]

@@donahuco 252 represents the average number of trading days in a year

[@ucao4197]

How to arrive at 35% and 15%? Is there a reason behind this? Or we can choose the minimum and maximum weight for each constituent weight to our liking?

[@knayak6062]

Yes. You can change them. It is the strategic decision.

[@mcpeinventors6094]

How exactly does the matrix multiplication work

[@RyanOConnellCFA]

This is my favorite walk through of how this formula works: www.wallstreetmojo.com/mmult-in-excel/

[@davidardevolduran398]

Why do you only use historical data from the last year and not over a period of, say, 10 years?

[@RyanOConnellCFA]

You could definitely use 10 years worth of data and that would likely make your results more accurate and reflective of the true covariances

[@neilhanks]

Why you didn’t use adjusted close?

[@RyanOConnellCFA]

In hindsight, adjusted close would have been better to use

[@eldersprig]

isn't expected return of gold = zero?

[@RyanOConnellCFA]

This may be true if you consider real returns (accounting for inflation), but most returns are quoted on a nominal basis (not adjusted for inflation). A rule of thumb is that the return on gold should be similar to the inflation rate