The model presents an example of a Monte Carlo Simulation using excel to estimate the Net Present Value of an investment.
Monte Carlo simulation is commonly used to evaluate the risk and uncertainty that would affect the outcome of different decision options. It also allows to incorporate the total effects of uncertainty in variables like sales volume, prices and costs, interest and exchange rates, as well as the effect of distinct risk events.
Monte Carlo methods in finance are often used to evaluate investments in projects at a business unit or corporate level, or to evaluate financial derivatives. They can be used to model project schedules, where simulations aggregate estimates for worst-case, best-case, and most likely duration for each task to determine outcomes for the overall project.
In this excel model, we will use the Monte Carlo simulation to evaluate a simple investment project.
In our case we have three base case assumptions. These are: the investment, the cash inflows for the first year, and the cash outflows for the first year. We modify these three assumptions accordingly. The investment and the cash outflow should be entered as negative numbers, while the cash inflow as a positive number.
In lines 28 to 33, we have the monte carlo simulation parameters. More precisely, these are:
The investment variability: the user needs to set the upper and lower values of the uniform distribution which will be a random number within this range and will increase the investment by a random number in this range.
For the Project Inflows and Outflows Variability in the first year as well as the discount rate, the user sets the mean and the standard deviation of the normal distribution, which will be used to increase or decrease the first year Inflows or Outflows.
Finally for the Project Inflows and Outflows Yearly Growths, as well as for the discount rate, we have to input the upper and lower percentages of the uniform distribution. A random number is selected within this range.
By combining all the above assumptions for the investment, the cash inflows, cash outflows, and the discount rate, we are able to calculate the Net Present Value of the investment.
The next step is to enable the macros, which can be done through the Excel Options, Trust Center, Trust Center Settings, and select “Disable all macros with notification”.
Save Excel and close it. Once you reopen Excel, you will be presented with a security warning, press “Enable Content”.
Afterwards, press the command button “Run Monte Carlo”, this will produce 5 thousand simulation based on the parameters set.
Once the simulation is over, go to the charts on the right where you can see the results. An histogram is produced which presents for each bin of Net Present Values, the occurrences in this range (otherwise known as frequencies).
The user can also see the relevant table used to produce the histogram chart, as well as various metrics such as: average Net Present Value, Standard Deviation, Minimum, Maximum, and Median.
Similar Products
Other customers were also interested in...
Leveraged Buyout (LBO) Model
Leveraged Buy Out (LBO) Model presents the business case of the purchase of a company by using a hig... Read more
Capital Budgeting Model Excel Template
Capital Budgeting consists of a process that companies use for decision making on investment project... Read more
Mergers and Acquisition (M&A) Financial Model
Merger and Acquisition Model template consists of an excel model which assists the user to assess th... Read more
Dynamic Arrays Financial Model
Dynamic Arrays Financial Model generates the three financial statements (profit & loss, balance ... Read more
Cash Budget Model
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compa... Read more
Operating Company Financial Model
Operating Company Model consists of an Excel model which allows the user to generate forecast financ... Read more
Options and Real Options Model Template
Options and Real Options model consists of an Excel model which presents a valuation framework calcu... Read more
Inventory Dashboard Model Template
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Sales Commissions Dashboard
This model will allow you to plan your commission structure and monitor the performance of your sale... Read more
Dashboard Actuals PnL Model
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Reviews
As with most efinancial models, this Montecarlo simulation is ready to use and easy to understand.
Very practical in its structure
16 of 37 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
-
Thank you for your review and rating, really appreciated it!
16 of 31 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
This is a very useful tool. Very well constructed and thought out.
31 of 53 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
-
Thank you for your review and rating, it helps us a lot!
13 of 22 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
My dear friend,thank you for your free Monte Carlo simulation in Excel file.I didnt have time to test it.I was fixing my own first Monte Carlo Simulation haha.Ones i used i will give back feedback.I will use yor instructions because i take a look but something was not working.
Thank you Chris
26 of 51 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
-
Thank you Chris for your review and rating!!
24 of 47 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
You must log in to submit a review.