No video

Efficient Frontier Explained in Excel: Plotting a 3-Security Portfolio

  Рет қаралды 21,305

Ryan O'Connell, CFA, FRM

Ryan O'Connell, CFA, FRM

Күн бұрын

Delve into the world of portfolio optimization with our step-by-step guide on 'Efficient Frontier Explained in Excel: Plotting a 3-Security Portfolio.' Learn to calculate expected returns and standard deviation for individual securities, assign random weights, and effectively use the Sharpe Ratio and Covariance Matrix for risk management. We conclude with plotting the Efficient Frontier using Monte Carlo Simulation, helping you identify the optimal portfolio.
💾 Purchase the file created in this video here: ryanoconnellfi...
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
👨‍💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
Chapters:
0:00 - Intro to "Efficient Frontier Explained"
0:41 - Calculate Expected Returns: Individual Securities
3:30 - Calculate Standard Deviation: Individual Securities
4:31 - Assign Random Weights
5:40 - Calculate Total Portfolio Expected Return
6:13 - Create Covariance Matrix
8:31 - Calculate Total Portfolio Standard Deviation
9:29 - Calculate Sharpe Ratio
10:42 - Plot Efficient Frontier Using Monte Carlo Simulation
12:17 - Find the Optimal Portfolio: Portfolio Optimization
*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

Пікірлер: 83
@RyanOConnellCFA
@RyanOConnellCFA Жыл бұрын
💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/efficient-frontier-mastery-excel-workbook-for-multiple-security-portfolio/
@I_Depreciate_Land
@I_Depreciate_Land 8 ай бұрын
I think you should be using sample variance (=VAR.S), not population variance(=VAR.P). If you are using the variance to impute your expected future returns, then you are calculating a statistic (i.e. from a sample) from which you can infer features of the population (i.e. all stock returns ever, including future returns). It doesn't really matter economically with as large a sample as you are using, but it is a statistically important concept. Choosing population vs. sample variance is not about the number of observations; it is about what we are using the point estimate for. Just my opinion. I love all of your videos, please keep it up!
@RyanOConnellCFA
@RyanOConnellCFA 8 ай бұрын
Good call out, I'm in alignment with you! It is just a sample of the total population of returns so VAR.S makes more sense than VAR.P. I also agree that it won't make much of a statistical difference with samples of this size. I'm glad you're enjoying the videos 😀
@stephenhobbs948
@stephenhobbs948 11 ай бұрын
For the portfolio standard deviation, =sqt(mmult(mmult...etc, remember to press control, shift, enter, not simply enter. Good video and use of graphics. control shift enter
@RyanOConnellCFA
@RyanOConnellCFA 11 ай бұрын
Thank you! And good call out on that. For most people it won't matter but for people with older versions of Excel you are correct, control + shift + enter makes array formulas work
@martinabsil4961
@martinabsil4961 2 ай бұрын
Hello thank you for the tip, it helps! However, when I press control, shift, enter, the value is then fixed and won't vary when changing my weight. Any idea why is that? Thank you for your help !
@stefanslab
@stefanslab 3 ай бұрын
How did you add the yellow dot to the Frontier Graph?
@alessandrogiovati2096
@alessandrogiovati2096 3 ай бұрын
I’d like to know it too
@trevortyne534
@trevortyne534 2 ай бұрын
Thanks!
@RyanOConnellCFA
@RyanOConnellCFA 2 ай бұрын
Thanks man! I think you're the 2nd person to ever send me a super thanks on here. Much appreciated
@williama.rivera9414
@williama.rivera9414 Жыл бұрын
Very technical and instructive material.
@RyanOConnellCFA
@RyanOConnellCFA Жыл бұрын
Thank you William, I always appreciate your comments
@yousouflane3361
@yousouflane3361 8 ай бұрын
Great video. Wish I had this video to guide me in 2021 when I was doing my FI 4000 project . 👍🏾👍🏾👍🏾
@RyanOConnellCFA
@RyanOConnellCFA 8 ай бұрын
Thank you! It'll hopefully help the next person trying to complete a similar type of project
@MrHailian1
@MrHailian1 6 ай бұрын
Hi Ryan, thank you for creating such a quality content over KZbin. I really like your videos on portfolio optimisation and efficient frontier. This is another amazing video. However, I think there are couple more steps that are missing in efficient frontier construction. 1) plotting Global Minimum Variance Portfolio, 2) introducing Risk free rate and CAL line that will connect to Optimal portfolio of risky assets. At that line any portfolio (combination of Risk free asset and risky assets) will be plotted. Could you please make a video on those steps? Or if you already have that video published, could you please share the link? Thank you.
@RyanOConnellCFA
@RyanOConnellCFA 6 ай бұрын
Thank you! And it is my pleasure to create this type of content. I do not have a video on the Global Minimum Variance Portfolio but that is certainly a topic I can look into in the future. As for the risk free rate and the CAL line as it relates to the Efficient Frontier, I have a video on that exact topic here: kzbin.info/www/bejne/mnvMoZRmgJtnfqs
@MrHailian1
@MrHailian1 6 ай бұрын
Thank you@@RyanOConnellCFA. One further question please. Multiplying daily returns and variances with 252 to annualise them is understandable, to account for only trading days in a year. Could you please suggest or make a video on how to annualise monthly returns and variances? In many videos, they are multiplying them with 12 (as there are 12 months in a year). It means even non-trading days are also being considered and no discount is being applied to them. Should not it be 8 or 8.4 (12 * 0.7) rather than 12? Which number would you use to annualise monthly data? Note: 252/360 = 0.7
@user-uf7uj4yo4x
@user-uf7uj4yo4x 4 ай бұрын
Amazing video it has helped me an immense amount! I echo Mr Hailian's sentiments that demonstrating how to plot the Global Minimum Variance Portfolio would be very handy to see. You van identify the MVP easily from the SD but I couldn't figure out how to identify the weightings of that particular portfolio.
@DeepVoiced
@DeepVoiced Ай бұрын
Ryan. Great video. A great follow-up for anyone who has watched the Excel Sharpe Ratio Optimization Video. Hats-Off to you, brother. By the way, I assume you live in the US. By any chance, do you live in Massachusetts?
@RyanOConnellCFA
@RyanOConnellCFA Ай бұрын
Thank you, I appreciate that! I live in Dallas, Texas! How about you, Massachusetts?
@wbbrown
@wbbrown 2 ай бұрын
This is very helpful, thanks so much! I cross referenced your adjusted close prices for SPY and BND with Yahoo finance, and it looks like you might have used the close price. Is this working as intended?
@yueming2548
@yueming2548 Жыл бұрын
Hi Ryan, the video is great and illustration is very clear! But just one question, why is the efficient frontier drawn in your another video just a curve (without any combinations under the curve), but there are many combinations under the curve in this video?
@RyanOConnellCFA
@RyanOConnellCFA Жыл бұрын
Hello! Good question, the videos both take a separate approach. The other video uses a 2 asset portfolio. This video uses 3 assets and a monte carlo method to simulate the returns. You could use a monte carlo method for a 2 asset portfolio as well, which would also result in having combinations under the curve like this video has
@yueming2548
@yueming2548 Жыл бұрын
Hi Ryan, thanks a lot for this answer, it cleared up a lot of my confusion. But there is still a question, why is the frontier curve drawn so perfectly (no combinations below the curve) when Monte Carlo simulation is not used? Is it because the probability is not introduced, so the return is a definite value, under the condition of given variance?@@RyanOConnellCFA
@RyanOConnellCFA
@RyanOConnellCFA Жыл бұрын
@@yueming2548 I believe the higher number of simulations you use in the monte carlo simulation the more perfectly drawn the top end of the curve will appear to be. Due to the law of large numbers, you will fill in the gaps at the top of the curve. You could test this theory out by running a lower number of simulations.
@yueming2548
@yueming2548 10 ай бұрын
Great! Thanks Ryan. Very Helpful!@@RyanOConnellCFA
@edoardoalbani7552
@edoardoalbani7552 3 ай бұрын
hi Ryan, first of all thanks for all these videos I'm an Italian finance student and I often find your videos much more interesting than the courses I'm studying. I would like to ask you if you have any advises on how to resolve this excel error, trying to build the covariance matrix excel says "Input range must be contiguous". I've copied your whole layout so i really don't understand. Thanks!!
@RyanOConnellCFA
@RyanOConnellCFA 3 ай бұрын
Thank you for your nice feedback! Unfortunately I have not faced that error myself , nor have I heard of it, so I'm not sure. You should try going step by step back through from the beginning as there will definitely be something that you missed
@AdamAdam-vu3qt
@AdamAdam-vu3qt 18 күн бұрын
Ryan, I just recently got my bachelors in finance. Would I now be eligible to take the CFA technically?
@miklosszabo5622
@miklosszabo5622 3 ай бұрын
Hi Ryan, my weights will not add up to 100%. What could I be doing wrong?
@RyanOConnellCFA
@RyanOConnellCFA 3 ай бұрын
Hi @miklosszabo5622, it sounds like there might be an issue with how the constraints are set up in Solver. Make sure that you have a constraint in Solver that specifically states the sum of the weights must equal 100%. If the weights aren’t adding up correctly, double-check that this constraint is active and correctly entered, and ensure all cell references are accurate. Let me know if you need a step-by-step walkthrough to adjust this setting!
@josephelahi8853
@josephelahi8853 6 ай бұрын
Hey Ryan, can you do a video including a portfolio with crypto? I’ve followed your model but the only difference with crypto is it trades 365 days a year so how do I go about changing the calculation and setup. I would love a video on this or if you could provide guidance as to how to change the formulas
@RyanOConnellCFA
@RyanOConnellCFA 6 ай бұрын
Good idea for a future video and I can look into this topic in the future!
@josephelahi8853
@josephelahi8853 6 ай бұрын
@@RyanOConnellCFA looking forward to it
@betrayal47
@betrayal47 3 ай бұрын
Yes, I'd be interested in this as well! Thanks for the videos Ryan!
@zackploeger8451
@zackploeger8451 3 ай бұрын
Ryan, thanks for the content! Question: I'm running a 4-security portfolio and my EF is plotting pretty lumpy and less frontier looking....any tips to what I may be doing wrong? Thanks!!
@RyanOConnellCFA
@RyanOConnellCFA 3 ай бұрын
Hi @zackploeger8451, thanks for your question! A lumpy efficient frontier could result from not having enough data points or the variance-covariance matrix not being accurately calculated. Make sure you’re using a sufficient number of return scenarios for your simulations, and verify that your variance-covariance calculations are correct for all securities. Also, consider checking the frequency and range of the returns data you are using. Fine-tuning these elements should help smooth out your efficient frontier plot!
@edwardchau5818
@edwardchau5818 9 ай бұрын
Is it unusual for the efficient frontier to deviate from the shape you have in the video / textbooks? Mine looks more like a blob than a smooth frontier. Also, how come you are not setting the min/max weights as in the other video for solver? I notice that when I don't set that, it automatically allocates 100% of my portfolio to a single stock.
@RyanOConnellCFA
@RyanOConnellCFA 9 ай бұрын
Hello Edward, I think it would be rather unusual for the Efficient frontier to look like a blob. It is hard for me to determine what is going wrong in your analysis unfortunately. You could add min/max weights to this analysis as well if you wanted to. The reason I didn't in this video is just that I already felt like the video was a bit long
@yugarthsharma626
@yugarthsharma626 7 ай бұрын
11:07 Make sure to simulate both the variables together, similar to Mr. Ryan here. I was simulating the variables in different data tables and got the blob shape you're talking about.
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
@@yugarthsharma626 I appreciate you chiming in on this! It should help people in the future have the same problem
@VivekYadav-vk2lh
@VivekYadav-vk2lh 4 ай бұрын
loved it
@user-uu6dh1sj4m
@user-uu6dh1sj4m 9 ай бұрын
Hi Ryan. Such an interesting video. I have very basic question. Why do you use sqrt(var.p)? There is a difference between var.p and stdev formulas in divisor - n and (n-1).
@RyanOConnellCFA
@RyanOConnellCFA 8 ай бұрын
Hello! The reason we use the square root of the variance (sqrt(var.p)) to find standard deviation is because variance is the square of the standard deviation, which makes the standard deviation the square root of the variance. The distinction between var.p (population variance) and var.s (sample variance) comes down to the divisor-var.p uses 'n' for the whole population, while var.s uses 'n-1' for a sample, correcting for bias in estimating a population parameter from a sample
@ExpectChaos
@ExpectChaos 6 ай бұрын
Hi Ryan, thanks for this video. I am studying variance and covariance among portfolio distribution, i would ask you if the procedure you explained to get the efficient frontier is considering the correlation/decorrelation between assets. Is that included in var/covar matrix? Sorry for dumb question, just learning 😅
@ExpectChaos
@ExpectChaos 6 ай бұрын
Oh ok time to answer to myself. I think yes, covariance/variance matrix is already also a sort of "correlation" not normalised matrix which needs to be multiplied for assets weight in order to catch up the whole portfolio variance. I hope this is correct :)
@RyanOConnellCFA
@RyanOConnellCFA 6 ай бұрын
Absolutely, you've got it right! The covariance/variance matrix indeed incorporates the correlation (and decorrelation) among assets by measuring how asset returns move together, which is then used alongside asset weights to calculate the portfolio's overall variance, contributing to the construction of the efficient frontier. Keep up the great learning!
@anaisp3464
@anaisp3464 9 ай бұрын
Hi Ryan, in a previous video, you calculated the daily returns of two assets using =(stock day price 2/stock day price 1)/2. How is it different in this case? Can I calculate the annual returns with this formula?
@RyanOConnellCFA
@RyanOConnellCFA 9 ай бұрын
The other video was calculating daily returns as simple returns. This video is using log returns. Generally, log returns are recommended. I break down the differences between the two methods in great detail in this video: kzbin.info/www/bejne/nKW7iWekj69nps0
@xcbx7664
@xcbx7664 7 ай бұрын
Im trying to make the 5000 Simulation, but it doesnt change the numbers. It keeps copying the numbers from the Portfolio/ Simulation Row. What am I doing wrong?
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
It is really difficult for me to tell from this comment, all I can say is to re-trace your steps and try to do every single step exactly as I've done it in the video and you should not have any problems
@ndondaful
@ndondaful 6 ай бұрын
I have the same problem
@devilsfoodkitchen
@devilsfoodkitchen 6 ай бұрын
Same here. Is there a cell formula that was assumed?
@MSM5500
@MSM5500 5 ай бұрын
@@devilsfoodkitchen, that's because you have to pay attention to what's in the video: In *Data Table* window you must keep you cursor flashing not in a *Raw input cell* box as you do but in a *Column input cell* one instead. Hope this helps.
@vCredist
@vCredist 4 ай бұрын
@@MSM5500 I don't get it .. I have the exact same issue
@sindyy4074
@sindyy4074 2 ай бұрын
thank you for the video!! i have a question to 11:32 every time i do this, there will be all same answers. what did i do wrong? i followed the steps :(
@pipecuadros12
@pipecuadros12 Ай бұрын
Same as me. Have you solved that error?
@xcbx7664
@xcbx7664 8 ай бұрын
Hey, I just bought the file and having an issue with the hight numbers of simulation. Is there a way to delete a part/minimize the simulation to like 1000. My pc takes around 10 min for one calculation. 😢
@RyanOConnellCFA
@RyanOConnellCFA 8 ай бұрын
Wow your laptop must be quite old! Thank you for purchasing the file. You could reduce the number of simulations to a lower number and follow the same steps that I have done here. You would just do it exactly as I have done in this video but only go down to the number 1000.
@tsunningwah3471
@tsunningwah3471 7 ай бұрын
support!
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
Thank you!
@faridsalehi3380
@faridsalehi3380 7 ай бұрын
Dear Ryan, how to create a Capital allocation line in case of three security portfolio?(in this video that Efficient Frontier always changes)
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
Hey there! Once you have found the optimal portfolio as I've described in the video you are currently commenting on, you can follow the methodology that I used in this video to graph the capital allocation line on top of the graph: kzbin.info/www/bejne/mnvMoZRmgJtnfqs
@faridsalehi3380
@faridsalehi3380 7 ай бұрын
Thank you , got it@@RyanOConnellCFA
@bemineni
@bemineni 3 ай бұрын
Can we apply this methodology for options?
@RyanOConnellCFA
@RyanOConnellCFA 3 ай бұрын
Hi @bemineni, thanks for your question! While the Efficient Frontier methodology is typically applied to portfolios of stocks or bonds, it can be adapted for options. However, the calculations become more complex due to the non-linear payoffs of options. You would need to consider the specific characteristics of options, such as their Greeks (Delta, Gamma, etc.), and possibly use simulation techniques to estimate expected returns and variances
@xcbx7664
@xcbx7664 7 ай бұрын
@RyanOConnelCFA Could you explain why you multiply by 252 at the "Portfolio Summary-STD Deviation" because you already annulized the returns at the top and are using those to calculate.
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
Hello, the way you annualize standard deviation is to multiply the daily standard deviation by the square root of the # of trading days in a year. It is a totally separate calculation than the annualized return.
@tsunningwah3471
@tsunningwah3471 7 ай бұрын
it may be a bit of a digress, but in statistics, sometimes ln(odds) is used. Just like you used ln(return) in the video.
@RyanOConnellCFA
@RyanOConnellCFA 7 ай бұрын
Interesting point! I'll look into it
@aarondelarosa3146
@aarondelarosa3146 Жыл бұрын
Excellent. Can you replicate it in python?
@prettywitty9051
@prettywitty9051 Жыл бұрын
Excel and python can do the same things however people who use excel do not want use python and vice versa. These okes creating programs has to stick to the same terminology. It's getting ridiculous
@RyanOConnellCFA
@RyanOConnellCFA Жыл бұрын
@aarondelarosa3146 that video is on my to do list for down the road!
@aarondelarosa3146
@aarondelarosa3146 Жыл бұрын
@@RyanOConnellCFA I can't wait!
@andywang5500
@andywang5500 4 ай бұрын
Is plotting a 4-security portfolio any different?
@spideyfanboy
@spideyfanboy 11 ай бұрын
I got a linear shape... and I can't seem to know where went wrong
@RyanOConnellCFA
@RyanOConnellCFA 11 ай бұрын
It is hard for me to diagnose your issue just from this. I would advise you to start from step 1 and go step by step and check each one to ensure that you have done it exactly the same. You'll likely come across your error this way
@spideyfanboy
@spideyfanboy 11 ай бұрын
Hi Ryan, thanks for the reply. Could it be the data set that is the issue? What are the reasons for the graph being a straight line?@@RyanOConnellCFA
@RyanOConnellCFA
@RyanOConnellCFA 11 ай бұрын
@@spideyfanboy That seems unlikely that it would be the data set. If every security is perfectly correlated with every other security then it is possible. You could test this using =CORREL()
@k60ntkl84
@k60ntkl84 10 ай бұрын
Maybe the correlation between the securities is near to 1, which means they tend to vary in the same direction and at the same unit. This is about the choice of securities and imply that the portfolio is risky and not diverse. It can be fixed by eliminating 1 or 2 and adding other ones.
@Relaxation-Music-Top-of-Mind
@Relaxation-Music-Top-of-Mind 4 ай бұрын
How about Portfolio with 25 assets? I've done it...😅
@zackploeger8451
@zackploeger8451 3 ай бұрын
I'm trying 7 an my EF is plotting extremely clumpy....any ideas??
Create a Live Stock Portfolio Tracker in Excel  | No Microsoft 365 Required
21:02
Ryan O'Connell, CFA, FRM
Рет қаралды 58 М.
Portfolio Optimization in Excel: Step by Step Tutorial
15:26
Ryan O'Connell, CFA, FRM
Рет қаралды 39 М.
A little girl was shy at her first ballet lesson #shorts
00:35
Fabiosa Animated
Рет қаралды 22 МЛН
小丑把天使丢游泳池里#short #angel #clown
00:15
Super Beauty team
Рет қаралды 36 МЛН
Can This Bubble Save My Life? 😱
00:55
Topper Guild
Рет қаралды 49 МЛН
Monte Carlo Method: Value at Risk (VaR) In Excel
10:13
Ryan O'Connell, CFA, FRM
Рет қаралды 46 М.
Mastering Multi-Asset Portfolio Analysis: Standard Deviation & Returns in Excel
14:34
🚨 YOU'RE VISUALIZING YOUR DATA WRONG. And Here's Why...
17:11
Adam Finer - Learn BI Online
Рет қаралды 60 М.
Graph The Efficient Frontier And Capital Allocation Line In Excel
8:47
Ryan O'Connell, CFA, FRM
Рет қаралды 112 М.
Modern Portfolio Theory Explained!
16:31
QuantPy
Рет қаралды 82 М.
Portfolio Optimization using five stocks in excel | FIN-ED
17:36
10 Excel Formulas That Will Set You Apart (+Cheat Sheet)
18:04
MyOnlineTrainingHub
Рет қаралды 246 М.
Stock Portfolio Monte Carlo Simulation In Excel
8:09
Ryan O'Connell, CFA, FRM
Рет қаралды 27 М.
This is how I ACTUALLY analyze data using Excel
24:05
Mo Chen
Рет қаралды 146 М.
A little girl was shy at her first ballet lesson #shorts
00:35
Fabiosa Animated
Рет қаралды 22 МЛН