Portfolio Optimization in Excel: Step by Step Tutorial
Вставка
- Опубліковано 19 чер 2024
- "Portfolio Optimization in Excel: Step by Step Tutorial" is your ultimate resource for mastering portfolio management techniques using Excel. This tutorial will walk you through step-by-step instructions on how to maximize returns and minimize risk, leveraging data-driven strategies for smarter investment decisions. Whether you're a novice investor or a seasoned portfolio manager, this video will provide you with the tools and insights needed to optimize your portfolio effectively.
💾 Purchase the file created in this video here: ryanoconnellfinance.com/produ...
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
Chapters:
0:00 - Intro to "Portfolio Optimization in Excel"
0:48 - Inputs Required to Find the Optimal Portfolio
1:18 - Calculating the Expected Return of Individual Securities
5:49 - Calculating the Standard Deviation of Individual Securities
7:16 - Assigning Minimum & Maximum Weights
8:02 - Creating the Covariance Matrix
10:29 - Calculate Portfolio Standard Deviation
11:36 - Calculate Portfolio Expected Return
12:10 - Find the Risk Free Rate of Return
12:35 - Find the Optimal Portfolio in Excel
*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC
💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/investment-portfolio-optimizer-excel-workbook/
Being a fellow CFA and an FRM charterholder I can attest to the beauty of the presentation. Absolutely fantastic. Blown away …. Ryan
Thank you so much Abhishek, I appreciate that! Its awesome to see someone who makes similar videos, keep it up!
Very clear and concise explanation! Thank you so much!!
My grueling 2-months long portfolio theory course in fifteen minutes. I appreaciate it.
Haha it is my pleasure! This is a great compliment
my teacher explained it in 3 hours. you got me in 20 min
I've watched about five of this type of video, and yours is excellent...by far the best (and easiest to understand) I watched .thanks
Glad it was helpful! It is my pleasure and I'm happy to hear 😀
Your videos are really helping me get through the back end of my finance degree. Thanks for the great content and high quality videos!
I really appreciate it, and it is my pleasure! Good luck finishing up your degree 💪
Great video, Ryan. Thanks for the information!
I appreciate it Jacob, my pleasure!
Explained CFAL3 Asset allocation chapter in one video! Great!
Awesome, I remember that being a good section!
Excellent information and presentation. Everyday one can learn something new.
I really appreciate it William, thank you!
Super video, as usual, many thanks 👏👏
Thank you too!
great video! thank you
Much appreciated and thank you for the feedback!
Many thanks for the super video as usual.
It is my pleasure!
Thank you Ryan
My pleasure!
youre my savior
Haha thank you Jeanette
Hi, thanks for the guide, its really helpful! may i know why you did not consider correlation coefficient in determining the overall portfolio standard deviation? given that the generally taught concept of portfolio standard deviation argues that diversification lowers the portfolio risk if the assets have some form of negative correlation to each other
Great video! The weakness of this and any other model is that you are just looking at the past performance which works great in a stable economy. However, if we are at the end of an economic cycle, as we seem to be in January of 2024, you can argue the economy is not as stable as it was in the past and any spark could generate a massive move up or down rendering your calculations worthless. What about adding a signal that uses the standard deviation to sell your position? Say, if the daily movement moves farther than 6 sigma (six standard deviations) you'd be capturing 99.9% of events and help you detect anomalies in stock movement. You could than program your brokerage account to automatically sell your position if the price goes down by more than six sigma. Just an idea...
You're absolutely right that relying solely on past performance for portfolio optimization has its limitations, especially in uncertain economic times. Incorporating a risk management signal based on standard deviation, like the six-sigma rule you suggested, is an interesting approach. You're correct that this strategy can help in identifying and reacting to significant market anomalies. However, it's important to consider the rarity of six-sigma events and the potential for false signals or missed opportunities, so balancing this approach with other risk assessment measures could lead to a better strategy. Perhaps 3 or 4 sigma would be more appropriate
hello,
can you explain quickly why we use the sumproduct formula for calculating the expected returns of the pf ? from which regular formula is this excel formula is coming from ?
Thanks!
have it with more colluns or more securitys?
Ryan - Fine demo and explanation or the model.
However, you will note that the solutions are pretty much obvious. They are: Maximize the allocation to the assets that have the highest expected returns and minimize the others, both based on the minimums and maximums pre-set by the user.
You are missing the component of standard deviation and correlation. If a high returning asset has an extremely high standard deviation and/or is highly correlated with other assets, the risk will outweigh the return and it will not be allocated a high percentage
Hey Ryan, Great informative video as usual! is it possible for you to make an auto-update stock screening excel sheet with auto-update keystats from yahoofinance (BVPS, EPS, etc)
Great suggestion! I'll look into building a sheet like this
Good Stuff!! Some of the ETFs are fairly new, like within the last 9 mths. So, instead of multiplying 252 would I multiply by the number of trading days in 9 mths?
Thank you! No, I would still use 252 because annual return = daily average return * 252 regardless of the sample size of data used to determine daily average return
Hi, Ryan thank for this great tutorial. Can you show us how to backtest this portfolio?
That is a good idea, and I can add this to my list for future video ideas
@@RyanOConnellCFA Can I do this but in weekly data or I must convert it into annual data like in the video?
@@hermemory5297 You could definitely do this with weekly data and then use weekly returns and standard deviation for each stock. You could also convert the weekly data to annual similar to how I did with the daily data. But with weekly, where I used 252 (trading days), you'd instead use 52 (weeks)
can you put the raw data used in this video for download. so that we can follow the steps manually and can learn. Thanks for amazing video.
It is my pleasure! That would be tough for me due to the way my site is set up, I only have complete files so I would need to change some things on my site to do a raw data file like that. You could get the data you need following this tutorial: ua-cam.com/video/ZgIgoTlSQU4/v-deo.html
Or you could do it the old fashion way by just going to the pages on Yahoo Finances website. It should only take a few minutes to get all the data the way I have it in this video
Thanks for the video. Trying it out and getting some weird results on the annual return. For instance, I grabbed Apple's data from the past 5 years. The Annual Return I'm getting is like 39% with a 32.2 deviation. NVDIA was a 72%! Double and triple checked the math. It looks right but that number seems insane. Any advice or help?
A stock like Nvidia is going to absolutely break the scales when using historical returns to approximate expected future returns because it has performed so strongly throughout history. You're better off using your own estimated expected annual return for their stock going forward, or finding an analysts estimate of their expected return going forward. You could also lower the maximum allocation bound so you don't end up over allocated to Nvidia
Hi Ryan, can you just use the yield of the treasury rate as the risk free rate? Isn’t it necessary to calculate the return first and then take the average of the values (example period of 6 months)? Im asking for more complex portfolio analysis and am not quite sure how to derive the return if only the yield is provided. Thanks!
Hi, that's a great question! When performing more complex portfolio analyses, it is indeed more accurate to use the average return of the risk-free asset over your specific analysis period, rather than simply using the current yield on the 10-year Treasury rate.
@@RyanOConnellCFA thanks for the reply! Do you know which formula I need to use to get the return? Would help a lot! Thanks for your time :)
HI ryan, i purchased the model and had a question. if you are using stocks with different inception dates, so one stock with 10 years of historical data vs 6 years, does that affect the covariance matrix? right now i have 3 stocks all with the same years of historical data and then 2 that do not have the same amount of years of data. so i am wondering if this affects the calculations. please advise, thanks
Hi @sixgod3963, thanks for purchasing the model and for your question! Yes, using stocks with different lengths of historical data can indeed affect the covariance matrix calculations. This is because the variance and covariance are calculated based on available historical returns, and having different time spans can lead to inconsistencies in the data. To maintain accuracy in your portfolio optimization, it’s best to use the same timeframe for all stocks if possible. Thanks for reaching out!
hi Ryan, thank you for the video. If I have monthly returns do I still have to multiply by 252 or by 12? thank you!
That is correct if you've used log returns!
what is the difference between log returns and usual percentage growth? if I used the second then times by 12 months is wrong?@@RyanOConnellCFA
Great video. Any chance you’d have an idea what could cause a value error for my portfolio standard deviation (the formula with mmult)?
Thank you! It is really hard for me to say. The best thing you can do is go back and very carefully check each character in the formula. Also remember to use CTRL + SHIFT + ENTER when finished with the formula
@@RyanOConnellCFA Good one Ryan. It was the "Shift" for me. I'll look up why "Shift" is used when dealing with Arrays. If I don't find the answer, I'll make sure to come back and ask you here.
So each investor would take this data and implement it according to their own risk tolerance? The efficient frontier could be plotted according to your other videos and then we'd be looking for the sharp ratio that corresponds to the desired risk tolerance? Although the sharpe ratio provides the "optimal" investment mix, not every investor may be able to stomach the risk level associated with that optimization, whether they are operating inefficiently or not. At least this would be my understanding of these topics
The idea behind the optimal portfolio is that no matter your risk tolerance you would invest in that portfolio. But if you have lower risk tolerance than the optimal portfolios standard deviation, you can make a portfolio that matches your preferred risk tolerance by investing some of your money in the optimal portfolio and then lending some of your money at the risk free rate (in treasuries). Look into the capital allocation line as I show towards the end of this video: ua-cam.com/video/dJipa0K64HI/v-deo.html
@ 9:45 -The square of the standard deviation for SPY at cell C4 is not equal to SPY-SPY Variance from the Covariance matrix?
Would it have an impact on the calculation of the portfolio variance/standard deviation?
Good point! It may be because we annualized the SPY variance but the covariance matrix is still in daily
I figured out if we were to multiply the covariance elements by a factor of 252 then the square of the individual std deviations will agree with the variances from the covariance numbers. In this case, we should not multiply the annualized portfolio standard deviation by a factor of 252.
Having said that, calculating your way will also lead to the same correct number for the annualized portfolio std deviation. Only it does not look right (at first glance) as we always expect the variances from the two differrent sources of calculation to be equal.
Multiply the covariance matrix by a factor of 252 or not should not affect the realtive correlation between elements of the marix as long as we properly calibrate the calculation of the annualized portfolio std deviation.
Thank you for taking the time to respond to my remarks.
@@RyanOConnellCFA
Can I do this but in weekly data or I must convert it into annual data like in the video?
You could definitely do this with weekly data and then use weekly returns and standard deviation for each stock. You could also convert the weekly data to annual similar to how I did with the daily data. But with weekly, where I used 252 (trading days), you'd instead use 52 (weeks)
Do the adjusted close prices take into account taxation?
Adjusted close prices do not take taxation into account. Only stock splits and dividends
Hi Ryan, for the historical data, can i take data from 10 years ago for lets say 3 stocks and 15 years for the other 2 stocks?
Or all must have the same starting point?
Hey there, it would be best to use the same time period for all of the stocks to get the most of an apples to apples comparison. If you use different time periods then it would suffer from a bias of stock market cycles and economic cycles where some stocks have a more favorable time period and thus outperform
Great videos (I'm not a financial guy but enjoy just learning new techniques in Excel). I'm sure that I'm doing something incorrectly. I followed the video exactly. I am assuming that the totals of the desired weight should equal 100. However on my runs it always comes out greater than 100 % ? Any Ideas ?
That's great you have stumbled across my stuff even though you aren't a finance guy! Make sure to watch starting @12:55 and you will see that I include a constraint where the sum of all weights must equal to 100%
I wasn’t very clear. It’s when I run solver that they do not equal 100.
@@PeterMarcaurelle @13:20 I show exactly how to add a constraint in the solver that requires the total of the weights to sum to 100%
Hi Peter, ensure your total weight cell is SUM() to the optimal weights. I also forgot about that when doing the tutorial. Then your solver constraint can be =100
All set, I found my error. Thanks
Very interesting but is there a way to know the stocks that will maximise the Sharpe ratio ? without having to go through all of them ?
In the future, only if you have a crystal ball 😂
Using past returns as we did here, there is no way to know without including all of them
@@RyanOConnellCFA oh thanks
What does the evidence say about choosing the minimum and maximum weights? Is there industry best practice for what these upper and lower limits are?
There is no universal standard for these limits as they depend on individual risk preferences and investment goals. However, a common practice is to adjust maximum weights based on asset volatility and to keep minimum weights close to zero, ensuring continuous adjustments to adapt to market shifts and changes in the investor’s circumstances. I personally would also make the maximum sizes a function of the number of securities in the portfolio so that as the # of securities rises, the max percentage decreases
I am wondering if you have ever encountered scenarios where the optimal portfolio was X and hence, that's the portfolio with the highest Sharpe ratio and yet the corresponding weights just didn't seem right, i.e. they were much too high with a lot of concentration in a few equities. Obviously, I think human/professional judgement is needed, but the model is supposed to be reliable given the empirical support it has received.
Do you know also whether the stock function in excel returns the adjusted closing prices?@@RyanOConnellCFA
Hello, just a question. Covariance between SPY and SPY; BND and BND and so forth souldn´t be = 1?
Yes, same question. Why isn't the covariance 1 between like assets?
@markinhos97 I was getting my terminology confused. If this helps: Covariance indicates the direction of the linear relationship between variables while correlation measures both the strength and direction of the linear relationship between two variables. Correlation is a function of the covariance.
Thank you for answering the question @devilsfoodkitchen !
Great video! Is the file still still available for purchase?
Thank you Robert! Yes, you can purchase the file created in this video here: ryanoconnellfinance.com/product/investment-portfolio-optimizer-excel-workbook/
Thank you@@RyanOConnellCFA
@@roberthuff3122 My pleasure, thank you for purchasing the file Robert!
Based on what basis and principle do we determine the minimum and maximum weight? Why? And how do we add skewness and kurtosis to the equation with the solver?
Great questions! The minimum and maximum weights in a portfolio are typically set based on investment goals and risk tolerance, ensuring diversification and limiting exposure to any single asset. To include skewness and kurtosis in your optimization, you would need to modify the Solver's objective function and constraints to incorporate these higher moments, which represent the asymmetry and tail risk of the return distribution, respectively. I have not tried doing that myself and I think it may get convoluted for you
Thanks a million for your time, GOD bless you
@@RyanOConnellCFA
Ryan, the solver kicks out a response that states the lower and upper bounds on variables allow no feasible solution. My sharpe ratio is 67.11% with 12.5% allocation across 8 funds. Am i doing something wrong? My expected returns are 11.37% and the standard deviation is 11.62%. I have tried anywhere from 0-100% for min and max. Also, does it make a difference if you use the Canadian 10 year bond vs US treasury? I was using ETFs traded on the TSX.
I've had this issue before and it is definitely user error. There is a constraint that you put into the solver that is incorrect that is causing it not to find a solution. You should check all your constraints one by one. US Treasuries are the standard for the risk free rate as there is less risk of a US Government default than a Canadian government default
@@RyanOConnellCFA thank you so much for your reply. I will go back and check all of my constraints.
@@aliciamarie3661 Good luck!
If we have monthly returns, am I correct we just multiply by 12 instead of 252 in all the formulas you demonstrate?
also do you not have to annualize the covariance matrix?
Yes, you just multiply by 12 instead of 252 if using lognormal returns. I believe you also need to annualize the covariance matrix when using monthly returns. Simply multiply each element in the matrix by 12 to adjust for annualization.
Hey Ryan,
I noticed that the only time you used the adjusted close price was for SPY. For the other four ETF’s, you just used the close price. Is there a reason for this?
Hey, they should all be adjusted closing prices! It is likely I did not update the python code I used to pull these prices on this iteration. For everyone reading this I recommend using adjusted closing prices as I mentioned in the video
@@RyanOConnellCFA Thanks Ryan! This video is incredibly helpful, and I love your content. Subscribed!
@@Cunninghammock0893 Awesome, thank you so much for the positive feedback!
hi ryan how to make efficient frontier curve with 10 stocks
The Efficient Frontier video on this data can be found here: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
Hi Ryan. I tried to recreate your spreadsheet in Excel on my computer but when it comes to Standard Deviation formula it persistently gives me #VALUE! error for some reason. I've bought you spreadsheet file and once open it shows the Standard Deviation correctly. But as soon as I put a cursor on the formula it returns me the same #VALUE! error as in my spreadsheet. So it makes me thinking that there is something wrong with the Excel itself. Do you have any thoughts on how to get the issue fixed? Thanks.
I have been having the same issue! Please revert.
At 10:29, the standard deviation shows #VALUE!
@@BlinkBookSummaries, Don't worry, mate! I've finally hacked the Excel, so originally you should see the *curly brackets* like that *{}* wrapping up the *Standard Deviation* formula. This means that this is an *array formula* . These *curly brackets cannot be typed in manually as a symbols.* Instead the *array formula must be defined by pressing a chord CTRL+SHIFT+ENTER at once* while in a *formula bar.*
Hope this helps.
@@MSM5500 thank you so much. This was very helpful.
@@BlinkBookSummaries, no worries!
Excellent. Your forgot to plot Efficient Frontier.
The Efficient Frontier video on this data can be found here: ua-cam.com/video/AGjsvdDMyhE/v-deo.html
@@RyanOConnellCFA Excellent. I can't wait! Don't forget to plot Efficient Frontier in Python and Excel.
@@aarondelarosa3146 Absolutely, the new efficient frontier in excel video is already shot. Plotting the efficient frontier in python is on my future to-do list
Why not compare each asset's starting and ending price for each year? The difference is the actual annual return you got on each of the assets.
How would you implement this practically for a dataset with many years of data in Excel?
Easier and faster than the method shown in the video. The actual annual return does boil down to the difference between the starting and the ending price (or the log) and no need for overcomplicating it :)@@RyanOConnellCFA
@@spikeyspike79 I don't see how it would be faster as you'd have to go find all the individual rows that only have year ends and only use those. The method shown in the video is extremely quick and simple, you just use one formula to get the daily return and paste it all the way to the bottom. Then you just use one formula to annualize all the daily returns. It takes about 10 seconds if I don't need to explain it. The method you are proposing would take much longer and would also be less useful when considering standard deviation calculations
Why use Var.Population and not Var.Sample ???
In hindsight, I would use Var.Sample because this is truly a sample. With a sample this large, it really won't make much of a mathematical difference which one you choose
Don't we need to exponentiate those standard deviations? It looks like we're taking the variance of the lognorms