Dr. Colby, I am a fresh graduate and this really helps me to improve my investment plan and really gives me relief in investing. thanks for posting it!
Thank you so much for your teaching! I'm a student from China, and your teaching is so beneficial! I have already recommend your video to my classmates!
Thank you great presentation. It's so much easier now with Excel. Amazingly, I was able to reproduce your results using "old school" formulas from "Portfolio Analysis", 2nd edition by Francis and Archer. I learned this at California State University, Sacramento by professor Ahmadi, back in Fall 1985. The Markowitz optimal portfolio, using the inverse matrix of the var-covar plus 1's in last row and 1's in last column : Weights(88.6%, 20.9%, -35.0%, -44.1%, -5.7%, 14.2%, 50.1%, 11.0%), return(0.67%) and risk(2.16%). An alternative formula, in the book, solves for weights using desired rate of return (your 1st result was 1.31%), this is how I checked against your first results and got almost exact match. The great thing about using Excel is the solver allows for more constraints. Thanks again.
Save my life... after hours and hours of losing my mind, this video solved my issues in 5 minutes (mainly how to use the mmulti and transpose to get the variance of portfio). Thanks for this.
You seriously blew my mind. I was searching for something like this for days. I just have 2 questions if you don't mind: 1-What is the best time frame for which to get historical monthly returns (I'm assuming as long as possible)? 2-Where can you find historical returns and their standard deviations? Bloomberg? Thank you so much
probably too late for this reply but the way you get their historic returns is by downloading the data of the stock prices and doing the log return for each of the days, so the formula is =LN(N/N-1) which is just the day you are doing the stock return for divided by the day before which should give you the log stock returns and therefore the historical returns
Are you going to upload a video on how to build the variance/covariance matrix you spoke about at 3:18? It would be greatly appreciated thanks! Great video!
Thank you for the tutorial. Very clear and understandable. Unfortunatly I didn't get the CTRL+SHIFT+ENTER in =MMULTI formula and took me a while to complete... hahaha
Sir, this is the most exciting videos I have ever seen on youtube, thx for your excellent lecture and work. P.S if spreadsheet in the video could be downloaded, that would be awesome :) Have a good day. By the way, do you recorded some videos about how to use SAS in financial data analysis?
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?
Why aren't you my lecturer?!?! I think i just watched your video 30 times in the last 3 days. I was wondering, how do u construct a graph with these optimal portfolios? Fingers crossed you can make one in the next 24 hours. Or am i just wishing for too much?
For calculating the expected returns of the portfolio, why don't you just multiply the asset returns with the corresponding weights and sum (i.e sum-product)? I am asking because the two formulas give different answers.
Thanks! I successfully managed it with your help. However I face a new problem. I would like to expand the mean-variance analysis and do a mean-variance-skewness-kurtosis method. I found that it is common to use polynominal goal programming in order to optimize the portfolio also for higher moments. I was wondering if you could help me with the application ( eg. right tools to use)??? It would be great if you have the time to respond! Thank you so much, Kind Regards!
thank you DR colby for this excellent video , but i have a question and i will appreciate your answer, why do we use assets class for constructing the efficient frontier , is it possiple that if we use individually the securitie's std and expected returns instead of the assets class(the securities that grouply constitute the asset class) we will have a better efficient frontier or because the asset class have already diversified the unsystematic?
Hi, this video is really helpfu, however I dont undertand the step at 5:58, where you are calculating the statdard devaion, why do you have to multiply it again with the weight matrix. Kindly respond. Thank you
this is cool! only problem is that the stock prices are markovian chains, aka they are not influenced by their former values, so this portfolio optimization does not guarantee that you will earn these returns in the future or experience these risks.
I believe this assumption (stock prices are markovian chains) is the main limitation of Modern Portfolio Theory. Is there any other theory or technique to build a better portfolio?
Dear Colbym thank you very much for your effort. I've noticed that in the formula you cant use just the weight, but u have to W(n)*SD(n) for each asset. Am I mistaken?
@ 18:13 Quick question . When looking at the Portfolios table we have created, more specifically at the Max Return and Max SR columns, why is it that the Max return column has a lower Expected return (.8495%) than the Expected Return of the Max SR column (.9272%)? Anyone willing to clarify?
Great video. Where can one find (ie a website that provides the information) the average monthly returns of a company other than going to yahoo finance etc and calculating them in excel from the given historical prices. Thanks
Thank u for a well presented topic, I am wondering if u had the chance to try this with Excel 2010 ? the solver have more choices and maybe u could shed some light into how to use it. Regards
Dr. wright in previous message i asked about that how to make an efficient frontier of n assets and you suggests me about it but i try my best and failed. please help me i m now stuck off at this stage and my master thesis work has been stopped till now due to this problem please please tell me that how to construct an efficient frontier of N risky assets. i m waiting your kind reply.
Thanks so much for this video, Dr.Colby Wright. Would you please post a video about plotting the efficent frontier with n assets in portfolios or email me with some tips? thanks again.
The only thing that I don't understand is the computation for the portfolio standard deviation. I see we use the formula (WTΣW)^(1/2). That being said, why is the input for the sigma variable the entire covariance matrix. To my knowledge, whenever I see Σ in a formula I interpret it to be "the sum of". Why is the covariance matrix plugged in for this variable? Any clarification would be greatly appreciated. Thanks!
Hi Thanks for the video. for the equally weighted portfolio return, why do we need to use transpose function for the weights? Isn't it we just need to calculate [sum of (12.5% x mean return of individual asset)]?
Very helpful, Dr. Colby Wright. Do you have any idea why after I use the solver function with only the constraint for Ew = 1 I get rediculous numbers: i49.tinypic(dot)com/2hpt4d1.jpg marked in red. However, if I add a constraint for my StDev to be lower or equal to the highest StDev in the assets (BAC) it shows decent numbers, is this a good thing (in blue)? And how do I read the weights? Short all the minus and add positives?
Hey there guys new to this process. I used this formula =SQRT(MMULT(MMULT(TRANSPOSE(C324:H324),C32:H319),C324:H324)) is it possible this isn't working because I'm attempting to do it on a mac?
Yes, you can, but at first you must have correct data (weakly or monthly returns) and your aproach is more secure then simply used other stocks. Optimization of mutual funds makes optimal portfolio of portfolios (mutual funds).
Dear Dr Wright (or anyone else who might have an answer), I have used this method for a large selection of around 100 different ETP's. The problem is that the solution results in around 30 weights that are in some instances less than .05%. For mutual funds, this wouldn't be a problem because even .05% is a stack of money. However, for an average investor with less money, it would be silly to set up a dozen weights of 1 or 2 dollars. So my question is: is there a constraint I can use to give me the optimal portfolio if each position were to have a minimum weight of 3%? (Although I understand that this may no longer be considered 'optimal') By the way, making all weights >3% (rather than 0) results in no feasible solution because I don't want all positions above 3%, I want positions to be either A) >3% or B) =0. If anyone has any solution, it would be greatly appreciated! Thanks, Tom
A little late... but stumbled upon your question.. try this: say you have i stocks (or ETP's) let w(i) be the weight of stock i define x(i) and y(i) as binary variable cells (this will be the same size of your w(i) array). add the constraints to the solver: w(i) >= .03*x(i) w(i) = .03 and w(i) = .03). When x(i)=0, y(i)=1. Then, w(i) >= 0 and w(i) =.03 or that it = 0. Be sure to clear the variable cells (w(i), x(i), and y(i)) every time you solve. You may run out of space in excel solver because of the number of constraints added (in your case this adds 400 constraints and 200 variables). I suggest finding alternative software or doing subsets of smaller sub-problems (although the solution will be sub-optimal). Dylan
I've read that the first thing you shall do is to limitate the investment horizon. If you want to build a portfolio for the next 3 years, use the former 3 years of data. Though, I believe this assumption is not useful if you want to build a 1 year/6 months portfolio. I would analyse min 3 years, max 5 years. Considering you might rebalance your porfolio every 6 months, that's what most financial consultants/managers use to do.
Hello, I am currently remaking this model. Is there a file that you have uploaded so that I can compare whether my model is accurate. Or at least the copy sheet with your levels data. Thank you.
Has anyone figured out how to read in individual stock prices via Yahoo using VBA since they updated their API format? I had it figured out and could run the program quarterly to redistribute my portfolio. Problem is, Yahoo changed their API format and the CSV files are no longer being retrieved with simple add in buttons in excel.
Great Video! Thanks for uploading! Does anyone have a working copy of this xls? When I run solver, I usually get an answer that is 100% in one asset. I can't figure out where or what I did wrong. Any body got an idea or working file?
Best portfolio optimization tutorial in the whole of RUclips (plus possibly in outside world)
Dr. Colby, I am a fresh graduate and this really helps me to improve my investment plan and really gives me relief in investing. thanks for posting it!
Thank you so much for your teaching! I'm a student from China, and your teaching is so beneficial! I have already recommend your video to my classmates!
Thank you great presentation. It's so much easier now with Excel. Amazingly, I was able to reproduce your results using "old school" formulas from "Portfolio Analysis", 2nd edition by Francis and Archer. I learned this at California State University, Sacramento by professor Ahmadi, back in Fall 1985. The Markowitz optimal portfolio, using the inverse matrix of the var-covar plus 1's in last row and 1's in last column : Weights(88.6%, 20.9%, -35.0%, -44.1%, -5.7%, 14.2%, 50.1%, 11.0%), return(0.67%) and risk(2.16%). An alternative formula, in the book, solves for weights using desired rate of return (your 1st result was 1.31%), this is how I checked against your first results and got almost exact match. The great thing about using Excel is the solver allows for more constraints. Thanks again.
Save my life... after hours and hours of losing my mind, this video solved my issues in 5 minutes (mainly how to use the mmulti and transpose to get the variance of portfio). Thanks for this.
Thank you DR. Wright this is comprehensive and easily understandable. God bless you
Many thanks Sir for such a lucid explanation of the concept !! Seeing the numbers changing runtime for different scenarios helped in quick grasping!
You are the man Dr. White. You saved my ass for my midterm
Thank you very much Dr Colby Wright.. . for posting these wonderful videos... Thank you very much again..from... Raghu..,Mangalore, India...
Thanks for the clear explanation. Demo makes it clearer !!
Colby! You just bailed out half of my MBA program. Keep it up!
I think I love you Colby Wright!
Thanks for a very informative video. I'll definitely watch it again.
Thank you for sharing and making the video, I have better understanding of utilizing excel to optimize the portfolio.
Thank you so much Dr. Wright!!! This is so helpful for my dissertation!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
thank you so much!! I got 95% out of my report because of your video :))
Thank you for the video. Now I really understand what portfolio optimization means
truly helpful and a very professional video..
hello Dr. Colby , this is very helpful, thanks !
Thanks..very clear explanations for a newbie like me..
thankyou very much! this is a great little tutorial, very professional and interesting for anyone who wants an optimal portfolio :)
Great! I find it really helpful and clarifies concepts from Finance class
You seriously blew my mind. I was searching for something like this for days. I just have 2 questions if you don't mind:
1-What is the best time frame for which to get historical monthly returns (I'm assuming as long as possible)?
2-Where can you find historical returns and their standard deviations? Bloomberg?
Thank you so much
probably too late for this reply but the way you get their historic returns is by downloading the data of the stock prices and doing the log return for each of the days, so the formula is =LN(N/N-1) which is just the day you are doing the stock return for divided by the day before which should give you the log stock returns and therefore the historical returns
Are you going to upload a video on how to build the variance/covariance matrix you spoke about at 3:18? It would be greatly appreciated thanks! Great video!
very well explained ....
thank you very much prof
amazing video.. this has been reall helpfull.... i really enjoyed following your instructions .. thank you so much
Thank you for the tutorial. Very clear and understandable. Unfortunatly I didn't get the CTRL+SHIFT+ENTER in =MMULTI formula and took me a while to complete... hahaha
Very helpful video. Thank you so much.
great video! btw where is the ppt you refer to?
Such a great video! So helpful, thanks!
Very good video.
Sir, this is the most exciting videos I have ever seen on youtube, thx for your excellent lecture and work. P.S if spreadsheet in the video could be downloaded, that would be awesome :) Have a good day. By the way, do you recorded some videos about how to use SAS in financial data analysis?
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?
Why aren't you my lecturer?!?! I think i just watched your video 30 times in the last 3 days. I was wondering, how do u construct a graph with these optimal portfolios? Fingers crossed you can make one in the next 24 hours. Or am i just wishing for too much?
This was great, very well explained! Thanks a lot!
Muy interesante. Muchas gracias.
Excellent! And now how do you estimate Minimum Variance Portfolio and the Efficient Frontier?
very helpful video! thank you!
For calculating the expected returns of the portfolio, why don't you just multiply the asset returns with the corresponding weights and sum (i.e sum-product)? I am asking because the two formulas give different answers.
Thanks! I successfully managed it with your help. However I face a new problem. I would like to expand the mean-variance analysis and do a mean-variance-skewness-kurtosis method. I found that it is common to use polynominal goal programming in order to optimize the portfolio also for higher moments. I was wondering if you could help me with the application ( eg. right tools to use)??? It would be great if you have the time to respond! Thank you so much, Kind Regards!
thank you DR colby for this excellent video , but i have a question and i will appreciate your answer,
why do we use assets class for constructing the efficient frontier , is it possiple that if we use individually the securitie's std and expected returns instead of the assets class(the securities that grouply constitute the asset class) we will have a better efficient frontier or because the asset class have already diversified the unsystematic?
Hi, this video is really helpfu, however I dont undertand the step at 5:58, where you are calculating the statdard devaion, why do you have to multiply it again with the weight matrix. Kindly respond.
Thank you
Is this the same as Markowtiz method? If not could you please specify which model/method has been used.
Just finally got it posted. Search for "Generating the Variance-Covariance Matrix." Hopefully it helps.
Dear Colby Wright
Could I use daily data for more precision or I just need to use monthly data.
Thanks in advance.
this is cool! only problem is that the stock prices are markovian chains, aka they are not influenced by their former values, so this portfolio optimization does not guarantee that you will earn these returns in the future or experience these risks.
I believe this assumption (stock prices are markovian chains) is the main limitation of Modern Portfolio Theory. Is there any other theory or technique to build a better portfolio?
@@gabrieldferreira try using a crystal ball!!
Dear Colbym thank you very much for your effort. I've noticed that in the formula you cant use just the weight, but u have to W(n)*SD(n) for each asset. Am I mistaken?
pure awesomeness. thank you!
@ 18:13 Quick question . When looking at the Portfolios table we have created, more specifically at the Max Return and Max SR columns, why is it that the Max return column has a lower Expected return (.8495%) than the Expected Return of the Max SR column (.9272%)? Anyone willing to clarify?
Hi Colby, I do have a similar project to get done. Do you know anyone who can help me with that? A tutor or a student who masters this subject?
Great video. Where can one find (ie a website that provides the information) the average monthly returns of a company other than going to yahoo finance etc and calculating them in excel from the given historical prices.
Thanks
how do you have the value of constraints?? and we need that to come out the weight right?
thx you for this video , may be u can upload a video with a risk free asset, thx again
How can you short something by -113%? by using leverage? Shouldn't we SUM the ABS values?
Amazing, thank you for y oy r time and efforts.
Thank u for a well presented topic, I am wondering if u had the chance to try this with Excel 2010 ? the solver have more choices and maybe u could shed some light into how to use it.
Regards
Thanks this is very helpful.
Thank you very much! very helpful
did you calculate the average monthly returns in discrete or continuous terms?
could you show us how to simulate a CPPI strategy on excel using one portfolio like apple ...
Thanks Dr. White
Dr. wright in previous message i asked about that how to make an efficient frontier of n assets and you suggests me about it but i try my best and failed. please help me i m now stuck off at this stage and my master thesis work has been stopped till now due to this problem please please tell me that how to construct an efficient frontier of N risky assets. i m waiting your kind reply.
Thanks so much for this video, Dr.Colby Wright. Would you please post a video about plotting the efficent frontier with n assets in portfolios or email me with some tips? thanks again.
The only thing that I don't understand is the computation for the portfolio standard deviation. I see we use the formula (WTΣW)^(1/2). That being said, why is the input for the sigma variable the entire covariance matrix. To my knowledge, whenever I see Σ in a formula I interpret it to be "the sum of". Why is the covariance matrix plugged in for this variable? Any clarification would be greatly appreciated. Thanks!
It's explained in one of his other videos why we use cov matrix,far as I remember once you have more than 3 inputs,the calculation becomes to complex.
hoadley
thank you, this is great video.
Hi, how can I calculate max. return ratio? Thanks.
very well done
Hi Thanks for the video. for the equally weighted portfolio return, why do we need to use transpose function for the weights? Isn't it we just need to calculate [sum of (12.5% x mean return of individual asset)]?
is it the same with Markowitz's method to optimize the portfolio? can i use this too?
very nice.. very helpful thanks a lot
just a suggestion, if you can upload a file that you are showing on video that would actually help... We can do it as a practice as you showing...
Very helpful, Dr. Colby Wright. Do you have any idea why after I use the solver function with only the constraint for Ew = 1 I get rediculous numbers: i49.tinypic(dot)com/2hpt4d1.jpg marked in red. However, if I add a constraint for my StDev to be lower or equal to the highest StDev in the assets (BAC) it shows decent numbers, is this a good thing (in blue)? And how do I read the weights? Short all the minus and add positives?
Dr. where can I get access to the Power Point Presentation that you mention on this video, regards, tomate
Hey there guys new to this process. I used this formula =SQRT(MMULT(MMULT(TRANSPOSE(C324:H324),C32:H319),C324:H324)) is it possible this isn't working because I'm attempting to do it on a mac?
+Bryan Begane i think you need to press ctrl + shift + enter, after typing in the formula (instead of just enter)
I was just wondering whether this can be applied to a portfolio of normal funds (ie mutual funds) instead of ETF's? Is there much difference?
Yes, you can, but at first you must have correct data (weakly or monthly returns) and your aproach is more secure then simply used other stocks. Optimization of mutual funds makes optimal portfolio of portfolios (mutual funds).
thanks so much! this is really helpful!!!
Thank you!
love you so much
Dear Dr Wright (or anyone else who might have an answer),
I have used this method for a large selection of around 100 different ETP's. The problem is that the solution results in around 30 weights that are in some instances less than .05%.
For mutual funds, this wouldn't be a problem because even .05% is a stack of money. However, for an average investor with less money, it would be silly to set up a dozen weights of 1 or 2 dollars.
So my question is: is there a constraint I can use to give me the optimal portfolio if each position were to have a minimum weight of 3%? (Although I understand that this may no longer be considered 'optimal')
By the way, making all weights >3% (rather than 0) results in no feasible solution because I don't want all positions above 3%, I want positions to be either A) >3% or B) =0.
If anyone has any solution, it would be greatly appreciated!
Thanks,
Tom
A little late... but stumbled upon your question.. try this:
say you have i stocks (or ETP's)
let w(i) be the weight of stock i
define x(i) and y(i) as binary variable cells (this will be the same size of your w(i) array).
add the constraints to the solver:
w(i) >= .03*x(i)
w(i) = .03 and w(i) = .03). When x(i)=0, y(i)=1. Then, w(i) >= 0 and w(i) =.03 or that it = 0. Be sure to clear the variable cells (w(i), x(i), and y(i)) every time you solve.
You may run out of space in excel solver because of the number of constraints added (in your case this adds 400 constraints and 200 variables). I suggest finding alternative software or doing subsets of smaller sub-problems (although the solution will be sub-optimal).
Dylan
hi thanks for the video ,where can i download the excel spreadsheet for the tutorial
Do you know how to optimize portfolio using cvar in excel
can i know what is the best time frame or interval should we use?
I've read that the first thing you shall do is to limitate the investment horizon. If you want to build a portfolio for the next 3 years, use the former 3 years of data. Though, I believe this assumption is not useful if you want to build a 1 year/6 months portfolio. I would analyse min 3 years, max 5 years. Considering you might rebalance your porfolio every 6 months, that's what most financial consultants/managers use to do.
Hello, I am currently remaking this model. Is there a file that you have uploaded so that I can compare whether my model is accurate.
Or at least the copy sheet with your levels data.
Thank you.
U rock man! Thanks a lot!
Can you do this with 2 risky assets?
What shortcut do you use to "lock in" your formula at 7:58?
F4
F4
Hello, where can i get the excel file ?
thanks
2023
Sir, the matrix formula for SD is not very correct. Instead of just weights, you should take weight*stand.dev
Pretty sure those are continuously compounded: ln(Pt/Pt-1)
Sorry, I found an error in the var-covar matrix, everything works fine now,
thanks
why use the sharpe ratio and not relative standard deviation? (st. dev / average) its more intuitive to understand
I got more out of this video than 10 hours in my Finance class.
Has anyone figured out how to read in individual stock prices via Yahoo using VBA since they updated their API format? I had it figured out and could run the program quarterly to redistribute my portfolio. Problem is, Yahoo changed their API format and the CSV files are no longer being retrieved with simple add in buttons in excel.
love u man
Does anyone know if the portfolio standard deviation that he calculates is the monthly volatility or the annual volatility?
Thanks
I believe he uses monthly volatility in all formulas, as long as his database is monthly.
Sir, your students are very lucky :)
I like using MarketXLS. It's great for me.
i am poor at listening but i got it thank you
Great Video! Thanks for uploading!
Does anyone have a working copy of this xls? When I run solver, I usually get an answer that is 100% in one asset. I can't figure out where or what I did wrong. Any body got an idea or working file?