💾 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/
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.
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.
@@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 ?
@@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
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?
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?
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.
@@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.
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: ua-cam.com/video/ZOBeh-utLTE/v-deo.html Correlation is not the same as the risk free rate
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:)
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
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.
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
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%
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.
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
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?
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
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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
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
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 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
@@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: ua-cam.com/video/ftXX4nZJ4pk/v-deo.html
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.
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: ua-cam.com/video/TFxk_CyK6g8/v-deo.html
@@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?
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: ua-cam.com/video/4zKipaNAnOM/v-deo.html
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!
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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
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?
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.
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
@@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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
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
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:)
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/
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!
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
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
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 :)
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: ua-cam.com/video/4zKipaNAnOM/v-deo.html
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
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?
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
@@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
@@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!
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
@@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?
@@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
@@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 In the Portfolio Statistics Column, you have calculated the Optimal Point(You named Optimal Portfolio) By highlighting Yellow color. How did you calculated?
@@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
💾 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/
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.
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
Would you please send me the file
@@faridsalehi7275 You can download the file for free here: ryanoconnellfinance.com/product/efficient-frontier-and-capital-allocation-line-excel-file/
I found this channel a week ago, and absolutely love it. Keep em coming Ryan!
Your comment will give me good motivation to keep them coming! I really appreciate the feedback
very usefulllllll！！！！！！！！！！！！！！！！！！！-A student from IMPERIAL COLLEGE BUSINESS SCHOOL
Thank you and hello from the US!
Thanks Ryan! Just found this and it really helped with my Investment class.
Nice video. Explained clearly in easy to understand language.
Thanks for the positive feedback!
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.
Thank you, William, I appreciate the feedback! I guess practice makes perfect haha
Best video I have watched so far. So well explained and easy to understand. Keep it up
Awesome, thank you!
thank you so much for your video Ryan!!! that's a perfect explanation!
You're very welcome! Thanks for letting me know
Great explanation and easy to understand! Thank you!
You are welcome!
BLESS YOU FOR SHOWING HOW TO GRAPH THE LINE YOURE SUPER DOPE THANK U THANK U
My pleasure Nuria!
yoo, thanks for the video. your explanation helped me on the efficient frontier graph for my assignment.
Great to hear!
thanks so much for this video! I needed this for my Investments class!
Its my pleasure! Really glad it helped
Thank you very much for putting this up. Much appreciated
My pleasure Ivan!
Hello Ryan, thank you so much. I'm from Brazil and you helped me a lot! Tks :)
Glad you enjoyed it and thanks for the feedback Matheus!
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 ?
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.
@@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 ?
@@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
Thanks, Gentleman for your useful videos
My pleasure!
Thank you so much Rayan! Your video helped a lot
Glad it helped!
Your help is priceless, thank you so much
Happy to help! It is my pleasure Aji
Really cool content man! I tweaked this slightly and used the Excel Solver to find the optimal weights.
Thanks Vignesh! How do you tweak the Excel Solver?
Very cool! You save my college work XD
Glad I could help!
you're a good guy brother! thanks for the help
Much appreciated my friend!
Thanks. great. Could you please provide training for multiple-asset portfolio rather than two asset.
Hello, you can find my video on the efficient frontier for portfolios with more than two assets here: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Very usefull man! Greetings from Chile
Thank you and greetings from the USA!
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?
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?
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.
@@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.
Really nice work!
Thank you!
how to know the risk free rate for 12 month in 1 year? and is correlation is same as risk free rate?
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: ua-cam.com/video/ZOBeh-utLTE/v-deo.html
Correlation is not the same as the risk free rate
Should you use the geometric mean or the simple average? And why? The geometric mean would be a more realistic long term return.
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
Keep doing great work, Ryan🎉
Thank you Jawad! Will do!
Great video again ! 😊
Thank you 🤗
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:)
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
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.
Jesper, the solver would give you an even more accurate result. I have another video on that topic here:
ua-cam.com/video/4zKipaNAnOM/v-deo.html
COOL.. IM HELPED
Very glad to hear!
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
PLS CAN YOU GUIDE ME ON HOW TO DO THIS. THANK YOU
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%
Hi Ryan, just wanted to ask, should we lock the Risk free asset while computing the Sharpe ratio formula
Yes! That is the one part of this video I messed up :(
king!!! THANK YOU
Happy to help!
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.
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
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?
Great content as always. I highly recommend using Excel without a mouse as that will separate you further from the rest. Cheers.
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
why we have not use "ln" to cal. the returns like in other times ..?
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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
good video thanks but you forgot to lock in your rfr cells a couple of times
Thank you!
My pleasure!
Why do you choose population variance formula insted of sample population formula?
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
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!
I would recommend using the natural log of returns! In fact, I I have a new video out now using this method: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
@@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
@@RyanOConnellCFA link? :)
@@austinbrown2726 Here is the link to the new efficient frontier video! ua-cam.com/video/AGjsvdDMyhE/v-deo.html
@@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: ua-cam.com/video/ftXX4nZJ4pk/v-deo.html
why do you allocate weight of CAL to 200% ? Thank you for your helpful video 🥰
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.
Thank you.
does everything compute the same if we use annualized return and sd figures?
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: ua-cam.com/video/TFxk_CyK6g8/v-deo.html
Really helpful, keep the good work up brother
Thank you and will do brother
@@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?
This video answered all of my questions than youu
My pleasure Murat
for the Portfolio sharpe ratio u didn’t lock in the risk free rate. should we be cell locking that?
Good catch! You should absolutely be locking that cell
Ryan O'Connell i really need your guidance, i've got a uni assignment in modern portfolio theory.
Hey Timothy, what is the hold up?
@@RyanOConnellCFA is it possible to share with you the assignment via email?
thanks brother
My pleasure!
great video about the efficient frontier, how would the calculation change if you add 1more stock to the portfolio?
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: ua-cam.com/video/4zKipaNAnOM/v-deo.html
Hi buddy, I think there is error in Sharpe ratio, Risk free rate is not freezes, you must freez that too. Kindly check it.
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!
Hi, how do you find the tangency portfolio with two assets and how do you find the weights for the two assets
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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Thanks for the video very helpful. How would you calculate the covariance on a 3 stocks?
You're welcome Parker! Check out this video where I make a covariance matrix on a 4 stock portfolio: ua-cam.com/video/4zKipaNAnOM/v-deo.html
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?
nice. thank you.
No problem!
What is the OPTIMAL PORTFOLIO formula in the RETURN line?
The portfolio with the highest Sharpe ratio
I dont know why we have 200% in weights, please explain it for me
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.
Hi, why do we use 0,100 and 200% CAL? Can someone please explain this?
hi, for the risk free rate if i count for 10 years should it be 0.02/3600?
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
I didn't get the tangent line on the graph, it only shows the orange fot of the optimal portfolio
It is difficult for me to tell what is going wrong from this comment. You may need to follow the steps once again
Just Amazing
Thank you!
Great
Thank you!
using 2% for the risk free rate, what about calculating for 6 different asset while using daily returns??.
hy ryan how if we have 10 asset? how to make efficient frontier curve?
My new Efficient Frontier video on portfolios with more than 2 assets can be found here: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Great video Ryan! Can you do it with a 10 assets portfolio? How to set the weights?
Thank you
Luca, I've been thinking about revisiting this video with a larger portfolio. I likely will be it won't be for a while
How are you getting the VXUS numbers? I don't see it anywhere in the data sheet?
please do this but with like 10 assets
I will look into this in the future, thank you for the suggestion
Hello.., how about constructing Efficient Frontier on 3 Different Assets??
I got you covered right here! ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Thank u sir, coz i am doing MBA Thesis on Portfolio Construction, thz for ur kindness
@@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: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Hi dear Ryan, how can I rotate a straight line ( capital allocation line without adding data)in Excel relative to a point?
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
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:)
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/
awesome!! where do can I deposit few bucks to support your channel?
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!
@@RyanOConnellCFA will do. A word of advice regarding autocorrect when typing… don’t use it. I didn’t proofread my comment, hence, the outcome 😅.
@@martincontreras6031 Haha thank you for that! Sticking to a computer rather than a smart phone helps with these type of issues!
Thanks you
My pleasure!
Hi Ryan, i have never come across sharpe ratio in per cent. How is that to be interpreted? Thanks a million in advance
Stephan, you could just change it to decimal form instead of percentage if you prefer. It shouldn't change the outcome
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
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
can you do for the three-stock portfolio ?
I can look into doing this sometime down the road my friend
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 :)
💾 Download the file created in this video free here: ryanoconnellcfa.com/product/efficient-frontier-and-capital-allocation-line-excel-file/
What if I have 30 securities in my portfolio?
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: ua-cam.com/video/4zKipaNAnOM/v-deo.html
Just one thing, for the sharp ratio, you didnt lock the Rfr there for it took 0% for every other data row.
Yup, good catch! This is fixed in the downloadable file
Beautiful
Thank you!
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.
Hello Bobby, please take a look at the description. I re-posted the file with a link to Github
Why did you use simple returns (Pt/Pt-1)-1 and not compounded returns (ln(Pt/Pt-1) ?
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
ryan. i cannot download the file. its missing. btw thank you for the knowledge!
Just updated with a Github link!
You didn't lock the risk-free rate cell in the Sharpe ratio calculation at 5:27
i tried downloading your file, but it doesnt seem to work. It says Page Not Found (404)
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
The risk-free rate was not locked (F4) in the video.
Good catch! I've got it fixed in the downloadable file
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?
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
@@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
@@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!
Hey amazing content. Why not use the function stdev.s for standard deviation?
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.
in portfolio weights, we set each weights randomly?
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
@@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?
@@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
@@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 :(
@@tientrinh3.2 You can actually perform thousands of scenarios an instant if you follow the trick I used here: ua-cam.com/video/4zKipaNAnOM/v-deo.html
should lock the cell referencing "risk free rate"
You are correct Stephanie, this error is fixed in the free download file in the description
05:32 i think you didn't lock 'I7' (risk free rate)
tip top
How to find optimal portfolio?
It is the portfolio with the highest Sharpe Ratio
Anyone send me the file in this video, please.
You can download that here: ryanoconnellfinance.com/product/efficient-frontier-and-capital-allocation-line-excel-file/
What is the codes for Optimal Portfolio Row?
Hello Animesh, I'd love to help you but could you please be a little more specific with your question?
@@RyanOConnellCFA In the Portfolio Statistics Column, you have calculated the Optimal Point(You named Optimal Portfolio) By highlighting Yellow color. How did you calculated?
@@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
@@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
Fking life saver!!!
Lol I'm glad you thought so!
Yur sop 500 index is wrong
Please elaborate on why you think the S&P 500 index is wrong