Graph The Efficient Frontier And Capital Allocation Line In Excel

Graph The Efficient Frontier And Capital Allocation Line In Excel by Ryan O'Connell, CFA, FRM
Chapters:
0:00 - Download Historical Data from Yahoo Finance
0:42 - Calculate Returns from Historical Prices
1:08 - Calculate Asset's Average Return, Standard Deviation, and Covariance
2:18 - Assign Portfolio Weights
2:56 - Calculate Portfolio Expected Return
3:27 - Calculate Portfolio Standard Deviation
5:03 - Calculate Portfolio Sharpe Ratio
5:47 - Graph the Efficient Frontier
6:40 - Graph the Capital Allocation Line (CAL)
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfi...
👨‍💼 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

[@RyanOConnellCFA]

💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/efficient-frontier-and-capital-allocation-line-excel-file/ 🎓 Tutor With Me: 1-On-1 Video Call Sessions Available ► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/

[@jasonl1887]

Perfect, I downloaded the excel spreadsheet and noticed in the video the sharpe ratio was different. This is just because the risk-free rate was not locked with F4 if anyone also wondering what is different, cheers Ryan! This has helped me a lot with my financial understanding.

[@RyanOConnellCFA]

Glad it helped! Thank you for the feedback Jason. Not locking that risk free rate is the one regret that still haunts me with this video haha

[@faridsalehi7275]

Would you please send me the file

[@RyanOConnellCFA]

@@faridsalehi7275 You can download the file for free here: ryanoconnellfinance.com/product/efficient-frontier-and-capital-allocation-line-excel-file/

[@mcpeinventors6094]

I found this channel a week ago, and absolutely love it. Keep em coming Ryan!

[@RyanOConnellCFA]

Your comment will give me good motivation to keep them coming! I really appreciate the feedback

[@williamrivera162]

Hi Ryan. I saw several videos, but I consider that your video is one of the best. Good explanation and easy to understand. Congrats go ahead.

[@RyanOConnellCFA]

Thank you, William, I appreciate the feedback! I guess practice makes perfect haha

[@shiyaohong2728]

very usefulllllll！！！！！！！！！！！！！！！！！！！-A student from IMPERIAL COLLEGE BUSINESS SCHOOL

[@RyanOConnellCFA]

Thank you and hello from the US!

[@ishmailsannoh2486]

Best video I have watched so far. So well explained and easy to understand. Keep it up

[@RyanOConnellCFA]

Awesome, thank you!

[@JKInvestments]

Nice video. Explained clearly in easy to understand language.

[@RyanOConnellCFA]

Thanks for the positive feedback!

[@connie7419]

thank you so much for your video Ryan!!! that's a perfect explanation!

[@RyanOConnellCFA]

You're very welcome! Thanks for letting me know

[@sahyjuf1603]

Great explanation and easy to understand! Thank you!

[@RyanOConnellCFA]

You are welcome!

[@matheusgomes4592]

Hello Ryan, thank you so much. I'm from Brazil and you helped me a lot! Tks :)

[@RyanOConnellCFA]

Glad you enjoyed it and thanks for the feedback Matheus!

[@muhammadilman8195]

yoo, thanks for the video. your explanation helped me on the efficient frontier graph for my assignment.

[@RyanOConnellCFA]

Great to hear!

[@ivan_leung_aus]

Thank you very much for putting this up. Much appreciated

[@RyanOConnellCFA]

My pleasure Ivan!

[@jmnew3463]

Great Video It helped a lot! One single question: Where du you take the risk free interest rate from ? Current 3m, 2y or 10y US treasury bond yield ?

[@RyanOConnellCFA]

3-month Treasury Bill is the most frequently used term for the risk-free rate in portfolio optimization, as it represents a short-term investment with virtually no risk. It is often used in the context of short-term trading strategies and tactical asset allocation.

[@jmnew3463]

@@RyanOConnellCFA Thanks. So when we are looking at daily returns from the stock market - let's say over the last 10 years - we calculate our (expected) return and volatility on basis of these returns and then in the last step we divide the yield of the 3-m treasury by 252 to get our risk free interest rate ?

[@RyanOConnellCFA]

@@jmnew3463 If you are looking at daily returns for the stocks then you want to make sure that you are also going by daily returns for the treasury. Although it may make more sense to annualize them and then make calculations based on annual returns and volatility

[@zarpasuave]

BLESS YOU FOR SHOWING HOW TO GRAPH THE LINE YOURE SUPER DOPE THANK U THANK U

[@RyanOConnellCFA]

My pleasure Nuria!

[@AndreaMcNair]

thanks so much for this video! I needed this for my Investments class!

[@RyanOConnellCFA]

Its my pleasure! Really glad it helped

[@Rasamandrice]

Really cool content man! I tweaked this slightly and used the Excel Solver to find the optimal weights.

[@RyanOConnellCFA]

Thanks Vignesh! How do you tweak the Excel Solver?

[@mohammadvarchandi1574]

Thanks, Gentleman for your useful videos

[@RyanOConnellCFA]

My pleasure!

[@elaheabdolahi1878]

Thank you so much Rayan! Your video helped a lot

[@RyanOConnellCFA]

Glad it helped!

[@massoudbayati7571]

Thanks. great. Could you please provide training for multiple-asset portfolio rather than two asset.

[@RyanOConnellCFA]

Hello, you can find my video on the efficient frontier for portfolios with more than two assets here: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@mauropires1733]

Very cool! You save my college work XD

[@RyanOConnellCFA]

Glad I could help!

[@Zenttt0098]

how to know the risk free rate for 12 month in 1 year? and is correlation is same as risk free rate?

[@RyanOConnellCFA]

Sound likes you are looking for the 1 year forward risk free rate starting 1 year from today. You could calculate that using the approach I show in this video: kzbin.info/www/bejne/kICllptjqtl_iqc Correlation is not the same as the risk free rate

[@aji-tstafd8141]

Your help is priceless, thank you so much

[@RyanOConnellCFA]

Happy to help! It is my pleasure Aji

[@anastasioskondo9186]

you're a good guy brother! thanks for the help

[@RyanOConnellCFA]

Much appreciated my friend!

[@JAQ1988]

Keep doing great work, Ryan🎉

[@RyanOConnellCFA]

Thank you Jawad! Will do!

[@antsos]

Hi, why do we use 0,100 and 200% CAL? Can someone please explain this?

[@marvellousproductions8992]

Hey Ryan, thanks so much for sharing it is really helpful ! I have one question though, as I can see the intercept on the y axis is not with the value of your risk free asset. When I tried to plot mine, I get a small graph of the frontier and a a really large line. This is I guess because my risk free rate is really small so it produces a really unbalanced graph that is not readable without extremely enlarging it. I just wanted to ask if you did any other steps before in tour settings or something in order to get such a visible graph. Thank you:)

[@RyanOConnellCFA]

It is my pleasure! Without actually seeing your file, it is very difficult for me to figure out what the issue is. It could be that your risk free rate is just so low that it looks like it starts at the bottom left corner but it could also be that your horizontal axis settings are cutting off some of the capital allocation line. Try downloading my file using the link in the description and taking a look at the chart settings

[@cuad0130]

Hello, amazing video thank you! Just a quick question, how come for covariance you use the "Covariance.P" and not "Covariance.S" since we are using a sample of historical returns and not the total (population) amount?

[@josealejandroandraderodrig3123]

This is awesome Ryan, its very intuitive explanation, just a question, am I wrong or you made a mistake to not lock the risk free rate cell in the Sharpe ratio formula? and also for the return calculation of the CAL?

[@kylekelley3226]

Great video, super easy to follow. Ran into an inconvenience. When importing data from yahoo finance, it downloads furthest date first. Is there a way to download in descending order instead of ascending? I don't think this affected results at all, it's just something that is bugging me. Anyone have any tips for this?

[@jesperhjort6909]

Hey. Great video. One question though. Why not use the solver to find the optimal portfolio allocation? It seems like you randomly pick the portfolio weights and then just assume that the best allocation in your sample by definition is the optimal portfolio.

[@RyanOConnellCFA]

Jesper, the solver would give you an even more accurate result. I have another video on that topic here: kzbin.info/www/bejne/aquumqOXg6ahha8

[@satyapal18]

Thank you so much for the explanation Ryan, you have solved my biggest query. I am currently reading Simon Binnega's books financial modeling. This video has actually presented entire chapter no 8. I have no words to describe my emotions. Lastly I wants to know that could you explain what capital allocation line explain? I mean what it is trying to say investor?

[@RyanOConnellCFA]

Glad it was that helpful! It is amazing how much textbook info you can cover more efficiently in a video The capital allocation line represents a portfolio's risk versus expected return. It illustrates the risk-reward ratio for a given portfolio, combining risk-free assets with a portfolio of risky assets. If you move above the tangential point where it touches the efficient frontier, that means you are starting to short the risk free asset and invest more than 100% of your portfolio into the optimal risky portfolio. Essentially, it guides investors on how to maximize returns for a given level of risk through diversification.

[@satyapal18]

@@RyanOConnellCFA Sir, thank you so much for your contents and clarity. Your videos are helping me to grasp the concepts from the book. 🙂 I wish I would have seen your videos 10 years ago. Thank you so much sir.

[@simonpark6885]

Hi, how do you find the tangency portfolio with two assets and how do you find the weights for the two assets

[@RyanOConnellCFA]

You can use the Excel solver to answer this question. I have made a video more recently with 3 assets showing how to do this: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@akshaydamani]

Hi Ryan, just wanted to ask, should we lock the Risk free asset while computing the Sharpe ratio formula

[@RyanOConnellCFA]

Yes! That is the one part of this video I messed up :(

[@agustinamenabar4554]

Very usefull man! Greetings from Chile

[@RyanOConnellCFA]

Thank you and greetings from the USA!

[@iam-drake]

using 2% for the risk free rate, what about calculating for 6 different asset while using daily returns??.

[@amaniojo4164]

I FIND YOUR VIDEO REALLY HELPFUL BUT I HAVE ANOTHER QUESTION THAT I HAVE NOT SEEN IN YOUR VIDEOS. You require that your portfolio yields an expected return of 14%, and that it be efficient on the best feasible CAL. i. What is the standard deviation of your portfolio? ii. What is the proportion invested in the T-bill fund and each of the two RISKY FUNDS

[@amaniojo4164]

PLS CAN YOU GUIDE ME ON HOW TO DO THIS. THANK YOU

[@RyanOConnellCFA]

In the optimal portfolio indicated by the orange dot, we are putting 100% of our money into the optimal portfolio, and 0% in T-bills. The proportions to the risky assets are shown by the yellow highlighted line. 70% SPY, 30% VXUS. Standard dev is 0.92%

[@divypatel3677]

why we have not use "ln" to cal. the returns like in other times ..?

[@RyanOConnellCFA]

I was trying to keep the video simpler. Ln would be the more accurate way to calculate the returns in my opinion. For anyone curious, you can see this methodology of calculating returns using =ln() for the efficient frontier here: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@pacmanrocks94]

Great content as always. I highly recommend using Excel without a mouse as that will separate you further from the rest. Cheers.

[@RyanOConnellCFA]

Appreciate it Cole! Knowing the shortcuts can definitely speed things up. I like to use the mouse in videos like this so that people can see what is going on

[@stefanosmiltiadou9272]

Why do you choose population variance formula insted of sample population formula?

[@RyanOConnellCFA]

In hindsight I'd have chosen sample variance because this is truly a sample of the stocks total population of returns. However, neither choice will make much of a difference in samples of this size

[@mustardwithglasses6508]

How are you getting the VXUS numbers? I don't see it anywhere in the data sheet?

[@bambinoskebabs2278]

does everything compute the same if we use annualized return and sd figures?

[@RyanOConnellCFA]

Yes, as long as you annualize it the correct way, it will compute the same. This video goes into depth on how to annualize standard deviation and expected return properly: kzbin.info/www/bejne/infbnJJ5rrBpnZo

[@financialchimes4546]

Should you use the geometric mean or the simple average? And why? The geometric mean would be a more realistic long term return.

[@RyanOConnellCFA]

You are correct, I would recommend to use the geometric mean. Although, it won't be a material difference it will be slightly more accurate

[@morales_jason355]

for the Portfolio sharpe ratio u didn’t lock in the risk free rate. should we be cell locking that?

[@RyanOConnellCFA]

Good catch! You should absolutely be locking that cell

[@anasmerini9486]

You didn't lock the risk-free rate cell in the Sharpe ratio calculation at 5:27

[@kevinlaffey9310]

What are your thoughts on using the natural log of returns (LN(Cl/Cl-1)) as opposed to simple returns? Much of the literature recommends using log returns. There will be distortions at large values both positive and negative however. Thanks!

[@RyanOConnellCFA]

I would recommend using the natural log of returns! In fact, I I have a new video out now using this method: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@kevinlaffey9310]

@@RyanOConnellCFA Thank you Ryan. That sounds great. I did some research on my own as well. The consensus among the quant community, which I realise you are a part and hence already know but sharing here for others’ reference, is that owing to the asymmetric behaviour of simple returns, natural log returns which behave symmetrically should be used whenever the focus of interest is on the temporal behaviour of return. So this would include for metrics such as volatility, beta, correlation and VaR. All predictive measures as natural log returns are time additive, essentially representing a continuously compounded rate. Meanwhile, the asset additive simple (linear) returns are recommended to be used for performance measurement and attribution, basic risk analysis and portfolio optimisation. This would seem to imply to me that, for example, the simple returns of each asset in a portfolio should be used to calculate portfolio standard deviation as it is against those actual returns the actual weights of each asset in the portfolio must be multiplied against. Would you agree? I guess this also means that if one is interested in calculating portfolio beta (and the metrics of which it is composed) to a benchmark, perhaps for hedging purposes, then one would need to use natural log returns. Very interesting stuff I’m sure you’d agree! I presume this is why there’s so much commercially offered investment and portfolio analysis software out there as the multitude of steps one must take with handling the raw time price series data is substantial. But there’s clearly a great deal of satisfaction in doing it oneself with Excel or Python, etc, and actually knowing the approaches being used by the fancy software. I actually found two good academic research papers related to this topic. If you like I can send to you if you have a preferred contact method. Just let me know, here or on Twitter (@KevinLaffey3) or LinkedIn. Another thing I was exploring which I’ve had some success with is how to calculate the cross asset correlation within a portfolio to gauge the actual diversification benefits being achieved by the portfolio composition and weights. What I found there is that the basic approach mirrors the method employed by the CBOE for its implied correlation indices. But Goldman quants actually figured out a bit of a shortcut which a user on stack quant exchange was good enough to share. So I have info on that too if you’d like in case you may not have seen it. Cheers and thanks again

[@austinbrown2726]

@@RyanOConnellCFA link? :)

[@RyanOConnellCFA]

@@austinbrown2726 Here is the link to the new efficient frontier video! kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@RyanOConnellCFA]

@@kevinlaffey9310 Really fascinating Kevin! I would love to see that paper if you wouldn't mind sending it to me on LinkedIn. I recently was facing the same dilemma you mentioned in choosing whether to use log returns or simple returns for a client's project related to portfolio performance attribution. I ultimately ended going with simple returns for the reasons that you mentioned above. I also made a video breaking down simple vs log returns which I'd very very interested in getting your thoughts on since you understand the topic so deeply: kzbin.info/www/bejne/nKW7iWekj69nps0

[@rickguerrero2282]

Really nice work!

[@RyanOConnellCFA]

Thank you!

[@chadieltahan7436]

Many thanks Ryan for the informative content! I am CFA level I candidate and just studying this material in Portfolio Management. I am also planning to take a financial modelling course between level l and level ll. Any specific course you recommend? And what could be the specific job titles that are mainly focused on creating financial models such as this one? Thanks again.

[@RyanOConnellCFA]

Hello! I'm not certain of the best financial modeling course to take. But as far as jobs that use skills such as the one I created in this video, you could search on job boards for the titles: Financial Analyst Investment Banker Private Equity Analyst Corporate Finance Analyst Quantitative Analyst Risk Manager Portfolio Manager Financial Planning and Analysis (FP&A) Analyst Venture Capitalist Real Estate Analyst Credit Analyst Mergers and Acquisitions (M&A) Analyst Derivatives Analyst Fixed Income Analyst Hedge Fund Analyst

[@wajihchtiba34]

Great video again ! 😊

[@RyanOConnellCFA]

Thank you 🤗

[@SA-ft4gu]

I didn't get the tangent line on the graph, it only shows the orange fot of the optimal portfolio

[@RyanOConnellCFA]

It is difficult for me to tell what is going wrong from this comment. You may need to follow the steps once again

[@TimothyMbuga]

Ryan O'Connell i really need your guidance, i've got a uni assignment in modern portfolio theory.

[@RyanOConnellCFA]

Hey Timothy, what is the hold up?

[@timtio3373]

@@RyanOConnellCFA is it possible to share with you the assignment via email?

[@tamnguyenlequynh6335]

I dont know why we have 200% in weights, please explain it for me

[@RyanOConnellCFA]

The 200% indicates that we are going short the risk free rate 100% and investing that money that we borrowed into the risky portfolio. So lets say that we have $10,000 hypothetically. We then borrow an additional $10,000 and pay the risk free rate of interest to do so. We then take both our initial $10,000 and then additional $10,000 that we are borrowing, and invest the total $20,000 into the risky portfolio.

[@iam-drake]

COOL.. IM HELPED

[@RyanOConnellCFA]

Very glad to hear!

[@sheilaharty3167]

good video thanks but you forgot to lock in your rfr cells a couple of times

[@noviandwiramadana2190]

hy ryan how if we have 10 asset? how to make efficient frontier curve?

[@RyanOConnellCFA]

My new Efficient Frontier video on portfolios with more than 2 assets can be found here: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@matan1998]

king!!! THANK YOU

[@RyanOConnellCFA]

Happy to help!

[@ChuAnhThu-oq2xl]

why do you allocate weight of CAL to 200% ? Thank you for your helpful video 🥰

[@RyanOConnellCFA]

The 200% indicates that we are going short the risk free rate 100% and investing that money that we borrowed into the risky portfolio. So lets say that we have $10,000 hypothetically. We then borrow an additional $10,000 and pay the risk free rate of interest to do so. We then take both our initial $10,000 and then additional $10,000 that we are borrowing, and invest the total $20,000 into the risky portfolio.

[@priyankmaru823]

Hi buddy, I think there is error in Sharpe ratio, Risk free rate is not freezes, you must freez that too. Kindly check it.

[@RyanOConnellCFA]

Absolutely, I've corrected that in the downloadable file! And I've tested that if the cell is frozen, the same portfolio still has the highest Sharpe Ratio. Thank you for your comment!

[@Sosojuju]

Thank you.

[@parkerjones6402]

Thanks for the video very helpful. How would you calculate the covariance on a 3 stocks?

[@RyanOConnellCFA]

You're welcome Parker! Check out this video where I make a covariance matrix on a 4 stock portfolio: kzbin.info/www/bejne/aquumqOXg6ahha8

[@antonzharkov6036]

great video about the efficient frontier, how would the calculation change if you add 1more stock to the portfolio?

[@RyanOConnellCFA]

Hello Anton, you could do it mostly the same way as a showed in this video. The big difference is that you'd need to make a correlation matrix to measure portfolio standard deviation. I show how to do that in this video: kzbin.info/www/bejne/aquumqOXg6ahha8

[@muratgelir]

This video answered all of my questions than youu

[@RyanOConnellCFA]

My pleasure Murat

[@faridsalehi7275]

Hi dear Ryan, how can I rotate a straight line ( capital allocation line without adding data)in Excel relative to a point?

[@RyanOConnellCFA]

Hello, I'm not sure why you would want to rotate the CAL line. It is the point of tangency between the risk free rate and the optimal portfolio. It would rotate if either of those changed

[@billqin7869]

Hello, Ryan. Your videos are quiet useful! But the download file is missing. The only problem I don't understand is how to create the formula to capture the optimal portfolio. Could you upload this file again? Thank u so much:)

[@RyanOConnellCFA]

Thank you for the feedback Bill! It should be fixed now. Please let me know if you have any other problems 💾 Download the file created in this video free here: ryanoconnellcfa.com/product/efficient-frontier-and-capital-allocation-line-excel-file/

[@thutanaing2024]

Hello.., how about constructing Efficient Frontier on 3 Different Assets??

[@RyanOConnellCFA]

I got you covered right here! kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@thutanaing2024]

Thank u sir, coz i am doing MBA Thesis on Portfolio Construction, thz for ur kindness @@RyanOConnellCFA

[@RyanOConnellCFA]

@@thutanaing2024 It is my pleasure, good luck with your thesis and I hope my videos can be of some help to you! You may want to check out my video on portfolio optimization as well: kzbin.info/www/bejne/d3jNpKmaebKsnqc

[@chitsai_]

Thank you!

[@RyanOConnellCFA]

My pleasure!

[@ankurdhawan132]

Really helpful, keep the good work up brother

[@RyanOConnellCFA]

Thank you and will do brother

[@ankurdhawan132]

@@RyanOConnellCFA How can one plot utility curve on such a capital allocation line? I mean which values of sd and E r will be used for the utility curve?

[@lucagiussani7109]

Great video Ryan! Can you do it with a 10 assets portfolio? How to set the weights? Thank you

[@RyanOConnellCFA]

Luca, I've been thinking about revisiting this video with a larger portfolio. I likely will be it won't be for a while

[@user-qu2xy8uw8c]

Great

[@RyanOConnellCFA]

Thank you!

[@falinoluiz5962]

What if I have 30 securities in my portfolio?

[@RyanOConnellCFA]

You'd follow a similar process. The daily returns would be easy to calculate with a weighted average. But the standard deviation would be difficult as you'd have to create a correlation matrix as I do in this video: kzbin.info/www/bejne/aquumqOXg6ahha8

[@bobbybaylonjr7403]

Great video. Thank you. Is it ok for you to share the file? It can't be downloaded! Please keep on recording videos like this.

[@RyanOConnellCFA]

Hello Bobby, please take a look at the description. I re-posted the file with a link to Github

[@amanda-ellesbethneemia3959]

This is a really great video and very easy to follow-thanks for posting. I'm having trouble though creating the formula to capture the optimal portfolio, can you reshare the link to the spreadsheet :)

[@RyanOConnellCFA]

💾 Download the file created in this video free here: ryanoconnellcfa.com/product/efficient-frontier-and-capital-allocation-line-excel-file/

[@behnamhoseinnezhad7080]

What is the OPTIMAL PORTFOLIO formula in the RETURN line?

[@RyanOConnellCFA]

The portfolio with the highest Sharpe ratio

[@stephanwagner4578]

Hi Ryan, i have never come across sharpe ratio in per cent. How is that to be interpreted? Thanks a million in advance

[@RyanOConnellCFA]

Stephan, you could just change it to decimal form instead of percentage if you prefer. It shouldn't change the outcome

[@henokhaile2899]

Hi, are you using a time interval from newest to oldest? i got the high set sharp ratio, the standard deviation and the return in the last "-30%, 130%, but not on the middle yellow color which is portfolio optimizerng. why that? i can't use stocks vs indices, or stock vs stock and both "indices" and "indices" ? and what is the limit by choosing ?thanks for the help

[@RyanOConnellCFA]

Hello Henok! Did you happen to use a different set of securities or a different set of returns than I did in the video? And if so, you may have had a case where one security so substantially outperformed the other asset that the analysis says that you short one and go longer than 100% in the other

[@ChintavCBD]

thanks brother

[@RyanOConnellCFA]

My pleasure!

[@koroshk5748]

Beautiful

[@RyanOConnellCFA]

Thank you!

[@kokomisangonomiya5154]

i tried downloading your file, but it doesnt seem to work. It says Page Not Found (404)

[@RyanOConnellCFA]

I appreciate you letting me know. I just fixed this error and the file should be available for free download. Please let me know if it works for you

[@Analyst101]

Just one thing, for the sharp ratio, you didnt lock the Rfr there for it took 0% for every other data row.

[@RyanOConnellCFA]

Yup, good catch! This is fixed in the downloadable file

[@user-jy5ei2rh6z]

hi, for the risk free rate if i count for 10 years should it be 0.02/3600?

[@RyanOConnellCFA]

No, you would likely take the 10 year risk free rate (perhaps the 10 year Treasury Note rate as an example) and then divide it by the number of trading days in the year (252) to get the daily risk free rate of return

[@jmnew3463]

In the Literature i found, they call the tangential portfolio the point, where the CML (not CAL) tangents the efficient frontier. Why did you choose to call it CAL?

[@RyanOConnellCFA]

Hello JM New, it is possible that the literature you're referring to is misleading. I hope this helpson the differences between the CAL and CML: The Capital Allocation Line (CAL) and the Capital Market Line (CML) are both concepts used in portfolio theory, but they serve different purposes and have different characteristics. Here's an overview of each: Capital Allocation Line (CAL): The CAL represents the risk-return trade-off for different combinations of a risk-free asset and a risky portfolio. It's a graphical representation of a linear relationship between expected returns and risk (as measured by standard deviation) for various portfolios that include both a risk-free asset, like a government bond, and a risky portfolio, such as stocks. The CAL has the following properties: The y-intercept of the CAL represents the risk-free rate of return. The slope of the CAL, known as the Sharpe ratio, measures the risk-adjusted performance of the risky portfolio. A higher slope indicates a better risk-return trade-off. An investor can choose any point along the CAL, depending on their risk tolerance and desired returns. By adjusting the weights of the risk-free asset and the risky portfolio, investors can create their optimal portfolio based on personal preferences. Capital Market Line (CML): The CML, on the other hand, is a specific case of the CAL. It represents the risk-return trade-off when the risky portfolio is the market portfolio, which consists of all risky assets in the market. The market portfolio is assumed to be the most diversified and efficient risky portfolio available, as it lies on the efficient frontier in the Markowitz's Modern Portfolio Theory. The CML has the following properties: Like the CAL, the y-intercept of the CML represents the risk-free rate of return. The slope

[@jmnew3463]

@@RyanOConnellCFA Thank you for you detailed anwser! I did a little bit more research and think that this is the correct answer: The CAL actually includes the individuall preferences of the investor, but if we assume Investors with risk same preferences then we have CAL=CML. Because in the scientific literature I found, the tangency point of CML and efficient frontier is the portfolio with the highest Sharpe ratio which means that this is the tangential portfolio = global market portfolio

[@RyanOConnellCFA]

@@jmnew3463 Really interesting point! Thank you for coming back and sharing. I'm sure your comment will help people that visit this video in the future!

[@martincontreras6031]

awesome!! where do can I deposit few bucks to support your channel?

[@RyanOConnellCFA]

Hey Martin, that is very nice of you to offer! The easiest way is to go to this page on my website and buy a file of your interest: ryanoconnellfinance.com/financial-models/ I really appreciate the support!

[@martincontreras6031]

@@RyanOConnellCFA will do. A word of advice regarding autocorrect when typing… don’t use it. I didn’t proofread my comment, hence, the outcome 😅.

[@RyanOConnellCFA]

@@martincontreras6031 Haha thank you for that! Sticking to a computer rather than a smart phone helps with these type of issues!

[@izzatimalek]

ryan. i cannot download the file. its missing. btw thank you for the knowledge!

[@RyanOConnellCFA]

Just updated with a Github link!

[@jmnew3463]

Why did you use simple returns (Pt/Pt-1)-1 and not compounded returns (ln(Pt/Pt-1) ?

[@RyanOConnellCFA]

In hindsight, I'd prefer to have used ln(). It is too late to edit the video now but I'd recommend using the natural logarithm going forward as it better reflects the effect of compounding

[@gudapativenkateswararao706]

Just Amazing

[@RyanOConnellCFA]

Thank you!

[@jaiso434]

nice. thank you.

[@RyanOConnellCFA]

No problem!

[@sh2957]

05:32 i think you didn't lock 'I7' (risk free rate)

[@minhhangpham6649]

can you do for the three-stock portfolio ?

[@RyanOConnellCFA]

I can look into doing this sometime down the road my friend

[@Dongnanjie]

The risk-free rate was not locked (F4) in the video.

[@RyanOConnellCFA]

Good catch! I've got it fixed in the downloadable file

[@alejadroigoyanes]

please do this but with like 10 assets

[@RyanOConnellCFA]

I will look into this in the future, thank you for the suggestion

[@permetras5460]

Thanks you

[@RyanOConnellCFA]

My pleasure!

[@tientrinh3.2]

in portfolio weights, we set each weights randomly?

[@RyanOConnellCFA]

Hello Tien, in this example I didn't set them randomly. I set them in 10% intervals switching the allocations between each asset by 10% for each example portfolio

[@tientrinh3.2]

@@RyanOConnellCFA in this example, just have 2 assets which is easy to switch the allocations 10% between these assets. For example, if i have 5 assets at the same time, how can i handle them?

[@RyanOConnellCFA]

@@tientrinh3.2 I will be posting a video on this exact topic in the next 3 weeks. If you can wait for that, it will show you step by step on how to accomplish exactly that. But yes, the best method is to randomize the weights for thousands of scenarios and plot the expected return standard deviation pairs

[@tientrinh3.2]

@@RyanOConnellCFAoh my goddess, thousands of scenarios? It will take me lots of time for sure. Because my lecturer asked me to hand in this task for him in the next 4 days so it’s urgen now, I might not wait for ur new video related to this topic :(

[@RyanOConnellCFA]

@@tientrinh3.2 You can actually perform thousands of scenarios an instant if you follow the trick I used here: kzbin.info/www/bejne/aquumqOXg6ahha8

[@faridsalehi7275]

Anyone send me the file in this video, please.

[@RyanOConnellCFA]

You can download that here: ryanoconnellfinance.com/product/efficient-frontier-and-capital-allocation-line-excel-file/

[@muktanaik822]

Hey amazing content. Why not use the function stdev.s for standard deviation?

[@fredve3984]

Thats what I use and I think it's more correct. He's using the population SD when, in fact, he doesn't have the entire population of data.

[@animeshmaiti607]

What is the codes for Optimal Portfolio Row?

[@RyanOConnellCFA]

Hello Animesh, I'd love to help you but could you please be a little more specific with your question?

[@animeshmaiti607]

@@RyanOConnellCFA In the Portfolio Statistics Column, you have calculated the Optimal Point(You named Optimal Portfolio) By highlighting Yellow color. How did you calculated?

[@RyanOConnellCFA]

@@animeshmaiti607 I get it now! I just looked at the row that had the highest Sharpe Ratio and highlighted it yellow. If you want to use a formula, you could use MAX() and select the whole range of Sharpe Ratios to find the highest one

[@RyanOConnellCFA]

@@piranha_finance Hello, I believe mean-variance frontier is synonymous with efficient frontier. Meaning, it is the same as what I did in this video

[@Grace-tj4qc]

How to find optimal portfolio?

[@RyanOConnellCFA]

It is the portfolio with the highest Sharpe Ratio

[@user-cn4ye3hp4t]

should lock the cell referencing "risk free rate"

[@RyanOConnellCFA]

You are correct Stephanie, this error is fixed in the free download file in the description

[@yoyospencer8416]

tip top

[@daiphutran1548]

Fking life saver!!!

[@RyanOConnellCFA]

Lol I'm glad you thought so!

[@julieta1949]

Yur sop 500 index is wrong

[@RyanOConnellCFA]

Please elaborate on why you think the S&P 500 index is wrong

[@goodlyboony]

You forgot to $ the risk free rate for the sharp ratio. The calculation aren't accurate

[@RyanOConnellCFA]

Good catch. The file in the description available for download has that cell locked. I'd recommend people to look over that file