Portfolio Optimization in Excel: Step by Step Tutorial

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

👨‍💼 My Freelance Financial Modeling Services:

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

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

[abhishekbal]

Being a fellow CFA and an FRM charterholder I can attest to the beauty of the presentation. Absolutely fantastic. Blown away …. Ryan

[lottis]

My grueling 2-months long portfolio theory course in fifteen minutes. I appreaciate it.

[michaelcruz]

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

[mitchellwalsh]

Your videos are really helping me get through the back end of my finance degree. Thanks for the great content and high quality videos!

[tusharmathur]

Explained CFAL3 Asset allocation chapter in one video! Great!

[williama.rivera]

Excellent information and presentation. Everyday one can learn something new.

[leungwaihong]

great video. u teach the portfolio optimisation much better than my university lecturers. They normally just teach us the stupid method to calculate the optimized ratio for 2 assets only.

[horacioballinas]

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

[ecordionite]

Very clear and concise explanation! Thank you so much!!

[jacobpatterson]

Great video, Ryan. Thanks for the information!

[mmdkur]

Super video, as usual, many thanks 👏👏

[mohamedmadkour]

Many thanks for the super video as usual.

[jimgrant]

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.

[elemdarzayev]

If portfolio solely consisted of stocks, is it appropriate to use simple daily retrun not log?

[evantan]

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)

[prasheelgupta]

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.

[octavearmand]

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!

[chrisyangg]

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

[jboy]

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?

[muhamadfarhan]

Hi, Ryan thank for this great tutorial. Can you show us how to backtest this portfolio?