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.
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 ?
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
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
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
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.
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?
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
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 :)
@@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
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?
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?
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 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.
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
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.
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!
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.
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.
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
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?
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?
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?!
