Monte Carlo Simulation¶
Assign probability distributions to inputs to be able to get probability distributions of outputs. Enables a much deeper understanding of model results and especially the risk of a result.
Resources¶
Introduction to Monte Carlo Simulations¶
Notes¶
Monte Carlo simulation is the external counterpart to internal randomness
The core model is still (probably) deterministic, but then we add randomness into the model by randomizing the inputs to the model and running it many times
Adding this randomness allows us to answer deeper questions about the problem, such as what is the chance of some outcome occurring
The process is the same as sensitivity or external scenario analysis, just run the model multiple times with different inputs. Only here we are randomly drawing the inputs from distributions
We will also do some additional analysis on the results from the MC simulation
Transcript¶
- hey everyone
- nick duraburtis here teaching you
- financial modeling today we're going to
- be doing an
- introduction to monte carlo simulation
- and this
- is part of our new lecture segment on
- the same monte carlo simulation
- so we've explored in this course already
- a couple other ways of exploring the
- parameter space
- looking at different inputs and how they
- affect our model
- we've already looked at sensitivity
- analysis and scenario analysis
- and now monte carlo simulation comes to
- round out that set of possibilities
- so monte carlo simulation
- is unique in that it allows you to take
- a deterministic model which has no
- notion of randomness or
- probability and be able to make
- conclusions
- about the chance of certain outcomes
- occurring
- in your model so you're able to take
- this model with no probability
- in it at all and add it externally
- without having to change the core model
- itself
- um so that will give you a good
- understanding of
- not just what is kind of the expected
- outcome from the model
- but also what's the full range of
- possible outcomes and
- what is the chance of each of these
- outcomes occurring
- and certainly in finance this is an
- important thing to consider
- because we're always concerned about the
- risk return
- trade-off and we can
- think of in a sense the baseline output
- from our model as
- being kind of the return or you know
- whatever objective
- that we're uh looking at evaluating in
- the model
- and then the chance of of getting
- different
- outcomes we can think of that as the
- risk so
- running the model without monte carlo
- simulation we're kind of just looking at
- one side of the risk return trade-off
- and so it can be very helpful to bring
- this in to fully evaluate the problem
- so i think it's useful to motivate this
- by an example
- um so let's talk about a potential
- that you can place um so
- you have an opportunity to place a bet
- for one dollar
- and if you win that bet you're going to
- get two dollars
- but if you lose the bet then you're
- going to lose 750 000
- and there's no way to avoid this payment
- through legal means
- you're gonna have to be obligated to pay
- that no matter what
- and then looking at the odds of this bet
- uh one in a million you lose that 750
- 000 and every other time
- 999 999 times out of a million
- you're going to win the two dollars
- and if you just go and you take the
- expected value
- of this bet then the expected profit is
- 25 cents so just looking
- at kind of the expected outcome you
- should definitely take this back
- but uh you know any
- reasonable person looking at this bet
- would think twice and at least carefully
- consider should i really take this bet
- because that downside of losing 750
- thousand dollars
- is so severely bad that
- even though it has such a low
- probability
- you might not want to take the bet
- because you only have such a small
- amount to gain in the bet
- so if you make decisions 100 percent on
- expected value
- you would take the bet but
- more than just the expected value
- matters here the probabilities of
- different outcomes
- occurring and what possible outcomes can
- occur
- still matter even beyond expected value
- so this is to some extent the
- kind of concepts we're trying to get at
- with monte carlo simulation
- you want to see what are the different
- possible outcomes from the model
- and consider what that means for your
- particular situation
- and then as far as running monte carlo
- simulation
- this is a visualization of how that
- looks
- and you may notice that this is
- uh very similar to what we had seen
- for sensitivity analysis
- in fact it's the same image here
- describing monte carlo simulation
- and that's because the process to run
- each of them is almost exactly the same
- so the same for external scenario
- analysis
- really all three techniques you follow
- the same pattern
- uh basically just run the model a bunch
- of times passing in different inputs
- each time
- and associating those inputs with the
- outputs and putting it all together in
- some sort of analysis and visualization
- at the end
- um so the the difference
- because the process is so similar the
- difference with monte carlo simulations
- is that we are assigning distributions
- probability distributions
- to each of the inputs and we're randomly
- drawing
- the values of those inputs to put into
- our model
- whereas with external scenario analysis
- we said
- what are all the inputs that make sense
- for this situation
- we're manually picking those values and
- also with sensitivity analysis we said
- we want to look at
- you know investment rates between one
- and five percent
- and so you're manually saying well i
- want to look at one two three four five
- percent
- um but with monte carlo simulation you
- just say well the
- interest rate is going to have a mean of
- three it's
- going to have a standard deviation of 2
- percent on a normal distribution
- and then each time that you run the
- model you don't know what interest rate
- is going to go in the model it's just
- randomly picked from the distribution it
- could be 5
- this time 1 the next time and so on
- and then what this allows you to do
- which is unique
- to monte carlo simulation is
- because we have probability
- distributions of the inputs
- that allows you to assign a probability
- distribution to the outputs
- as well um so that's where
- we're going to be able to get into
- talking about the chance
- of a certain outcome occurring uh you
- know maybe it's a capital budgeting
- setting and you're
- looking at the the probability that
- you're going to have a positive
- mpv uh or it could be a portfolio
- setting where you're saying uh you know
- there's
- a 10 chance that we're gonna lose as
- much
- as three hundred thousand dollars uh
- there are a lot of different situations
- where you wanna think about the
- probability
- and monte carlo simulation is a nice way
- to
- be able to come to those conclusions
- with an otherwise
- deterministic model
- so that's the quick intro of monte carlo
- simulation
- we're gonna do a different order for
- uh exposing everyone to this we're going
- to go
- next to actually running the simulation
- um and then we're gonna come back and
- discuss
- more formally what we're doing i think
- it's easiest to learn all this by
- example
- because when we talk about it formally
- it can sound a little bit complicated
- but really it's not
- and just seeing it by an example really
- drives that home
- so we'll come back next time to look at
- that example of
- running a monte carlo simulation so
- thanks for listening
- and see you next time
Monte Carlo Investment Returns¶
Notes¶
Running Monte Carlo simulations in Excel without the use of an add-in is complex
Running Monte Carlo simulations in Python just a few lines of code
If you want to add Monte Carlo simulation to an Excel model, it is easiest to use xlwings to connect Python to run the simulations on your Excel model
After running the simulations, you must analyze and visualize the output
A histogram is a good choice for showing the output distribution
A table of the percentiles of the distribution and values corresponding to those percentiles is a more quantitative way to show the output distribution
If we have some specific objective or loss in mind, we can determine the probability of achieving the objective/loss
Resources¶
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling
- today we're going to be looking at an
- example of how to apply
- monte carlo simulation and this is going
- to be
- in the context of a portfolio model
- where we're looking to
- allocate resources between two
- assets this is part of our lecture
- series on
- monte carlo simulation so
- we introduced the whole idea of what
- monte carlo simulation
- is and why we would want to go about it
- and
- i mentioned that we're going to look at
- an example of how to do it before
- getting into the more formal definition
- of
- what it is that we're doing i think it's
- easiest to learn this by example
- before we look at that example let's
- just quickly talk about
- how you would actually go and run this
- so monte carlo simulation can be applied
- to
- any model it doesn't matter if it's in
- python or excel
- but it is definitely easier to run monte
- carlo simulations
- in python if you want to do a pure
- excel monte carlo simulation
- unless you're getting some kind of
- add-in uh
- then you're going to be looking at doing
- some kind of
- data table approach which is going to be
- fairly complicated to work out
- for more than two inputs
- um so i generally wouldn't recommend
- that
- you can also go to vba but
- it's generally easier to just handle
- this with python
- uh because in python you just do a loop
- over the number of iterations each time
- you draw the random inputs you run the
- model
- and then you collect the output so it's
- really not more than a few lines of code
- to
- make this happen in python
- so we're going to look first at the
- python example
- of how to do this
- because it's more straightforward since
- ultimately we're going to run the monte
- carlo simulation in python so if the
- model is already in python
- it's very straightforward and then when
- we go over to
- running a monte carlo simulation on an
- excel model
- in a future video then we're going to
- use
- excel wings to go back and forth between
- python and excel and have python
- orchestrate the monte carlo simulation
- on the excel
- model
- so the problem that we're going to look
- at here
- which is good application for monte
- carlo simulation
- is an investment problem we have a
- thousand dollars now
- we need a thousand and fifty a year from
- now
- and we have a choice of investing in two
- different assets a risk-free asset
- and a stock and the risk-free asset
- being that it's risk-free it always
- returns the same percentage
- whereas the stock the return is going to
- be drawn
- from a normal distribution
- with a 10 average and 20 standard
- deviation
- so the question here is how much should
- we allocate
- to the risk free and to the stock
- in order to maximize our chance of
- meeting our objective
- of having 1050 in one year
- i'm gonna intentionally set up the
- values of the inputs this way so that
- you couldn't just put 100 in the risk
- free
- that is not going to be able to get you
- to the goal of
- a thousand and fifty dollars so you do
- have to put some amount into the stock
- but how much should you put into the
- stock
- so the way we're going to approach this
- is first we're going to
- build out the basic model
- to get the portfolio value for given
- returns
- and then we're going to
- [Music]
- get that stock return from a normal
- distribution
- and re-run the model for a number of
- iterations
- and put this all together to analyze and
- visualize
- and then uh because we're trying to
- evaluate
- what's the best weight we're going to
- then repeat
- this whole process or a number of
- different weights
- into the two assets and then pick the
- one
- which has the highest probability of
- achieving the objective of a thousand
- fifty dollars
- so let's go ahead and jump over to the
- jupiter notebook
- example for this there's already a
- completed example
- on the course site that you can download
- and take a look at
- so this is that jupiter notebook and
- here at the beginning
- it again describes uh the problem
- and our general approach for going about
- it
- so let's go ahead and jump into the code
- since we've
- already kind of covered this well
- quickly i'll just talk about the math
- going on here
- so in order to get the return on the
- portfolio of these two assets
- we take a weighted average of the
- returns on each of the assets
- so it's the weight the weight and the
- risk free times the return in the risk
- free
- plus the weight in the stock times the
- return on the stock
- um but we can simplify this a little bit
- to remove
- one input from the model uh because we
- only have two assets
- the weights must sum up to one we must
- be 100 invested
- in total and so then the weight on the
- risk free is one minus
- the weight on the stock so we can
- eliminate that weight on the risk free
- and we have the return on the portfolio
- being the risk-free
- times one minus the stock weight plus
- the
- stock return times the weight on the
- stock
- so translating that over into python
- code we can define some variables here
- for the stock return the risk free and
- the weight on the stock
- and we can create this formula here
- which gets us the portfolio return
- so that's just taking this formula and
- implementing it
- in python risk-free times one minus
- stock weight
- uh plus the stock return times the stock
- weight
- and with these baseline values we get a
- 6.5 percent portfolio return
- so then we can take the initial value on
- the portfolio
- and multiply it by one plus the
- portfolio return this is a one-year
- model so we can multiply by one plus the
- portfolio return
- and get the ending value on the
- portfolio
- so this is basically our entire
- core model here i wanted to keep this as
- as very simple so we can focus on the
- monte carlo simulation
- so we can just put that all into a
- function
- we want to definitely have a function
- that runs our core model
- as we go into the monte carlo simulation
- so this is just taking that logic we
- worked out getting the portfolio return
- and then applying that return to the
- initial value on the portfolio
- to get the end value and we can pass any
- of these
- as inputs so we can say what if the
- uh stock return was instead 20
- then we would be getting even more money
- so yeah here just showing trying with
- some other inputs
- so our core model is done we have one
- function that runs the core model
- and you would want to have this kind of
- structure
- for any model any python model that
- you're going to go and do monte carlo
- simulations on you want to have one
- function
- which can take the model inputs and
- return the outputs from your model
- so we want to get into running the
- simulation now
- and recall that the first step in the
- monte carlo simulation process
- is to randomly pick the values of our
- inputs and so we've got to set it up so
- that we can randomly pick those
- values so we already covered in the
- continuous random variable material
- that we can use the python's random
- library and random.normal variant
- to pull numbers from a normal
- distribution
- so you'll see as i've run this multiple
- times i get different values
- uh according to pulling random numbers
- from
- a distribution which has a 10 percent
- mean and a 20 standard deviation
- so then we can put this together
- with the
- function which runs our model to now
- run the model along with a random
- uh rate of return on the stock so
- you're just that same uh logic to get
- the random stock return then i'm just
- printing out
- what that stock return is uh before
- running it through the model so you can
- see each time that i run it we get
- different stock returns
- and we get different portfolio in values
- as a result
- and this kind of gets into the core
- of why we need to do a model like this
- you can see that
- we're getting quite different portfolio
- values each time
- and a number of them are not meeting our
- goal of 1050 and so this definitely
- is um something we need to carefully
- consider
- if we have that goal of getting a
- thousand and fifty dollars in a year
- so that's how you can run one simulation
- at a time
- now let's run as many simulations as we
- want
- so we basically take that same exact
- thing that we just did
- uh getting the random stock return and
- then
- running the model with that random stock
- return and we just put it into a loop
- a loop over the number of iterations
- we set up an outputs list we append our
- result to that outputs list
- and that allows us to run the model as
- many times
- as we want or as many simulations as we
- want on the model
- so we can see here's three simulations
- we could change it to five
- and have five simulations there
- so we'll just take that same logic and
- just
- wrap it up into a function of course you
- know if you're doing this in your own
- model you wouldn't want to have both
- still sitting around
- you would just convert this into that
- but they're shown separately to
- show how you can kind of build it out
- um and so this is just that same logic
- now in a function
- format so
- and this now runs with a thousand
- iterations by default
- and here we're just showing uh how many
- results there were
- and the first five results so now
- this function we are running a thousand
- simulations
- on the model and we could change that
- uh number of simulations to whatever we
- want
- so um you know this is where i mentioned
- that uh you can run that you can run the
- simulation and you get some results but
- you really have to
- analyze and visualize them to get any
- kind of meaning out of that
- so a good first
- approach for the visualization is to
- create a histogram
- of the outputs
- so i'm just going to make a data frame
- and then
- put those results into the data frame as
- the portfolio in values
- and then do a histogram over those
- values
- [Music]
- and we can now see
- a distribution of the outputs the
- outputs also themselves
- look normal the portfolio in values we
- can see that it's kind of centered
- around
- this is maybe somewhere around 10 and
- 50.
- and we can see some values are
- as low as almost down to 600 more
- commonly down to 800
- and for getting as high as over 1500
- or over 1400 and more commonly we're
- getting high values in the
- 12 to 1400 range and
- you can also the kde is an alternative
- to the histogram
- which basically gets at the same concept
- but it just tries to kind of smooth out
- the curve
- so another way to look at this
- distribution
- is to say well you know five percent
- of the time we're going to uh get at
- least this value and 95
- of the time we're going to get at least
- this value um
- so we can do that with a table
- of the probabilities and the percentiles
- of the distribution
- so to get there first we can form the
- percentiles that we want to look at here
- just doing five percent increments
- throughout the whole range of
- percentiles
- and we can use the uh quantile
- method on the data frame
- column in order to see that so we just
- pass
- these percentiles this list of
- uh the percentiles to the quantile
- method and with that we get uh these
- results
- saying that um in 95
- of cases you're going to have less than
- 1231. so only in five percent of cases
- do you get
- at least 1231. um
- and in five percent of cases you get
- lower than
- 900 so we're already starting to
- get out what we want here we can see
- you know somewhere in the 40s chance
- of being lower than our objective
- so there's a 50-something percent chance
- of meeting our objective
- um but there's a more direct way to get
- at this probability of meeting the
- objective
- another note is you don't have to do
- this on the column you can do it on the
- data frame
- itself um and that achieves the same
- thing and then if you have
- other if you have your input values in
- the data frame as well then you'll see
- percentiles of those as well
- um so now we're getting to the
- probability of achieving a certain
- objective which makes a lot of sense to
- evaluate in this model where our
- objective is to have a thousand and
- fifty and we're trying
- to allocate to meet that objective and
- so this is kind of the main output for
- this model
- now in some other models you might not
- have an objective
- specific objective that makes sense
- in which case you don't need to do this
- analysis
- but it's useful in cases where you have
- some kind of target number that you're
- trying to achieve
- and you want to evaluate the probability
- of achieving that
- um so unfortunately there's not like a
- direct
- function in pandas to do this analysis
- but
- here's a simple one-liner which will
- accomplish that
- for you um so you can't feel free to
- just copy paste this
- into your model and change out you know
- whatever
- output you're looking at in the data
- frame
- but we'll break this down now so you can
- understand
- what's going on here
- so the first part going on here
- is we're checking which of the values
- which of our simulations met the
- objective
- that we're trying to achieve so we can
- compare
- a column of data frame to a number or
- whatever else
- and it will give us true a column of
- true false
- um representing we did meet the
- objective
- or we didn't meet the objective and just
- to see
- what values that's based on um
- you can see for these first four rows
- indeed they're above 10 50. we met the
- objective
- and then we get to this uh fifth row
- which it was below 10.50 and so we did
- not meet the objective
- so we we can't
- immediately do math with um boolean true
- false
- so then we convert this into ones and
- zeros
- so that we can do math with it um
- so now this is the same thing
- uh but we've just converted it into one
- means
- uh that we have met the objective and
- zero means we have not met the objective
- so now that covers this part of the
- expression
- and the last part is then just taking
- the average
- so when we take the average
- of one for yes we met the objective zero
- for no we did not meet the objective
- that gets us to the
- probability based on these simulation
- results
- of meeting the objective so that'll be
- explained
- in greater detail in the next video
- where we formally go over
- everything that we're doing um but just
- know for now that taking the average of
- this
- um one for we met the objective zero we
- didn't meet the objective
- will get you to an estimate of the
- probability of meeting the objective
- so now we have a few different results
- and analysis and visualization of the
- simulation
- but we mentioned in our approach to this
- that now we're going to have to evaluate
- this for the different
- weights that we can put into the stock
- so let's go ahead and wrap this up into
- functions
- so that we can easily reuse that logic
- across a number of different uh weights
- in the stock
- so here i've created functions for all
- these things
- that we just looked at um so
- first um here you'll see i made a
- function that
- takes the results from the simulation
- and creates a data frame
- uh from them i created a function
- which takes the data frame and
- does the histogram
- of the results and
- as you go to start putting
- visualizations into functions
- you should get familiar with this plt
- dot
- show like normally when you're doing a
- visualization out in a cell it doesn't
- matter
- but when you're running it in a function
- you generally want the visualization to
- show up
- as soon as you run it so that all the
- output can stay in order
- and this plt.show is going to ensure
- that
- so you just import matplotlib.piplot as
- plt that's the convention
- remember that matplotlib is the plotting
- library under the hood for pandas
- and then we just put this plt.show after
- any spot that we're doing
- a plot and this is a general practice
- that you should follow for
- any visualizations you have in functions
- and then we have another function here
- to produce that
- table of the probabilities that creates
- the percentile and then
- does quantile on the data frame with
- those percentiles
- and we have a function which gets us the
- probability
- of the objective and
- then another function which
- puts all of this together all the
- outputs
- so it takes the results creates the data
- frame it visualizes those results of the
- histogram
- it creates the probability table and it
- creates the probability of achieving the
- objective
- and then it returns the probability
- table and
- probability of achieving the objective
- finally
- one last function which does the whole
- summarization
- of the analysis it calls this function
- to get the
- probability table and probability
- objective
- and the plot is already going to be
- shown
- the histogram is going to be shown while
- we call this
- and then we can
- add some other output here to kind of
- separate things out so a header here to
- show the probability table and then
- formatting
- the probability table and then some
- space
- and then a sentence about the
- probability of
- meeting the objective and another space
- uh when i call this you can see
- now i'm taking the results from our
- prior simulation
- then we can see it shows the histogram
- it shows the probability table
- and it shows the probability of meeting
- our objective
- all in one function call
- so next we're going to get into okay now
- let's run this with the different stock
- plates and
- try to pick which is the most
- appropriate allocation based upon the
- simulation results
- um but before we get there i just want
- to show
- one other thing we can do to format the
- output
- and that's that um super notebooks
- work uh by using html and css
- and we can use that to our advantage we
- can actually put html
- in the notebook and display it and have
- whatever kind of formatting of the
- output that we want
- so i'm showing you a little
- snippet here we can use the ipython
- library
- and the display module of that library
- and we can import html and display from
- there and then when you do display
- html then it's going to
- show it's going to format as html
- whatever you have in there
- so you can um
- create whatever kind of output you want
- with html
- um here and this is not
- we're not teaching html in this class
- but just know this h2 thing that's a
- level two header
- and so you can use this function you can
- just copy paste this into your own model
- you can change these twos to anything
- from one to six
- for different levels of headings and if
- you just use this function
- then you just now have a function which
- displays a header
- so you can just pass whatever string
- that you want there and it's going to
- show a header
- that's going to be useful because i mean
- you see how much output we had just from
- one
- run here now we're going to have a bunch
- of different runs and we want to make
- sure we know
- what output corresponds to what
- so now we're coming to choosing
- uh the appropriate weight and so let's
- look at
- uh 10 increments and the stock weight
- going from 10 to 90
- um and then we're just going to do a
- loop
- over those weights we're going to use
- that display header
- functionality we just built out to
- separate it out
- um to whatever weight that we're looking
- at
- and then use our simulation function to
- get the results
- with whatever stock weight and then
- display the results
- and because we've wrapped everything
- nicely up in functions
- this code ultimately becomes very simple
- and that's how you can kind of build
- layers on layers
- with python and do quite complicated
- things without ever having to write
- complicated code
- so now we see we have the results for
- all these different weights on the stock
- so we can kind of look through that and
- see
- i mean really the most important output
- here is the probability of getting the
- objective
- but you can also get a better
- understanding of what's going on by
- looking at the other
- values um so when we're 10 in the stock
- you can see there's not a very big range
- of
- the possible values uh but we also only
- have a 20
- chance of meeting our objective whereas
- we come all the way to 90
- in the stock and you can see we have a
- much larger range here
- uh in the distribution but we do have a
- higher
- probability of meeting that objective
- then we go back to eighty percent and
- the probability goes
- up and seventy percent it went down a
- little bit
- sixty percent it was back up um so
- it looks like you know between um
- and by the time we get to uh 40
- it's going substantially down um so we
- know that the proper range in the stock
- is going to be somewhere between 50 and
- 80 percent
- and you can run this with additional
- simulations maybe run it with 10
- 000 or 50 000 simulations instead of a
- thousand
- and that will get you better stability
- in the results
- but this is the main idea here of
- we wanted to evaluate different weights
- the probability of getting the objective
- with each of those weights
- and setting up the functions and
- everything in a clean way
- that we can repeat all this analysis for
- the different weights without having to
- repeat the code
- so that's an overview of how we can
- apply monte carlo simulation in a simple
- model
- um in the other videos in this segment
- we'll look at applying monte carlo
- simulation to an existing model
- and also applying it to an excel model
- so thanks for listening and see you next
- time
Monte Carlo Dividend Discount Model (DDM) Lab Exercise¶
Notes¶
This is an example of applying Monte Carlo simulations to a typical model just to better understand the probability distribution of the results
Be careful that if the growth exceeds the discount rate in the model, it becomes invalid, so some conditions in the model may be needed to address this
Transcript¶
- hey everyone this is nick diabetis
- teaching you financial modeling
- today we're going to be going over the
- lab exercise
- on applying monte carlo simulation to
- the dividend discount model
- this is part of our lecture series on
- monte carlo simulation
- so we have already introduced what monte
- carlo simulation is and then we went
- and applied it in the context of a
- portfolio model choosing between two
- different assets
- and now we're reaching the lab exercise
- at the end of that material
- and it's focused on the dividend
- discount model
- so the situation here is that you're
- trying to value
- a mature company they have stable
- dividend
- growth and so this is a reasonable
- model to look at for evaluation
- and the model is defined as you see here
- in the second point the price is equal
- to the next dividend
- over the cost of capital or
- discount rate of the stock minus
- the growth rate of the dividends on
- stock
- and i gave you the initial
- values to use for the inputs so the next
- dividend is going to be a dollar
- uh the discount rate cost of capital
- is gonna be nine percent and the growth
- rate
- is going to be four percent so the first
- step here is just to build out the core
- model
- which is able to take these inputs and
- produce the price
- from those inputs
- but then as the modeler you're concerned
- that some of these inputs could have
- been mis-estimated
- maybe the growth isn't four percent
- maybe the cost of capital isn't nine
- percent
- so how can we evaluate changing these
- and understand what are the chances of
- achieving different
- prices uh based on the possibility that
- these values could be different
- that's where the monte carlo simulation
- comes in
- so for the level one exercise
- uh we're going to take the growth rate
- and now draw that from a normal
- distribution with a mean of four percent
- standard deviation of one percent and
- run that through the simulations
- and ultimately visualize and summarize
- the
- resulting probability distribution of
- the price
- and then coming to the level 2 exercise
- it's going to be continuing on from the
- first
- but here you're just also concerned that
- the cost of capital
- could be misestimated so for that
- we'll also be drawing from normal
- distributions using a mean of nine
- percent
- standard deviation of two percent and
- the growth is also being randomly drawn
- at the same time
- and then you want to run through the
- simulations and
- visualize and summarize the resulting
- probability distribution of the price
- and now you have to be careful in this
- level 2
- exercise that there is a condition
- in the dividend discount model for it to
- be valid
- when we look at the dividend discount
- model we have this denominator
- the uh cost of capital minus the growth
- rate
- for the model to be valid the cost of
- capital has to be greater than
- the growth rate otherwise this
- denominator becomes negative the price
- becomes negative which is nonsensical
- so that's actually an assumption of the
- dividend discount model
- that the cost of capital should be
- greater than the growth rate
- and as you're doing the level one it's
- it's pretty unlikely
- that that situation would occur
- that the growth rate would be greater
- than the cost of capital uh because
- we're drawing with a mean of four
- percent standard deviation of one
- percent it's pretty unlikely that it's
- going to hit nine percent
- but then once we start also varying the
- cost of capital
- now if we happen to get a low cost of
- capital at the same time we're getting a
- high growth rate
- then that would lead to this condition
- or violation a violation of the
- assumptions of the model
- uh that the cost of capital should be
- greater than the growth rate
- then you'll get negative crazy prices in
- your model
- so what you need to do in addition to
- building the base model and building the
- simulation is
- you need to be able to check the
- simulation inputs
- before passing it through the model and
- you want to check
- that the cost of capital is indeed
- greater than the growth rate
- and if not just reject that simulation
- you want to draw new inputs because it's
- not a valid
- run of the model
- so that's the overview of the live
- exercise
- on adding monte carlo simulation to a
- dividend discount model
- so thanks for listening and see you next
- time
Formal Introduction to Monte Carlo Simulations¶
Notes¶
The process described here to run Monte Carlo simulations may sound very similar to that to run sensitivity analysis, and that’s because it is. The only difference is that you randomly pick the input values from distributions with each run of the model rather than having fixed input ranges
Running the Monte Carlo simulation is not enough. You will have a bunch of outputs, but you must analyze them and visualize them to extract meaning
The main insights we can draw from analyzing a Monte Carlo simulation relate to the probabilities of certain outcomes in the model. We can also get a deeper picture of the relationships between inputs and outputs in a more complex model where that may not be clear
The probability table is the quantitative version of plotting the data on a histogram. I would generally recommend including both as the histogram allows quick understanding of the shape of the entire distribution whereas the probability table helps in quantifying the distribution
The Value at Risk (VaR) represents losing at least some amount with a degree of confidence, e.g. in 95% of periods the portfolio should not lose more than $1,000. The probability table can be interpreted in the same way if the outcome you are analyzing is the gain/loss
The probability of a certain outcome makes sense when you have some kind of goal in mind, then you can evaluate the probability of achieving that goal. If there is no specific goal in mind, there is no need to carry out this analysis
Transcript¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling
- today we're going to be doing a formal
- introduction
- to monte carlo simulation and the
- analysis
- of the outputs this is part of our
- lecture series
- on monte carlo simulation
- so we already ran through a general
- introduction
- of what monte carlo simulation is why we
- might want to go about it
- and then we kind of flip the structure
- on its head to first go through an
- example
- of how to do monte carlo simulation
- and i flipped it because i think it's
- easier to see by example
- um before getting formally introduced to
- it because it can sound a little bit
- complicated when you look at it formally
- but really it's a fairly simple process
- and seeing the example makes that clear
- so if you haven't viewed the video on
- the example go back and look at that
- first
- so we're now looking
- at theoretically what is monte carlo
- simulation and what is the process
- and as you look at this if you've seen
- the prior videos
- on sensitivity analysis you'll
- notice that the process here and the
- setup is almost exactly the same
- we have some model here we're
- representing the model
- mathematically as we get some output
- model is some function which converts
- the inputs to the output
- and we have multiple different inputs
- and in order to run these simulations
- we first assign a probability
- distribution
- to each input and then
- for each input we're going to randomly
- pick values from their distributions
- and we're going to repeat that previous
- step
- for n times n is our number of
- iterations number of simulations
- um and so then we're going to have all
- the random inputs and
- then you want to calculate the model run
- the model
- with those simulated input values
- so for each simulation
- you've got each random input you pass it
- into the model
- and you get the result then we want to
- keep the inputs associated to the
- outputs so you know which inputs
- produced which outputs and the final
- step
- is to visualize and analyze the results
- so basically
- all these last steps are
- all these last three steps are the same
- as sensitivity analysis running the
- model with each of the inputs
- keeping the model uh the inputs
- associated with the outputs
- analyzing the resulting outputs um
- the only thing that's different here is
- this random piece so
- assigning a probability distribution to
- each input and we're going to randomly
- pick the value of the input from the
- distribution
- each time that we want to run the model
- so let's say now we've run the
- simulation
- we've got our 10 000 different results
- from the model
- now what can we do with those results so
- there are a few outputs
- from the analysis and visualization
- that we can gather
- so the first category here is
- probability distributions of the output
- and we looked at two different ways in
- the prior example
- of how we can get at this
- one is with a histogram over all the
- results
- and then the other is with a table of
- the
- probabilities the percentiles of the
- probability distribution
- um and the value
- of the variable at that percentile in
- the
- distribution um
- so in the investment returns example
- that was you know saying that
- 45 of uh
- cases we got less than a thousand and
- twenty dollars and having that
- for the range of different percentiles
- then we also have the probability of a
- certain outcome
- so this i mean it is kind of within this
- idea of looking at the probability
- distribution of the outputs
- but it's just looking at a one
- particular point on the probability
- distribution
- and that particular point is some
- goal or objective that we care about and
- so we want to
- evaluate the probability of achieving
- that objective or outcome
- so in our investment returns model that
- was what's the chance that we're gonna
- get the thousand fifty dollars that we
- need to satisfy our
- obligation and then the last
- uh main output which we haven't looked
- at calculating yet and we're going to do
- in the next video where we add monte
- carlo simulation to the dynamic salary
- retirement model
- is we can look at the relationship
- between the inputs and the outputs
- so monte carlo simulation
- we can use similarly to
- sensitivity analysis where we're trying
- to see what changing an input does to
- the model
- we can get at the same kinds of
- questions with monte carlo simulation as
- well trying to understand
- how the inputs affect the outputs
- and in order to
- [Applause]
- [Music]
- analyze this the two main methods that
- we'll look at are
- visualizing it via a scatter plot
- and using a multivariate regression to
- get at it quantitatively
- so digging into the outcome probability
- distributions
- so uh you can see on the left here
- uh the kind of outputs that we had from
- our
- investment returns example we have a
- histogram
- distribution of the
- whole of all the outputs all the
- different
- portfolio and values and then we have
- this probability table
- as well as a quantitative way of showing
- that information
- these are both getting at just the
- chance of having different outcomes
- in your model so
- uh you can visualize this with a plot in
- a table and with a plot
- usually use a histogram you can also use
- a kde
- kernel density estimation plot as the
- other
- potential way to visualize this uh
- which just gives a smoother looking
- output
- um and this helps you understand
- at a high level what the distribution
- looks like is it basically a normal
- distribution
- as is the case here what's kind of the
- the range of the distribution
- um and just understanding at a high
- level are there heavy tails
- is it you know non-normal et cetera
- and then the probability table helps you
- quantify some of this and think about
- the chance
- of hitting certain values in the model
- so what this probability table says is
- that
- in 25 percent of cases we're gonna have
- less than
- a thousand and twenty dollars and fifty
- percent of cases we're going to have
- less than
- 1039 dollars and in 75 percent of cases
- we're going to have less than
- 1053 dollars
- and then i just wanted to note here that
- the value at risk is a common
- measure that's used in the industry and
- the value at risk
- is typically looking at a portfolio
- but it evaluates at a certain
- confidence level or certain probability
- uh the the minimum amount
- that you're going to lose so
- it's saying like we're in 95 percent of
- days
- we're not going to lose more than a
- thousand dollars
- or yeah at 95 percent of days
- we're going to lose less than a thousand
- dollars
- and those other five percent of days it
- doesn't say anything about that it could
- be
- 10 000 could be a million loss just that
- 95 of the time the loss is going to be
- less than
- uh a thousand dollars so this
- probability table
- um it actually gets at the same
- concept so the probability table is
- actually a more
- general version of the value at risk
- measure which
- probability table tells you all these
- different
- values and as general to whatever kind
- of output you want to look at
- whereas value at risk is specifically
- talking about some kind of loss
- in your model and usually
- most commonly to look at 90 95 or 99
- um percentiles so if you need to
- calculate the var
- from a monte carlo simulation look no
- further
- than the probability table there are
- other ways to calculate var
- in other situations but it's getting at
- the same concept as this probability
- table
- so then coming to the probability of a
- certain
- outcome um
- so this was where we were saying uh
- what's the chance of getting a thousand
- fifty dollars
- in our portfolio
- so in order to understand how this works
- let's look at a very simple example
- so you have some box here and this box
- has red and blue balls on the inside
- and you can't see what's inside the box
- you don't know
- how many red balls and how many blue
- balls there are in the box
- and what you want to get is an estimate
- of what is the chance
- when i reach in to pull out a ball that
- i'm going to get a blue ball
- when i do that
- so what's the process you might go
- through
- to figure this out
- well just reach in grab a ball so i get
- red or blue let me write it down
- put it back in take the box up mix it up
- whatever so it's random
- and pull another one out write down its
- color and just keep doing the same thing
- a thousand times
- and so you get a blue ball 350
- out of 1000 times so then what is your
- probability
- of getting a blue ball and so
- a decent amount of people will probably
- just kind of intuitively
- understand this and say well that's
- that's 35
- chance of getting a blue ball
- how do we get there so
- we can estimate the probability of
- some outcome by basically trying a bunch
- of times and
- seeing how many of those times we
- achieve the objective that we want
- and you just divide the number of times
- you hit the objective
- by the total number of times and that's
- going to be an estimate of the
- probability of achieving that objective
- so in the ball situation three 350 blue
- balls
- um when whenever we draw a blue ball
- that's meaning our objective
- of getting a blue ball whenever we draw
- a red ball that's
- not meeting that objective and so it
- doesn't enter into the numerator
- so uh we get 350
- times where the trial was successful we
- pulled the blue ball
- and so that's the numerator and we had a
- thousand times that we pulled balls
- in total and so that's the denominator
- and so we get that 35 percent
- estimate of the probability of getting a
- blue ball
- and when we apply this in the investment
- investment example
- it was the same exact logic
- it may have looked a little bit
- complicated what we did in pandas
- but all that we did was convert it into
- this kind of format
- where we assigned a one or a zero
- based on whether we met the objective if
- we got at least a thousand and fifty
- dollars it became a one
- if we got less than that then it became
- a zero so that's the same
- as here when we get a blue ball we make
- it
- one and when we get a red ball we make
- it zero
- and then sum it all up and that's going
- to be
- just the count of how many times that
- you got the blue ball
- or just the count of how many times that
- we got a thousand and fifty dollars
- so then that's the numerator and the
- denominator is just the total
- number of trials
- number of simulations
- and that you know taking
- the sum of all that divided by the count
- that is an average right so really
- it's just getting a one for the positive
- outcome a zero for the negative outcome
- and then taking the average
- of that and that will give you an
- estimate of the probability of achieving
- the objective
- that you desire
- so that's a formal introduction
- to monte carlo simulation
- we're going to come back next time to
- discuss how we can analyze
- the relationship between the inputs and
- the outputs
- so thanks for listening and see you next
- time
Analyzing Relationships with Monte Carlo Simulations¶
Notes¶
The results from the Monte Carlo simulation can be run through multivariate regression or another empirical method to better understand the relationship between inputs and outputs
Sensitivity analysis gets at the same goal, but sensitivity analysis is a bit more narrow because at most one other input is changing at the same time. With Monte Carlo simulation, all inputs are changing with each run and so if inputs have complex interactions in the model they will be better understood through MC simulation
The multivariate regression results give the quantitative interpretation of the relationship while scatter plots can help visualize the relationship
Transcript¶
- hey everyone this is nick diabetis
- teaching you financial modeling
- today we're going to be looking at how
- we can analyze the relationship
- between inputs and outputs in our models
- by using
- monte carlo simulation this is part of
- our lecture segment on
- monte carlo simulation so
- we've already covered an intro to monte
- carlo we've run
- monte carlo on a model and then we went
- through the
- formal introduction of what we're doing
- in monte carlo simulation so now we're
- going to
- dig into how we can
- establish relationships and quantify the
- relationships
- between our inputs and outputs using
- monte carlo simulation
- so we
- the monte carlo simulation is not the
- only tool that we can use
- to achieve this objective we've already
- looked at
- sensitivity analysis which can get at
- the same basic idea what is the
- relationship
- between the inputs and the outputs with
- sensitivity analysis we're changing one
- or two inputs at once
- and seeing what happens to the
- output of our model with those changing
- input values
- with monte carlo simulation then
- we're uh running the model a bunch of
- different times with all these
- randomized inputs
- and that allows us to get a more
- full picture of how the inputs relate to
- the outputs
- because when you change just one or two
- inputs at a time
- you may be leaving out that
- when other inputs are at different
- values
- these inputs that you're changing have
- different effects
- so thinking about the retirement model
- when we're changing the interest rate
- and seeing how that changes our years to
- retirement
- well the interest rate is going to have
- a bigger impact
- on the model if the initial salary
- was higher to begin with so you might
- you know thinking about this in advance
- you might do a sensitivity analysis
- of interest rate versus the initial
- salary so you can see how these two
- interplay together
- but you may not have thought about this
- relationship at the get-go
- or there may be other relationships
- which
- matter as well i mean with a higher
- savings rate
- also the interest rate is going to be
- more impactful
- so uh doing it the monte carlo
- simulation route to analyze the
- relationship
- you're kind of bringing all these
- different relationships together
- and not having to explicitly think about
- them as the modeler
- you just kind of throw everything in
- with random inputs
- and the simulation is going to reveal
- those relationships
- so by changing all the inputs each time
- that you run the model
- then you're going to get cases uh
- with each different values of the inputs
- so
- some cases you're going to have a high
- interest rate and within that in some
- cases you're going to have a high salary
- some cases you're going to have a low
- salary
- some uh simulations you're going to have
- a low interest rate and within that
- you're going to have some that have a
- high salary and some that have a low
- salary
- as well as different values of the other
- inputs as well
- so as long as you do enough simulations
- a high enough number of iterations
- then you're going to capture cases of
- all these different values of the inputs
- interplaying together and that gives you
- a much more
- fuller picture of the relationship
- between the inputs and outputs
- now the issue with this with sensitivity
- analysis it was fairly straightforward
- to understand uh how we can just look at
- those results we just see the result
- from the model
- we can visualize it using conditional
- formatting
- or a hexpin plot and it's fairly
- straightforward
- it's a little more complicated to take
- okay well now we've got 10 000 results
- from this model
- how do we get an understanding of the
- relationship between the inputs and
- outputs
- so i'll discuss two different approaches
- that we can use here
- to get an understanding the first is
- by visualizing uh via a scatter plot
- so the scatter plot
- shows the relationship between two
- variables one that gets plotted on the
- x-axis and one that gets plotted on the
- y-axis
- and each point is the values of the x
- and the y's together
- so this could be looking at that
- investment rate on the x and looking at
- the years to retirement
- on the y dimension and you would say
- well one time i ran it
- and i had a 2.2 interest rate and i got
- 22 years to retirement uh
- et cetera and
- um the disadvantage of
- scatter plots is that it only does look
- at one variable at a time so you do have
- to have
- like one scatter plot for each variable
- but then each graph is is very focused
- on that variable
- and so what you're looking for when you
- look at these scatter plots
- is you want to see some kind of pattern
- um if the points are just kind of in a
- cloud
- as we see in the bottom picture here
- there's no kind of linear
- or shape in here it's just kind of an
- ambiguous cloud that
- is supportive of there not being a
- strong relationship
- between the variables whereas
- when the points seem to kind of fit
- along a line
- or like a u shape or some other kind of
- very defined shape
- [Music]
- then that's evidence in support
- of there being a relationship between
- the two variables
- so
- as far as quantifying this then we're
- going to
- look at regressions multivariate
- regressions
- to accomplish that the scatter plot just
- gives you a quick picture
- visualization that you can quickly see
- the relationship and
- how the relationship changes throughout
- the range of the input
- but the multivariate regression is going
- to be able to give you
- quantitatively what is the impact of the
- input on the output
- so that allows you to answer questions
- like if i earned
- ten thousand dollars more for my
- starting salary how much sooner
- would i be able to retire uh
- so of course in order to answer that
- question
- an easy attempt at it is to just go to
- your model inputs
- and increase the salary by 10 000 and
- see what happens
- to the years of retirement that's kind
- of
- that's you know basically the
- sensitivity analysis approach
- um but it is a simplistic way of looking
- at it
- it doesn't take into account how all the
- other inputs
- in the model could change you're still
- just assuming that those other inputs
- are at their baseline values
- so by doing the monte carlo simulation
- we take into account
- all these cases of all the other inputs
- being at different values
- so multivariate regression basically we
- put
- whatever output we're trying to analyze
- as our y variable
- and then each of our inputs as the x
- variables
- and this will be able to tell us
- quantitatively
- what is the relationship what's the
- strength
- magnitude of the relationship and
- direction
- uh between the input and the output
- so the process or how you interpret the
- uh results of that as we run a
- multivariate
- regression we get some fit statistics
- and then the part that really matters
- for this
- is the coefficients and the
- p-values and if the p-value is high
- that's evidence of there not being a
- relationship
- if it's low then there is at least some
- relationship it doesn't necessarily mean
- that the relationship is strong or
- even meaningful in your model but it
- does mean that there is evidence that
- there is a relationship
- and then you look at the coefficient to
- assess the strength or magnitude of that
- relationship so the coefficient
- in multivariate regression is
- how much does the outcome variable
- change when there
- is a one unit increase in
- the input variable or x variable
- so to give an example of that say we're
- working still with this
- retirement model and you get a
- coefficient of negative .0002
- on starting salary when years to
- retirement is your y variable
- so what that means is a one unit
- increase in our x
- is associated with a negative point zero
- zero zero
- two unit increase or decrease in the y
- um so our salary is in dollars and so
- that means that's a one dollar increase
- in salary
- and then our year's retirement is in
- years
- and so that's a one dollar increase in
- salary associated with a
- decrease in year's retirement of 0.0002
- years
- of course that's not
- a nice way to interpret it right like
- who cares about a one dollar increase in
- salary that's not
- gonna be a meaningful thing uh but the
- nice thing about these
- uh relationships is you can just
- multiply them up
- in order to get it in terms of something
- which is meaningful
- so we can multiply both sides here by
- ten thousand
- to now change it into a ten thousand
- dollar increase in salary
- is associated with a decrease in use to
- retirement by
- two years so whenever you interpret the
- coefficients
- you want to put them in terms of
- something which is meaningful
- for your model no one cares about a one
- dollar change in salary how about a ten
- thousand dollar change
- and let's interpret it in that context
- an important thing as you go to
- interpret the results from the
- regression
- um and this can definitely be a point of
- confusion
- is that these coefficients are
- all less all else constant so that means
- that this you know ten thousand dollar
- increase in salary decreases years to
- retirement by two years
- that is not taking into account um
- that you know when you're able to earn
- more money you're probably able to save
- more money as well and so the savings
- rate is going to be higher for an
- individual who makes
- more money that is not being captured
- in the coefficient now
- a big reason that i've been saying we
- should use this approach rather than
- just
- sensitivity analysis is because it takes
- into account that all the other
- variables are changing
- so that's where students can get
- confused by this
- because now here we're saying oh but
- we're basically treating all the other
- inputs as constant
- um but it's
- even though we're kind of isolating the
- effect to this one
- uh input with this coefficient
- the regression model is still
- considering all these different cases of
- the input values so you can kind of
- think of it as an
- average across you know thinking about
- um earning salary uh you can think of it
- as an average across
- like sometimes the investment rate was
- high sometimes the investment rate was
- low
- sometimes the saving rate was high
- sometimes the saving rate was low
- taking the average across all these
- different cases
- what was the overall effect of just the
- salary portion
- so if you know that two inputs in your
- model
- are linked as is the case
- uh potentially here with starting salary
- and savings rate you know that
- if you have a higher starting salary
- you're gonna have a higher savings rate
- then you can basically combine the
- coefficients and say well i know when
- the
- salary goes up at ten thousand the
- savings rate is going to go up by five
- percent and then you can add those two
- effects together
- to get the total effect on the years to
- retirement so you can still use these
- regression results to get at the full
- relationship
- it's just that each coefficient is
- interpreting
- just the effect of that variable
- and another thing to be careful about
- here is the units we already talked
- about
- you know how this is a one unit increase
- and you're probably
- in a lot of cases going to need to
- adjust the units
- in order to get that to a meaningful
- number
- uh and that's definitely the case when
- you think about decimals versus
- percentages so you know we're always
- representing our investment returns in
- decimal format
- in our python models and
- a one unit change in decimal format is
- actually a 100
- change it's going from zero to one which
- is zero to one hundred percent
- um and so the coefficient that you'll
- get
- for the investment rate or any other
- decimal
- um decimal number that is actually a
- percentage
- um then basically
- the coefficient is going to be much
- larger
- uh it's going to be a hundred times as
- large as
- the value would be for a 1 change so you
- have to divide by 100
- to get it to a one percent change
- um and if you you know have things in
- percentages it could go the other way
- around in the model
- and so you just need to be careful about
- the units and thinking through
- the coefficients and what
- makes sense to have everything in the
- proper units
- so that's an overview of theoretically
- how and why we're going to analyze the
- relationship
- of inputs and outputs through monte
- carlo simulations
- we'll come back next time to apply monte
- carlo simulation
- to the dynamic salary retirement model
- and within that we're going to see an
- example of
- how we can do all this analysis of
- relating inputs to outputs
- so thanks for listening and see you next
- time
Applying Monte Carlo Simulation to a Python Model¶
Notes¶
It can make sense to set up a separate dataclass for your simulation-specific inputs, or you may add them to the existing dataclass
Once you start running large numbers of simulations, some unexpected situations may occur in your model such as inputs going negative that were supposed to only be positive, or one input being greater than another when it is supposed to be less. To solve this, we can build functions which produce the random inputs according to the necessary conditions in our model
Create a function which runs a single simulation, then call that function in a loop over the number of iterations to run all the simulations
Because we typically have multiple changing inputs and may even have multiple outputs, it is useful to store data as a list of tuples and then create a DataFrame at the end
It doesn’t hurt to take the quantile of the entire DataFrame to see the distributions of the inputs as well. It can be a nice check to make sure your random inputs are working appropriately
After running a multivariate regression, be sure to add some text interpreting the results
We can check the standardized coefficients (coef * std) to understand which inputs have the greatest impact on the outputs. Be careful that these results are influenced by your choice of the input distributions. If your input distributions are not reasonable, neither will be the results
Transcript¶
- hey everyone
- nick diabetes here teaching you
- financial modeling today
- we're going to be looking at an example
- of how to add
- monte carlo simulation to an existing
- python
- model this is part of our lecture series
- on monte carlo simulation
- so we introduced monte carlo we looked
- on how to build out a model with monte
- carlo
- and we went through a more formal
- introduction and an
- explanation of everything that we're
- doing and now
- it's time to go and apply monte carlo
- simulation
- to our existing dynamic salary
- retirement model
- and you can find the full completed
- exercise there on the course site so
- that you can
- take from that example to build out your
- own monte carlo simulations
- so let's jump over here to the dynamic
- salary
- retirement model and i'm just going to
- go ahead and
- restart kernel run all cells so that we
- can
- get everything defined to get ready to
- do our monte carlo simulations
- so we can add a new section here monte
- carlo simulation
- and you would want to describe
- what you're doing here what's the goal
- of the simulation etc i'm going to skip
- over that
- for brevity in the video and go right to
- the code you can see all of that
- in the completed example so
- we're going to have some additional
- inputs now
- from the simulation
- we're going to need to draw all the
- different inputs from normal
- distributions
- and so we're going to have to have means
- and standard deviations of those
- distributions
- and so we can
- use our existing baseline
- input as the mean
- so we don't have to add all the means
- we do need to go and add these standard
- deviations though
- and we're also going to need to have a
- number of iterations for the simulations
- as an input so we've got a number of
- different inputs to
- manage here because we've got a
- bunch of inputs to manage it makes sense
- to create a data class
- to manage them there's a number of ways
- you could set this up you don't
- necessarily need to use the data class
- you could go and add these inputs to the
- existing model inputs data class
- but i'm just going to create a separate
- simulation inputs data class
- and so in that i'm going to put the
- number of iterations
- uh that would be an integer let's
- default it to 10 000. oh
- we're building this out i'll put it at
- 100 then i'll go back and change it to a
- thousand
- later uh we're gonna have the
- starting salaries so let's look at what
- we
- have in the model data
- starting salary
- so we want a standard deviation for that
- um and let's make that ten thousand
- dollars
- um and as you go to pick
- a standard deviation for your
- distribution
- so the mean you know whatever the kind
- of expected or most likely value is
- should be fine for the mean we already
- have that from our baseline
- values so that's fine the standard
- deviations you want to think about
- uh one standard deviation changes in
- either direction should happen often so
- going
- between 70 and between 50 and 70 000
- salary happens often that makes sense
- two standard deviations in either
- direction
- should be not happening very often but
- not
- rare either so that's
- going from a 40 000 to an 80 000 salary
- that
- seems reasonable three standard
- deviation changes should be rare
- um so going from thirty thousand
- to uh ninety thousand yeah those those
- outer
- thirty to forty and uh eighty to ninety
- seem pretty rare for a starting salary
- and outside like four times standard
- deviation should like almost never
- happen
- um so 20 000 starting salary or 100 000
- starting salary
- for you know if this is some just
- undergraduate getting a job
- both of those almost never going to
- happen
- so that seems like a reasonable standard
- deviation and that's how you can think
- through
- what standard deviation should i pick
- for my distribution
- so then we can go
- to create the rest of our standard
- deviations promo every new year's
- std um
- let's put that at 1.5
- um the cost of living raised
- let's put that at a half of a percent
- um the savings rate
- let's put that at seven percent
- and the interest rate
- let's put that at one percent
- okay and then we can create an instance
- of our
- simulation inputs
- and we have everything there
- so the first step in the monte carlo
- simulation
- is to draw the random values
- of the inputs in order to run them
- through the model
- but looking at the inputs into our model
- before we go and draw random values we
- want to think about
- what are valid ranges of these inputs
- in our model is it possible that we're
- going to hit some
- invalid numbers by pulling these
- random values um
- so salary how often you're getting
- promotions
- cost of living raised promotion raise
- savings rate
- um all these things really they need to
- be positive they don't make sense
- if they're negative
- and the interest rate i would say you
- know if this was
- each individual year we were getting a
- random interest rate sure that can go
- negative
- but if we're talking about a long-term
- interest rate
- that also should be positive so really
- all these inputs that we're randomizing
- should be positive
- in the model so
- knowing that knowing the conditions that
- we need to have on our inputs
- we can write functions to draw the
- random inputs
- that are always going to satisfy these
- conditions
- so
- well first i'm gonna i'm gonna go back
- up to the top and import random
- uh because we're definitely going to
- need that
- to draw the values from normal
- distributions
- um and so
- you recall from the um
- continuous random variable material
- random.normal variant is able to draw
- values from a normal distribution
- um and so let's just
- take an example mean here
- of two and a standard deviation
- of one then i set these up because i
- know
- that this is going to go negative in
- some cases it's only two standard
- deviations away from zero and so that
- should happen decently often
- um so putting the mean and standard
- deviation
- then we get random values from that
- normal distribution
- um and most of them are going to be
- positive but some of them are going to
- come up negative i saw one that was
- negative there
- um so
- what we can do um we
- want to figure out a way so that every
- value that we draw is going to be
- positive
- and let me actually just increase this
- so that it's a lot more likely
- to get negative numbers in here
- so that it's really clear that this is
- working appropriately
- so what we can do is basically
- pick the value and then if we didn't
- get a value that meets our conditions in
- this case that is a positive number
- then we're just going to keep drawing
- values until we do
- so what we can do is use a while loop
- for this because the while loop executes
- until
- some condition evaluates to false as
- long as it's
- true it's going to keep executing and so
- this is the perfect fit here
- because we want to keep drawing random
- values until
- we meet our condition of
- it being positive so
- that condition so let's um
- call this drawn value um
- our condition would be while the drawn
- value
- is less than zero so as long as we're
- getting a negative number
- keep going so it's basically the
- opposite
- of the condition that you want we want
- the drawn value to be greater than zero
- so as long as greater than or equal to
- zero
- so as long as it's not the case that it
- satisfies that condition
- as long as it's a negative number then
- we're going to keep
- drawing additional values um but then
- you go and run this and you'll get the
- name error that
- drawn value is not defined because we
- don't define it until here
- so we also need to initialize it so just
- initialize it to some value which is
- going to satisfy
- the reverse condition so basically put
- it
- at a value which is not acceptable for
- your model
- and that will make sure that it goes
- into the while loop
- and so then we just show the drawn value
- at the end
- and then you'll notice that no matter
- how many times i run this
- it's going to come up positive every
- time
- um even though we saw it was decently
- often
- that we were getting negative numbers
- before
- so now we have a function we can call
- this
- uh random normal
- positive which takes a mean and a
- standard deviation
- um and returns that drawn value at the
- end
- so now we can just do random normal
- positive
- with whatever mean and standard
- deviation
- and it's going to uh give us
- values from the normal distribution
- basically but just
- chop off any of those ones which are
- negative and
- try again
- so we can apply this function across all
- the different inputs that we're
- randomizing
- in the model
- so um and of course you would add a doc
- string to explain
- what this does i'm just skipping that
- for
- keep the video short but definitely take
- a look at the completed example
- for having all the doc strings and
- everything filled out
- so what we want to do next is we want to
- pick the random values of all these
- inputs
- so
- all these different inputs here we want
- to
- randomly draw them um
- so i'm just going to copy these
- to just make my life easier to type this
- out
- um so then i can get
- all the names of the different inputs
- there
- delete off these commas
- and then um
- we can then um
- that's not gonna work delete off these
- values as well
- we're going to use the random normal
- positive function that we just created
- in order to um
- let me put a space there random normal
- positive
- um and
- we want to
- do the mean there
- as the original input value and we want
- to get from the sim data
- the std of
- that value so then
- we have drawn all these different inputs
- now let me add a data equals model data
- um and then i named one of these
- oh i did i forgot to put promotional
- arrays standard deviation
- so um that in here as well
- promo raise std
- and what's the reasonable value for that
- let's say 5 on that
- so now hopefully this will work yep um
- and so now we have all these different
- random values interest rate
- promotion promotional raise etc
- we're getting random values for each and
- they're always positive
- um so then we can
- make a function out of this um so
- we can call this
- years retirement
- simulation inputs
- i'm going to take the data and the sim
- data
- and
- then we can return all of these values
- so we want to return all these different
- values and so it's doing it as
- a tuple
- where we're returning all these at once
- then we can call this years to
- retirement simulation inputs
- with the data and the sim data and we're
- going to get
- all these different random values and
- you can see they're changing each time
- first one corresponds to salary second
- promotions every nears
- and so on
- so now we're able to draw the random
- values
- of all our inputs and so the next step
- is then to get to
- running a single simulation
- so we
- are going to call this function
- um and we want to save
- the results of it so we can do
- we can take the same thing to split it
- back out into the
- individual variable values
- so you see i run that and now all these
- things
- are defined individually
- and then we want to create the data
- so create an instance of the model
- inputs
- with these values
- so i'm going to grab these again
- and let them equal put a comma
- and now we should have that new data
- created appropriately
- so i run this and i get the model data
- being created
- with random values now
- now that we have the data into the model
- inputs data class
- now we can run the model so we can do
- years for retirement equals
- we have the years to retirement function
- um
- we want to pass the new data and we want
- to make sure
- we don't need to print out the do i have
- the print output in this version of the
- model
- i might not
- okay it seems it's not there let me
- quickly add it um otherwise we're going
- to have
- a huge amount of output coming out of
- this
- so if we're an output and just wrap
- all the print statements in that
- three print statements here
- um and then coming back over to here
- now for an output equals false
- so with that we get the year's
- retirement and we're going to get
- it should be different using retirement
- but we're getting the same
- year's retirement so if the print output
- wasn't there i think i might have used
- the version yep which had this model
- data mistake
- so make sure it flows all the way
- through redefine that
- and now hopefully we'll get different
- years of retirement with each run
- of the model
- and we're still not getting that that's
- odd
- let me just restart this and run all the
- way through while i
- um go back and take another look
- um oh we have
- oh i was editing this wellsdf function
- okay so this is the one that also had
- that model data mistake
- your data data okay we're good now
- hopefully it should come through now yes
- okay now we're getting different years
- of retirement with each one of the model
- so you definitely want to do these
- checks on your own with your own model
- as you build it out
- if one simulation does not work properly
- then certainly
- 10 000 are not going to work properly
- either
- um and now that we have the logic to
- produce
- one simulation then we can wrap that up
- into a function
- so i'm going to call this producer
- retirement
- single simulation it takes the data
- and the sim data
- and then all this and return the years
- or we want to return more than just the
- years for retirement though
- um we want to actually
- return all the inputs as well so we can
- return all the inputs
- and the years to retirement um
- so that we have all the inputs
- associated with the output
- so then when i call this we then get
- all those inputs again but also the
- output the year's retirement as well and
- that's all
- associated together so now we can
- run a single monte carlo simulation with
- a single line of code
- so now that we can do that we want to
- get to running the full
- monte carlo simulation process with
- however many iterations that we want
- so um we want to basically call this a
- loop
- over the number of iterations and
- all we're doing is just calling this a
- bunch of times and putting it into a
- list so i'm going to use a list
- comprehension
- to simplify that loop so just calling
- the function
- or i in range
- sim data dot number of iterations
- hold out all results and then
- we can look at let's just look at the
- first
- five because there's going to be a lot
- in there and we can see we're getting
- multiple runs of this with the inputs
- associated with the outputs
- so then we can put this into a data
- frame
- and if i imported andis uh
- yep uh so put this all into a data frame
- pd.dataframe
- of all results
- and the columns then we want to
- name these columns so we're going to
- have starting salary
- first and you want to go in the same
- order as whatever you have in the tuple
- the starting salary and then promos
- every n years
- then the cost of living raise
- and then the promotion rate
- and then the savings rate
- the interest rate and finally the years
- to retirement
- and you don't want to have really long
- cells like this it's just really
- difficult to read so i'm going to split
- this
- onto multiple lines it's within
- parentheses and so i can split it
- and this is going to make the code
- easier to read
- so then we should have
- our data frame created
- and we see that here so i have it set at
- 100
- simulations right now that's why we have
- 100 rows in the data frame each row is
- one simulation
- and we see all the input values
- associated with
- the output so
- now we're able to run all the
- simulations so let's make a function for
- that year's retirement
- monicarlo takes the data and sim data
- and let's end in all this and return it
- so now i can call this
- and we should get the same thing
- and of course i could you know change it
- and
- run for
- say a thousand iterations and then we
- would see a thousand rows in the data
- frame so everything
- seems to be flowing through properly
- so um
- now we've got the simulation results and
- we can get them with a single function
- uh let's go ahead and save those results
- into a data frame
- so now we've got this data frame
- but it doesn't have great formatting we
- might want to apply some formatting to
- it
- um so style format um
- so starting salary and i'm just gonna
- i want to probably format all of them so
- i'm just gonna copy these to get started
- with
- um and then starting salary
- um that's going to be uh dollars
- and i wanted to have commas and zero
- decimal places
- promotions every n years um that can
- just have
- one decimal places one decimal place
- um cost of living raise
- that's going to be a percentage
- we can give it up to two decimal places
- promotion raise same thing really all
- the
- percentages same thing promotion raise
- uh savings rate and interest rate
- and then use retirement
- we can make it zero decimal places
- um
- okay so now we see that with proper
- formatting
- um and the other thing we might want to
- do is add some coloring to it
- so i'm going to add the background
- gradient with the
- red yellow green color map
- on just the years to retirement
- column
- i see that coming there and
- you be careful that you don't style your
- data frame which has
- 10 000 rows in it because it is going to
- show all of it
- um
- and we'll notice that um this is going
- the opposite of the direction that we
- want right it's showing green for high
- values but really green is
- our low is good in our model so we want
- to reverse the color map and so we can
- add
- underscore r and now uh
- when the year's retirement are low we're
- seeing the dark green
- and when they're high we're seeing the
- red
- so then we can wrap this in a function
- style df takes the data frame
- and then returns this
- so that we can just hear the shortcut
- which can see the top five rows so just
- look
- at the data we can then apply style df
- to that
- to just keep a look into our data in
- the model
- and it's useful to say
- company simulations were run so we can
- do the length
- of data frame simulations we're running
- so now we have the results from the
- simulation and we want to
- visualize and analyze them
- so let's visualize the results
- um well so this
- file data frame that's the first part
- um just example results
- um this this can go at the end of the
- results there we go
- um the next what we want to visualize
- is the distribution of the output
- here's retirement
- um so we can take
- this data frame user retirement
- and do a histogram
- um and see the output there
- um let me go ahead and just
- run this with more iterations at this
- point
- so that we'll have a good idea what the
- output
- is going to look like
- um 50 is not going to be enough pins for
- that let's try
- 100 that's a little bit more
- reasonable um
- so then
- um we want to create the
- um probability table
- so uh probability table
- um and we can get the quantiles
- uh we're gonna do this i develop
- five percent um percentiles
- i have a twenty for i in range uh from
- one to twenty
- so in range so we get that five percent
- to 95 percent
- and then we can do vf.quantile
- on that
- so here's another advantage of the
- data frame styler function pattern now
- we have
- a different data frame which is in the
- same structure
- we can apply the same function to that
- so now this
- probability table is nicely formatted as
- well
- and this is telling us you know only
- five percent of the time will you be
- able to retire in less than 21 years
- and five percent of the time it could
- take longer than 39 years to
- retire based on the distributions that
- we have assigned
- so next
- now we're going to get into analyzing
- the relationship of the inputs versus
- the outputs so the first that we can do
- is uh plots of inputs
- verse use retirement
- um so if we do df df.plot.scatter
- and we tell it the y is here's
- retirement
- and then the x is whatever
- input that we want to look at then we're
- going to get a
- scatter plot as a result
- so we want to do this but we want to do
- it for all the different possible inputs
- so i'm going to go back i'm going to
- grab this list
- so we can call this the
- input columns
- and then for each
- column in the input columns then we want
- to do this
- scatter with that particular column
- then we now have all the scatter plots
- for each of the different inputs so we
- can see the relationships
- um and we don't need years to retirement
- we only want the inputs not the output
- here
- so i'm going to remove that one
- um and you know you can see
- some of these have clearer patterns than
- others like here with
- savings rate you can see it's kind of a
- curve here
- that's a fairly defined pattern um
- and with cost of living raise it's a
- little more of an ambiguous cloud
- here so just based on the scatter plots
- it suggests a fairly strong relationship
- between savings rate and years for
- retirement
- and not a very strong relationship
- between the cost of living raise and
- years to retirement
- so then we can go on to the quantitative
- analysis of the relationship between the
- inputs and the outputs
- and that is through the multivariate
- regression
- so we're going to use the stats models
- package in order to run the regression
- so i'm going to import stats models uh
- dot api as sm
- and this is another one of those
- conventions
- just take this import and use it as is
- and then we'll use
- sm to interact with stats models library
- so what we want to do is
- i'm going to say that our output column
- is used for retirement
- and we already had our input columns
- defined here
- so ultimately what we're doing
- is we're going to get our x variables
- as the uh input columns
- from the data frame so then we have just
- the inputs no years to retirement on
- here
- we're going to get uh the
- um here's to retirement here
- as our y variable um
- and then we're going to create the
- regression
- model object so we're going to do an
- ordinary least squares regression ols
- regression uh which is the standard
- and we're going to put the y first and
- then the x
- and then in order to get results from
- that we're going to
- fit the model call.fit on the model
- and then we call the summary method on
- the result object
- in order to produce this summary that
- you see here
- so now the top part is the general fit
- statistics
- not too important for this what we're
- really concerned about is the p-values
- and the coefficients
- so all the p-values are low and so
- there's
- no evidence from the p-values that any
- of these
- uh inputs are unrelated to the outputs
- it seems that there is an evidence of a
- relationship
- with each one of them and we can look at
- the coefficients
- in order to interpret the um strength of
- that relationship
- but there is one other thing that we
- need to do here
- um which is that
- the you'll notice here that there's no
- constant or intercept if you're familiar
- with
- running regressions you typically have a
- constant or intercept
- as one of the x variables and that is
- not included by default
- in stats models you do have to add it
- explicitly
- so in order to do that we do sm.add
- constant
- and then in here we do has const equals
- true
- and then when we run this again now
- we'll see we have this constant
- in there um and when we look at the x
- that basically added a column of ones
- into the model
- and that's how it works with the
- constant
- um we're not going to be diving into the
- theory of ols regressions why you should
- have this constant
- but just in general you should probably
- have the constant and so make sure to
- add it
- so you know you can just copy paste this
- code snippet
- or your own model and just switch out
- the output columns
- and the the output column and the input
- columns
- so now we have the regression results
- and we want to interpret them
- so we can go ahead and already look at
- these and start doing some
- interpretations you'll notice
- um that for promotions every n years we
- have a 1.2648
- coefficient so what that's saying is if
- we get uh if it takes one year longer
- to get a promotion on average
- that's going to lead to a 1.26
- additional years it takes until we get
- to
- retirement
- and so another question is you know
- which of these
- inputs is most impactful which matters
- the most and so you might think well
- just whichever have the biggest
- coefficients those should be
- the most impactful but that's not the
- case you have to also consider
- the standard deviation of the
- inputs so we can evaluate that by
- looking at the standard deviation on the
- data frame that will tell us the
- standard deviation of each of our inputs
- and those should basically be the
- standard deviations that we set out
- in our simulation data um
- which they are um
- so we take that standard deviation
- and then on this result
- object we have result.params that gives
- us
- a panda series which has all those
- coefficients that we saw up here in the
- nice summary output
- so what we can do is we can actually
- multiply these two things together
- and that gives us what's called
- standardized coefficients
- so what that is saying is now it's
- instead of a one unit
- increase in the input variable it's a
- one standard deviation
- increase in the input variable
- so that's saying that a one standard
- deviation
- increase in the cost of living raise
- decreases years to retirement by 0.9
- years so these coefficients
- are comparable in terms of which has the
- biggest impact
- so you can basically think in the
- absolute value of these whichever are
- the biggest
- are going to have the biggest impact on
- the model so here
- is saying that savings rate has the
- biggest
- impact on the years to retirement
- followed by the starting salary
- and in your model you're going to want
- to
- include some text at the bottom that
- interprets this
- and draws conclusions from the
- coefficients talking about the original
- coefficients as well as the standardized
- coefficients
- so that it's very clear for the reader
- of the model basically what was
- important
- from doing all of this analysis
- so that's the general process of adding
- monte carlo simulation to an existing
- model
- and analyzing the relationship between
- the inputs and the outputs
- now to go along with this
- there is an analogous lab exercise here
- so the lab exercise for this is then to
- do something very similar
- for the project one model project one
- python model
- now i'm not asking you to do it with
- every input there here in the level one
- just do it with the interest rate
- just randomize that and then
- run ten thousand simulations get the
- years or
- the mpv results visualize
- and then create this table of
- probabilities and
- get the chance that the mpv will be more
- than 400 million
- and then in the level two um
- then you're going to be doing the same
- thing continuing on but then also
- drawing the number of phones from an
- oral distribution as well
- and doing the same kind of analysis but
- then following it up
- with analyzing the relationship between
- the inputs and the outputs so doing the
- scatter plots
- and the multivariate regression and then
- interpreting
- the results of that
- so that wraps up um
- adding monte carlo simulation to python
- models
- thanks for listening and see you next
- time
Applying Monte Carlo Simulation to an Excel Model¶
Notes¶
The process for running Monte Carlo simulations in Excel is nearly the same as that in Python when we use Python to run the simulations on the Excel model using xlwings
The main difference is that we write the inputs into Excel and extract the results using xlwings rather than running Python logic for the core model
Excel recalculates whenever an input is changed. So writing the inputs in is enough to get the result calculated
For the analysis, you can either keep the results in Python and follow the process for analyzing the results in Python, or you can output them back to Excel and analyze the outputs there
Keep in mind that if you visualize the outputs in Excel, next time you run the simulation it will go slow due to the visualizations. Because of this it may be a better idea in general to do the analysis in Python if you have a choice
Transcript¶
- hey everyone
- nick dear bird is here teaching you
- financial modeling so today
- we're going to be talking about how we
- can add monte carlo simulation
- to an existing excel model this is part
- of our lecture series on monte carlo
- simulation
- so this video is going to wrap up the
- lecture series we already talked about
- what monte carlo simulation is why we
- would want to do it
- looking example of running it on a new
- python model
- i did a more formal introduction of
- monte carlo and all the parts of it
- and the analysis of it and then went and
- applied it to an existing python model
- so all that's left is to apply it to an
- existing
- excel model so
- if you're thinking about just using pure
- excel
- for monte carlo simulations it is
- definitely a
- challenge there are add-ins
- which are able to do this for you but i
- don't know
- of any um good really flexible
- free add-ins for this mostly good ones
- you're gonna have to pay a substantial
- premium to get that add-on
- [Music]
- and without the add-on then with only
- excel
- pretty much you're going to be going to
- vba to complete this
- there are ways to hack it with data
- tables
- but it can get quite complicated to do
- that
- especially if you only have one or two
- inputs varying at a given time
- that's not too bad with a data table um
- but as soon as you want to change more
- than two then it starts to get
- uh to be quite a hacky kind of approach
- to make that happen with some kind of
- lookup in another table
- in order to make that happen
- and you might be able to hack it some
- way
- or you're going to using vba
- or python so
- you know generally i would recommend
- just to use python to be able to
- run your monte carlo simulation in excel
- so you know we've already learned how to
- combine
- excel in python in the prior lecture
- series
- and so we can leverage that knowledge to
- take our excel model
- and use python to run monte carlo
- simulations
- on it
- and the process that we're going to
- follow there is
- extremely similar to the one that we
- just carried out
- in python all we're doing is
- changing the inputs running the model
- and storing the output
- each time the difference here is just
- that
- instead of running python code to um
- change the inputs and uh run the model
- we're going to use excel wings to take
- the
- inputs from python put them into excel
- and then get the result that we want
- from excel
- back into python
- um so same exact kind of flow
- but just having the excel model hooked
- up instead of the python core model
- and then so at the end of that process
- you'll have all your simulation results
- in
- python and it's up to you at that point
- whether you want to just go ahead and
- analyze them in python
- and then you'll do the exact same kind
- of analysis
- that we showed in adding
- a monte carlo simulation to an existing
- python model
- or you can take all those simulation
- results and output them back
- into excel and then do an analysis on
- them
- in excel
- so let's look at an example of how we
- would actually go about this
- so i've got the dynamic salary
- retirement model up here on the left and
- a fresh
- jupiter notebook up here on the right
- so you know this model is already set up
- so that everything flows through we give
- it different
- inputs and it's going to change the
- output
- so first thing we want to do is import
- excel wings as xw
- and we're going to need pandas as well
- so just add those inputs there
- and you can look on the course site
- to see a fully built out example of this
- uh which has all the proper um
- explanations and formatting of
- everything
- but i'm going to go ahead and just get
- right to the code
- here so we're going to now use excel
- wings to get a connection
- to the workbook
- so this is dynamic valerie
- retirement model i just realized that
- these are not in the same folder
- so let me
- let me move that into the same folder
- just do that over here
- on my other screen here
- give me a moment for that okay
- now they're in the same folder
- so that's the potential pitfall as you
- try to do this you want to make sure
- they're in the same folder or otherwise
- you're going to have to put the full
- file path
- of the
- excel model
- so this is uh copy
- two um
- copy e2 um
- i'll not found so i must not have gotten
- that name right
- dynamic salary retirement model
- copy two oh right i need to put the xlsx
- okay now i have the connection to the
- book and so now i can get
- the inputs and outputs sheet
- and ultimately we're going to use two
- different sheets so i'm going to call
- this i o sheet
- [Music]
- and this is going to be book.sheets
- inputs and outputs to reference our
- inputs and outputs worksheet here
- um so
- [Music]
- now we're going to run a single
- simulation
- um so
- um all that we need to do to run a
- simulation in excel
- is change the input that's going to
- automatically trigger excel to
- recalculate the model
- and so then the output will change as
- well
- so let's look at just varying the
- interest rate
- so here in b10
- we have the interest rate so io sheet
- dot range uh b10
- value and let's just try it out by
- putting a value in there eight percent
- let's run that and we see this has
- updated to eight percent and the years
- for retirement
- as similarly updated so then the other
- side of this is then just
- getting that out the io sheet
- uh we want to get the output here b18
- is the output range so b18
- value and we can see that gets us the
- years to retirement
- so we can save that as years retirement
- [Music]
- and that's basically it um you know
- we've got to add the random part to do
- the simulation but
- just you know running these two cells is
- how we can run the excel model from
- python um
- and then we're going to show in this
- example
- analyzing the outputs in excel so we'll
- ultimately need to get the outputs back
- to excel
- so i'm going to go and create a new
- worksheet here
- call this simulations
- and then i'm going to create
- a reference to that sim sheet
- book.sheets
- simulations um and now
- i can do the simsheet.range
- uh a1 value equals your search
- retirement
- and now we see that came into there so
- now we have recorded the result
- of that simulation back into excel
- so that's just a single
- run of the model not even really a
- simulation because
- this wasn't random but let's now
- go to uh running multiple simulations
- so we're gonna need the random module as
- well
- and let's put a mean of the interest
- five percent let's put a standard
- deviation of the interest three percent
- um and now we can do
- random.normalvariate
- to get a random interest rate drawn
- from a normal distribution so then the
- interest rate
- um we can see we run it multiple times
- we get different values of the interest
- rate
- so then
- we want to basically
- do this but in a loop over the number of
- iterations
- so i'm going to add a number of
- iterations
- as another variable there
- and then
- we're going to go through the range of
- the number of iterations
- and um we're going to get the interest
- rate
- and then we're going to
- put that interest rate into the model
- and then we're going to extract the
- years to retirement from the model
- and that would be running the
- simulations
- so then the other thing is just to save
- the results
- so all retirement years
- uh all retirement years dot append
- here's retirement
- so now i run this and we can see we get
- 10 different
- aggregate retirement and if you look
- over at the excel model while this
- happens you can see
- that the interest rate is changing
- around and it actually changes around
- more than you can even see because it's
- going really fast
- but you do see it changing around as we
- run this
- um so we want to bring these values back
- into excel
- um and if you recall we probably want
- these in a column that generally makes
- more sense in excel
- you recall we had this trick where we
- wrap each
- item into its own list in order to get
- it to output vertically
- so we can do vertical retirement years
- do list comprehension uh
- just putting a list around
- um each of the retirement years
- so that we have something that
- looks like that and now that um
- we're able to write back into excel
- um in a column format so i'm going to
- go to the same spot as before but i'm
- going to put the
- vertical retirement years and now you
- can see that
- each time i run this it's going to bring
- this into there
- so i run these two together we're going
- to get new simulation results
- coming in each time and back into excel
- so then it makes sense to wrap all this
- up in a function
- um so
- [Music]
- uh retirement simulations
- it takes the number of iterations the
- interest
- mean and the interest standard deviation
- does all this and then does this as well
- and we can have it also return the all
- retirement years just in case
- uh we later wanted to do analysis in
- python
- as well um and then
- we can do uh retirement simulations for
- the results let's
- go up to a thousand iterations this time
- with a
- 10 mean and a 5 standard deviation
- and then look at top 10 results
- and we'll see that run for a while we
- can
- [Music]
- um
- excel kind of froze up while it was
- running but now we can see that we have
- a thousand different results here from
- 1000 different simulations
- and we also have those same
- results in python as well
- so now we have our results in excel
- and we have them in python so you could
- go and do your analysis
- in either at this point but
- we've already seen how to do the
- analysis in python so i'm going to show
- doing the rest in excel
- so we have all these results here
- the first thing that we might want to do
- is
- a histogram to see the distribution of
- the results
- i just highlighted all of that i'm going
- to go
- and insert chart um
- and then i'm going to go to histogram
- and add the histogram and we can see the
- basic distribution here
- and
- see uh we can change the number of bins
- here
- uh generally better to have more bins
- for these simulations because you've got
- so many different
- cases
- so 100 is maybe two mini bins because
- now it looks really sparse
- let me go with let's try 25 on that
- which looks a little bit more reasonable
- this would be um
- probability distribution
- of used to retirement
- okay um the next thing that we'll want
- to look at
- is the percentile table
- so in order to do that first you want to
- set up your
- uh percentiles so i'm just going to
- start with five ten percent and then
- i'll be able to drag for the rest of the
- range
- um this is going to be yours to
- retirement
- and then excel has the percentile
- function which is like the quantile
- in pandas and then we're going to grab
- all that data and then the
- percentile is going to be the one which
- is there to the left
- and make sure that you fix the range on
- the
- data because you don't want that to move
- as you drag down but we do want the
- percentile to move
- so then we can complete that
- and we can see that it looks right um
- that you know five percent of the time
- we can retire in less than 20 years
- and 10 of the time it takes at least 40
- years
- and then the
- last thing that we can do here is get
- the probability of a certain
- outcome so
- for that then um we can recreate what we
- have done in panas
- by um
- let's just say our objective is retiring
- in 25 years
- so objective
- 25 by productive
- used retirement to be more clear
- um and then
- we just do equals if uh remember we want
- to check
- did the simulation meet the condition
- so is the year's retirement
- less than the objective
- your retirement that means we met the
- objective and make sure we fix
- that objective um
- and if we met the objective we get a 1
- otherwise we get a zero
- and then we can just complete that for
- all the results we can see whenever it's
- less than 25
- um well really it should be less than or
- equal to
- because 25 is also fine um
- so yeah anything which is less than or
- equal to 25 is now showing up as a one
- and anything greater is zero so then
- the probability
- of uh year's retirement
- less than or equal to the objective
- is going to equal the average sorry it's
- average in excel
- average of this column that we just
- created
- so we get a 33 chance
- that we're going to be able to retire in
- 25 years
- or less so that's
- the basic monte carlo
- analysis in excel
- so that wraps up our example on how to
- add monte carlo sim
- simulation to an existing excel model so
- thanks for listening and see you next
- time
Relationship of Inputs and Outputs in Excel Monte Carlo Simulation¶
Notes¶
This continues off the prior lecture to keep the inputs associated with the outputs in the Excel output, and then to do the analysis of how the inputs relate to the outputs
It is easier to go to DataFrame output into Excel to keep everything together
We create scatter plots and run a multivariate regression, just as in Python
You may need to enable the Data Analysis Toolpack add-in in Excel to get access to multivariate regression
Transcript¶
- hey everyone this is nick durabartis
- teaching you financial modeling
- today we're going to be talking about
- how to analyze the relationship
- between inputs and outputs in our model
- using monte carlo simulation in the
- context
- of an existing excel model
- this is part of our lecture segment on
- monte carlo simulation
- so we left off last time
- we were working on this example of how
- to run monte carlo simulation on an
- existing excel model
- and we went ahead and got it to where we
- were able to run the simulations
- and output the results of those
- simulations into excel
- and be able to get the probability of a
- particular objective
- a histogram of the distribution
- and a table of the percentiles of the
- distribution so definitely watch that
- prior video
- before coming to this one what we're
- doing in this video
- is we're going to modify the simulation
- a little bit so that it keeps
- the interest rate associated
- with the uh years to retirement
- and that will allow us to then
- analyze the relationship between the
- interest rate
- and the years for retirement
- so i'm going to come over to here um
- and we already have this function
- which is getting us the random interest
- rate putting into the model
- getting the result from the excel model
- which has recalculated that point and
- saving it
- so what we need to do is
- now we're gonna save not just the years
- to retirement but also
- the um interest rate as well
- so i'm going to rename this list to all
- data so it's more indicative of what
- we're doing
- and then here i'm going to append not
- just the years to retirement but also
- the
- interest rate um
- and let me just quickly grab that logic
- [Music]
- that we now
- have those results in a list here
- and then what we can do is we can create
- a data frame
- from those results so data frame
- [Music]
- and then the columns are going to be
- interest and use retirement
- and then we have that data frame
- um and then instead of
- i'm gonna go ahead and move this over
- one
- um instead of writing using the
- column and uh you know list within list
- approach we can actually just
- write the data frame back into
- excel um
- so then we're going to do uh this same
- assignment but we're going to
- assign the data frame instead so
- but i know if i do this right now it's
- going to bring the index over and so
- it'll be three columns and it's going to
- overwrite what we have here
- so i'm going to also put options
- data frame uh index equals false
- um and then we should get it coming in
- it will have the headers um but that's
- that's fine
- so let's give that a try um
- and now we do see coming over here what
- we expected to see
- of course we only have it at 10
- iterations right now instead of the full
- um instead of the full thousand
- now we can see that works so let's bring
- that back into our function
- rather than what we had before
- of doing the list within less approach
- so bring all that into here and then we
- can return the data frame instead
- and now we should be able to run this
- thousand simulations
- ten percent mean five percent interest
- or
- standard deviation and
- we want to see uh
- the first few results out of that so
- let's give that a try
- it's going to take a little bit to run
- through the model
- [Music]
- and oh i didn't redefine this that would
- cause it to not actually change
- i've accidentally got this still in here
- definitely don't want that
- that was the old code so now let's
- redefine this okay now let's try this
- again
- um so again it's going to take a little
- bit to run
- but now we do see all the interest rates
- associated with the year's retirement
- coming in
- and now we can modify this
- because now the years to retirement have
- moved
- and so we can get back to our
- probability of achieving the objective
- and so now and then this also we want to
- move over
- and same thing with the percentile
- because we did have to move
- um that column
- so just uh carrying those results all
- over
- great so
- we have everything we need in excel now
- let's
- go to do the analysis of how the inputs
- relate to the outputs
- so the first thing that you want to do
- is a scatter plot so we can just
- highlight
- all of these data
- back up to the top and we want to insert
- and we can see that the scatter plot
- comes up as the first
- recommended chart there so let's add
- that
- and we can definitely see a very clear
- relationship here
- between the year's retirement and
- the interest rate
- it also highlights that we have some
- issues here we are getting some
- negative interest rates and so we would
- probably want to
- deal with that in the model as well
- um let's come over to python to quickly
- fix that
- so what we can do is instead of
- random.normal variant we can
- call that but um we're going to do
- while the value is less than zero we're
- going to keep drawing more
- values um and initialize the value at a
- negative number
- and we can call this random normal
- positive you can see it i explained this
- in more detail
- and the adding uh
- monte carlo simulation to a python model
- in that video
- uh same built the same function over
- there
- um and then that can take the mean and
- the
- standard deviation
- and then finally return the value
- so then we can use that instead of
- random normal variant
- redefine that let's go ahead and try
- this again
- and then after this finishes we'll see
- the members update
- yep and now we see that we don't have
- those negative numbers in the
- distribution
- so doing plots of your output is a great
- way to
- check and understand everything that's
- going on in your
- simulations so now we can see there's a
- clear relationship
- just from the scatter plot as the
- interest rate goes up your retirement
- goes down
- and it's a non-linear and it's it's
- a steeper decrease at first and then it
- flattens out as you get to higher
- interest rates
- but we can quantify this relationship
- using the regression so
- regression excel is going to live on the
- data tab
- and then it would be over here
- you can notice that i have nothing over
- here right now and that's because i need
- to enable
- the add-in or the data analysis tool
- pack
- for that to show up so you might already
- have
- data analysis showing up over here but
- if you don't
- it is built into excel you just have to
- enable it so in order to do that you do
- file and then options
- and then add-ins
- um and then you want to manage excel
- add-ins
- and then
- that's where it does say that i have it
- enabled
- let me try disabling it and then
- re-enabling it to see
- if that will allow it to come up um
- hopefully this will come up okay good
- good so now we have the data analysis
- section
- showing up over here and so we can go to
- do
- our regression so just click data
- analysis it brings up a lot of options
- the one we want to use here
- is regression and then it's going to ask
- for your y's and your x's
- so the y is always going to be
- whatever your output is here used to
- retirement
- and the x you can add multiple x
- variables
- and you should if you're changing
- multiple things in your simulation
- here we just have one variable so i'm
- going to add that
- and you'll notice with both of these
- that i picked up the
- label as well and so i'm going to check
- that they have labels and that will
- allow that to come through into the
- regression results as well
- and i'm going to output that analysis
- um right here you can also put it on a
- new sheet
- if you'd like um and then hit okay to
- run it
- and then we see the regression output
- coming up here
- um so then we
- um get the result here of a negative
- 95.8 coefficient with a very low
- p value so it definitely is
- significantly related
- and negative 95.8 that's saying a one
- unit increase
- in the interest rate decreases years to
- retirement by 95.8 years
- now you might say whoa that's huge why
- is that so huge
- that's because a one unit increase here
- is going from
- zero to one hundred percent interest so
- that's obviously uh
- much larger than a realistic interest
- rate change
- uh so to get it to a one percent entry
- increase in interest rate which is a
- more reasonable thing to talk about we
- just divide by 100
- um and so that would be a one percent
- increase in interest rate
- decreases years to retirement by almost
- a year
- um so
- that um we can use that to interpret it
- um and then you know if you had
- other inputs they would just show up as
- additional lines here and you could
- interpret those
- coefficients in a similar fashion
- um and then the one other part of the
- analysis
- is when you do have multiple inputs you
- can't just directly compare the
- coefficients to determine
- which is the most impactful you have to
- compare these standardized coefficients
- and to get the standardized coefficients
- um
- so you would do this for each one of
- your um
- you don't care about the intercept uh
- you would do this for
- each one of uh your inputs
- you would go and you would calculate the
- standard deviation
- uh of that input
- and then
- the standardized coefficients so this
- would be standard deviation
- and then standard coefficients
- so again interest it's just going to be
- standard deviation multiplied by the
- coefficient
- so that's now saying that a one standard
- deviation increase
- in the interest rate leads to
- uh is associated with a decrease in
- years to retirement of
- almost four and a half years and a one
- standard deviation increase and interest
- rate
- is close to five percent
- um and then when you have other
- coefficients here you can just
- pick the largest in absolute value and
- those are going to be the ones
- which are the most impactful inputs in
- your
- model so that shows
- how we can do this analysis of the
- relationship between the inputs and
- outputs
- in excel and then
- to wrap up all this material on monte
- carlo simulation
- there's also a lab exercise here on
- doing this process for
- your project one model in excel
- it's going to be very similar to the
- python
- project one extension exercise that was
- mentioned
- in the prior video on extending the
- dynamic salary retirement model in
- python
- um you're just going to go through this
- process of adding
- a monte carlo simulation to your excel
- model
- so here in the level one you're going to
- be varying the number of phones
- um and then analyze the results
- table of probabilities um chance of
- reaching 800 million mpv
- et and cetera two then you're going to
- do the same
- keep that varying as it was but also
- vary the
- lifespan of the machines
- and then in addition to
- the visualization we just talked about
- then you want to go through this
- analysis
- of what's the relationship between the
- inputs
- and the outputs so
- that wraps up this segment on monte
- carlo
- simulation so thanks for listening and
- see you next time