I have so much respect for how a good explainer you are. This video is amazing. Very clear, structured and most importanty calm (good comfort for ones who find these processes taunting already).
I did my senior project on optimization for my math degree and I tried applying it to my investments. I kept getting stuck and frustrated and your video showcased what my problems were: I didn't have a good objective function (I didn't know about the Sharpe Ratio) and I wasn't using my tool correctly (I didn't know about the Ctrl+shift+enter you had to do for matrix multiplication). Thank you so much for explaining these things, I'm going to try these out as soon as I can.
Rather than just using the closing prices, adjusted prices that consider dividends should be used. ATT is low because their dividend of about 5% is being excluded. The adjusted prices are available from Yahoo. Also, the annual return should be compounded and not just the monthly return times 12.
I agree with you to take adjusted prices instead of closing price. Regarding your second remark I disagree since the whole calculation is based on arithmetic returns and not geometric returns, therefore multiplying by 12 is the right approach.
Hello, I don't understand for the last formula to calculate y* how dow you decide the A value. There's a range of values based on risk aversion? Thanks
I have one thing I'm thinking of. Would it be possible to deviate from the MPT and for the historically expected returns (used for the sharpe ratio) instead use forecasted returns based on DCF or similar? The resulting weights should then better fit future expectations of the return rather than the historical, or is my way of thinking causing some unwanted side effects?
Thanks alot sir I really appreciate your help with this video, I became successful in the online trading market because I decided to do my homework and not enter the market as a novice Thanks to your mentoring program Mr Romero pieto.
Many thanks to Mr Romero pieto for helping me and my family this year I invested with him with the minimum amount of $20,000 and he profits me $78,000 per week his indeed an heaven sent.
Jessica Vincent Am from Greece 🇬🇷 and am also a newbie in the online Trading market so please how can I get hold of Mr pieto and his trading services ?
There is another, less cumbersome, way to fill in all the Variance/Covariance values which can be done in less than a minute. Might be handy if you have >4 assets/stocks... Go to DATA Select DATA ANALYSIS Select COVARIANCE Fill out the Dialog Box and create the COVARIANCE Matrix COPY & PASTE THE MATRIX with the Headers anywhere in your Sheet COPY the values only from the original matrix PASTE SPECIAL in the new Matrix after selecting the Values only In PASTE SPECIAL select VALUES, SKIP BLANKS, TRANSPOSE and click OK The Matrix should now be completely filled in, but you still have to multiply by 12 Type 12 in an empty cell and COPY it Select all the values in the new matrix Select PASTE SPECIAL Select MULTIPLY and click OK
Thanks a lot for the video. I had a couple of questions though. 1. How would this change if you were using daily instead of monthly returns? 2. How would it change if short-selling was allowed vs. if short-selling wasn't allowed? Thanks for the help
After 1 yr here is the reply, 1-it dosent change much, it change for the fact that for the annual return you don’t have to multiply by 12 but for the number of day. 2-if it’s allowed to use negative weights then if in the portfolio there are very low performance stock the weight could be negative, that means that it’s optimal if you short that stock, otherwise if you don’t use negative weight the optimization should say that the optimal weight is 0
Roberto Letts there are so many things wrong with this video. Including the returns. You are right, for returns for more than a one year period you want to use the formula ((1+mr)^12-1). The variance needs to be multiplied by the square root of 12, the time ratio because of the assumption of random walk theory and because standard deviation of a portfolio scales disproportionately, i.e it is not additive. Finally, when he multiplies the covariance matrix by 12, I have no idea why or how mathematically that works because it is squared standard deviation. If you take monthly data and run the solver function for covariance it will spit out the correct numbers, but even assuming his logic of “annualizing” them. Take the same stocks and get annual returns. You will not get the monthly variance *12.
@@Stasthagod the return is arithmetic or geometric, it should be approximately acceptable. The writer is right on "variance" multiply 12; if "standard deviation", square root of 12 as a multiplier.
Thanks for great video. Risk-free asset is a fixed term in solver for maximisation. So, the weights will be the same for any given risk-free. right? if so, what is the role of risk-free asset factor in your calculations?
So avg monthly returns being multiplied by 12 and that being the annual is slightly wrong conceptually. For example a avg monthly return of 0.75% across 12 months will yield a return of 9.381% But if you multiply it like you did in your video it comes out to 9.00%. Maybe I'm misunderstanding this but idk if this is just a simple draw back of using this method
i have some problems with the solver... it says that it had found a solution but when i click 'okay' it doesn't change the weights or anything else... and i followed all the stes to use it help :)
Thank you very much. So valuable. Could you please share the data (excel file) for me to practice? I would like to do it on my own and check with your result to make sure I follow your steps appropriately
@@choyonmazumder3780 Go to DATA Select DATA ANALYSIS Select COVARIANCE Fill out the Dialog Box and create the COVARIANCE Matrix COPY & PASTE THE MATRIX with the Headers anywhere in your Sheet COPY the values only from the original matrix PASTE SPECIAL in the new Matrix after selecting the Values only In PASTE SPECIAL select VALUES, SKIP BLANKS, TRANSPOSE and click OK The Matrix should now be completely filled in, but you still have to multiply by 12 Type 12 in an empty cell and COPY it Select all the values in the new matrix Select PASTE SPECIAL Select MULTIPLY and click OK
Thank you Mr. Dalsem for your amazing video. I have a question, how can we determine the weighted value for each stock? Is that depend on how many fund we invest in?
I have 1,250 ETF lists from the New York Stock Exchange I need help on how to choose optimal portfolios using cumulative Abnormal return and Behavioral ETFs
Hi Shane.Nice video. It's very informative. Do you have an updated link for the referenced document? the old link seems to be broken. Or maybe you have the title for the document that I can look it up. Thanks!
Thank you for this great video . Question? Checking the box “make unconstrained variables non negative” on solver is to NOT short sell. But if the solver gives you a value of “0” for weights , wouldn’t that mean to short sell? If it’s “0” doesn’t that mean to NOT INVEST into that particular stock ?
Joel Hastings If it shows 0 for weights then it simply means not to invest in it at all. Does not mean to short sell which would be going negative weights.
That's not uncommon. For my examples, sometimes I have to go through many different companies that will give me weights greater than 0 for all of them. The ones that have 0 weight likely have low or negative returns and/or high volatility and don't improve the risk-return relationship for the portfolio.
Very interesting. Thanks. Can I ask what do you use to develop efficient frontier asset allocation area graphs? Is it possible with Excel or do you use Python or other tool?
Does anybody have explanation(link?), how shall this be adjusted to stock prices? Here we sum up relative returns, not taking into account prices of assets, but they are different. Say, 1% return of 100$ stock is obviously bigger than 1% return of 1$ stock.
Thank you so much for video. Can you please advise min period of prices in tabel. You have data from 1/4/2011 up to 1/4/2016. Is it importante to have data for 5 years? Thank you in advance. Have a nice day.
Hello Nemo, the standard deviation of the return of the risk-free asset is 0%. So the complete portfolio (the combination of the risky portfolio and the risk-free portfolio) has a standard deviation of the proportion of the complete portfolio invested in the risky portfolio multiplied by the standard deviation of the risky portfolio. For example, if the standard deviation of the risky portfolio is 20% and 60% of the complete portfolio is invested in the risky portfolio, then the standard deviation of the complete portfolio is 0.2 x .6 = .12=12%. I hope that this answers your question.
What is your reccomendation if you had 3000 different stock. Only using the ones on the efficiënt frontier? OR should i only use 4 of the seemingly best ones, also, do i include the Risk free rate?
Shane I'm having trouble with the Expected Return Calculation - I've compiled 40 equity's for the analysis and can't seem to get it to work, could you help me by chance? Trying to do this for some extra credit
Great video!! I have one question: Great video!! I have 12 assets and their prices of 29 different weeks. I want to do portfolio optimization by minimizing the Mean Absolute Deviation. I have calculated r, E[r] , E[r-E[r]] and |E[r-E[r]]| using Excel . What do I have to do next?
Hello Bob, I checked the link and didn't receive the error that you mentioned. I've had a few people access and download the document since you originally sent me an e-mail and did it myself without any problems. Unfortunately, I don't run the website where the document is posted. You might try using a different browser.
@@svandalsem Thanks, Shane. Chrome worked but Opera, Firefox, and Edge all reported an SSL certificate expiration error (46 days) and blocked a purchase. Makes me suspicious of Chrome as It should have done the same. Seems that Plasso has been acquired by another company, perhaps explaining a miscommunication about the site's SSL certificate configuration. If you have a contact at Plasso, you may want to inform them. Also, is the finished Excel doc available? Yes, I am lazy...:-) Thanks again.
Could you please upload again the document?, I tried to access the document with the link in the description but I can't open it on my browser... I don't know if it's just me or someone else has the same problem... Thank you!
This is a great video, but I have one question. From what I understand, the usual range for the risk aversion coefficient (A) is 0-5. However, if I plug in the value of 5 for A, the allocation percentage to a risky asset comes out to 195%! This is the result I get using the values in the video: ER=13.61%, RFR=3%, and SD=10.44%. What am I doing wrong?
I get something like 400% into risky portfolio and -300% into risk free asset. Am I doing something wrong or do I need to borrow all the money and invest it in the risky portfolio with 4x leverage?!
I have so much respect for how a good explainer you are. This video is amazing. Very clear, structured and most importanty calm (good comfort for ones who find these processes taunting already).
I did my senior project on optimization for my math degree and I tried applying it to my investments. I kept getting stuck and frustrated and your video showcased what my problems were: I didn't have a good objective function (I didn't know about the Sharpe Ratio) and I wasn't using my tool correctly (I didn't know about the Ctrl+shift+enter you had to do for matrix multiplication).
Thank you so much for explaining these things, I'm going to try these out as soon as I can.
Hey Josh! I could you please tell me the title of your project? I am also majoring in math.
Rather than just using the closing prices, adjusted prices that consider dividends should be used. ATT is low because their dividend of about 5% is being excluded. The adjusted prices are available from Yahoo. Also, the annual return should be compounded and not just the monthly return times 12.
I agree with you to take adjusted prices instead of closing price. Regarding your second remark I disagree since the whole calculation is based on arithmetic returns and not geometric returns, therefore multiplying by 12 is the right approach.
Massive thanks. Seeing how things are done is way easier than trying to understand the formula... at least for me :P
I wanted be a professional portfolio manager, this will help me to understand much better
Thank you so much for your explanation! i finally figured out the error that i've been trying to identify for the past few hours 😭
Thank you for that tutoring. I was very confused and now it is much clearer.
Thank you very very much! You just helped me to solve a problem in my article!
thank you so much sir. This is perfectly explained, this is exactly what i needed.
Hast du zufällig deine Bachelor Arbeit dazu geschrieben?
Impressive work
Sir i wana,more videos on portfolios risk ans return comparison between stocks
Your method of teaching is very good
Hello, I don't understand for the last formula to calculate y* how dow you decide the A value. There's a range of values based on risk aversion? Thanks
Thanks Sir! Such a complete guide for the beginners. Much appreciated.
Why not use LN to calculate monthly return?
Thank you!!! Such a great explanation...
I have one thing I'm thinking of. Would it be possible to deviate from the MPT and for the historically expected returns (used for the sharpe ratio) instead use forecasted returns based on DCF or similar? The resulting weights should then better fit future expectations of the return rather than the historical, or is my way of thinking causing some unwanted side effects?
Thanks alot sir I really appreciate your help with this video, I became successful in the online trading market because I decided to do my homework and not enter the market as a novice Thanks to your mentoring program Mr Romero pieto.
His consistency in profiting his various clientele really amaze me when Trading on their behalf.
Yes in recent times I profit weekly when investing with Mr Romero pieto he indeed trades on my behalf, making me earn much profit every week.
Highly intensive teaching sir.
Many thanks to Mr Romero pieto for helping me and my family this year I invested with him with the minimum amount of $20,000 and he profits me $78,000 per week his indeed an heaven sent.
Jessica Vincent Am from Greece 🇬🇷 and am also a newbie in the online Trading market so please how can I get hold of Mr pieto and his trading services ?
Nicely done, very thorough
Thank you very much for your tutorial. it worked nicely for me. Great job !
Thank you so much!!This is really helpful
How do i calculate my own A (investor risk aversion)?
You SAVE my exam!!!!!!!
Excellent demonstration thanks.
Very nice work. One question though, what is the range of A (risk aversion)? I mean what does the number 10 stand for?
10 is an indicator for high risk averseness.
It is usually around 2-3 according to a paper I read, and empirically it is around 2.25 from some psychological experiment
@@jujanangelo Yeah so is 10 a percentage value or an absolute unit? Is it like 10% risk averseness?
Amazing video!
There is another, less cumbersome, way to fill in all the Variance/Covariance values which can be done in less than a minute. Might be handy if you have >4 assets/stocks...
Go to DATA
Select DATA ANALYSIS
Select COVARIANCE
Fill out the Dialog Box and create the COVARIANCE Matrix
COPY & PASTE THE MATRIX with the Headers anywhere in your Sheet
COPY the values only from the original matrix
PASTE SPECIAL in the new Matrix after selecting the Values only
In PASTE SPECIAL select VALUES, SKIP BLANKS, TRANSPOSE and click OK
The Matrix should now be completely filled in, but you still have to multiply by 12
Type 12 in an empty cell and COPY it
Select all the values in the new matrix
Select PASTE SPECIAL
Select MULTIPLY and click OK
What you just described is population variance, how do you find sample variance?
Thanks a lot for the video. I had a couple of questions though.
1. How would this change if you were using daily instead of monthly returns?
2. How would it change if short-selling was allowed vs. if short-selling wasn't allowed?
Thanks for the help
After 1 yr here is the reply,
1-it dosent change much, it change for the fact that for the annual return you don’t have to multiply by 12 but for the number of day.
2-if it’s allowed to use negative weights then if in the portfolio there are very low performance stock the weight could be negative, that means that it’s optimal if you short that stock, otherwise if you don’t use negative weight the optimization should say that the optimal weight is 0
Superb
That is great and really helpful!!! Thank you soooo much!
Question on your investor risk tolerance level. How did you derive the "10" and its relation to the 97% allocation into risky assets?
Great explanation. Could I please have access to the document used in the video as well?
Could you please send me the word document at the beginning of the video? Would be a big help!
Huge respect❤
Very well explained
Would you recommend to use Kelly's Criterion for the last shown proportion?
Really helpful. Quick question though. What will I need to make different if I want to get the constrained minimum variance portfolio?
isnt the correct way to make the monthly return to annual return --> ((1+mr)^12-1) mr=monthly return, same for the variance?
Roberto Letts there are so many things wrong with this video. Including the returns. You are right, for returns for more than a one year period you want to use the formula ((1+mr)^12-1). The variance needs to be multiplied by the square root of 12, the time ratio because of the assumption of random walk theory and because standard deviation of a portfolio scales disproportionately, i.e it is not additive. Finally, when he multiplies the covariance matrix by 12, I have no idea why or how mathematically that works because it is squared standard deviation. If you take monthly data and run the solver function for covariance it will spit out the correct numbers, but even assuming his logic of “annualizing” them. Take the same stocks and get annual returns. You will not get the monthly variance *12.
@@Stasthagod the return is arithmetic or geometric, it should be approximately acceptable. The writer is right on "variance" multiply 12; if "standard deviation", square root of 12 as a multiplier.
Thanks for great video. Risk-free asset is a fixed term in solver for maximisation. So, the weights will be the same for any given risk-free. right? if so, what is the role of risk-free asset factor in your calculations?
Could you just upload the document to Github? The link is broken.
Hello Jimmy, I've updated the link.
What's the range of possible values for A? How do we quantify A? What does "10" mean for instance?
Could someone please provide academic references to the information/ Formulae at the beginning of the video
I just want to let you know that the link to the document given in the description throws 404: Page Not Found
So avg monthly returns being multiplied by 12 and that being the annual is slightly wrong conceptually.
For example a avg monthly return of 0.75% across 12 months will yield a return of 9.381%
But if you multiply it like you did in your video it comes out to 9.00%.
Maybe I'm misunderstanding this but idk if this is just a simple draw back of using this method
i have some problems with the solver... it says that it had found a solution but when i click 'okay' it doesn't change the weights or anything else... and i followed all the stes to use it
help :)
thank you! that's really helpful! :)
it was very helpful. Thanks a lot!
Thank you very much. So valuable. Could you please share the data (excel file) for me to practice? I would like to do it on my own and check with your result to make sure I follow your steps appropriately
Great video! Thank you!!!
Can you explain one more time about the proportion?
I can't access the document without paying a subscription fee on coursehero. Is there any other way for me to access the full document?
Thanks for this helpful video. But why did the produce the co-variance matrix manually? Could have used data analysis...
How Sir? Can you share any link ?
@@choyonmazumder3780 Go to DATA
Select DATA ANALYSIS
Select COVARIANCE
Fill out the Dialog Box and create the COVARIANCE Matrix
COPY & PASTE THE MATRIX with the Headers anywhere in your Sheet
COPY the values only from the original matrix
PASTE SPECIAL in the new Matrix after selecting the Values only
In PASTE SPECIAL select VALUES, SKIP BLANKS, TRANSPOSE and click OK
The Matrix should now be completely filled in, but you still have to multiply by 12
Type 12 in an empty cell and COPY it
Select all the values in the new matrix
Select PASTE SPECIAL
Select MULTIPLY and click OK
can anyone explain the matrix notation of the standard of deviation of the portfolio please....
Thank you Mr. Dalsem for your amazing video. I have a question, how can we determine the weighted value for each stock? Is that depend on how many fund we invest in?
Please share the article link sir ...
great video
very helpful
Hi, do you still have that document ? Because that link does not work :). So if I may ask you for update once again :)
Hi, if we have more than 10 assets that are from different industries (i.e. at least 2 stocks in each industry), can we still use this method?
I have 1,250 ETF lists from the New York Stock Exchange I need help on how to choose optimal portfolios using cumulative Abnormal return and Behavioral ETFs
Very clearly and easily to understand. Could you share your documents (word and excel files) ? Thanks!
Is this the Modern Portfolio Theory ? And can we create an Efficient Frontier Chart with this ?
What's the difference between Markowitz portfolio optimization and risk parity portfolio optimization? Is it the same process in Excell for both?
Hi Shane.Nice video. It's very informative. Do you have an updated link for the referenced document? the old link seems to be broken. Or maybe you have the title for the document that I can look it up. Thanks!
Updated
Thank you for this great video . Question? Checking the box “make unconstrained variables non negative” on solver is to NOT short sell. But if the solver gives you a value of “0” for weights , wouldn’t that mean to short sell? If it’s “0” doesn’t that mean to NOT INVEST into that particular stock ?
Joel Hastings If it shows 0 for weights then it simply means not to invest in it at all. Does not mean to short sell which would be going negative weights.
Really helpful and concise
Hi! I tried with 14 different shares and after using solver, 9 of them were suggested as 0% of the portfolio. Do you know why?
That's not uncommon. For my examples, sometimes I have to go through many different companies that will give me weights greater than 0 for all of them. The ones that have 0 weight likely have low or negative returns and/or high volatility and don't improve the risk-return relationship for the portfolio.
If you do not know the degree of risk aversion, how do you calculate y* to get the optimal weights for the risky and risk free portfolio?
Rule of thumb: 3 or 4
excellent work
Very interesting. Thanks. Can I ask what do you use to develop efficient frontier asset allocation area graphs? Is it possible with Excel or do you use Python or other tool?
well taught , thank you
Does anybody have explanation(link?), how shall this be adjusted to stock prices? Here we sum up relative returns, not taking into account prices of assets, but they are different. Say, 1% return of 100$ stock is obviously bigger than 1% return of 1$ stock.
Thank you so much for video. Can you please advise min period of prices in tabel. You have data from 1/4/2011 up to 1/4/2016. Is it importante to have data for 5 years? Thank you in advance. Have a nice day.
This video is very clear!
How did you get the risk aversion level of 10? what does it actually signify, can you tell me please? only that part is not clear to me.
hye, how can i fix for N/A error.. i stuck on COVAR.
You should use geometric mean not arithmetic mean. It would be skewed a lot.
My solver does not change any of my weights even though it says it found a solution. I’m using excel 2010
24:40 How would you calulate the Standard deviation of the proportion portfolio with the risk free asset?
Hello Nemo, the standard deviation of the return of the risk-free asset is 0%. So the complete portfolio (the combination of the risky portfolio and the risk-free portfolio) has a standard deviation of the proportion of the complete portfolio invested in the risky portfolio multiplied by the standard deviation of the risky portfolio. For example, if the standard deviation of the risky portfolio is 20% and 60% of the complete portfolio is invested in the risky portfolio, then the standard deviation of the complete portfolio is 0.2 x .6 = .12=12%. I hope that this answers your question.
What is your reccomendation if you had 3000 different stock. Only using the ones on the efficiënt frontier? OR should i only use 4 of the seemingly best ones, also, do i include the Risk free rate?
Mr. Van Dalsem,
You are a weapon mate. Top stuff.
Sincerely,
Some very stupid finance students xo
Shane I'm having trouble with the Expected Return Calculation - I've compiled 40 equity's for the analysis and can't seem to get it to work, could you help me by chance? Trying to do this for some extra credit
Thank you! Helped me out
When and how to re-balance the portfolio?
Great video!! I have one question: Great video!! I have 12 assets and their prices of 29 different weeks. I want to do portfolio optimization by minimizing the Mean Absolute Deviation. I have calculated r, E[r] , E[r-E[r]] and |E[r-E[r]]| using Excel . What do I have to do next?
Hello Anastasia, I would need to see the worksheet that you are working on to answer that question.
i dont understad at all , inthe matrix is the same company should be 1 right??
Well done.
Tried to purchase the offered accompanying documents, but the plasso commercial site is inaccessible because of an expired SSL certificate.
Hello Bob, I checked the link and didn't receive the error that you mentioned. I've had a few people access and download the document since you originally sent me an e-mail and did it myself without any problems. Unfortunately, I don't run the website where the document is posted. You might try using a different browser.
@@svandalsem Thanks, Shane. Chrome worked but Opera, Firefox, and Edge all reported an SSL certificate expiration error (46 days) and blocked a purchase. Makes me suspicious of Chrome as It should have done the same. Seems that Plasso has been acquired by another company, perhaps explaining a miscommunication about the site's SSL certificate configuration. If you have a contact at Plasso, you may want to inform them. Also, is the finished Excel doc available? Yes, I am lazy...:-) Thanks again.
Could you use CAPM to calculate the expected return?
what should i do if one of my stock 0% after i used the solver
Great video.
Just one question.
Does this approach work for a 3 asset portfolio as well?
This approach should work with a minimum of 2 stocks and theoretically above 2 number of stocks.
One question was the risk free rate give or did you calcilate it
It reminds me of my University :)
Could you please upload again the document?, I tried to access the document with the link in the description but I can't open it on my browser... I don't know if it's just me or someone else has the same problem... Thank you!
Updated
What is A in it ?
It is super!! Where can i get doc file?
Can you explain these in R?
Great video!
why I use your method and get all the result is one asset weight is 1?
This is a great video, but I have one question. From what I understand, the usual range for the risk aversion coefficient (A) is 0-5. However, if I plug in the value of 5 for A, the allocation percentage to a risky asset comes out to 195%! This is the result I get using the values in the video: ER=13.61%, RFR=3%, and SD=10.44%. What am I doing wrong?
That would be leveraged, and you would short risk free and use those proceeds to invest in risky asset
the reference link is paid one can you share the reference
Why not use log returns?
Can you explain what is exactly the risk free rate and how you got it?
i have the same question
@@salmaenct4269 after doing some research you choose your risk free rate from us treasury bonds or given you're chosen country...could be wrong though
I get something like 400% into risky portfolio and -300% into risk free asset. Am I doing something wrong or do I need to borrow all the money and invest it in the risky portfolio with 4x leverage?!
In Solver you can you can choose to require that the weights all be positive (including 0).