Stock Portfolio Monte Carlo Simulation In Excel

Просмотров 31 тыс.

% 765

Ryan O'Connell, CFA, FRM shows how to build a Stock Portfolio Monte Carlo Simulation In Excel.
👨‍💼 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/
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfinance.com/product/stock-portfolio-monte-carlo-simulation-excel-spreadsheet/
Chapters:
0:00 - Define Assumptions About S&P 500 Index
0:50 - Calculate the Ending Value of the Portfolio
2:30 - Create Monte Carlo Simulation Using Data Table in Excel
4:06 - Calculate Stock Portfolio Summary Statistics
6:28 - Create a Histogram of the Monte Carlo Simulation Results
*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

Опубликовано:

3 май 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 43

@RyanOConnellCFA Год назад

👨‍💼 Freelance Financial Modeling Services: ► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/ 💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/stock-portfolio-monte-carlo-simulation-excel-spreadsheet/

@alainrouleau 6 месяцев назад

Very interesting, thanks. I love Monte Carlo simulations. Keep up the good work! 👍

@RyanOConnellCFA 6 месяцев назад

Glad you enjoy it and there are more to come!

@TreyJam2 3 месяца назад

I appreciate the video you explained it well and it was concise 👍

@RyanOConnellCFA 3 месяца назад

Glad it was helpful! Thank you for letting me know. I just looked up your channel and your videos are hilarious! Keep it up

@richardgordon 4 месяца назад

Just learning about Monte Carlo Simulations and this is a very good example!

@RyanOConnellCFA 4 месяца назад

Thank you Richard!

@ManjitSandhu 5 месяцев назад

Thank you for the share. Excellent video and chart. One question.. It seems to be set for 40 years. What if I need to do only 30 years. I do not see that option. If I delete the last 10 years the chart does not function.

@RyanOConnellCFA 5 месяцев назад

My pleasure! You would need to update any formulas that have the range of data in them so that you are not breaking any formulas

@williama.rivera9414 2 года назад

Thank you for share Ryan. Another interesting topic.

@RyanOConnellCFA 2 года назад

You are very welcome and I'm glad you enjoyed it William

@octavearmand4679 3 месяца назад

Hello, Can you explain why we use the norm.inv formula, and why is it more relevant than simply using the random variable ? Thanks

@jonholston1080 Год назад

Wow, amazing tool and description. Thank you. . Investigation into mean-variance of portfolio asset allocation led me here. Where could I find a definitive source of (historical) means and standard deviations of various allocation % across asset classes? . I'm thinking I could plug in those figures for each asset allocation choice into the spreadsheet to get a feel for which allocation breakdown(s) are best suited for me.

@RyanOConnellCFA Год назад

Thank you! You can Google your inquiry and there are a number of different studies that come up. They will differ based on the time periods used. I don't feel comfortable calling any of them the definitive source. You can also just calculate these values yourself using ETFs or indexes as proxies

@paulosewnetu7044 Год назад

Thank you so much sir, it helped me a lot.

@RyanOConnellCFA Год назад

You are most welcome

@mz7877 2 года назад

Hey Ryan thnak you so much for rour interesting videos on VaR, can you please do a video on how to calculate a stressed VaR i've been looking for it in youtube for a good time and nobody explain how to implememt it. thank u so much

@RyanOConnellCFA 2 года назад

Good suggestion, I'll look into that in the future!

@irenemii 15 дней назад

How did you calculate the year end value? It doesn’t appear automatically for me so there is a mistake in table

@navagiopoint Год назад

beautifully explained.

@RyanOConnellCFA Год назад

Thank you!

@monodactyl3975 2 года назад

Thanks for the video! Question: how do you account for portfolios that might have a much higher standard deviation such that you might randomly simulate returns of less than -100% (losing more than principle)

@RyanOConnellCFA Год назад

I think you could use an IF function in excel to wrap around the value and essentially say that if the loss goes below -100% it will just be -100%

@יעקבע-ל8נ 5 месяцев назад

remarkable!👏

@hsf2047 11 месяцев назад

I have come across a research paper in which they have developed regression models using past data (uptil 2016). The pdfs of variables were already known from literature review. For 2017 onwards, they utilized the forecasted values as the mean of the distribution and took 10% of that mean as standard deviation for each year, and performed MCS to calculate annual uncertainty. Is there any relation or basis for taking 10% of mean as SD for future uncertainty analysis? I have analyzed the past data as well SD is nowhere near 10% of the mean value.

@RyanOConnellCFA 11 месяцев назад

That is really strange to me and I can't intuitively figure out why they would use 10% of the mean as standard deviation off the top of my head. That doesn't make sense to me but maybe there is something we are both overlooking

@hsf2047 11 месяцев назад

@@RyanOConnellCFA could you kindly provide me with your email or any other medium where I could share the paper with you along with my working?

@ParkerOrndorf Год назад

What is the main reason we would use a monte carlo simulation? If we can just do the math (Return * n) - zscore*(sqr(n)*standard deviation) for investment returns why would we use the simulation? It makes sense in this use case because it was by far quicker to use the simulation than to do the math by hand, but if there is a use case where doing the math by hand is relatively easy would we want to use Monte Carlo?

@RyanOConnellCFA Год назад

Great question! While mathematical models can be very effective, they often assume a normal distribution and constant volatility, which may not accurately reflect the real-world complexity of financial markets. Monte Carlo simulations offer a way to model random variables with different types of uncertainty, and they allow us to account for events that might not be considered in traditional mathematical models, such as rare events with significant impacts (i.e., black swan events). Simulations can also take into account the potential dependencies and interactions among multiple variables, which could be hard to incorporate into a simple equation. Therefore, even if the math might be easier or faster to compute in some cases, a Monte Carlo simulation can provide a more realistic, detailed, and nuanced view of potential outcomes. However, as always, the choice between a simulation and a mathematical model depends on the specific circumstances and the level of complexity you're comfortable with.

@achalagarwal2004 7 месяцев назад

Hi Ryan what is the time horizon for this simulation?

@RyanOConnellCFA 6 месяцев назад

@2:16 it can be seen that the time period for this simulation is 40 years

@petergriffin4044 Год назад

What can you do about equity research (buy, hold, sell) on monte carlo

@RyanOConnellCFA Год назад

Monte Carlo simulations can be super helpful for stock analysis! Just create a model using historical data, financial ratios, and other factors. Run the simulations, check the risk-reward balance, and see how it fits your investment goals. Then, decide whether to buy, hold, or sell. Just remember, these simulations have their limits, so don't forget to do some extra research and seek professional advice

@handsomehugh Год назад

The chart's y axis should be whole number representing number of iterations rather than currency.

@RyanOConnellCFA Год назад

Good catch on that one hugh

@charlziedouglas-mo7uc 8 месяцев назад

Hi Ryan can the Markov transition matrix apply to this?

@RyanOConnellCFA 7 месяцев назад

Hello, yes, a Markov transition matrix can be applied to a stock portfolio Monte Carlo simulation in Excel to model the probabilities of different market states and their impacts on portfolio returns

@simonr-vp4if Год назад

At 6:59 you should not change the y-axis to dollars - it's a (dimensionless) count of how many trials ended up in each bin. Dollar value is on the x-axis.

@RyanOConnellCFA Год назад

Good point, that should have just stayed numerical!

@leomcrose1180 Год назад

can you combine this with solver?

@RyanOConnellCFA Год назад

If you're looking to find an optimal portfolio using the solver then check out this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XQS17YrZvEs.html