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

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.

🎓 Tutor With Me: 1-On-1 Video Call Sessions Available

👨‍💼 My Freelance Financial Modeling 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

[I_Depreciate_Land]

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!

[williama.rivera]

Very technical and instructive material.

[stephenhobbs]

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

[stefanslab]

How did you add the yellow dot to the Frontier Graph?

[Uniworld]

Great video. Wish I had this video to guide me in 2021 when I was doing my FI 4000 project . 👍🏾👍🏾👍🏾

[MrHailian]

Hi Ryan, thank you for creating such a quality content over YouTube. 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.

[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?

[josephelahi]

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

[yueming]

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?

[antsos]

Hi, thanks for making the video. At step 11:36, I just get the same value in each column so just 8, 17% and 3, 45% whole way down in all 5000 rows. What might be the error here? Thanks!

[AdamAdam-vuqt]

Ryan, I just recently got my bachelors in finance. Would I now be eligible to take the CFA technically?

[edoardoalbani]

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!!

[zackploeger]

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!!

[wbbrown]

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?

[NazarY-yt]

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).

[edwardchau]

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.

[ExpectChaos]

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 😅

[bemineni]

Can we apply this methodology for options?

[tsunningwah]

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.

[anaisp]

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?