I just want to highlight to anyone studying this video, that the author made a slight mistake at 07:50 when he clicked on cell B84, setting it as the minimizing goal! It was supposed to be G84 (Portfolio Variance) and that´s why gs (Goldman Sachs) stock was set to 0. This had the author puzzled for a while, but then later he reviewed it and corrected the mistake. Anyway thank you so much for this excellent tutorial!
I was stuck on an assignment for my financial modeling class because I didn't know how to find the portfolio variance of a 10 stock portfolio. Thank you so much for sharing this method. I would've never thought to use MMULT and the variance-covariance matrix. I was beginning to think I'd have to go through the stocks' returns and get the covariance of each combination pair by pair and then write out the conventional variance formula with all 45 pairs of covariance as terms. Imagine the length of that formula!
Bit late to mention this but: the reason why you get different values is because the initial run of solver optimised B84 instead of G84 (8:37) and again at 13:51. Last run was actually correct.
Thank you Sir for your video. Finding the variance for a multi asset (more than 2) portfolio was my stumbling block and your explanation was very clear.
Yes, it seems that the optimal portfolio is based on the optimization of the sharpe ratio (expected return per unit of SD), not on the minimization of the variance.
Hello there, I have a question. How to successfully implement STOCK PRICE constraint into solver before calculating. Since every stock has its price at which buys, we can't get their weights accurate, since it need to be the whole number. For example we can't buy 1,3 stocks. The weights sum doesn't need to be 100%, but very close. MY ATTEMPT: I used as variables: weights, money weight in each stock (weight multiplied with total funds - 1000$) and number of stocks. I tried to integer the formula for number of stocks for each stock (sum invested divided by stock price), but it doesn't work, it only rounds the number but when you check it, it's not good. I also tried to constraint the total sum invested to near 100%, so if i have 1000$, the constraint is from 950$ to 1000$. Hope you understood me and manage to find some solution.
Why not just round the stocks down to the nearest whole number? It's still going to be close enough to an optimal portfolio. In other words, you would expect the nearest whole number of stocks (rounded down if need be) to be good enough. The only scenario this doesn't work is if you're buying a very small number of shares or if the shares are extremely pricey.
dont usually comment but when he starts to use solver he initially selects cell G84 to minimize but accidentally changes it to cell B84 while explaining...?
So, portfolio return-- as calculated in this video-- is in monthly terms. Are standard deviation and variance also in monthly terms, since the underlying figures are, too? If so, how do we convert stdev and var into annual while considering compounding?
I have a dataset of 30 columns and 102 rows of percentages, and have entered the equation exactly as you have it here. It refuses to accept the semi-colon and tells me "there's a problem with this formula". Any advice?
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?
Thank you for the posting. This is very helpful. I have a question about this model. For this method to work, we have to assume that our stocks are either uncorrelated or negatively correlated right?
Uncorrelated, but not necessarily negatively correlated. For example, the four stocks in this video are likely to be at least moderately correlated, but the model still works!
I followed word for word and solver isn't working at all. I keep getting objective cell must have formula but I've literally been following the video for practice.
I think you just have to go to yahoo finance or investing.com and select the stock you"d like to analyse. You have to select the adj close values for each stock, and make sure that the dates of each stock matches.
Very easy to follow, good speech, speed, knowledge of material. Thank you for making the video.
I just want to highlight to anyone studying this video, that the author made a slight mistake at 07:50 when he clicked on cell B84, setting it as the minimizing goal! It was supposed to be G84 (Portfolio Variance) and that´s why gs (Goldman Sachs) stock was set to 0. This had the author puzzled for a while, but then later he reviewed it and corrected the mistake. Anyway thank you so much for this excellent tutorial!
It should be G85. Minimising the variance is the concern
@@inspectordeeprajdas If you pay close attention you´ll notice the variance calculation cell is actually G84!
I was stuck on an assignment for my financial modeling class because I didn't know how to find the portfolio variance of a 10 stock portfolio. Thank you so much for sharing this method. I would've never thought to use MMULT and the variance-covariance matrix. I was beginning to think I'd have to go through the stocks' returns and get the covariance of each combination pair by pair and then write out the conventional variance formula with all 45 pairs of covariance as terms. Imagine the length of that formula!
Bit late to mention this but: the reason why you get different values is because the initial run of solver optimised B84 instead of G84 (8:37) and again at 13:51.
Last run was actually correct.
Thank you Sir for your video. Finding the variance for a multi asset (more than 2) portfolio was my stumbling block and your explanation was very clear.
Excellent... Complexity made simple and explained well! Thank you for the video.
Excellent lecture! very easy to follow. Very useful, thanks!
Thanks you saved us a lot of time!
This clip is very helpful for me to follow the author's instruction and make it for myself. Thanks a lot! Author
Thank you so much. It helped me to find MVA for multi-asset portfolio.
thank you so much! it's really helpful!
Just an FYI, the GRG Nonlinear solver will calculate a better solution than your initial guess, but it is not guaranteed to give the best solution.
That's not an optimal portfolio that's minimum variance portfolio, there is a difference between those two
Yes, it seems that the optimal portfolio is based on the optimization of the sharpe ratio (expected return per unit of SD), not on the minimization of the variance.
Sorry but I hate to say that the object of solver is actually cell B84 instead of cell G84 and that’s why the weight of gs is zero
Very many thanks indeed for help, it was very useful and easy to understand!
Great video! Highly appreciated
Hello there, I have a question.
How to successfully implement STOCK PRICE constraint into solver before calculating.
Since every stock has its price at which buys, we can't get their weights accurate, since it need to be the whole number.
For example we can't buy 1,3 stocks. The weights sum doesn't need to be 100%, but very close.
MY ATTEMPT:
I used as variables: weights, money weight in each stock (weight multiplied with total funds - 1000$) and number of stocks.
I tried to integer the formula for number of stocks for each stock (sum invested divided by stock price), but it doesn't work, it only rounds the number but when you check it, it's not good.
I also tried to constraint the total sum invested to near 100%, so if i have 1000$, the constraint is from 950$ to 1000$.
Hope you understood me and manage to find some solution.
Why not just round the stocks down to the nearest whole number? It's still going to be close enough to an optimal portfolio. In other words, you would expect the nearest whole number of stocks (rounded down if need be) to be good enough.
The only scenario this doesn't work is if you're buying a very small number of shares or if the shares are extremely pricey.
How you came up with first table for gs, wmt, ba,msft?
Variance covariance matrix
This tutorial is great. What is the thory behind this? Is it the Lagrange Multiplier Method?
Great job!
dont usually comment but when he starts to use solver he initially selects cell G84 to minimize but accidentally changes it to cell B84 while explaining...?
Yes and that was a mistake.
That's why solver gave a 0 weight for gs - it got minimized.
So, portfolio return-- as calculated in this video-- is in monthly terms. Are standard deviation and variance also in monthly terms, since the underlying figures are, too? If so, how do we convert stdev and var into annual while considering compounding?
You are AMAZING - THANK YOU
I have a dataset of 30 columns and 102 rows of percentages, and have entered the equation exactly as you have it here. It refuses to accept the semi-colon and tells me "there's a problem with this formula". Any advice?
There are regional differences in excel syntax. If you are from Eastern Europe, try using colon instead
Is it possible to provide a video with instructions, how to insert capital market line into the Solver portfolio graph?
how do I resolve the error message. solver encountered an error value in the objective cell or constraint cell. and the weights are all valued at 0.00
same problem here
how do we forecast for commodities, property, cash etc using historical data such as GDP, inflation, price indices
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?
Thank you sir for your video but I am unable to find the weight, it shows values in variance, can you please help?
Thanks you SO MUCH
1.64608E-05
im getting variances like this..what do they mean and how can i correct them
In the middle of the Home panel, there is a number format that is set to the general by default. Change it to the number. (no native English speaker)
Hey, what if I have also given the correlation factor between two projects? Then how do I calculate weights?
Hi Sir. I was wondering what happen in case you have a portfolio with long and short positions?
if you short just flip the percentages. What do you mean?
Thank you for the posting. This is very helpful. I have a question about this model. For this method to work, we have to assume that our stocks are either uncorrelated or negatively correlated right?
Uncorrelated, but not necessarily negatively correlated. For example, the four stocks in this video are likely to be at least moderately correlated, but the model still works!
if i am gonna invest 1000 dollars so should i change the sum of weights from 1 to 1000?
I am doing exaclty what you describe, my weights are changing but my portfolioreturn and variance is 0. How is that possible?
How can I make the covariance matrix?
The 1st video covers that part. Try look through his videos. This is a direct continuation of that video.
Does this really find highest return, because this looks like portfolio of least variance, optimal should be tangent to the risk free rate no?
hello is there any way to contact you? i have a deadline soon and i am struggling
Hello the return who have found it is a return for 1 month or for 1 year ?
Monthly
Solver is giving different results because GRG Non linear is chosen instead of Simplex.
why all the weights have the same number and zero?
I followed word for word and solver isn't working at all. I keep getting objective cell must have formula but I've literally been following the video for practice.
Geoffrey Dukes same here
so how?
dont forget to press ctrl enter
you are a gift from God
can not understand solver weight ,pls explain
Thanks mate.
You saved my life!! thanks!!!
Please provide the training Excel file
Thank you!
thank you dude
Sir ,upload the same data (excel_file) to test it ,sri lanka
gs only went to 0 weight because of what happened 7:50 XDDD
hallo please Arabic
How you came up with first table for gs, wmt, ba,msft?
I think you just have to go to yahoo finance or investing.com and select the stock you"d like to analyse. You have to select the adj close values for each stock, and make sure that the dates of each stock matches.