Thanks for watching

Glad it helped.

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?

Thank you. Glad it helped.

Thanks for the comment.

Thanks. Glad it helped

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.

Glad it helped

Thanks. Glad it helped.

Thanks. Glad it helped.

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.

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: kzfaq.info/get/bejne/sNihfJB_x8C3mmQ.html

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: kzfaq.info/get/bejne/qJxnkryD2b_Hgmw.html kzfaq.info/get/bejne/fuCcp5uB3pO-l3U.html

@@MattMacarty thank you. I will watch those videos. Your teaching style is so easy to follow and pleasant.

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.

Glad it helped

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.

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

Thanks. Glad it helped

Thanks. Glad it helped

+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!

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

Glad it helped. I kind of like that one. My four year old took it.

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.

Glad it helped

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: kzfaq.info/get/bejne/qJxnkryD2b_Hgmw.html

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.

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.

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

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.

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.

Glad it helped

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

If you are modeling out more than one day you would need a different formula. See my video on VaR: kzfaq.info/get/bejne/sNihfJB_x8C3mmQ.html

You can download the spreadsheet I used and change the time horizon to anything you like.

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.

I think you can do what you want with something like this: =IF(RAND()

Glad it helped

Thanks. No this is for demonstrating the concept of Monte Carlo primarily.

+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!

Glad it helped

Technically you can do 1,000,000 but I think it will be quite slow.

@@MattMacarty thanks!

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.

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

Thank you. Glad it helped

Because I am only moving one day a time. Something like this is probably more of what you are looking for: kzfaq.info/get/bejne/qJxnkryD2b_Hgmw.html

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

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.

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 .

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: kzfaq.info/get/bejne/a92imNaVrdXTeIU.html

@@MattMacarty Thanks!

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. 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: kzfaq.info/get/bejne/qJxnkryD2b_Hgmw.html

@@MattMacarty Thanks!

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 :)

You can download the file I used by following the link in the description

@@MattMacarty ah okay great thank you

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

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.

Yes it is the std dev of daily price change, but it is just estimated in the is video.

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.

OFFSET is a reference function that picks off a cell value a specified number of rows and, optionally, columns away.

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

How stock prices changed over years >>> kzfaq.info/get/bejne/ptlyhpd9rs_OnY0.html&t

@@sinandmrhn Great visual

@@MattMacarty Thanks

So the theory goes something like an "expected change of 0, with a positive drift".

You should be able to look up the price online or use a data API to continuous update it.

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!

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.

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.

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.

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.

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.

There is a link in the video to download the file used.

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: kzfaq.info/get/bejne/h5t2qpV6t7POgIU.html

Thank you

You can download the spreadsheet used in the demo by following the link the the description

No I don't have any, but I will see what I can do.

Thanks!

What about Potential Future Exposure?

Not sure what you mean.

I made the graph ahead of time. It is a line graph based on the E column.

Sort of. This isn't the traditional way a data table is used.

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: kzfaq.info/get/bejne/sNihfJB_x8C3mmQ.html

@@MattMacarty Yeah a histogram would help a lot. Will see the other one too, thanks.

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.