Monte Carlo Simulations: Run 10,000 Simulations At Once
Vložit
- čas přidán 10. 09. 2024
- Run Monte Carlo simulations in Excel with this simple workaround.
Produced by Sara Silverstein
------------------------------------------------
Follow BI Video on Twitter: bit.ly/1oS68Zs
Follow BI Video On Facebook: on. 1bkB8qg
Read more: www.businessins...
------------------------------------------------
Business Insider is the fastest growing business news site in the US. Our mission: to tell you all you need to know about the big world around you. The BI Video team focuses on technology, strategy and science with an emphasis on unique storytelling and data that appeals to the next generation of leaders - the digital generation.
Amazing what this woman taught in 3 minutes. Honestly, spectacular! Used what she taught successfully.
This woman is able to demonstrate complex calculations using simple methods, which is stunning.
absolutely brilliant, can't believe so much was stuffed in in just 3 minutes
Thanks! Half the internet said you couldn't do this, but I knew there had to be an easy way. This was perfect!
There is a saying in Russian "коротко и ясно", meaning "clear and concise". This is about this video.
Just the exact tutorial I have been looking for. She is the best.
This was VERY helpful…and all in less than 4 minutes! Now can I get back the 2+ hrs I spent watching the other MCS videos? 🤔
Thank you for taking the time out to explain this in such simple terms.
You're welcome!
Nothing is interesting about the Monta Carlo simulation of a single distribution/ It is only useful for analyzing the combinations of distributions.
Hey! Could anyone please explain to me what she did for cell "Tuesday" x "Cumulative"? Thanks y'all!
really love this video. finally understand the meaning of Monte Carlo
Quite interesting. You should doubled the trials to see if your outcome was approximately the same. 📊
Thanks for great video! One question, in your example, you simulate weekly return actually, is it equivalent to use weekly return mean and weekly return stdev to proceed? ( is it equivalent to scale daily parameters to weekly then proceed ?) Thank you!
very much to the point explanation. thank you!
Thankyou for helping out with the school project
My 3 hrs of lecture in just 3 minutes.. and much better understood..
Very nice use of Excel's Data Table! Thanks for sharing Sara.
Kevin Lehrbass Thanks! I think we need a little more Data Table love in general. It is probably time for a Data Table video.
Thanks Sarah. Valuable content!
Thanks Sarah.
Clever girl. Liked the direct approach. Thanks.
I have been waiting for this day.. hurray. I was looking for how to save all your videos on the BI website.. the opportunity is here.
Thanks Emmanuel! I'm really excited about building this channel.
You are a wizard, Sara.
@BI EXCEL On the first attempt I got the random numbers to generate, but when I did the same thing it started generating the same number for the different trials. I don't know whats happening. Please help. Thanks :) I already tried redoing it on a new workbook.
Pretty big assumption that the S&P daily returns are normal. Cool video!
S van der Heijden it is in fact Gaussian
@@davidgutierrez8795 can you please provide a source, David?
@@SenorJoeBiden do your own research man, lol, there are plenty of sources online...you can also do the calculations and create a distribution chart with the % changes of the returns, you will see it follows a Gaussian distribution or more exactly a Cauchy distribution...
@@davidgutierrez8795 The burden lies on you to provide evidence. You said that it is Gaussian and then said it's more exactly Cauchy. Gaussian and Cauchy are two different distributions with very different structures. For one thing, Gaussian tails are too thin while Cauchy tails are too heavy. Both do not accurately describe S&P daily returns.
I have formula that can produce both negative and positive values but my output can't be negative. I try to treat negative values as zero but it truncated my output to positive and provide higher mean value , this help to over estimate the mean. If there is any way we can re-run excel formula to get only positive outputs for any iteration.
6 years late but abs
Speed everything up with VBA code, VBA formulas, Excel formulas through VBA , freeze the screen (screen.updating=false) first then compute everything then unfreeze the screen. Your computer can generate several hundreds of millions of pseudorandom numbers in a second and this was 15 years ago when I built simulation for bootstrapping confidence intervals for forecasts.
If you have the skill and the time, yes. But in places with security concerns VBA and macros may be forbidden. I find that can be a problem when running courses.
Also for small jobs writing and debugging a macro is slower and more error prone than the simple technique demonstrated here.
For serious work I would prefer to use a monte carlo package - the real cost of writing macros and creating code for sampling from different distributions or correlated variables, and graphing the outcomes can be shockingly high.
Nice video! Just why are u using (A+1)*(B+1)-1 instead of simply adding (A+B). Is an error to simply add the percentages in the cumulative?
I think no! It's easier A+B
I think she is coumpunding the returns which makes more sense than simply adding them
Yes, compound interest is multiplication.
Thank you very much! Still very helpful!
double A++ on the Excell, but I have serious reservations about the math. Since ever percent comes from a different base, growing bases shrinks returns and shrinking bases increase returns thus cycling produces growth estimates which probably are not real
Thanks for the solution
Very good explanation - precise, clear, and applicable. More power!! :)
Thanks Sara!
Hi.
We would get a Very similar result by taking the weekly average returns with r=(1+0.0003)^5, and std deviation with std=0.0097*sqrt(5).
Is this results only coincidence, or is usual that these approaches ends up in very close outcomes?
Nice video. Regards from Brazil!
What's the formula for NORMINV though? I can't see how this function works anywhere as I am trying to code this in PHP
Great tutorial. Thanks for sharing
Where did the equation come from at 1:13, the cumulative?
GRacias!!!!!, please more videos on power BI, excel, R,Phyton.
amazing explanation. Thank you!
You're welcome!
Hello I would to ask. How can I make Monte Carlo simulating if i have original numbers like: -150, +200,-90, +15.12, -55, +114,+113+500...... And i want to this numbers place tho the simulation and become another sequence. How can I simulate this numbers?
It should work in exactly the same way, just that your average and standard deviation will be numbers instead of percentages.
Fantastic!
Does anyone know of a way to do this same type of thing (simulate 10,000 times at once) in google sheets?
is 10k the max amount of simulations you can run?
thanks. nice short and sweet explanation.
you're welcome!
Great ... just great job...
KILLER video.
Great thanks
The BEST
Brilliant!
Hey this is an awesome account and I can't wait to see more!
Can you quickly explain how to take this example one step further? Could you expand to show how to make this specific model project the probability of the S&P 500 fluctuating 5% MONHLY? Thank you this will be a big help!
To analyze a month instead of a week you need to add a bunch of rows and change Mon-Fri to 1-21. (There are about 21 trading days in a month). And copy the formulas down for all 21 days. Then you will run your analysis on the cumulative return on the 21st day.
Hope that helps!
Amazing
Great video!
When you say the daily Standard deviation is 0.97%. it is the st.dev of the stock price or the returns?I think it has t be the st.dev of returns.can some one confirm
It's the standard deviation of the daily returns.
How are you getting % sign on the days numbers
Bravo! Subsribed!
I love it
Cool
Excellent
Omg. Love you queen
Thnkssss...
I love you.
where did you get the data from? is there any website I can find the information, too?
Get the daily historical data from Yahoo finance and use excel to calculate the daily mean return and standard deviation
If you get a bunch of the same numbers, press f9 and that may refresh it so that you get the random numbers you hoped for.
great!!!
Hi mam plz explain how to use this tricks in stock market plz
confused why you do (A+1)*(B+1) -1?
It's to reflect the fact that the growth rates are cumulative. Assume the stock index grows by 1% on Monday and 2% on Tuesday. Then by the close of Monday the index level is (original value)*(1+1%). On Tuesday, the growth applies to its original value plus the growth that occurred on Monday. So by the close of Tuesday the level of the index is (original value)*(1+1%)*(1+2%).
If you want to express the total growth of the index as a percentage, the formula would be:
[ (original value)*(1+1%)*(1+2%)-(original value) ] / (original value)
This simplifies to (1+1%)*(1+2%)-1. This is what is being reflected in the formula.
You are absolutely right but that isn't what she used in her calculation. Using your example, she used ((1+1%)+1)(1+2%). Please double check and let me know.
Thnksss...
That is because you want the cumulative change between days. Take day 1 the change is 1%, day 2 the change is -0.3%, for the interval the total change would be: Initial value *(1+0.01) this gives us the value at the end of day 1, we continue: Initial value * (1+0.01)*(1-0.03) this is the value at day 2. If are interested in the porcentual change for the two days period is simply (1+i) being i our change for the two consecutive days. (1+0.01)*(1-0.03)=(1+i), if we solve for i we get: (1+0.01)*(1-0.03)-1=i , the used formula is correct.
Why are you using norminv?
+fadi al naji
She is assuming that the S&P 500 returns are normally distributed. To get a meaningful result out of the simulation she is generating random returns which follows the same distribution. For this step she is using norminv.
She samples a normal distribution with the daily mean and std of S&P500. Therefore, if you choose a random number between 0-1, and take that together with the other parameters, you get as output the value of the S&P500 being less or equal with the used random probability.
What if we only want the positive return to price a european call? Thank you very much! :)
Try this: czcams.com/video/r67_YRtYcR8/video.html
what a badass
Hey, woman! Come back to youtube!!!
I have some questions
How can i do if data isn't not normal distribution?
+Jek Jekkey
There are some plug-ins that enables excel to generate random numbers, which follow particular distributions, e.g. log-normal distribution, triangular distribution,... One plug-in is SIP-math
Instead of a random normal number you could also do a randomly chosen bootstrap sample to generate the returns, and use the same "data table" to pick the sample 10000 times. For instance you could use random 3 month samples out of the historical weekly S&P historical returns 1871 to present. The sample size is key though since if you make it too small (e.g. one week) you'd lose the serial correlation (return to mean) that separate stocks from random walks.
The cumulative calculation is questionable. Who says the Video needs to be hurried upin 3 mts, leaving doubts to those who watch it.
Brill video Sara
Have a good day
Do these a week before covid 19 shut us down... Place your bets and get wipe out :) . Cool excel tips.
Hit me up, i will show an easy way around for this one 1:25
Very fast, difficult for beginners
slow it down to 0.5 the normal play speed!
good tutorial but this method isn't accurate...
That's why I stick with python. All those clicking around and adding formulas on a spreadsheet drives me crazy.
HI Ahmad , how u use Python to combat these formulas inexcel. I m learning python, but it will be great if u guide me a bit. @ ak7862000@gmail.com
how woud I even graph this?
With a histogram. It would show you the distribution of possible outcomes.
OHHHHHH HOTTIE ARRIVED AT THE END OF THE VIDEO...!!!!
Jı
This video raises more questions than it answers. You've taken S&P500 data and completely butchered it in order to make a 3 minute video of a MC simulation. I hope nobody is placing trades based information from this video.
I think the point of this video is to demonstrate how to conduct a Monte Carlo simulation, and not how to invest your money based on S&P 500 data.
in israel sarah and silverstein are common names