No video
Monte Carlo Simulation in Excel: Financial Planning Example
Рет қаралды 63,765
Күн бұрынEnjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoff...
Buy complete spreadsheet (must purchase simulation add-in first; see below): www.buymeacoff...
Buy me an item from my wishlist: www.buymeacoff...
CORRECTION: Starting at minute 14:05 I say something that in a way that can be misleading. We are not building the confidence interval (CI) for the NPV. We are building the CI for the average NPV. If you go to minute 14:05 of the video, the CI in row 20 means: there's a 95% chance that the average NPV will be between 27.89 and 34.68 million. This does not mean there's a 95% chance the NPV from a single simulation will be between 27.89 and 34.68. To calculate this latter chance, you go to the cumulative charts at minute 15:44. That chance would be approximately 16.35 - 13.9 = 2.45%. Notice the difference between these two things. I added a warning about this to the closed captions as well.
The simulation add-in I am using in this video: treeplan.com/s... (I'm not sponsored by this company).
What's the expected NPV of cashflows for a 5-year project? What's the chance the project will result in a loss? This video was created for the benefit of my students in the MAS 632/641 classes at Miami Herbert Business School. The SimVoi add-in I am using can be purchased for a small fee (student version) at treeplan.com/. Disclaimer: I am not paid by that company. The reason I chose this add-in is because it works natively on both Mac and Windows computers.
@007mrsilent 3 жыл бұрын
This is the beauty of the excel! This channel needs more exposure! Such a great tutoring skills! Big Thanks to you!
@TallysYunes 3 жыл бұрын
Thank you!
@lagor76 3 жыл бұрын
#2 lockdown 32 days of the series where the quarantine will lead me. So here I am
@TallysYunes 3 жыл бұрын
Welcome to my channel and stay safe!
@NZAbbasi 3 жыл бұрын
And whislt flicking through senless Britain got Talent videos I stumble upon this Gem! KZfaq algorithm is not designed to behave this way :D Thank you for uploading this video
@TallysYunes 3 жыл бұрын
You're welcome. Glad you stumbled around here by accident and liked what you found.
@eben3357 3 жыл бұрын
This can also be applied to a rental property portfolio for estimating rents over time e.g. for a given unit, the initial rental return in year 1 is estimated between $300 to $350 per week. Then, more units are built in the area decreasing rental return in the third year to $290 to $330 p/w. Due to inflation, the rent gradually creeps up to $330 to $360 p/w in year 5. If you are thinking of purchasing another unit, or, a renovation project, or, building from a plan, this sort of analysis becomes critical also while considering a particular individual's equity and choosing between different towns/locations where to purchase property. Bear in mind there are many other variables to consider.
@TallysYunes 3 жыл бұрын
Absolutely!
@lumberjaxx3335 3 жыл бұрын
This was an awesome example of a monte carlo simulation combined with NPVs and staistics, i really appriciate this as i have almost the same topics at business school right now (apart from MC simulation) but i was always interested in the MC simulation so thanks a lot for showing this step by step, i will also purchase the programe
@TallysYunes 3 жыл бұрын
Glad you enjoyed it. If you're interested in MC simulation, I have a few other examples of it. Just look through my "Excel Models" playlist. The Arcteryx one, in particular, is the first one I show my students before this one.
@Untilitpases 3 жыл бұрын
It clicked. Thank you for clarifying it and making this video public.
@TallysYunes 3 жыл бұрын
You're welcome. Happy to hear it was helpful.
@irmahersel409 2 жыл бұрын
This is helping, I have to do a simulation and we are not supposed to use solver so I am looking up other ways.
@pablodbarros 3 жыл бұрын
I've to say one word about it: amazing! Thanks for sharing your knowledge with us!
@TallysYunes 3 жыл бұрын
Thank you! My pleasure!
@louisliu6584 3 жыл бұрын
Brilliant session Sir! Appreciate it!
@TallysYunes 3 жыл бұрын
Glad you enjoyed it!
@williamelliott5999 2 жыл бұрын
My memory of statistics and probability is a little rusty but I'm not convinced you're right with regard to the confidence levels, You have a NPV distribution which is actually driven by the interaction of 5 instances of a triangular distribution (one for year one sales and four for decay levels) together with weighting and summing the results. You appear to be then treating the output as having or approximating to a normal distribution in using the 1.96 times standard deviation multiplier to get the 95% range.
@TallysYunes 2 жыл бұрын
Hi William. Good question. If you look at the histogram plot of the 1000 NPV outcomes, it is indeed not like a bell curve. The probability distribution of the NPV of each simulation run is not normal. However, and this is what the famous Central Limit Theorem is all about, the average of those 1000 NPVs, which is a random variable, does follow a normal distribution. Because we are building a confidence interval for the average NPV, we can assume it is normal and use the 1.96 value. I talk a bit more about this in this video: kzfaq.info/get/bejne/n7Z3rM-nu62vpac.html
@wesleyfound3577 3 жыл бұрын
Interested in how you created the user defined randtriangular() function in excel. Really insightful videos. I am thoroughly enjoying them
@TallysYunes 3 жыл бұрын
Glad to year you're enjoying the videos. Not sure I understand what you mean by how I created the function. The randtriangular function is provided by the simulation add-in.
@wesleyfound3577 3 жыл бұрын
@@TallysYunes Thank you. I was reading the plug-in website but have yet to download. Answers my questions :)
@fap9067 3 жыл бұрын
Thank you Professor. What I didn't get was why the NPV has around 29% possibility of being negative and at the same time has about 95% of being positive (between a range of values).
@TallysYunes 3 жыл бұрын
I think you misunderstood something. There's a 29% chance of the NPV being negative. Where are you getting the 95% chance of being positive? That can't be. The chance of positive NPV is 1 minus 29% = 71%.
@KunaalBhagani 3 жыл бұрын
@@TallysYunes would it be right to say there is a 71% chance of getting a positive npv. and if it is positive then we are 95% confident that the npv will be between 27m and 34m with the estimated npv being 31m? curious how to word this right.
@TallysYunes 3 жыл бұрын
There's a subtle thing going on here. Each time you run the simulation, you'll get a spread of NPV values, which is given to you by the cumulative chart on the right-hand side of the univariate summary tab. That chart allows you to estimate things like: what's the chance that the output being monitored (in this case NPV) will be below a certain value, or above a certain value, or between two given values. Another thing is the AVERAGE or EXPECTED value of the output being monitored. That is the number you get when you take the average of the value of the output over all the trials you ran. In our example, we ran 1000 trials. When we average these 1000 NPV values, we get the EXPECTED NPV. Let's say I ran this simulation, and you ran this simulation, and several other people watching my video ran the simulation on their computers. Because of randomness (and assuming we all used different seeds, which happens by default), all of us will obtain a different value of EXPECTED NPV (the mean in cell B4 of the univariate summary tab). The confidence interval we calculated says that 95% of the people who run the simulation and compute this mean or expected NPV will obtain a value that is inside that interval. So we are 95% sure that the *mean* NPV of any given simulation run with 1000 trials is between 27.9 and 34.7 million.
@cunymathguy5534 3 жыл бұрын
I'm not part of your Class but this Video is Amazing!
@TallysYunes 3 жыл бұрын
Thanks! Happy to hear it was helpful.
@stephenburgan7245 3 жыл бұрын
Excellent video! Your teaching style is very easy to follow. As a construction estimator, I've been interested in using Monte Carlo analysis to help me determine the level of confidence regarding my cost estimates. I have tried using @Risk but the level of instruction I got for that software was too complicated for me to understand. What I've learned about Monte Carlo is self-taught, so I have a lot of gaps in my knowledge. One of the questions I have regarding your example is the formula you used to calculate the triangular distribution. You used =randtriangular(E3,F3,G3). Is the formula =randtriangular(E3:G3) the same? I didn't know if the formula randtriangular needed to have the three values separated by commas. I have a lot to learn and want to understand the nuances so I can rely on what the Monte Carlo analysis is telling me. Also, do you offer online classes that I can attend? I want to learn more about about Monte Carlo, TreePlan and Sensitive Analysis to a better job of estimating my construction projects. Thank you for these videos. I learned more from this video that I have from attending workshops by some of the "experts".
@TallysYunes 3 жыл бұрын
Hi Stephen. Thank you for the feedback. I do not offer online classes to the general public. The triangular distribution does need 3 parameters, in order, the lowest possible value, the most likely value, and the highest possible value for the random number in question. It's a good model of a random number to use when you don't know much else than these three quantities. If you haven't seen them yet, I have a couple of other videos on Monte Carlo simulation examples that you could watch. Go to my Excel models playlist here and scroll until you find them: kzfaq.info/sun/PLB96NKn65ikokWCuX2ScxdFDe7pqKf-jX
@bartosak 3 жыл бұрын
Wow! Thank You - Perfect teaching skills
@brandonjohnson8880 3 жыл бұрын
Interesting. Correct me if I'm wrong but mustn't you be sure that a distribution is normal before you use confidence normal confidence intervals? Also the median =/ mean on this distribution so shouldn't our actual most likely NPV be higher than what is shown here? Ie. Using median as center of confidence band?
@TallysYunes 3 жыл бұрын
Good question regarding the normal distribution requirement. Short answer is: yes, you're right, but it turns out that the expected value of an output monitored in a Monte Carlo simulation will follow a normal distribution. For a more detailed answer, see this video, which I ask my students to watch before the first Monte Carlo class: kzfaq.info/get/bejne/n7Z3rM-nu62vpac.html As for the second question, I'm not sure I fully understand what you mean. The confidence interval I create in this video is for the mean, not for the median. And if you consider that there's a continuous range of possible NPVs it makes more sense to think of the likelihood of a range of NPVs, rather than a single NPV value. This range can be as wide or as narrow as you want. To find its likelihood, you can go to the cumulative chart table (right-hand side of the univariate summary tab).
@nonsensepoopypants7415 3 жыл бұрын
This is a great video. We used triangular distributions and monte Carlo simulations in one of my engineering classes to simulate project timelines, mechanical maintenance and repair times, etc. I like how you use it to calculate financial risk. Do you have any examples of using this method for equity investments?
@TallysYunes 3 жыл бұрын
Thank you for your comment. Unfortunately, I don't have an example of the type you describe. I myself need to learn more about finance in general :-)
@nonsensepoopypants7415 3 жыл бұрын
@@TallysYunes I was thinking that after due diligence, and you've narrowed your potential investment down to 5 or so companies with good balance sheets, you could use the 52 week highs, lows, and expected average price for the triangle distribution. Or the projected expected earnings. Would be interesting to set your buy and sell points based on probability, rather than emotion.
@nonsensepoopypants7415 3 жыл бұрын
@@TallysYunes Also, to get more viewers to your channel, you can reach out to an investment youtube channel and do a collaboration video. Their viewers would probably check out your videos after seeing you on the other channels video.
@koji.o 5 ай бұрын
Subscribed! I hope there's also a version using purely excel formulas, without using 3rd party paid programs such as 'Simvoi'
@TallysYunes 5 ай бұрын
Thank you! There may be, but I'm not aware of any native Monte Carlo facilities for Excel other than the brute force way that can become very time consuming.
@Ickabodxx 3 жыл бұрын
Now to figure out how to apply this to daily fantasy football
@sarimzahid3481 3 жыл бұрын
Excellent video!
@TallysYunes 3 жыл бұрын
Thank you!
@rolandumlauft1109 3 жыл бұрын
I think your 95% confidence interval is confidence about the distribution mean, so what you say that you can be 95% sure that your NPV will be between27,9 and 34,7M is incorrect. You can be 95% confident that the mean NPV should be in that interval, right?
@TallysYunes 3 жыл бұрын
You are absolutely correct. I must have been distracted and said it incorrectly. I added a note in the description of the video and also added a textual warning in the closed captions (and forced closed captions to be turned on by default). Will see if there's a way to paste a banner on the video at the correct moment pointing out the mistake. Thanks for catching this!
@DanielUtsch 3 жыл бұрын
A really interesting video thanks for uploading it. Watching and thinking about it one question came to mind though: You are using the normal distribution for producing your confidence intervals. Would it not be necessary to first go through distribution testing for normality before using it? You have a skewed distribution that (at least with my eyes) does not look normally distributed. It would be great to get your take on this questions and why using the normal distrubition is allowed if you disagree with my observation.
@TallysYunes 3 жыл бұрын
Good question. The histogram of the resulting NPVs from the 1000 simulation trials does not resemble a normal distribution. However, we are creating a confidence interval for the expected NPV of several simulation runs (and that one does follow a normal distribution). This is a subtle difference that is explained in more detail in this video, which I ask my students to watch before the first Monte Carlo class: kzfaq.info/get/bejne/n7Z3rM-nu62vpac.html
@SF-fz9qr 3 жыл бұрын
Hi, does this mean you should have run more simulations to get the "perfect" mean for the confidence interval (referring to you video about the theory behind the Monte Carlo simulation) Because in your case you picked the first mean even though it’s not normally distributed. If yes, how many simulations do you recommend to build the desired normal distribution and isn’t this really time consuming? Thanks for your videos and your help!
@TallysYunes 3 жыл бұрын
To avoid confusion, let's establish that a trial is one calculation of your spreadsheet so that, for each random number it contains (i.e. the RAND functions), one randomly chosen value is assigned. A simulation is a collection of many trials whose outputs are then plotted and averaged out to produce the reports we look at. One of the outputs produced in these reports is the average (a.k.a. expected) NPV. That is the average of the NPVs of each trial of the simulation (in our case, an average of 1000 NPV values). This average NPV *does* follow a normal distribution (by the central limit theorem). That is, if I ran a 1000 simulations and plotted the histogram of the resulting 1000 *average* NPVs, that histogram would look like the histogram of a normal distribution. Therefore, the average NPV of *any* simulation can be used to calculate a confidence interval for the *true* average NPV of the distribution of the average NPV. So running multiple simulations doesn't really add value. One thing you can do is to run a simulation with a larger number of trials. This will make the confidence interval around the average NPV narrower because it will reduce the mean standard error (which affects the width of the confidence interval).
@SF-fz9qr 3 жыл бұрын
@@TallysYunes Thanks for your quick response! I totally get the second part about the x simulations (which aren't required if we assume the average NPV follows a normal distribution). However, maybe you can help me point out my thinking mistake: One trial results in one NPV (pressing F9 we get another one, and so on and so forth...) One simulation (in our case consists of 1000 trials) creates 1000 NPVs. Then we plot this simulation (those 1000 NPVs) and calculate the average NPV. Based on this average NPV we build the CI as the average NPV follows a normal distribution. This suggests the created plot (resulting from this one simulation) has to be normally distributed. But it isn't though? According to the CLT, they always talk about plotting the sample means, that is way I came up with the assumption that we need to run x simulations (which results in x NPV means) and plot those x NPV means and calculate an NPV mean on those NPV means, which then will be normally distributed.. Hope you get my point and can help me out, thanks for your patience and assistance.
@TallysYunes 3 жыл бұрын
You were correct until the "this suggests." The NPV of a single simulation does *not* follow a normal distribution (as you can see from the histogram of a single simulation). We are not building the CI for the NPV. We are building the CI for the *average* NPV. If you go to minute 14:05 of the video, the CI in row 20 means: there's a 95% chance that the *average* NPV will be between 27.89 and 34.68 million. This does *not* mean there's a 95% chance the NPV from a single simulation will be between 27.89 and 34.68. To calculate this latter chance, you go to the cumulative charts at minute 15:44. That chance would be approximately 16.35 - 13.9 = 2.45%. Do you see the difference? I realize now that the way I spoke at minute 14:05 can be misleading. I'll add a note below the video description and a text in the video warning viewers.
@radeksevcik6661 3 жыл бұрын
Great video, thank you for sharing your knowledge, but could I ask you something? Why did you use triangle distribution? Why is this distribution the best to use? (why not BetaPer for example)
@TallysYunes 3 жыл бұрын
No particular reason other than the fact that the story said to use triangular (I wasn't the one who wrote the problem statement). You could replace it with any other distribution that you believe better represents the uncertainty in your numbers. The rest of the analysis remains the same.
@radeksevcik6661 3 жыл бұрын
@@TallysYunes thank you very much. And I am looking forward to watch another of your interesting videos.
@sgv13full 2 жыл бұрын
Thanks!
@TallysYunes 2 жыл бұрын
Thank you so much!
@michelacason5199 4 жыл бұрын
May I ask whether it is statistically correct to use the inverse of a normal distribution to compute the confidence level? I suppose the NPV is not normally distributed, since it depends on two triangular distributions that don't seem to resemble a normal distribution themselves. But I also think using normal distribution for confidence interval might be a valid proxy.
@TallysYunes 4 жыл бұрын
The expected or mean NPV, which is what is being estimated, will follow a normal distribution regardless of the distributions of the random inputs (this is true because of the Central Limit Theorem). So the confidence interval calculation is correct. See this video for a more detailed explanation: kzfaq.info/get/bejne/n7Z3rM-nu62vpac.html
@tokhmi2006 2 жыл бұрын
@Tallys can you show us what you have in the Nu Bank tab? Curious what analysis you were doing on that company.
@TallysYunes 2 жыл бұрын
There's an entire video about the simulation model for the particular example in that tab here: kzfaq.info/get/bejne/or1hpLySxJ2pXWQ.html
@ChiChi-sw5iu 4 жыл бұрын
Thank you so much for this!
@TallysYunes 3 жыл бұрын
You're welcome!
@braedonleslie712 2 жыл бұрын
Hey @Tallys, I really enjoyed this video. I have a question: What if you are putting together a proforma and due to a lack of volume in Y1, possibly Y2, etc., you have a negative margin. How would you get the model to account for that?
@TallysYunes 2 жыл бұрын
Without knowing all the details involved, it seems to me that it would be just a matter of letting the margin number go negative (either as a constant input value or as a randomly generated number).
@braedonleslie712 2 жыл бұрын
@@TallysYunes Got it. I opted to create a range for Y1 costs using randtriangular - doing so allowed the Y1 net income to dip below 0.00 when appropriate. Much appreciated.
@boukhaladnane3165 4 жыл бұрын
Hi Sir can you tell me how to stop the Add in because i want to calculate something in Excel without taking into consideration the simulation
@TallysYunes 4 жыл бұрын
I'm not sure I understand your question. If you want to disable the simulation add-in, go to the configurations where the add-in check box is checked, and just un-check it.
@machkouratibrahim7949 9 ай бұрын
Where do we add Long-term debt on DCF Analysis ?
@TallysYunes 9 ай бұрын
I'm not a finance person, so I'm going to need you to give me a thorough explanation of what you're trying to do exactly. Beginning with the definitions of "long-term debt" and "DCF Analysis." Haha!
@ecaterinacazacu5004 3 жыл бұрын
Hello, I have a question, how we can add the “randtriangular” formula in excel , because I don’t have it. Thank you :)
@TallysYunes 3 жыл бұрын
This additional functionality does not come as part of Excel by default. You need to install an add-in for Monte Carlo simulation. There are many to choose from. The one I use in this video (and in the course I teach) is this one: treeplan.com/simvoi/
@nehap1749 4 жыл бұрын
Thank you Professor for the informative video. I have a question- Do you think the management will go ahead with the project in this case with a positive mean NPV but around 27%~30% chances of getting a negative NPV? How will the company decide or what other factors company need to look to make that decision? Thank you!
@TallysYunes 4 жыл бұрын
This question has no single right or wrong answer and will depend on the leadership's appetite for risk as well as other factors, like you indicated. For example, do they have reserves to cover the downside in case it happens? What about the upside (that is if things go very well). There's a large degree of subjective decision making that needs to take place, but at least you have more data to inform that decision.
@nehap1749 4 жыл бұрын
@@TallysYunes Thank you! That is really helpful.
@viswanathtumu2097 4 жыл бұрын
Can you provide a link to download the PDF and Excel file for this very nice example?
@TallysYunes 4 жыл бұрын
All you need to know about the story, I explain in the video. And all you need for the Excel sheet is what you see written in the sheet I use in the video (the data). There are no pre-typed formulas in any cell.
@hirwajeanluc6294 2 жыл бұрын
@Tallys, I tried run this simulation as you did it (except 20 years instead of 5). However, my simulation results come with the Mean being exactly the same as the NPV, the St. Dev. and Mean St. Error are 0, and Skewness +1.000. Do you know why that is coming like that?
@TallysYunes 2 жыл бұрын
When the std dev comes back as 0 it's usually because the formulas in the cells that are being monitored as output cells are not referring to the cells that contain the randomness in the model. That is, when you refresh the sheet by pressing F9 on Windows or Cmd+'=' on the Mac, the output cell doesn't change in value.
@hirwajeanluc6294 2 жыл бұрын
@@TallysYunes That is very helpful. Thank you very much!
@ChiChi-sw5iu 4 жыл бұрын
Hi Sir. I do not have randtriangular in my excel. How might that be?
@TallysYunes 4 жыл бұрын
You need to install an add-in that can be purchased here: treeplan.com/
@IAP_mkt 3 жыл бұрын
thank you so much! obrigado
@TallysYunes 3 жыл бұрын
You're welcome! De nada!
@isabellagallardo2200 Жыл бұрын
How do we get the annual margin decrease and the discount rate ???
@TallysYunes Жыл бұрын
Those numbers were part of the story. Input data. The margin decrease was probably an estimate the company's forecasting team came up with.
@MrWalo1990 3 жыл бұрын
What kind of add-ins you used for the simulation.
@TallysYunes 3 жыл бұрын
See description box below the video.
@MrWalo1990 3 жыл бұрын
@@TallysYunes Thanks
@SuperReddevil23 Жыл бұрын
How to run this in windows excel
@TallysYunes Жыл бұрын
In the description box below the video I provide a link to a site where you can buy the add-in that I'm using, which adds the Monte Carlo simulation functionality to Excel.
What the Meowk!
Рет қаралды 9 МЛН
WallStreetMojo
Рет қаралды 33 М.
Tallys Yunes
Рет қаралды 30 М.