Calculating the Optimal Portfolio in Excel

No video

Calculating the Optimal Portfolio in Excel | Portfolio Optimization

Рет қаралды 58,329

Ryan O'Connell, CFA, FRM

Күн бұрын

Пікірлер: 98

@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 2 ай бұрын

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

@RyanOConnellCFA Ай бұрын

Glad you found it now!

@williamrivera162 2 жыл бұрын

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

@RyanOConnellCFA 2 жыл бұрын

You're welcome!

@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: kzfaq.info/get/bejne/pdCdYKeg2bvGaWg.html

@aniruddhaganguly5699 Ай бұрын

Yes brother. I read it

@aniruddhaganguly5699 Ай бұрын

@@RyanOConnellCFA Thank you brother

@aniruddhaganguly5699 Ай бұрын

Welcome

@RyanOConnellCFA Ай бұрын

@@aniruddhaganguly5699 My pleasure, and glad it helped!

@evangelineolaer8 4 ай бұрын

Interesting subject. It helps a lot. Thanks

@mohamedmadkour6926 8 ай бұрын

Thank you so much Ryan, much appreciated

@RyanOConnellCFA 8 ай бұрын

My pleasure!

@ceyhunozdemir6057 3 ай бұрын

Very useful... Thanks

@RyanOConnellCFA 3 ай бұрын

My pleasure!

@Bubblegun14 3 жыл бұрын

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

@RyanOConnellCFA 3 жыл бұрын

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

@KalyanisMusicandMelody 3 ай бұрын

Thank you!🙏

@RyanOConnellCFA 3 ай бұрын

My pleasure!

@kr1tical41 4 ай бұрын

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 :)

@leandrobaptista Жыл бұрын

OMG! This is amazing. Thank you so much!

@RyanOConnellCFA Жыл бұрын

You're so welcome!

@saharabdullah48 2 жыл бұрын

Very informative ! Thank you

@RyanOConnellCFA 2 жыл бұрын

My pleasure!

@yi-news 2 жыл бұрын

wonderful lesson,thanks

@RyanOConnellCFA 2 жыл бұрын

You are welcome!

@johncarroll6910 Жыл бұрын

Thank you. Great job!

@RyanOConnellCFA Жыл бұрын

Thank you too John!

@sunnypj1892 2 жыл бұрын

Great videos. Thanks!

@RyanOConnellCFA 2 жыл бұрын

Thanks for watching!

@patrickkevin7064 2 ай бұрын

Very helpful

@RoniCleveland1122 10 ай бұрын

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

@RyanOConnellCFA 10 ай бұрын

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.

@Persian5life 2 ай бұрын

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

@ucao4197 5 ай бұрын

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.

@sairajshindexie7253 Ай бұрын

How do we take max weight and min weights?

@octavearmand4679 2 ай бұрын

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

@RyanOConnellCFA 2 ай бұрын

You can get more precise and scientific in this step by following my 5 minute video on this topic here: kzfaq.info/get/bejne/a5xzmql-1Me1hqM.html

@TheRocketmaster6 2 жыл бұрын

lifesaver! thanks :)

@RyanOConnellCFA 2 жыл бұрын

You're welcome!

@murtalaa 9 ай бұрын

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

@RyanOConnellCFA 8 ай бұрын

I can look into this in the future!

@Ghaith7702 6 ай бұрын

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 6 ай бұрын

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

@maksymnikulyak2580 5 ай бұрын

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?..

@user-is1xg7lq3k 10 ай бұрын

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 10 ай бұрын

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

@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

@evangelineolaer8 4 ай бұрын

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

@alejadroigoyanes Жыл бұрын

u should put the standard deviation formula in the video description

@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

@nickheyburn 9 ай бұрын

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 9 ай бұрын

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!

@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?

@hybriddude007 29 күн бұрын

min weight is how much you want to be invested in a certain stock, the lower it is the better for optimization

@saidakbarkhasanov8370 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

@@RyanOConnellCFA Thank you very much, it worked.

@RyanOConnellCFA 2 жыл бұрын

@@saidakbarkhasanov8370 You're welcome!

@crazyKiLLAKid29D 2 жыл бұрын

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

@RyanOConnellCFA 2 жыл бұрын

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!

@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: kzfaq.info/get/bejne/jreDZJqK18Dadac.html

@tommypain Жыл бұрын

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

@jakekennedy6718 2 жыл бұрын

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 2 жыл бұрын

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

@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

@noldy90 3 жыл бұрын

NIce...

@charliegriffin3491 2 жыл бұрын

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 2 жыл бұрын

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

@sandmantheendless7780 3 жыл бұрын

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

@RyanOConnellCFA 3 жыл бұрын

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

@sandmantheendless7780 3 жыл бұрын

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

@donahuco 2 жыл бұрын

@@RyanOConnellCFA Why "*252"?

@gheorghebarbulescu2787 2 жыл бұрын

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

@mcpeinventors6094 3 жыл бұрын

How exactly does the matrix multiplication work

@RyanOConnellCFA 3 жыл бұрын

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

@williamrivera162 2 жыл бұрын

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 2 жыл бұрын

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

@GabrielaSanchez-qn9my 2 жыл бұрын

@@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!

@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