i'm trying to find the optimal weight for a portfolio of 4 assets (country indices) using the solver in Excel and maximizing the sharpe ratio. However, it always returns me the following percentages: 0,0,0,100%. Moreover, when I enter as constraint that the weights must all be different from each other, a message says that an optimal solution containing all constraints couldn't be found, and I get a solutin where the sum of the weights is above 1. How can I solve this?
Auke, I'm needing to find the minimum variance portfolio with over to 70 assets, but I'm having problems with Solver. Is that possible to do this with such a big data? Thank you ( sorry for my english, I'm Brazilian )
Can someone please help me :( .. I have followed everything and the solver does not calculate it, but displays an error message: "Error value for solver in the target cell or a secondary control cell". How can I solve the problem?
How practical is this model to be applied in real world as in these portfolio weights are based solely on expected returns which tends to differ from the company's fundamentals in the short run. So would you suggest this model to be theoretical or practical also please share your rationale. Thanks.
thanks for your lecture. can you help my problem? I want to apply LASSO into my portfolio. but I don't know how to make its algorithm on excel. I hope you can suggest or comment a positive direction to me
Can someone tell me how I can solve a similar problem where I for example constraint the number of investment opportunities? Say that instead of being allowed to invest in all five at one time, I am only allowed to invest in four projects at one time. I know using an if function on the design variables are wrong - how can I go about this problem? Many thanks
Hi there, I was wondering how did you calculate your alpha and beta and the covariance. Would it be possible to download this form you somewhere? Thank you
When you use an Array function i believe you need to hit Ctrl + Shift + Enter when entering the formula. If you just hit enter it will give you that value error.
Revered Mr. Plantinga, thanks for sharing the video, however I have one question the variance comes to be 0.73% but how can the value of SD be higher than that of the variance i.e., 8.54% in this case. Please explain the same.
+Aniruddha Ghosh This is because the variance (sigma-squared) is just the squared standard deviation. So to get from variance to std.dev. you take the square root :) As you take the square root of a number lower than 1 the result will be higher as the square root is the opposite of multiplying the number with itself :) In the example below taking the sqr root of s^2 will return it to s :) Example: s = 0.5 s^2 = 0.5 * 0.5 = 0.25
hello ; i wanna ask if u could help me in using the solver to find optimal portfolio when weights are unknown i am really want ???it in my assignment how to do that
The formula for variance of portfolio is x transpose * covariance matrix * x where x is the matrix of weights so that we get Weights squared. But you just multiplied the transpose of weight matrix (x) with the covariance matrix. That is not the formula. The formula is to further multiply with the weight matrix.
@@jucaalco risk free is dependent on the investor. Lots of people calculate risk free by taking the expected return of one month treasury bills over X years. An index like SPX or QQQ can be used as well.
Hello, I was wondering how would you go about creating the StDev for the portfolio if you are given three different covariances for three assets. I don't have a covariance matrix
can u make some videos about optimal portofolio using constant correlation model? or if u find some video about constant correlation model please tell me , reply my comment :"")) *sorry for bad english
thank you for useful video, it shows no value# when I using the formula you teach, why was that could be,, I exactly insert the formula in the correct way..
Sir, all of us don't know German, so it is advisable to change the language of your excel to German. Secondly, you haven't mentioned from and how did you get the co variance in the beginning.
The example illustrates how to calculate the tangency portfolio. The compositioon of the tangency portfolio can be calculated without the need to specify a utility function. It is not difficult to adjust the spreadsheet to accomodate a utility function and maximize utility.
I am trying to solve my work. They gave the utility function as a function of expected return and variance. So should I choose optimum portfolio, add weights to a risk-free asset and compare the weight composition that has the highest utility?
Utility function is E(R) - variance ( I assume that of the portfolio). There are 2 stocks. Plus a risk-free asset. Asking me to get the optimum portfolio mix.
Good night Auke, I have to do an exercice. I have to define the value of the weights to minimize the risk of a portfolio. If you want to help me, please, give me you e-mail and I will send you the exercice. Thank you
My idiot of a professor expected me to know this off the bat with no training so thanks for actually giving me this information.
on god
Auke Je bent de best!!! dit oplossing is belangrijk voor mijn MBA thesis Portfolio analysis Kent Buss School!!! Up Holland!!
You‘re the best!! Safed my project!
Yes, I found this useful. I've been wanting to do this type of analysis for quite some time. Thanks so much.
Very Useful.Thanks sir for posting. Expecting more on this topic + Equity valuation + Analyst's must know excel shortcuts
How do you use solver to get the weights at 2:45 seconds in the video?
Great video. This really helped me a lot. Thanks for uploading a quality video in HD so I can actually see what you're doing.
Thanks for the help, keep up the good work. Best of luck!
Wish my school had this guy as a professor
Thank you very much for this extremely informational video! It has helped so much with my Finance homework!
thank you for this video, it's clear and organized, and easy to follow.
Thanks. This was a simple recipe even I could follow.
So useful, thank you so much!
thanks for makin this video，really helpful
Very clear, well presented. Thank you!
This is a good lesson, thanks for your video. hahhhh. it helps me a lot, I am working on my assignment using this method. Thanks again.Love it
extremely useful. thanks for the tip. hope I would be able to make better portfolios :)
Why do you hit shift, ctrl, enter for the portfolio expected return formula?
Helped me a lot for my class! thank you!
could've shown how to build the matrix RIGHT?
How did you calculate the risk-free rate to be 1.5%?
How would the sharpe ratio calculation change if you were using daily return data?
Thank you! This saved me!
thank you very much Mr. Hollandaise sauce
MarketXLS works for me just fine for this. It's great.
Also, what is the formula for cell B17?
Do you need to divide by n-1 when you calculated stdev, where n is the # of observations. In this case, n is 5.
) Ух, ты ! Стиль заметен. Прекрасно изложено. Но, в другой обсуждаемой модели таких нюансов совершенно и не нужно.
why not use sumproduct function for portfolio's expected return?
i'm trying to find the optimal weight for a portfolio of 4 assets (country indices) using the solver in Excel and maximizing the sharpe ratio. However, it always returns me the following percentages: 0,0,0,100%. Moreover, when I enter as constraint that the weights must all be different from each other, a message says that an optimal solution containing all constraints couldn't be found, and I get a solutin where the sum of the weights is above 1. How can I solve this?
Auke, I'm needing to find the minimum variance portfolio with over to 70 assets, but I'm having problems with Solver. Is that possible to do this with such a big data?
Thank you ( sorry for my english, I'm Brazilian )
how do you get the expected returns of each asset?
this is helpful. thanks!
Can someone please help me :( .. I have followed everything and the solver does not calculate it, but displays an error message: "Error value for solver in the target cell or a secondary control cell". How can I solve the problem?
How to do the calc for each variable?
How practical is this model to be applied in real world as in these portfolio weights are based solely on expected returns which tends to differ from the company's fundamentals in the short run. So would you suggest this model to be theoretical or practical also please share your rationale. Thanks.
thanks for your lecture. can you help my problem? I want to apply LASSO into my portfolio. but I don't know how to make its algorithm on excel. I hope you can suggest or comment a positive direction to me
Can someone tell me how I can solve a similar problem where I for example constraint the number of investment opportunities? Say that instead of being allowed to invest in all five at one time, I am only allowed to invest in four projects at one time.
I know using an if function on the design variables are wrong - how can I go about this problem? Many thanks
thx a lot, this really help me with my assignment
Hi there, I was wondering how did you calculate your alpha and beta and the covariance. Would it be possible to download this form you somewhere? Thank you
Help me out here. I used the same formula to try to get Er and var and st dev. I kept getting "#value" I don't know what's wrong .
When you use an Array function i believe you need to hit Ctrl + Shift + Enter when entering the formula. If you just hit enter it will give you that value error.
Saved my Ass, can you explain why you need to press this combination?
@@johannhofer3961 Just extend the cell to the right. There is less space for the numbers to line up.
Can you upload this file here? It would very helpful
I’m lost... could someone tell me how to get the covariance matrix?
Revered Mr. Plantinga, thanks for sharing the video, however I have one question the variance comes to be 0.73% but how can the value of SD be higher than that of the variance i.e., 8.54% in this case. Please explain the same.
+Aniruddha Ghosh This is because the variance (sigma-squared) is just the squared standard deviation. So to get from variance to std.dev. you take the square root :)
As you take the square root of a number lower than 1 the result will be higher as the square root is the opposite of multiplying the number with itself :) In the example below taking the sqr root of s^2 will return it to s :)
Example:
s = 0.5
s^2 = 0.5 * 0.5 = 0.25
@@philipnelander9577 Right.
How do you calulate the covriance metrics?
hello ; i wanna ask if u could help me in using the solver to find optimal portfolio when weights are unknown i am really want ???it in my assignment how to do that
Did you figure it out? I got the same problem :(
how did you calculate the s.d coloumn?
The formula for variance of portfolio is x transpose * covariance matrix * x where x is the matrix of weights so that we get Weights squared. But you just multiplied the transpose of weight matrix (x) with the covariance matrix. That is not the formula. The formula is to further multiply with the weight matrix.
What I do in the video is exactly what you suggest, calculate the portfolio variance as x' COV x
Oh, yeah, my bad. I did not notice. I was rushing to solve my homework.
How to calculate Rf?
I did not understand either. Can somebody explain me? please
@@jucaalco risk free is dependent on the investor. Lots of people calculate risk free by taking the expected return of one month treasury bills over X years. An index like SPX or QQQ can be used as well.
To clarify I am talking about US treasury bills
How did you calculate the Cov Matrix?
it is given
Why is there a missing part from 2:42 to 2:44?
why do I get crazy numbers when I allow Short Selling (SS) ??
Thanks buddy!!
The first change by solver is max return, and the second one is mim risk? Pls someone else helps me!!!
Is this the same as optimizing a portfolio based on mean -variance?
Hello, I was wondering how would you go about creating the StDev for the portfolio if you are given three different covariances for three assets. I don't have a covariance matrix
Does this apply to bonds as well?
Thanks ! very clear :)
Guten Tag! Is it possible to download your spreadsheet?
Thank you!
great!
what is Resstd ?
Using the historical sample covariance.
How do you get the RF 1.50% ?
That's a given rate. You can't solve for it
thank you.
should the cov of the same factor be 1?
Is the interest paid by the treasure or your country.
can u make some videos about optimal portofolio using constant correlation model? or if u find some video about constant correlation model please tell me , reply my comment :"")) *sorry for bad english
thank you for useful video, it shows no value# when I using the formula you teach, why was that could be,, I exactly insert the formula in the correct way..
thank you!
why the covariance between A and A is not equal to 1?could anybody explain this? much thx¬
LIU Ting diagonal numbers are variance, others are covariance, It's a variance-covariance matrix.
Much thx, I got the answer.
jordanfuent31 thx bro~
thx so much!!!!!
Sir, all of us don't know German, so it is advisable to change the language of your excel to German. Secondly, you haven't mentioned from and how did you get the co variance in the beginning.
How did he get RF =1.50% ??
US 10 yr rate
Merci beaucoup!
Tu comprends anglias? Bon!
why m i getting all 0 ?
Where is your utility function?
The example illustrates how to calculate the tangency portfolio. The compositioon of the tangency portfolio can be calculated without the need to specify a utility function.
It is not difficult to adjust the spreadsheet to accomodate a utility function and maximize utility.
I am trying to solve my work. They gave the utility function as a function of expected return and variance. So should I choose optimum portfolio, add weights to a risk-free asset and compare the weight composition that has the highest utility?
What is the utility function that they gave you?
Utility function is E(R) - variance ( I assume that of the portfolio). There are 2 stocks. Plus a risk-free asset. Asking me to get the optimum portfolio mix.
please .I am need the file
Good night Auke,
I have to do an exercice. I have to define the value of the weights to minimize the risk of a portfolio.
If you want to help me, please, give me you e-mail and I will send you the exercice.
Thank you
I was lost 30 seconds in