Use Excel to graph the efficient frontier of a three security portfolio

PLEASE NOTE - I MADE AN ERROR IN THE VIDEO: you don't have to take the square root when calculating the correlation matrix, that was an error on my part sorry! This error didn't effect the overall results because the correlation matrix was for informational purposes in this video.

Important Notes: After posting this video I found out that it doesn't automatically refresh when you press F9, usually only up to a few thousand rows.

/* These Excel Lambda functions calculate various portfolio statistics from a list of prices (P) sorted in ascending order (from old to new), and portfolio weights (w) */
/* Please note: */
/* 1) Expected values are not guaranteed values, they are based off of historical data */
/* 2) Results will differ with frequency (daily, monthly, etc) and number of observations */
/* 3) David W. Johnk assumes no responsibility or liability for any errors or omissions. This is provided in a "as is" basis with no guarantees of completeness, accuracy, usefulness or timeliness */
periodicreturns = LAMBDA(P,OFFSET(P,1,0,ROWS(P)-1)/OFFSET(P,0,0,ROWS(P)-1)-1);
expectedrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array, AVERAGE(array))));
stdevrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,stdev.p(array))));
varrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,var.p(array))));
excessrets = LAMBDA(P,periodicreturns(P)-expectedrets(P));
varcovarmatrix = LAMBDA(P,MMULT(TRANSPOSE(excessrets(P)),excessrets(P))/(ROWS(P) - 1));
correlmatrix = LAMBDA(P,varcovarmatrix(P)/mmult(transpose(stdevrets(P)),stdevrets(P)));
portexpectedret = LAMBDA(w,P,sumproduct(w,expectedrets(P)));
portstddev = LAMBDA(w,P,SQRT(MMULT(MMULT(w,varcovarmatrix(P)),TRANSPOSE(w))));

[Secretsofsociety]

This is such an important concept that so many of my friends who trade and invest don't understand at all. So many keep going for small-mid caps that swing wildly and over the course of two years ends up under performing QQQ

[richardaristegui]

WOW! I'm taking a Portfolio Risk Managment class right now and this video summed up the first couple weeks of the class! Thank you so much for this great example. It really illistrates everything I have now been exposed to and see practical reasons for learning all the building blocks. I really enjoyed the video and played with the spreadsheet for hours looking up different stocks to come up with better mix of stocks with the current financial situation. Really interesting!!! Thanks!!!

[tomop]

This was just the refresher i needed to help me better understand what Portfolio Visualizer tool is doing. Thank you!

[LeoCavick]

I've just finished a Portfolio Management course and your video really sums up everything, thanks

[saadk]

this is the best video out there for efficient frontier in excel

[Davinder_Kaur]

HI DAVID, I liked your video. You explained everything in one video and did not waste time on one point. Please also post other videos related with optimization methods.

[spencerpencer]

Great video! One thing I noticed is that this is implicitly the efficient frontier in the case where short-selling is not allowed. Note the bottom portion of your graph tails off to become kind of linear. To get the frontier with short-selling, you can specify the min argument of the RANDARRAY function to be negative (e.g. -1) as the default argument is 0. This will allow negative numbers into your portfolio weights. It will also produce a bunch of outlier portfolios, but all you have to do is change the scale on the axes to see the hyperbola.

[RakanXYZ]

You are a wizard! that monte carlo trick, is a life saver.. i've gotta go through this again!

[charis]

Excellent demonstration professor. What I wouldn't do, is to make yearly returns in the way you did it @ 20:10. We are talking about simple compounding for up to 1 year, so I'd recommend x252

[golf_echo_november]

Thank you for this wonderful and very clear guide to a very complex topic to deal with!

[niranviki]

To be honest I would have learnt atleast 15 things in this one video

[falinoluiz]

I can't express how much love I have towards you right now

[peteryu]

Thx for sharing this! One of the most useful videos on YouTube!

[mohammadabusufian]

It is an excellent tutorial. Many many thanks for the outstanding effort.

[frankhuang]

Such a great lecture. Thank you David!

[prathameshdusane]

your this video is so helpful for me but please can you tell me how to you use rand between instead of rand array because i have older version of excel

[exploringeconomics]

thank you sir, this is the best video on this topic.

[AntonioGarcía-eh]

Thank you very much for the great help provided in this video. But I have a question, in the Sharpe ratio calculation, why the risk free return is not introduced? I understand that the Sharpe formula is: Asset return - risk free return / standard deviation. Thank you very much for your answer!

[gonzalocrs]

Hello! Great video, sums up the whole portfolio construction essential concepts. I’d like to know why some people use the log of the returns before proceeding to calculate everything you did and some don’t. Really appreciate it!

[harrym]

Amazing explanation! much appreciated!