How to Simulate Stock Price Changes with Excel (Monte Carlo)
Vložit
- čas přidán 10. 09. 2024
- Please SUBSCRIBE:
www.youtube.co...
alphabench.com/...
Monte Carlo Simulation of expected price changes using a stock's current price and historical volatility. Simulate stock price changes in Excel without Add ins using the NORMINV & RAND functions and the Data Table feature. Make a basic Monte Carlo simulation to develop a range within which prices should fall over a specific time frame.
It is absolutely incredible that the the same math tools repeat over an over across different lines of questioning. That you don't have to reinvent the wheel to get to qualitative or quantitative understanding across a wide range of phenomenon. Every application has its own basis, but they all meet under the framework of the numerical analysis. Those who have mathematical basis behind them to implement a Monte Carlo process, only have to explore other realms to work with the other realms, having a solid mathematical basis.
Thanks for watching
Far better than my grad school lectures. Higher education IS NOT what it was 10 years ago. I consistently feel that I’m being lectured by morons within my program. Crystal clear presentation.
Glad it helped.
Thanks! I did this for a school assignment. Your explanation was clear and easy to understand.
Thanks. Glad it helped
@@MattMacarty wow your still here also for a school assignment lol
can I send you my assignment so you do it please?
Amazing presentation Matt. It is beautiful, meaningful and so helpful - exactly what I need in my level of understanding. Thanks so much!
Thank you. Glad it helped.
Great example!
Going through a bunch of your videos to tighten up my concepts. Thanks :)
Thanks for the comment.
Thanks for your kind words. It is very interesting how easily you can generate "trends" from random numbers. But this is part of a much longer discussion that *would* require biscuits, and I suspect airfare.
This was an awesome explanation and use of MCS.
Thanks. Glad it helped
Thanks mate. Always great stuff on these channel!
Glad it helped
Great example. And i think, to make this more like a stock price, we need to add some sales growth data, that will impact on the moving higher
Yes we could use a slightly different model that incorporates the idea of a positive drift. This is just meant to exemplify the Monte Carlo method assuming 0 expected change with and normal distributed volatility.
Thank you so much. Just what I wanted to know! Very clean and understandable. :) I will start to follow you!
Thanks. Glad it helped.
This is simply amaizing! I want to learn more from you!
Thanks. Glad it helped.
Very clear and helpful Sir :)
Glad it helped
Awesome presentatio is an understatement. Ur a wonderful teacher! Kindly do a simple example on autocorrelation, n the markov chain monte carlo simulations.
Thanks you for your comment. I would actually use a more advanced tool to model things like correlation between daily prices. Either the Palisades Decision Suite or Crystal Ball, owned by Oracle.
YOU ARE AMAZING! THANK YOU SO SO MUCH!
Glad it helped
Simply Amazing!!!!
Thanks. Glad it helped
Thank you, keep up the great work!
Thanks. Glad it helped
Great video, thanks for that.
I think it should be mentioned that there is one significant assumption regarding normal distribution (which is not the distribution of stock prices). Also, if you use mean = 0 in the NORMINV function your mean stock price from Monte Carlo simulation will be always almost the same value as starting price of the stock (in the case of infinity tries the prices will be the same). It might be better to use different probability distributions.
Does anyone have any video or tips on how to use or create better probability distribution that can be used in this example?
Thanks a lot, have a nice day.
Thanks. For sure this is meant as an introduction to the idea of Monte Carlo. if I consider just one day out, then it's not too bad to assume a normal distribution, and then I chained a whole bunch of these together. I have a couple other videos where something closer to Black-Sholes is used to estimate volatility: czcams.com/video/zrqI-NbZSj0/video.html
Thanks!
Thank you. Glad it helped
Thank you very much.
Glad it helped
I really find your videos excellent and easy to follow. I am wondering if you have considered using a geometric mean and geometric standard deviation on the models as using the annual volatility and converting it to a daily value by dividing by square root does not get a good true geometric variation.
Thanks, glad they help. The method I am using here assumes an expected change of 0 and then I estimate the volatility. it's kind of a bootstrapping method of building a price path: I use geometric brownian motion in a couple of videos if you want to take a look:
czcams.com/video/r67_YRtYcR8/video.html
czcams.com/video/Hzlt8Py-ZgA/video.html
@@MattMacarty thank you. I will watch those videos. Your teaching style is so easy to follow and pleasant.
I have only access to Google Sheets, do you know an alternate way to do the "21 Day outcome a 1000 times" without the table trick since that isnt avalible in Sheets? Thanks!
Sorry you can't do this in Google sheet. You can try Microsoft Web Apps though. The free version should be enough to do this:www.microsoft.com/en-us/microsoft-365/free-office-online-for-the-web .
A problem with your approach is that stock prices are lognormally distributed. This becomes problematic for stock prices with a starting point closer to the zero bound, particularly if their standard deviation is high. Any thoughts on how to make that adjustment?
That's true, however by iterating each day you end up the with a log normal distribution x days out. But really the video is meant to demonstrate Monte Carlo techniques, not necessarily the best way to simulate a stock price action. I have several other videos that you may like better for simulating price action. There is an option pricing sim and VaR sim on my channel.
@@MattMacarty Fair enough. I was able to use Gen AI to get the right Excel coding for my task. I had to assume a lognormal distribution because the starting price for the asset I was modeling is close to zero (crypto token).
what is the maximum amount of iterations you can run on Excel for something such as this? I have a project similar but must run about 1 000 000 iterations
Technically you can do 1,000,000 but I think it will be quite slow.
@@MattMacarty thanks!
I'm attempting to make some adjustments to the model, but I believe I might be overlooking something. I'm trying to shift from daily stock prices to annual ones. I hypothesized that I would observe the stock price at the end of year 20 (and not day 21), so I used annual volatility (30,42) instead of the daily computed volatility. However, the results I'm obtaining are strange. Firstly, the mean price is no longer centered around the initial price. Secondly, the distributions of quantiles are inconsistent; I'm only observing 25% of simulated results exceeding the starting price, whereas I expect at least 50% on average. What could be the missing element in my approach?
The method I am using would require you to simulate each day out to 252 days. If you want generate a possible ending price in one-step you would need to use a different formulation: price * exp((r - .5 * sigma^2)* T + sigma * sqrt(T) * standard_normal variate)
@@MattMacartythanks, still I don’t quite get it.
The formula you provided is the one which I would use if I wanted to have the price at the end of the period right? (e.g. the price 21 days in the future)
I’m actually trying to “substitute” the days in the future with the years in the future in your calculation. For this I would use the annual volatility and keep the formula you used in the video but I get inconsistent results.
Also, i don’t quite understand why if the daily volatility is higher the distribution of ending prices is not normal anymore.
I’m worried I’m missing something
@@skynet7065Yes that's right, but it's a possible price
For some reason, mine won't work when applying column B3 into the formulae at 2:00
You can download the file I used by following the link in the description
@@MattMacarty ah okay great thank you
Hi. I would like to study stock price and macroeconomic variables linkages using monte carlo simuation boostrap procedure. Could you assist?
Thank
Berzanna
I think this would best be accomplished with a more sophisticated tool, such as Crystal Ball or Palisades Decision Tools. These are add-ins for Excel that you should be able to at least get evaluation copies of.
can you explain the percentile again becasue in the 5% percentile you said there is a 95% that the price will be above that? is that right?
Yes. At the 5th 5ile, only 5% of the time will you see results lower, so then 955 if the time results will be higher than the 5th %ile.
Hello and thank you so much for the video. I have a question. I want to run a Monte Carlo simulation to generate a mean and a standard deviation to create a normal distribution because I only have a capital cost and inflation rate. I know that in the formula you used in this video my standard deviation will be my inflation rate but your mean is 0 for the random walk theory what is there is not media, should I continue using 0?
Thank you in advance
I am not sure I understand your question, but if you are trying to estimate inflation I would think the inflation rate is the mean and you would need to calculate its standard deviation (or maybe it is given).
Hi, why do you divide the daily volatility with sqrt of trading days instead of just trading days. Thanks in advance
There is a fairly complex mathematical explanation, but for simplicity, volatility is the sqrt of the price movement variance and so the calculation would be variance * T, but since we can't really do anything with squared units we take the sqrt.
Hi there, what would be the difference if you were doing a monte carlo simulation for the next 30 trading days with 1000 simulations to calculate the average, median and stdev vs doing a monte carlo simulation for your stocks 'stock return' to calculate average, stdev and risk of loss. Would it make a difference to the daily volatility? As volatility is calculated using stdev on the daily price %. So in that case would you still use the same volatility and instead of using the adj close use the stock return as a starting price? Thanks Matt!
Thanks. There are a few ways to do this. Do you mean to go directly to a simulated price 30 days in the future in one step rather than in 30 steps? If that is is the case we would calculate with a different methodology. Take a look at my option pricing simulation: czcams.com/video/r67_YRtYcR8/video.html
Hi Matt thanks for the video it really helped me for my school project. I was just wondering ... i recently came across an article that said a normal distribution curve is used for modelling the returns in a stock prices and a lognormal is used for the fluctuations in stock prices. If possible, could you help me to understand why you chose a normal distribution to model the changes in stock price? Thank You
Hi. I used normal because it's usually better understood than lognormal. Also since my step is one day there shouldn't be any difference between the two.
Hi. I would just like to ask it was not clear enough for me. Why is the mean in the norminv 0? How did the random walk theory stated that the expected price change is "0"?
If I ask you to forecast how much a stock will change from one day to the next, the best estimate is a naive forecast, or tomorrow's price will be the same as today's. So the expected change is zero. However there is historical volatility associated with the stock, so that while I can not forecast a change I do expect he price to bounce around. The random walk theory actually says the expected change is zero , with a "positive" drift. In other words stock prices tend to go up over time.
Thank you for that information i now clearly understand random walk and and now maybe I can use it on my thesis :)
Even ten years later I dont understand all this concept, because of frensh speaking.(the second column has no label ??) But its look interessant to analyse. The question is how can we have a smaller range for the futur price ?
You can download the spreadsheet I used and change the time horizon to anything you like.
I downloaded a spreadsheet from your website
What does the "offset" do / means which did not show in your video?
thx
OFFSET is a reference function that picks off a cell value a specified number of rows and, optionally, columns away.
Great video!
Glad it helped
hi matt, thanks for the vid, btw I just wanna make it clear, is this annual volatility for stock price or the for the return of the price?
Yes the volatility quoted is annual, which I convert to daily to predict possible price paths with the simulation
Aight. I think you may wanna introduce volatility as it's for return while not price, otherwise people may confuse.
Ar 0:49 of the video, you divide average annual volatility by the square root of the # of trading days. I thought you were going to divid the annual volatility by the number of trading days, not it’s square root. Would you mind explaining the reason for taking the sauare root of total trading days? Excellent video, BTW!
Glad it helped. If you are working with a variance you would divide by number of days. Since we a re working with st dev (square rott of variance), the relationship is the sqrt of days.
Hello Mr. Matt, I copied 2:48 minutes formula but takes error, what can I do? would you share that excel by email?
There is a link in the video to download the file used.
When I press a button after making the table (not sure which buttons, happens with arrow keys definitely), all of the values recalculate. How do I stop this? On Windows by the way.
The RAND function is a volatile function, which means every time you change something in your spreadsheet it recalculates. If you want to freeze the values you could either copy and paste>>special>> values or you could turn off automatic recalculation for tables. This is found in the Formula tab on the right side of the ribbon. Click calculation options and select automatic except for tables. Tehn vlaue will only recalculate when you press F9
Thanks so much for taking the time to reply to an old video and for making the video. Your solution worked, thank you.
Hi Matt,
Great Video. I am researching how to formulate a plan to predict oil price in the future using Monte Carlo simulation and your video seems to be the closest to what I am trying to do.
Do you have any recommendations about how I could go about calculating oil price in the future using monte carlo sim?
Glad it helped
What measure of volatility are you using? Something like implied volatility or beta? I'm getting some information overload when I try to find a volatility number to plug in and give this a try.
I just estimated implied volatility when I made this video. You could download daily data and calculate it if you like. This is more than you need but here I calculate from historical returns: czcams.com/video/5wresdHooHQ/video.html
@@MattMacarty Thanks!
I was wondering if using Implied Volatility as input in place of its historical estimate could improve the quality of the output by providing forecasts based not only on the past information but also on the "forward-looking" set of info we currently have. What do you think?
+Alessandro Dusci Implied volatility is the "market's" best guess of what will happen by expiration. So it increases as known things like earnings are impending. You could use it as an input into the model to compare historical with implied. Sometime a diversion in the two numbers is used to measure whether options are fairly priced. In general implied volatility tends to be higher than historical, which would indicate that all options are somewhat overpriced!
What volitility are you using here? I know it is not SD. Is it beta?
Yes it is the std dev of daily price change, but it is just estimated in the is video.
How come you do not add in the expected mean daily return? With your method the calculated mean should be the current stock price as the volatility will pull it up half the time and down the other half.
+George Kennedy Yes that's right, the expected price "tomorrow" is the closing price "today", and allowing for the same closing price, somewhat less than half the time it will close higher, and somewhat less than half the time it will close lower. But the mean closing price is unchanged. Actually when forecasting a stock price the best forecast for tomorrow is today's price, and if I extend that out a number of days the only change is my degree of certainty which is captured by a larger volatility.
Matt,
When I arrange the simulated 1000 outcomes in ascending order and try to find out the VAR at 99% or 95% confidence interval, How do I find out which the 1% lowest stock price or 5% lowest stock price???????
+Manish Pratap Singh You can use the PERCENTILE.INC and PERCENTILE.EXC functions depending on how you want to think about the prices the simulation generates. I think you may be asking how to sort (or do other things) without the values changing. The way to do that is to copy the values in the data table and then use paste special >> values in another location, like another spreadsheet.
Thanks Matt. One more thing I want to clarify is that you have used simple formula to calculate the daily volatility. Now if i talk about calculating volatility in business like I want to implement the daily volatility calculation in my company software, what model and in which case should I use GARCH model in order to calculate daily volatility or can I proceed with formula which you have shown in your video?????
+Manish Pratap Singh You might be able to use a formula similar to what I have shown to simulate the error term in a regression model, with different parameters. So it's not much different with an expected error of 0, but you will need to figure the standard error in your setting.
Thanks for the video. Very helpful. How would you write a function if you want to get 100 samples with a pre-set probability? For example, in coin flip (head or tail), if I were to set 70% probability, how would I generate 100 Random samples with very roughly 70% heads and 30% tails? Of course, I will want to be able to change the probability rate and get corresponding samples.
I think you can do what you want with something like this:
=IF(RAND()
I think the problem with volatility is that its non directional. If we were truly trying to predict stock price there would have to be parameters in place that account for positive or negative growth - in other words trend.
As you mentioned, we know from the random walk theory that we can not predict stock prices. If we could then everyone would make money in the stock market.
I was hoping for a simulation that took fundamental aspects of stock price into account rather than using strictly historical data. There are much better methods than monte carlo simulations for day trading if that's someone's goal.
This video was not meant to be used for day trading. We are demonstrating possible paths that the stock may take without knowing any additional information. This particular video uses implied volatility which the options markets use to price options. Implied volatility will include any expected events, i.e. earnings, rate changes, etc. - but generally not direction.
Hello Matt,
Isn't the word "simulation" (as in the header of your video) another way of saying prediction which implies application of trading stocks? Correct me if I'm wrong but your header in column D states "Days in Future." If you are not predicting stock prices then why waste time pretending to predict (simulate) stock prices?
To the point,
If the future price of a stock is truly random then I find it pointless to build a monte carlo simulation. I mean, how do you predict randomness? Random by default means its not predictable.
We can argue that more than likely prices will fall within a certain range but the further out we get the more uncertain the price of the stock becomes. Specifically, stock prices are not completely random. All one needs to do is plot a line chart of the S&P500 for the last 80 years and see a clear upward drift with occasional corrections.
Perhaps, the exception (as you noted) are option strategies that are design to capture a short term range of stock prices. I imagine there are more losers than winners that gamble on option strategies built on MC simulations or implied volatility.
I can't imagine anyone in their right mind using a MC simulation to make long term investing decisions. Seems to me MC simulations are better suited for day traders and swing traders who are not concerned with the fundamental aspect of the firm's financials.
You should add to your title: "How to Simulate Stock Price Changes with Excel (Monte Carlo) in the Short Term." Or change it to: "How to calculate Implied Volatility of Stocks."
Sort of. Let's say you have a stock currently priced at $100, with 20% annual volatility. You can predict that there is about a 70% chance that in one year the price will be between $80 and $120. This video is really only meant to demonstrate the Monte Carlo method, not as a price predictor. However, if you know the range within which the price is likely to be at some point in the future you might be able to use something like this to sell option premium.
how to find the starting price if its not given??
You should be able to look up the price online or use a data API to continuous update it.
What determines the model that one must use for the stimulated stock price? so confused about that one
Do you mean why am I using a normal distribution? I am essentially using the same model that is used to price stock options. The model assumes that the expected daily change for a security is 0, but that changes follow a normal distribution based on the individual security's volatility. I calculated it in my presentation as the 21-day st. dev. of the ln of daily price change, so this would be historical volatility. You can just look up any option and it's implied volatility from a number of websites. Historical and implied volatility are rarely the same.
Matt Macarty Thanks for the explanation. Makes more sense now. Cheers
Hey, Matt, nice sim. I would like to ask: have you done any backtesting for this? Thanks!
Thanks. No this is for demonstrating the concept of Monte Carlo primarily.
Thanks for the video. What if I was given expected return of say 9%. How would the formula in cell E4 change?
Hi. If you are given an expected return then you would simply use it directly in whatever forecast you are trying to make. Since I am simulating a price change for "tomorrow", the expected return is 0. An alternative to simulating prices the way I am doing it here is to use Black Sholes, however in that case you still wouldn't use expected return, but you would include the risk free rate. I use it here to use Monte Carlo to simulate option prices: czcams.com/video/r67_YRtYcR8/video.html
@@MattMacarty Thanks!
Hi Matt, great video...very helpful. The data table in this helps to determine the end value of the stock, and then you can figure out the probability of the final value using the data table, am I correct?Quick question, if I had monthly closing prices for 12 month, and I wanted to figure out the probability of the stock prices for the next 12 individual months, how would I specifically be able to tell the probability of month 13 being X, and month 14 being X...and so on, without running simulations for each future month?
+Anik Ahluwalia There are a number of methods to calculate the probabilty, for example you could directly use the Black Sholes formula (there are lots of option price calculators you can access online) or you could use a binomial pricing calculator.
Thanks Matt!
Matt,
I love this video and enjoy the tutorial. I am a bit confused because since variance is proportional to the square root of time, why are you dividing? Shouldn't the Annual volatility be multiplied by Square Root(252)? I am curious to hear back from you on this so I understand what is going on here. Thanks!
-Kodi Billerbeck
+Kodi Billerbeck Somehow I missed your question. Sorry about that. The volatility typically quoted in annual rates is converted to a daily rate by division. So if a stock currently priced at $600 has a 30% volatility, in a year you can reasonably expect the price to be between $420 and $780. We wouldn't expect anything like those big moves in a daily time frame.
Can I apply this model for benchmarks like S&P 500? (I am new to finance)
If you just want to estimate how the S&P might move in a day you can use this kind of simulation. You may also want to see this:
czcams.com/video/Q5Fw2IRMjPQ/video.html
Thank you
Hello, is this model usable for long periods of time, such as years? (i mean the normal distribution hypothesis), great video anyway
If you are modeling out more than one day you would need a different formula. See my video on VaR: czcams.com/video/zrqI-NbZSj0/video.html
Great Video, but in the end you havent used the standard deviation from the 1000 days predictions for anything else at the model?
I use historical data to calculate a historical volatility, which is used to characterize how the security might move day-to-day.
@@MattMacarty First of all, thank you for the lesson and subsequent response. It was what I imagined. In fact, I was a little more "audacious" in the model and used random volatility as well. But between two known values, which in this case were the 10th and 90th percentiles within the year. I did this because I am primarily an options trader. So I thought of leaving the volatility as unknown in the Monte Carlo.
heyy, was wondering what if i wanted to do weekly not daily.
thank you for your videos
+monica dias I would adjust the volatility by multiplying the daily volatility by the square root of 5, the trading days in a week. Or you could find an annual volatility and divide it by sqrt of 52. Any of the volatilities you see quoted on financial sites are annual numbers.
Hey thanks for the video, unfortunately my formula is constantly giving me errors, "norminv" doesnt take arguments such as rand() into the formula" how can I change this? Cheers
This should work =B1+(1+NORM.INV(RAND(),0,B3)) or =B1+(1+NORMINV(RAND(),0,B3))
+Matt Macarty. Thanks for your video. When i am using your data, that is, 620 Initial Price and volatility 1.92%. I am getting simulated price for day one 621.01 not 615.58. Could you please help me out here as why this difference comes out? waiting for your response
+Aasif Shah the prices generated are random based on the mean stick price at he time and the standard deviation . Each time you generate a price it will be somewhat different due to random variation.
Dear Sir thank you for your response. I am actually teaching finance and most of times i am refereeing your videos to my students. For example if i take daily returns of Apple Company for January to March and simulate the prices for the month of April. When I simulate stock price for 01-april, and then dragging down from this cell in excel...I am getting simulated prices for all remaining days but if i again drag or calculate simulated price for second time I am getting totally different simulated prices. Could you help me out why this difference comes out? My students are getting different simulated prices although the returns, mean, SD are same. I don't know how to deal with this issue? If you dont mind Sir, I am expected an excel sheet from you with some simulation on stock prices. my email id: aasif127@gmail.com. Thank you very much for being kind
+Aasif Shah Yes you will get different numbers since the formulas used rely on the RAND() function, Excel's pseudorandom number generating function. The idea behind the simulation is that you don't really know what the price will be tomorrow but if you generate enough possible values by running many iterations you will create a distribution of possible values such that you can use the distribution to make probability statements about the stock being above or below a specific price.
Great video and very interesting. I am wondering if you are actually applying this in the current stock market. How effective is this simulation? Did you make profit by using this method? Would you do a couple of examples for current stocks?
This is a reasonable simulation of price moves, however I am not sure how you would use this to make money. I suppose you could use it to sell options, and collect premium using out of the money options, but you could do that with a basic understanding of statistics.
+splitaxis Well it does help rationalize option premium, and it also gives an idea of what volatility really means -- how much a stock may move in a given period of time. The interesting thing about a measurement like "volatility" is that it actually incorporates things like news and other factors in a handy easy to use number, since stock moving news events are captured in the measurement, making volatility at least as useful as any indicator that is based on past price movement. Stock price movement is not random, but it is impossible to divine, and from a "distance" it looks random which I guess is why so much financial research has been published based on the idea.
+Eric Choi First off thank you Matt Macarty for this great explanation. This is a good way to predict the stock prices; however, you can never accurately predict the stock prices. If you could predict the stock price of tomorrow, you would be billionaire very soon. It is just a random walk model with Monte Carlo Simulation. It however does give you an educated guess with the help of historical volatility. In this video Matt just assumed the starting stock price and used the daily volatility from annual volatility. You can certainly find many youtube video showing stock price simulation. Yahoo Finance is a great source to find the historical stock prices of a company (e.g., use Goog which is a ticker for google to find stock prices of google). From there you can estimate the standard deviation (i.e., volatility) and thereon use Matt's video.
Matt i was thinking of taking a course by you on Python for finance, since i love the way you explain.
However wanted to understand, how should i be prepared to take the course if i am a beginner in python. I have no background of python.
Thanks.
Keep spreading your classic knowledge on excel.
Hi thanks. I basically had beginners in mind with the course. The curriculum, after covering python basics, goes through the concepts covered in a finance course with investing focus.
@@MattMacarty Many thanks. Once again, love your video and the clarity of thought.
Will purchase it :D
if the random walk theory predicts any given stock's expected 1 day change to be 0%, why does the book also cite expected positive annual returns for index funds? If the 1 day change is expected to be 0%...then it follows that the 1 year change is expected to be 0%...
Yes it's a bit of a paradox. It's actually, expected change of 0 with a "positive drift", meaning over time it tends to go up.
Couldn't you select a better profile picture? ;)
Anyway, it was really helpful. Thanks.
Glad it helped. I kind of like that one. My four year old took it.
How can you have a positive expected annual return with a daily expected return of 0?
So the theory goes something like an "expected change of 0, with a positive drift".
How would you then convert daily to hourly? Also, would this do you any good? Great vid btw, looking forward to more.
Daily volatility to hourly volatility*
Mike P
You should be able to convert to an hourly volatility by dividing the daily volatility by the square root of 6.5.
Is it possible to get a graph with the average of all 1000 simulations for each day?
You mean path by path or a histogram of ending prices?
@@MattMacarty path by path. With time on the x axis and price levels on the y axis. I’m not very good at excel so my idea was to use the table trick to simulate price levels at different days, take the avarage out of every simulation and then piece it together on a graph, but I was hoping there might be an easier way?
@@Jak-ub3ds You can do that by replicating the price table (so just put another data table right next to the one there) but it will probably make Excel very slow if you try it for 1000 columns. Anyway the graph will become pretty unreadable if you put too many paths on it. Try 10 or so columns. You can download my spreadsheet here: alphabench.com/data/monte-carlo-simulation-tutorial.html
@@MattMacarty thank you!
Matt, why didn't you use Exponential growth and LogNormal?
Because I am only moving one day a time. Something like this is probably more of what you are looking for: czcams.com/video/r67_YRtYcR8/video.html
Does anyone know how this actually works? Like how would you program or replicate this?
You can download the spreadsheet used in the demo by following the link the the description
What if the initial stock price is 0. Then what is the equation in E4?
So if the initial stock price is 0, it can mean a couple of things, but mainly that the company has no value! While this model breaks down in that situation, it's probably safe to assume not much trading would be occurring.
I never heard of a stock price that equals zero. That means the firm is worthless. Where did you learn about buying and selling stocks?
Even private and bankrupted firms have intrinsic value.
Why doesn't this take drift into consideration? (Return rate)
Hi. You mean as in 0 expected return with a positive drift? I am simulating daily changes sequentially and so if each new day is independent of the last and we assume expected return is 0, the model does not account for the idea of positive drift. I suppose you could use empirical data to estimate the actual return, but depending on how much data you collect this doesn't guarantee a positive drift. You could also set this up to directly simulate say 21 days into the future using some derivation of the Black-Scholes model which incorporates the risk free rate.
How about incorporating dividend payment within the period ?
You should be able to incorporate dividends, which should decrease the value of the stock by the amount of the dividend. It probably won't change things much though. It would be more a factor in option pricing.
Matt Macarty Indeed for the option case. Might be interesting if you plan investing in a certificate with a low barrier with 6 month horizon. You could then estimate roughly the probability of the barrier being touched.
OK, let me see if I can put together an option pricing model and post the video.
do you have any VAR videos?
No I don't have any, but I will see what I can do.
Thanks!
What about Potential Future Exposure?
Hi can you help me I want make clum wen
If
Not sure what you mean.
Why do you need to take the square root of the trading days, why can't we just divide the annual volatility by the number of trading days? Noob here.
The square root of time is essentially an adjustment for the idea that volatility has a variance proportional to the amount of time that passes. Since we don't tend to think in units squared, volatility is typically presented as the standard deviation associated with price movement. It then follows that to present a some future time period's potential volatility that we need to use the square root of time.
The square root of time is essentially an adjustment for the idea that volatility has a variance proportional to the amount of time that passes. Since we don't tend to think in units squared, volatility is typically presented as the standard deviation associated with price movement. It then follows that to present a some future time period's potential volatility that we need to use the square root of time.
1. Select just the simulated price data, so in my video starting at E4 (don't select the D column dat
2. go to the insert tab and select line chart
This should do it.
How stock prices changed over years >>> czcams.com/video/psBS4LIijmY/video.html&t
@@sinandmrhn Great visual
@@MattMacarty Thanks
how to make graph??
I made the graph ahead of time. It is a line graph based on the E column.
how to trick excel?
Sort of. This isn't the traditional way a data table is used.
Do not work in real life😢
Right, it's just a simulation really meant to illustrate Monte Carlo. You could use something like this to determine if options are over/underpriced though.
TBH, I was expecting to see a Monte Carlo chart but none showed. Disappointing
You mean a histogram? So the way I am presenting this a histogram wouldn't make sense. I could directly estimate the sticks ending price with something like black sholes and then so that a1000 or so times to get data for a histogram, but I was more interested in demonstrating possible paths. Try this one: czcams.com/video/zrqI-NbZSj0/video.html
@@MattMacarty Yeah a histogram would help a lot. Will see the other one too, thanks.