This is such an important concept that so many of my friends who trade and invest don't understand at all. So many keep going for small-mid caps that swing wildly and over the course of two years ends up under performing QQQ
WOW! I'm taking a Portfolio Risk Managment class right now and this video summed up the first couple weeks of the class! Thank you so much for this great example. It really illistrates everything I have now been exposed to and see practical reasons for learning all the building blocks. I really enjoyed the video and played with the spreadsheet for hours looking up different stocks to come up with better mix of stocks with the current financial situation. Really interesting!!! Thanks!!!
HI DAVID, I liked your video. You explained everything in one video and did not waste time on one point. Please also post other videos related with optimization methods.
Great video! One thing I noticed is that this is implicitly the efficient frontier in the case where short-selling is not allowed. Note the bottom portion of your graph tails off to become kind of linear. To get the frontier with short-selling, you can specify the min argument of the RANDARRAY function to be negative (e.g. -1) as the default argument is 0. This will allow negative numbers into your portfolio weights. It will also produce a bunch of outlier portfolios, but all you have to do is change the scale on the axes to see the hyperbola.
Excellent demonstration professor. What I wouldn't do, is to make yearly returns in the way you did it @ 20:10. We are talking about simple compounding for up to 1 year, so I'd recommend x252
your this video is so helpful for me but please can you tell me how to you use rand between instead of rand array because i have older version of excel
@@DavidJohnk When using the =SQRT(VARCOVAR/MMULT(TRANSPOSE(STDDEV),STDDEV)), I get #NUM! error in the correlation matrix because of negative numbers. Is the square root function necessary?
hello, could you please explain why you didn't use the normal sharp ratio ? where we subtract the risk free rate and why did you subtract the expected return from the daily returns ? also can i subtract the risk free rate in this example ?
Hello! Great video, sums up the whole portfolio construction essential concepts. I’d like to know why some people use the log of the returns before proceeding to calculate everything you did and some don’t. Really appreciate it!
Good question! log returns assume continuous compounding. That has properties which are useful that periodically compounded returns don't. Both work for demonstration purposes.
at first I said that this video was too long, but as I watched i was immersed that i didn't want it to end -not really xD. anyways, it was an excellent explanation. wish you had to went deeper at the end regarding which point is the best? i mean it depends on the investor willingness of taking the extra risk right.. thanks again
Ali, this video just brushes on finding the efficient frontier. You might want to do some additional research on something called the Capital Allocation Line.
Thank you very much for the great help provided in this video. But I have a question, in the Sharpe ratio calculation, why the risk free return is not introduced? I understand that the Sharpe formula is: Asset return - risk free return / standard deviation. Thank you very much for your answer!
Thank you such a detailed step by step explanation. Could you also explain how to create a Capital allocation line in case of three or more security portfolio. Thank you.
Great question, I've actually been thinking of producing a video that does that. Keep an eye out for it by subscribing to my channel and enabling notifications if you haven't already.
@@DavidJohnk My frontier looks inverted for some reason. In. stead of trailing upward and to the right, it trails downward and to the right. Any ideas? I used same stocks daily return for 1 year (2023-2024)
Great video! I just have a question, would it be possible to know the portfolio composition of each of the risk/return profiles generated in the data table? Thank you
You would buy/sell shares of stocks to achieve desired individual security weights in the portfolio according to number of shares * market price/share of each. If you have a $900 portfolio and purchase an additional share of a new stock worth $100/share, the total portfolio will now be $1000, and the weight of the new stock will be 10%. It's actually pretty simple; picture a pie chart with each security, the amount of money in percent a slice of the pie.
This is by far, the most understandable efficient frontier explanation with step by step video. Anyway, i am exploring the Efficient Frontier concept with regards to oil & gas industry application; where, the expected return is not at daily rate as stock prices and is accessed at NPV at different scenarios. Do you think its a good idea to bring in the model for oil & gas industry application? Appreciate any thoughts.
hey there! Using analysis toolpak on Excel to carry out the correlation matrix I get quite different results from your method of doing it (as well as the method without square rooting it), is there a preferred way to do it/any reason why to pick one over the other?
HI, i choose 91 days timeframe, and it gave me negative returns ? also what time frame is the best to use ? is it based on the time i've been investing so far? or the time im planning in investing.
Hi Pierre, when selecting your securites, it's best to use those with positive historical returns if you want a positive result. Time frame is irrelevent pertaining to your question. Hope that helps!
@@DavidJohnk Thank you very much for the fast answer. Mathematically i still do not understand why you take the square root in the formula ? Thanks again.
@@waschbaer1 You know what, you're correct! I was doing the formula from memory and made a mistake, you don't have to take the square root, that's an error. Thanks, I made a note on the description, as you don't really need it for the portfolio optimization part.
Really great video and pleasure to watch and listen. When I tried it however, my graph looked more like a blob and nothing like the nice hyperbola you arrived at.
@@DavidJohnk Thanks for replying! I did it with eighteen securities. I then tried by splitting into two lots of nine; all three attempts produced a more-or-less circular blob; none had any clear hyperbolic edge of any sort! (I then made up some data for four securities and that did work, so at least the method worked for me - just not the actual portfolio I wanted to analyse, for some reason!).
Thank you so much sir! But i am having a little problem drawing the efficient frontier, i followed your calculations step by step optimizing my portfolio. Then, when i drew my efficient frontier it didn't form a nice arc shape, they just piles up randomly. Could you please help me fix it 🙏😊
Are you using securities that have a positive return over the period? Sometimes that effects results. You might also try different securities to see if it improves the graph. Sorry I can't be more help; it's difficult when I can't see your file.
Thanks for the video, really useful! What's the difference between your correlation and the correlation in the Data Analysis tool? I seem to get different results.
I made a mistake in the video ... it's pointed out in the first paragraph of my description: "Additionally, you don't have to take the square root when calculating the correlation matrix, that was an error on my part sorry!"
Mr. David. I developed an excel sheet for 15 stocks, but my chart does not fit the effective frontier design. for 3 stocks it is ok but why number of stocks influence on design on chart?
let me describe issue. At first I thought also that was because of number of random itterations. I started to fit model to your exact stoks and timeline. I received same design as ypurs. then I changed figiures to weeks rather than days, and design has changed but not so much. but when I increase number of stocks is changing too. lat me know please, if you will have any new model in the future with many stocks and you find reasoning about issue. thanks a lot.
Hey David! Thank you for your kind explanation. It helped me a lot. Can I ask you a question? I want to know why you made this "X" value and calculated it with this value. I thought I was just paying for Daily. What does this "X" mean? I'd really appreciate it if you could give me an answer. Have a nice day!
Hi David, very good video - I struggle in the moment with a negativ value in the varcovar matrix and this results in a problem with the calculation of the Correl Matrix due the squareroot calculation. As a fact in my own example I check with the Excel function "Correl" the relationship between two stocks and it is negativ. Hope you can help me with your expertise...
Thank you so much! this video helped me so much for my final year project. May I know whether it is possible to plot a tangent line that would cross the efficient frontier in excel? Im trying to find the most optimum portfolio
Thanks. What do you do if one (or more) business was started during the period of your data? This would mean one or more of the data columns has missing values down to a certain row. How do you deal with this in the variance covariance matrix in particular? In this scenario, this business would have the same value (0 - average return) for many rows in the X section. This means this business has a very low variance, so the solver will put a big weight on this business, which is not correct.
Great video. Once the efficient frontier is generated, and the appropriate portfolio is selected, how do you determine what weighted portfolio equals that data point? example: let's say out of 10,000 randomly generated data points, you select data point 31. How do you then determine exactly what weights are required for each security to ensure you create a portfolio that would apply to data point 31?
Thank you Sir. @@stylianosartsidakis I can use Solver in Bothe risk and Return after find data points? ( i supposed i cant "reset all" , i need to put 2 rules? beside = 100% ) - My plot dont have same format as yours, is more like a ball , this is because i made a mistake somewhere , or depend of data ? Thanks again apreciatted
Hi David. I used your excel but Data Table dosent work in my notebook. I dont know which could be the problem. Do you have any idea? So i can do the graph as you did it. Thank you very much.
I followed along with everything but I used a 20-stock portfolio instead of 3. instead of using daily returns i used monthly returns since 1/1/2020. everything worked great until i got to graphing the efficient frontier. the graph is quite literally zero correlation and shows no efficient frontier. Any way to troubleshoot?
20 stocks won't work well for that, you would need many more points. You could try maximizing your portfolio return at different levels of risk and plotting those points. Hope that makes sense!
I'm using excel on Mac, when trying to produce the randarray time series following exactly your steps I only generate zeros, how do I solve this problem? :)
@@DavidJohnk Hello again Sir, fortunately it worked, except for the Randarray function but I guess my excel version is the problem...yet, I really thank you ! Your video was extremely helpful
What a great way to plot efficient frontier that’s very intuitive and best explanation, just a question: Is there a way to plot the “result line/efficient frontier line” only by excel?
Very helpful. I wonder whether it is possible to also model the MAD version in this paper Konno, H., & Yamazaki, H. (1991). Mean-absolute deviation portfolio optimization model and its applications to Tokyo stock market. Management science, 37(5), 519-531. in excel?
Hi David, thank you so much! Very useful video. I have a question: I received a negative VARCOVAR for some investments. My excel then had a problem to calculate the correct correlation. Can I take the absolut value of VARCOVAR or is this the wrong way to fix this problem?
Hi Marco, I had put that I made a mistake on calculating the correlation matrix (I noted this in the video comment section), you don't have to take the square root! So sorry about that.
Hi David, thanks for the explanations, is it possible to generate a random draw if we allow a short position on one or more stock, and the total weight still equal to 1 ?
What is a good way to graph the efficient frontier including portfolios with short positions? I messed around with the random array min and max but the plot gets zoomed out so quickly
Also- when I try to maximize the sharpe ratio (risk free asset in calculation) while allowing short positions in the solver I get an absurd portfolio composition, is that correct for this problem? Note- I get the same maximized E[R]/SDp value as you so I entered the formula in correctly
@@johnperucki8689 I'm not sure about that John. Normally when you include the risk-free asset you move along the capital market line. I need to make a video on that. By the way, I modified the Sharpe ratio by not including the risk-free rate in the numerator.
Hi David, as you've mentioned, your expected return is based on past performance. Do you have any advice on how to efficiently (low effort) incorporate any assumption about future performance into the model? Nontheless thank you for the great lecture.
you mean to include implied volatility? Would be interested too... Especially if you consider that we dont have normal distribution of returns which is a key assumption for the model
thank you for the tutorial. what if the stock pays annual dividends ? the stock return considering just the stock prices doesnt show the complete picture, if there arent any dividends included, right ? so how do i it with a given annual dividend yield ?
You're correct you should definitely take dividends, and also stock splits into account! Finance.yahoo.com has historic price data available in the form of "adjusted close" which takes that into account.
Is there a limit to the number of Assets in a portfolio when using this method? I am analyzing 65 assets and it looks like after 5-6 assets the graph converges to a circle.
With that many assets, you would have to plot many more points, probably beyond the power of Excel. I'm not sure solver could handle it, but you could maximize portfolio return at incremental levels of risk to plot the efficient frontier.
Hi, I'm working on google sheets. I don't normally have an extensive use of excel therefore I never needed to download it. But I surely will after this video. I've followed step by step process and had success at every point up until now. since google sheets do not have a What-If analysis data table option. I was wondering if there's a workaround to populating trial table automatically on Google Sheet. I have been trying to find a way, but I'm very confused. Can anyone share a solution if they are facing similar hurdle. Thank You!
My Whatif Analysis datatable started to show the same return and risk numbers after a few hundreds of random sets, what could be the reason? I have changed iteration setting in options but still doesn't work. Thank you!
I'm not sure ... I just checked mine and the same thing happens after several thousand! I might have to do with the power of the computer? I even pressed F9 to force manual calculations and id didn't fix it. Interesting! Please, let me know if you figure it out.
@@DavidJohnk Thanks for checking on this! I ended up reducing the number of trials to 5000, options setting - auto calculation, iteration enabled for max 10000 times with max change 0.1. It worked but it could be the power of the computer too as I left it running overnight. Thanks again for the video.
Hi, I tried the same method for making an Efficient Frontier with 7 securities. I also generated 10000 trials but the graph doesn't look like yours Efficient Frontier with that curve on the left side. I just want to ask if this method doesn't work for more than 3 securities.
With more securities, you would need many more points. You can try maximizing the portfolio return at several levels of risk starting at the minimum variance portfolio. That would give you the outline of the efficient frontier. Hope that makes sense!
Amazing demonstration and great excel tips. About the excess return, is it a valid way to calculate it? I would calculate it as the stock return above a risk free return as reference instead.
@@lucassavosardaro3451 thank you for the reply. About the first sentence of your comment I can’t recall seeing it somewhere but I guess this can be a type of benchmark
Hi Sir, Thanks for the quality of the video. However I don't have in my Excel the Randarray Function. Do you have any alternative please? Thank you in advance.
Is using simple average for calculating average daily returns really a proper way to do it? Isnt using log returns or geometric mean a proper way for that?
I agree, ln or geo mean are more proper. I'll be re-recording this (using ln returns) in the near future with some interesting updates, keep an eye out for it!
I am very green field - basic understanding of markets etc. Accumulated stocks over a period of time. I have 34 stocks in the portfolio. I saw this video and calculated my portfolio's Std Dev and Expected return and Mod Sharpe Ratio. Since I have certain weighted stocks in my portfolio already, the choice of weights can now be made better. So I set up the random array and the Data Table as you did. But the problem is - if I have to pick a risk/reward combination on the efficient frontier, it is hard for me to grab the weightage of stocks. Because the random generator is kicking in somehow and changing the weights. I guess my question is - how do you get the weightage values corresponding to a risk/return point on the efficient frontier? Thanks in advance for your response.
I think you should use solver to solve for a specific return/std. An other thing you can do is just try to create the portfolio for wich the sharp ratio (re/std) is optimal. This means that for every x more risk you take on you will get the maximum possible extra return.
I used this method in sheet, the frontier turns out curved.When I tried to assimilate this in Excel offline, my capm come out as linear line instead of curved line. I increased the decimal places, still no changes. Help,please.
This is such an important concept that so many of my friends who trade and invest don't understand at all. So many keep going for small-mid caps that swing wildly and over the course of two years ends up under performing QQQ
Actually, in that short video is the central concept that won Markowitz the Nobel Prize!
WOW! I'm taking a Portfolio Risk Managment class right now and this video summed up the first couple weeks of the class! Thank you so much for this great example. It really illistrates everything I have now been exposed to and see practical reasons for learning all the building blocks. I really enjoyed the video and played with the spreadsheet for hours looking up different stocks to come up with better mix of stocks with the current financial situation. Really interesting!!! Thanks!!!
Thanks! Please note I made an error on the correlation matrix, you don't have to take the square root.
This was just the refresher i needed to help me better understand what Portfolio Visualizer tool is doing. Thank you!
I'll have to check out that tool
I've just finished a Portfolio Management course and your video really sums up everything, thanks
Glad it was helpful!
Glad it was a good summary for you!
this is the best video out there for efficient frontier in excel
Thanks for the positive feedback!
HI DAVID, I liked your video. You explained everything in one video and did not waste time on one point. Please also post other videos related with optimization methods.
Great video! One thing I noticed is that this is implicitly the efficient frontier in the case where short-selling is not allowed. Note the bottom portion of your graph tails off to become kind of linear. To get the frontier with short-selling, you can specify the min argument of the RANDARRAY function to be negative (e.g. -1) as the default argument is 0. This will allow negative numbers into your portfolio weights. It will also produce a bunch of outlier portfolios, but all you have to do is change the scale on the axes to see the hyperbola.
Good point.
Excellent demonstration professor. What I wouldn't do, is to make yearly returns in the way you did it @ 20:10. We are talking about simple compounding for up to 1 year, so I'd recommend x252
Good idea!
your this video is so helpful for me but please can you tell me how to you use rand between instead of rand array because i have older version of excel
You are a wizard! that monte carlo trick, is a life saver.. i've gotta go through this again!
Ha, glad it was useful!
Thx for sharing this! One of the most useful videos on UA-cam!
Glad it was helpful!
I can't express how much love I have towards you right now
Ha, glad it helped!
@@DavidJohnk When using the =SQRT(VARCOVAR/MMULT(TRANSPOSE(STDDEV),STDDEV)), I get #NUM! error in the correlation matrix because of negative numbers. Is the square root function necessary?
@@falinoluiz5962 Hi, you don't have to sqrt. Sorry, I pointed the error out in the description above, but it's hard to see!
It is an excellent tutorial. Many many thanks for the outstanding effort.
Thanks for the positive feedback!
hello, could you please explain why you didn't use the normal sharp ratio ?
where we subtract the risk free rate and why did you subtract the expected return from the daily
returns ? also can i subtract the risk free rate in this example ?
No reason other than I didn't want to go find it (the risk-free rate). It didn't really matter in this example.
@@DavidJohnk thank you
Hello! Great video, sums up the whole portfolio construction essential concepts. I’d like to know why some people use the log of the returns before proceeding to calculate everything you did and some don’t. Really appreciate it!
Good question! log returns assume continuous compounding. That has properties which are useful that periodically compounded returns don't. Both work for demonstration purposes.
To be honest I would have learnt atleast 15 things in this one video
Thank you for this wonderful and very clear guide to a very complex topic to deal with!
thank you sir, this is the best video on this topic.
You're welcome~
You're welcome, glad it helped! Check out my latest video that has Lambda functions for portfolio calculations.
at first I said that this video was too long, but as I watched i was immersed that i didn't want it to end -not really xD. anyways, it was an excellent explanation. wish you had to went deeper at the end regarding which point is the best? i mean it depends on the investor willingness of taking the extra risk right.. thanks again
Ali, this video just brushes on finding the efficient frontier. You might want to do some additional research on something called the Capital Allocation Line.
@@DavidJohnk I will read about CAL more, thanks for the reply David. Hope you have a nice day
Such a great lecture. Thank you David!
You're very welcome, glad it helped
Thank you very much for the great help provided in this video. But I have a question, in the Sharpe ratio calculation, why the risk free return is not introduced? I understand that the Sharpe formula is: Asset return - risk free return / standard deviation. Thank you very much for your answer!
Hi, I didn't subract it because it was negligible during the period i was using. Technically you should.
Thank you such a detailed step by step explanation. Could you also explain how to create a Capital allocation line in case of three or more security portfolio. Thank you.
Great question, I've actually been thinking of producing a video that does that. Keep an eye out for it by subscribing to my channel and enabling notifications if you haven't already.
@@DavidJohnk Sure, Thank you very much!
Thank you! You are very generous with your knowledge. God bless you!
Hi David, thanks for the positive feedback!
Amazing explanation! much appreciated!
Thanks for the positive feedback!
I'm not familiar with the modified Sharpe. Is it basically just the ratio without subtracting the return of a risk-free asset in the numerator?
Yes, I called it that because I didn't want to spend time getting the risk free rate.
@@DavidJohnk My frontier looks inverted for some reason. In. stead of trailing upward and to the right, it trails downward and to the right. Any ideas? I used same stocks daily return for 1 year (2023-2024)
Great video David and the plot was a lot of ilustrative.
Thanks for the positive feedback Jorge!
Great video! I just have a question, would it be possible to know the portfolio composition of each of the risk/return profiles generated in the data table? Thank you
Yes, absolutely. Just add the columns on the data table.
This is Excellent Video Sir!!! Thank you!!!
Glad it helped, thanks for the positive feedback.
Amazing video! This helped me alot for my Master. Thank you so much!
Glad it helped!
If I buy a share in a new company, how do I distribute the percentages of weights again with the old shares?
You would buy/sell shares of stocks to achieve desired individual security weights in the portfolio according to number of shares * market price/share of each. If you have a $900 portfolio and purchase an additional share of a new stock worth $100/share, the total portfolio will now be $1000, and the weight of the new stock will be 10%. It's actually pretty simple; picture a pie chart with each security, the amount of money in percent a slice of the pie.
This is by far, the most understandable efficient frontier explanation with step by step video.
Anyway, i am exploring the Efficient Frontier concept with regards to oil & gas industry application; where, the expected return is not at daily rate as stock prices and is accessed at NPV at different scenarios.
Do you think its a good idea to bring in the model for oil & gas industry application?
Appreciate any thoughts.
I could work, it doesn't have to be daily in frequency.
Thank you!!!! So helpful for my investments and portfolio management class!
Glad it helped!
Very instructive video and easy to understand. Thanks you so much for the link, this make more understood.
Thanks, glad it was useful.
Thank you so much for your video.
Glad it helped!
hey there! Using analysis toolpak on Excel to carry out the correlation matrix I get quite different results from your method of doing it (as well as the method without square rooting it), is there a preferred way to do it/any reason why to pick one over the other?
It might differ because one is calculated based on samples and the other population.
Excellent video
Thanks!
HI, i choose 91 days timeframe, and it gave me negative returns ? also what time frame is the best to use ? is it based on the time i've been investing so far? or the time im planning in investing.
Hi Pierre, when selecting your securites, it's best to use those with positive historical returns if you want a positive result. Time frame is irrelevent pertaining to your question. Hope that helps!
Thank you so much! Amazing video
Glad it was useful!
David - at minute 10:40 - why do you square the CORRELATION formula ?
I take the square root at 10:40, not square. That ^1/2 means square root in the formula. Hope that helps!
@@DavidJohnk Thank you very much for the fast answer. Mathematically i still do not understand why you take the square root in the formula ? Thanks again.
@@waschbaer1 You know what, you're correct! I was doing the formula from memory and made a mistake, you don't have to take the square root, that's an error. Thanks, I made a note on the description, as you don't really need it for the portfolio optimization part.
Really great video and pleasure to watch and listen. When I tried it however, my graph looked more like a blob and nothing like the nice hyperbola you arrived at.
Interesting, did you try the three securities I used? It always works for me, but it could be something about the securities you used ...
@@DavidJohnk Thanks for replying! I did it with eighteen securities. I then tried by splitting into two lots of nine; all three attempts produced a more-or-less circular blob; none had any clear hyperbolic edge of any sort! (I then made up some data for four securities and that did work, so at least the method worked for me - just not the actual portfolio I wanted to analyse, for some reason!).
@@kyrisaphiris with that many securities you would have to plot millions of points.
Not very doable on Excel
@@DavidJohnk So that's what it was! Thank you so much for letting me know.
Thank you so much sir! But i am having a little problem drawing the efficient frontier, i followed your calculations step by step optimizing my portfolio. Then, when i drew my efficient frontier it didn't form a nice arc shape, they just piles up randomly. Could you please help me fix it 🙏😊
Are you using securities that have a positive return over the period? Sometimes that effects results. You might also try different securities to see if it improves the graph. Sorry I can't be more help; it's difficult when I can't see your file.
Thank you, excellent presentation
You're welcome!
Thanks for the video, really useful! What's the difference between your correlation and the correlation in the Data Analysis tool? I seem to get different results.
I made a mistake in the video ... it's pointed out in the first paragraph of my description: "Additionally, you don't have to take the square root when calculating the correlation matrix, that was an error on my part sorry!"
@@DavidJohnk Many thanks! Sorry i missed that comment in the description. I can see that ties up perfectly when i make that change now. Thanks again!
@@garywedderburn6978 No problem, I would re-record the video but it's getting too many views!
Mr. David. I developed an excel sheet for 15 stocks, but my chart does not fit the effective frontier design. for 3 stocks it is ok but why number of stocks influence on design on chart?
I would have to see your spreadsheet to be sure, but you could increase the number of trials and see what it does.
I tried 1 000 000 rand, for 5 stocks and received design like head of fish :), design is reasonable but without uppear tail part :)
let me describe issue. At first I thought also that was because of number of random itterations. I started to fit model to your exact stoks and timeline. I received same design as ypurs. then I changed figiures to weeks rather than days, and design has changed but not so much. but when I increase number of stocks is changing too. lat me know please, if you will have any new model in the future with many stocks and you find reasoning about issue. thanks a lot.
Hey David! Thank you for your kind explanation. It helped me a lot.
Can I ask you a question?
I want to know why you made this "X" value and calculated it with this value. I thought I was just paying for Daily. What does this "X" mean?
I'd really appreciate it if you could give me an answer. Have a nice day!
It's the daily returns in excess of the return's arithmatic average. It's used for calculating the variance-covariance matrix. Hope that helps!
Hi David, very good video - I struggle in the moment with a negativ value in the varcovar matrix and this results in a problem with the calculation of the Correl Matrix due the squareroot calculation. As a fact in my own example I check with the Excel function "Correl" the relationship between two stocks and it is negativ. Hope you can help me with your expertise...
Hello, sorry about the problems in the correlation matrix, you don't have to take the square root there. It was an error on my part. Sorry again!
@@DavidJohnk Was dealing with the same problem as @signal4friends488, thank you for the tip! Everything seems to work now. Phenomenal video!
Thank you sir, helped me a lot with my Masters thesis.
I'm glad it helped!
Thank you so much! this video helped me so much for my final year project. May I know whether it is possible to plot a tangent line that would cross the efficient frontier in excel? Im trying to find the most optimum portfolio
Minimum variance, or sharpe ratio, use Solver
the trick with the data table is not working for me. can you help me
You might try a different computer. Not sure what the problems is, sorry. Maybe try it with less trials for testing purposes.
God bless people like you, cheers
Does this model includes correlation between the assets?
Yes but in the video, I made an error. Don't take the square root when calculating the correlation matrix.
Thanks. What do you do if one (or more) business was started during the period of your data? This would mean one or more of the data columns has missing values down to a certain row. How do you deal with this in the variance covariance matrix in particular?
In this scenario, this business would have the same value (0 - average return) for many rows in the X section. This means this business has a very low variance, so the solver will put a big weight on this business, which is not correct.
I don't know how you would accomodate missing data, I would probably drop that security from consideration for that reason.
May I know is there possible to draft a "efficient frontier" when the expected return of stock becomes negative?
I've never tried it, but it should be, I would think that stock with a 100% weight would take you negative on the y-axis.
I want to have a teacher like you 😁
Ha, thanks for the positive feedback!
Great video. Once the efficient frontier is generated, and the appropriate portfolio is selected, how do you determine what weighted portfolio equals that data point? example: let's say out of 10,000 randomly generated data points, you select data point 31. How do you then determine exactly what weights are required for each security to ensure you create a portfolio that would apply to data point 31?
use solver again and put constraints for the sd and the ER of that point
Thank you Sir. @@stylianosartsidakis I can use Solver in Bothe risk and Return after find data points? ( i supposed i cant "reset all" , i need to put 2 rules? beside = 100% ) - My plot dont have same format as yours, is more like a ball , this is because i made a mistake somewhere , or depend of data ? Thanks again apreciatted
is it the same procedure if it was monthly return instead of daily return?
Yes, you would probably want 5 or 6 years to get enough observations.
Thanks !!
Beautiful demonstration.
Thanks!
Hi David. I used your excel but Data Table dosent work in my notebook. I dont know which could be the problem. Do you have any idea? So i can do the graph as you did it. Thank you very much.
Hi, not sure what the problem could be, sorry.
I followed along with everything but I used a 20-stock portfolio instead of 3. instead of using daily returns i used monthly returns since 1/1/2020. everything worked great until i got to graphing the efficient frontier. the graph is quite literally zero correlation and shows no efficient frontier. Any way to troubleshoot?
20 stocks won't work well for that, you would need many more points. You could try maximizing your portfolio return at different levels of risk and plotting those points. Hope that makes sense!
@@DavidJohnk i appreciate the advice! I'll let you know if it works!
I'm using excel on Mac, when trying to produce the randarray time series following exactly your steps I only generate zeros, how do I solve this problem? :)
I'm not an Apple guy, hopefully someone else can help.
hello Sir would this work for like 40 securities? Thank you!
It might not work on Excel, that's quite a few securities.
@@DavidJohnk Hello again Sir, fortunately it worked, except for the Randarray function but I guess my excel version is the problem...yet, I really thank you ! Your video was extremely helpful
Try =rand() in each cell
Well explained and demonstrsted... Thank you!!!
Glad it was useful!
Magnificent, but one thing please, why you’re not using (Ln) to calculate the return?
Thanks! I've used continuous (ln) returns in the past, but % return is simpler understand for the beginner.
Are these calculations is used also for portfolio with 5 assets , or it changes ?
What a great way to plot efficient frontier that’s very intuitive and best explanation, just a question: Is there a way to plot the “result line/efficient frontier line” only by excel?
You can use solver to find the maximum return at intervals starting from the minimum variance portfolio. Hope that makes sense.
Very helpful. I wonder whether it is possible to also model the MAD version in this paper Konno, H., & Yamazaki, H. (1991). Mean-absolute deviation portfolio optimization model and its applications to Tokyo stock market. Management science, 37(5), 519-531. in excel?
Glad it was interesting. The MAD version you mention is easier for larger optimization problems, I'll take a look.
@@DavidJohnk Wonderful
I am having error when calculating correlation 4 of the slot show #NUM error
See my comment in the description, you don't have to take the square root. That might be the problem.
Very useful and understandable video. Thank you
You're welcome!
when we calculate the sharp ratio we can utilise the Risk free rate ?
For sure!
thankyou , and we will work with the annual expected return not the daily? sharp ratio= (Annula return-RF)/ annual Standard Deviation)
I love you so much 사랑해요ㅎㅎㅎㅎㅎ 알라뷰~~~~ 덕분에 공부 잘 했습니다!
Ha, thanks!
Excellent tutorial. Thank you!
Glad you enjoyed it!
Hi David, thank you so much! Very useful video. I have a question: I received a negative VARCOVAR for some investments. My excel then had a problem to calculate the correct correlation. Can I take the absolut value of VARCOVAR or is this the wrong way to fix this problem?
Hi Marco, I had put that I made a mistake on calculating the correlation matrix (I noted this in the video comment section), you don't have to take the square root! So sorry about that.
@@DavidJohnk Hi David, perfect my correlation matrix works perfect now. Great work which you provided, thanks a lot :)
Hi David, thanks for the explanations,
is it possible to generate a random draw if we allow a short position on one or more stock, and the total weight still equal to 1 ?
Yes, I did it right here: ua-cam.com/video/IRa64LEERhE/v-deo.html
I've seen different models where they've annualized the E(r) and stddev, in your model you are averaging the daily returns, is that correct?
That's correct, no need to annualize as long as everything is daily frequency. You could also do weekly, monthly etc.
@@DavidJohnk thank you!
What is a good way to graph the efficient frontier including portfolios with short positions? I messed around with the random array min and max but the plot gets zoomed out so quickly
Also- when I try to maximize the sharpe ratio (risk free asset in calculation) while allowing short positions in the solver I get an absurd portfolio composition, is that correct for this problem?
Note- I get the same maximized E[R]/SDp value as you so I entered the formula in correctly
I think you're on the right track, randomly allow negative holding weights.
@@johnperucki8689 I'm not sure about that John. Normally when you include the risk-free asset you move along the capital market line. I need to make a video on that. By the way, I modified the Sharpe ratio by not including the risk-free rate in the numerator.
Thank you so much for the video and for responding! These are the best finance lectures on UA-cam!
Hi David, as you've mentioned, your expected return is based on past performance. Do you have any advice on how to efficiently (low effort) incorporate any assumption about future performance into the model? Nontheless thank you for the great lecture.
you mean to include implied volatility? Would be interested too... Especially if you consider that we dont have normal distribution of returns which is a key assumption for the model
@@lotuskillerlp4736 That might be a good addition to the model!
Absolutely gem of a video❤
Incredibly done Sir!!
Thanks!
@@DavidJohnk what is X matrix
@@sudhirsharma8222 It's each securities average return subtracted from it's periodic return. You need it to calculate the varcovar matrix.
Should we not be considered the beta factor ?
Good question, this video demonstrates how diversification can lower risk overall portfolio risk, portfolio beta could be considered also.
thank you for the tutorial. what if the stock pays annual dividends ? the stock return considering just the stock prices doesnt show the complete picture, if there arent any dividends included, right ? so how do i it with a given annual dividend yield ?
You're correct you should definitely take dividends, and also stock splits into account! Finance.yahoo.com has historic price data available in the form of "adjusted close" which takes that into account.
Why do you call it sharpe ratio when you don't work with the risk free rate which is crucial for the sharpe ratio?
I thought I called it a "modified Sharpe ratio" for that reason. Sorry for the confusion.
I'm getting negative expected returns because of the recession for the past 3 months in the 3 bank stocks I selected. The frontier is a straight line.
Yes it works better if you pick stocks that have postive returns over your sample period, good point!
Thanks for this..!
Glad it was useful
what about the capital allocation line?
I need to make a updated video with that included
quick question, why didn't you use the Correlation or Covariance formula straight away rather than going through the detailed formulae?
Good question, just showing how it's done using matrix algebra. I was going to show both ways, but the video get's too long (too much information).
Is there a limit to the number of Assets in a portfolio when using this method? I am analyzing 65 assets and it looks like after 5-6 assets the graph converges to a circle.
With that many assets, you would have to plot many more points, probably beyond the power of Excel. I'm not sure solver could handle it, but you could maximize portfolio return at incremental levels of risk to plot the efficient frontier.
Hi, I'm working on google sheets. I don't normally have an extensive use of excel therefore I never needed to download it. But I surely will after this video. I've followed step by step process and had success at every point up until now. since google sheets do not have a What-If analysis data table option. I was wondering if there's a workaround to populating trial table automatically on Google Sheet. I have been trying to find a way, but I'm very confused. Can anyone share a solution if they are facing similar hurdle. Thank You!
I don't know too much about Google Sheets, I don't think it has a solver-like tool either though.
My Whatif Analysis datatable started to show the same return and risk numbers after a few hundreds of random sets, what could be the reason? I have changed iteration setting in options but still doesn't work. Thank you!
I'm not sure ... I just checked mine and the same thing happens after several thousand! I might have to do with the power of the computer? I even pressed F9 to force manual calculations and id didn't fix it. Interesting! Please, let me know if you figure it out.
@@DavidJohnk Thanks for checking on this! I ended up reducing the number of trials to 5000, options setting - auto calculation, iteration enabled for max 10000 times with max change 0.1. It worked but it could be the power of the computer too as I left it running overnight. Thanks again for the video.
@@mikozhou104 Thanks for the update!
=rand() in each cell you want a uniformly distributed number between 0 and 1.
Why is your Sharpe Ratio that is being used in solver under 1? I successfully followed your model and have the same problem.
Hi, I tried the same method for making an Efficient Frontier with 7 securities. I also generated 10000 trials but the graph doesn't look like yours Efficient Frontier with that curve on the left side. I just want to ask if this method doesn't work for more than 3 securities.
With more securities, you would need many more points. You can try maximizing the portfolio return at several levels of risk starting at the minimum variance portfolio. That would give you the outline of the efficient frontier. Hope that makes sense!
Very useful video, thank you sir
You're welcome, glad it helped!
Amazing demonstration and great excel tips. About the excess return, is it a valid way to calculate it? I would calculate it as the stock return above a risk free return as reference instead.
Excess return in portfolio management is the return above or below the average return. It is needed to calculate covariance and variance.
@@lucassavosardaro3451 thank you for the reply. About the first sentence of your comment I can’t recall seeing it somewhere but I guess this can be a type of benchmark
Hi Sir, Thanks for the quality of the video. However I don't have in my Excel the Randarray Function. Do you have any alternative please?
Thank you in advance.
You can use the =rand( ) in each individual cell
hey can you put the link to the excel sheet itself please
Your video is helpful
Glad it helped!
Is using simple average for calculating average daily returns really a proper way to do it?
Isnt using log returns or geometric mean a proper way for that?
I agree, ln or geo mean are more proper. I'll be re-recording this (using ln returns) in the near future with some interesting updates, keep an eye out for it!
@@DavidJohnk great, thank you, Sir! Subbed :)
I am very green field - basic understanding of markets etc. Accumulated stocks over a period of time. I have 34 stocks in the portfolio. I saw this video and calculated my portfolio's Std Dev and Expected return and Mod Sharpe Ratio. Since I have certain weighted stocks in my portfolio already, the choice of weights can now be made better. So I set up the random array and the Data Table as you did. But the problem is - if I have to pick a risk/reward combination on the efficient frontier, it is hard for me to grab the weightage of stocks. Because the random generator is kicking in somehow and changing the weights.
I guess my question is - how do you get the weightage values corresponding to a risk/return point on the efficient frontier? Thanks in advance for your response.
I think you should use solver to solve for a specific return/std. An other thing you can do is just try to create the portfolio for wich the sharp ratio (re/std) is optimal. This means that for every x more risk you take on you will get the maximum possible extra return.
I used this method in sheet, the frontier turns out curved.When I tried to assimilate this in Excel offline, my capm come out as linear line instead of curved line. I increased the decimal places, still no changes. Help,please.