Portfolio Optimization using Solver in Excel
HTML-код
- Опубликовано: 22 дек 2019
- ☕ Like the content? Support this channel by buying me a coffee at www.buymeacoffee.com/riskmaestro
Let's say you have a client who wants to construct a stock portfolio, and she chose the following stocks:
Apple (AAPL)
Boeing Airlines (BA)
Netflix (NFLX)
Tesla (TSLA)
Your client has stated that the objective of maximizing the Sharpe ratio of her portfolio. Her maximum risk tolerance is based on a standard deviation of 30% per annum.
You collected the monthly data of the stocks mentioned above and computed the monthly returns (from 1 Jan 2015 to 1 Dec 2019) on a continuously compounded basis. The data file can be found here: drive.google.com/open?id=1e7A....
For the purpose of this exercise, you assume the risk-free rate is 4% per annum.
Use Excel to compute the optimal weights for each stock in order to achieve the client's objective.
-----------------------------
Steps:
Compute the covariance of each stock.
Compute the average monthly return of each stock.
Based on an initial weight, we will compute the portfolio's monthly return and standard deviation.
Then, we will annualize the portfolio return and standard deviation.
We then use Solver to find the optimal weights based on the client's objective.
More resources on financial modeling on www.fabianmoa.com.
#FinancialModeling #Solver #PortfolioOptimization #HarryMarkowitz #MPT #ModernPortfolioTheory #Diversification
I am indeed thankful to Prof. Fabian with such knowledgeable and hands-on session in such an easy manner. Thanks and keep the great work going on!
Lifesaver! Ver clear - thanks Fabian!
Just brilliant! A huge thanks, some much knowledge transmitted in so little time :-) straight to the point! You show a good way of understanding how we can manage our portfolios efficiently.
Thanks, Perceval. Glad you found it useful
Great and efficient explanation! Was looking for this.
While reading for Corporate Finance for my MBA, this video cleared many doubts of mine.
Thank you so much for sharing this video!
Glad it was helpful, Ankur!
Excellent tutorial. Thank you very much!
Hi Fabian. Love this and really enjoying many of your tutorials. Thank you. Small top tip, trying to give back what little I can: Automatically name the columns by selecting the range (including the labels) > Control+Shift+F3 > Top Rows. ;-) Thanks again - great channel!
Great tip! Will try it out
Thank you for this informative but also very simple to understand video. Would it be possible to make a video regarding GARCH model for analyzing and predicting the volatility?
Thank you for making it perfectly clear! Not everybody is able to do that.
You're welcome!
Great video, fair play
Going to apply it in TASI market.
Great video. How would you integrate dividend yield + compound interest from that into this optimization problem in terms of total/expected returns?
Thankssssssssssss. Life saver
🙏 Thanks for the great tutorial 👍
Glad it was helpful!
Great video
Hey cool excel …. I did this staff in the late 90 :)👍 do you know if there is an app we can use on Mac or iOS ?
Please, can you expatiate further on the continuously compounding formula bit? Do you have another video showing that in detail?
Perfect!
Thanks, Joao!
I am a non finance guy and was trying to build a portfolio optimiser all by myself - this is easily the best video on this subject on YT for folks like me. What will change if I try to use daily returns instead of monthly returns?I am using data for the past 5 years to build a 20 stock portfolio.
I think everything will remain same except you need to use 252 instead of 12 to annualise the Return and Risk variables. Assuming 252 are the trading days in a year. Of course, Fabian can correct me here if it is not true what I said.
I have 1,245 ETF files using Cummulative Abnormal Return and BEhavioral ETFS? I got all these ETFs from the New York Stock Exchange and I am using the New York Composite Index as my market index. I need to know the most optimal portfolio using 1245 ETF lists.
Thanks
Great video. I have seen others about var-covar and solver. This one was very clear. I have daily returns that are negative (0.4, -0.2, for example) and you cannot have a negative log: =ln(.4/-2). What do you do in this case?
Hi Stephen, the LN() is applied to the ratio of prices, not on returns. So if you have the daily returns, you can proceed to generate the covariance matrix
Sir, how did you derive the return data? as an example I checked AAPL 12/01/2018 Price of $39.44, 01/01/2019 price of $41.61. So LN monthly return would be 5.36% but on your data table shows 1.86%. what am I missing here?
Super like. Thats really great and clear. Would you mind also putting a vidoe on how hedge fund managers decides when to sell a position and replace it with another one using the same mechanism in this video ? Thanks
Great question, hope we can get answer of this.
Thank you, Fabian. Lets say I have the returns in daily format, how do I annualize the variance and std deviation? If the daily data is for example 252 days per trading year.
For daily variance, multiply by 252. For daily standard deviation, multiply by square root of 252
Subscribed
Good
You can also add CML to this along with charts for clarity EF
Thanks for the input, Vivek. That would be for a Part 2 video
@@FabianMoa great look forward for 2nd part
@@FabianMoa has there been a part 2 for this? Would love to figure out how to align this data into potential scatter plots... like an alternative efficient frontier
Can you please explain the meaning of the Std Dev that you get for the portofolio? Why in order to have it you multiply the weights with the covariance matrix? I'm struggling to understand why is it still called std dev...
Moreover, if you calculate the std dev of every single asset, you can't compare it with the portfolio's std. dev, because values are too different. Please let me know
i’m 1 year late but it’s just the formula, portfolio risk formula. standard deviation just reflects risk levels
Regarding to the Risk Free Rate, I have a 5-year data of asset classes and the US 5 year treasuary note annual data, do I just make an assumption as you said in the video or use the average 5-year annual rate or the annual rate for the 2020 calendar year? Thanks in advance, Fabian.
The risk-free rate can be current risk-free rate (at the point of running the MVO)
Which way we import this data to excel?
Hi could you show a quick example of how you calculated the returns using the continuously compounded formula? I've tried following your example for cell A2 (AAPL 1/01/2015) and I got ln(118.05/111.39) = 0.05807 = 5.8% and not 9.208% like you got. Thank you.
I figured it out - it's the adjusted close price on Yahoo for anyone else wondering.
@@kierancook3397 Good catch! I was wondering this myself but may have missed the obvious.
Very good, but i think it would be better if you could speaker slowly, considering some people are not good at excel, we want to follow the steps
Hi! A quick question. What's the difference between the Markowitz portfolio optimization and risk parity portfolio optimization? Is it the same process in Excel?
For risk parity, we ignore expected return. The fund manager will have a target standard deviation for the portfolio, and each asset class will contribute the same amount to total portfolio volatility.
For example, if you take target a portfolio volatility of 20% and you have 5 asset classes, then the optimizer will select the asset class weights such that the contribution of each asset class to the overall portfolio volatility is 4%.
For Markowitz, we look at the mean return and variance/std deviation.
@@FabianMoa so risk parity considers just risk, while Markowitz considers risk and also reward? Said like that, Markowitz seems to be much better? Is that correct?
" so risk parity considers just risk, while Markowitz considers risk and also reward?"
Yes
"Markowitz seems to be much better? Is that correct?"
Not really. It depends on investors' objectives. Some wants to minimize risk, some want to maximize risk-adjusted return, etc. The models are chosen based on investor's objectives and concerns/preferences
Hi Sir, I don't get it the rationale behind the formula @ 6:45 for the variance part. It is different from the normal 2 stock variance formula that we normally used?
It's similar but the implementation in Excel is based on matrix algebra, which is more efficient.
@@FabianMoa thanks for your reply sir. Actually I don't quite get the matrix part (my math not good), may I know where can I further study/research to get the rationale behind the matrix implementation part? What topic should I search? 🙏 Appreciate your guidance
Great video. Can you show you to calculate the month return using the daily price? I can't find the monthly return so I have to use daily price for it. Thanks!
You can work with daily prices. Use it to calculate daily returns. To annualize the daily returns, multiply by 250 or 252 (based on number of trading days in the year). To annualize daily variance, multiply by square root of 250 or 252.
@@FabianMoa I found the option to download the historical price by month. The data is sorted in asc order, so calc the return by (month / previous month) -1. And the numbers come out close, but not exactly like you have on your sample data. Is that because your data took dividend and stock split into consideration?
@@jonathant1797 I might be wrong, but he took LN return. would appreciate if he could clarify it for us.
This is probably the best tutorial for portfolio optimization. Can I used this on a 10 stock portfolio? Thank you.
Yes, you can
As a rule of thumb in as much as the number of observations or the returns in this case is at least ten times the number of stocks. Though more computing power is required.
If I both long and short, how do I calculate portfolio optimization? Thanks!
If you want to include short positions, then do not set a constraint in Solver.
If you want to short a specific stock, you can set the constraint in Solver with e negative weight
Thank you very much
Great Tutorial on portfolio optimisation. I am just wondering why you multiplied monthly return and monthly Std deviation with 12 to annualise them? Okay, there are 12 months in a year so it makes sense. However, in many other videos of other experts, they multiply with 252 when they have daily returns data, not with 360 because there are only 252 trading days in a year approximately. In monthly returns data case, multiplying with 12 means you are compounding returns over weekends and holidays as well. Should not it be any lower number, let's say 8 or 8.4, rather than 12 to account for non-trading period in a year? Sorry for this lengthy comment but it requires clarity. What is the consensus of academicians and practitioners on this matter? Thank you for producing high quality content on the RUclips Fabian.
The mean and variance of returns are i.i.d., so the means and variances can be summed up respectively, which is seen as the effect of multiplication.
So if a weekly return (that consists of 5 trading days) is 2% per week, we assume that based on i.i.d.:
Week 1 return = 2%
Week 2 return = 2%
Week 3 return = 2%
Week 4 return = 2%
So a monthly mean return
= 2% + 2% + 2% + 2%
= 4 × 2%
= 8%
The same concept applies to variance.
And standard deviation is multiplied by square root of time.
@@FabianMoa That's great explanation, Thank you. One further query regarding this tutorial. Using Indirect function to calculate var-cov matrix is efficient, that's wonderful. Could you please explain why you first locked the Row only and second time you locked the Column only within the formula of Covariance calculation using Indirect function? Why locking is important here and why row in the first instance and column the second time within the same formula?
You can't do *12 for the yearly returns right? You have to do =((L15+1)^52)-1, right?
We can *12 if the returns are assumed to be i.i.d. (independent and identically distributed)
And ^52 is for weekly returns, not monthly returns
Yes I just read a paper written by Lo
@@FabianMoa How about if you have a weekly dataset over 15 years.
I compute the average returns for the data per year, but also for over the full set of 15 years per week.
The yearly per year averages I can compute by doing *52 assuming IID.
Can I do the same for the average over the full dataset? Or do i have to muliply that weekly everage by 52*15?
Answer 1: FULL DATA SET AV R*52
Answer 2: FULL DATA SET AV R*52*15
Thanks!
Answer 1 will do
@@FabianMoa Thanks a lot! I thought the same!
Have a nice day sir!
Pls i am unable to solve variance covariance through this method , pls i need your help
Did you follow all the steps shown in the video?
yes sir, i name my cell, APPLE, BA, NFLX, TSLA. Then entered equal sign>covariance>indirect>APPLE(f4).indirect>APPLE(F4))>Enter .
Then my Average returns are:0.015626, 0.018168, 0.027699, 0.083233 respectively
i use LN function to generate my return, the same data from Yahoo finance, 1/1/ 2015 to 1/12/2019(monthly) my return for TSLA is completely different to yours
note: > means then, i like the method, the most simple method online. thanks
Akinola, I'm sharing the source file to you for the 4 stocks. Can you check if the returns computed in my files are the same as the ones you have? Link: drive.google.com/open?id=1XXnchyeibkNFrBJSLop2lVanOVKZ-fvz
A few things to check on your side is, are the prices sorted according to the dates in ascending order (i.e. oldest to newest). And I'm calculating based on Adjusted Closing Price.
Let me know.
@@akinolaolatubosun4120 did you use the ticker AAPL for Apple when you called the data? Also, did you use this as your reference in the spreadsheet? Instead of spelling out Apple, just use the ticker.
why the covariance between the stocks with itself it´s not equal to 1?
Covariance between stock A with itself is the variance of stock A.
Correlation between stock A with itself is 1.
@@FabianMoa you are right, I confuse the terms. Thanks for your fast answer, new suscriber!
Hi, How can we optamize for a big portfolio. Say Portfolio with more than 40 stocks
Better to do in Python/R/VBA
@@FabianMoa I have learned basic R from my current university program. Should I take a course including Python and Data Analysis?
Why does it recommend me to always put 100% of my portfolio in a single stock?
Probably the return is much higher than the rest of the stocks, relative to the standard deviation
Video is blurr, cant see excel calculation
You can increase the video resolution to 720p/1080p
is this markowitz?
Yes, it's based on Markowitz's modern portfolio theory
standard deviation is not a percentage
wait... this is the standard deviation of a percentage.