Originally published: 28/03/2019 08:15
Last version published: 08/04/2021 16:22
Publication number: ELQ-33616-6
View all versions & Certificate
Last version published: 08/04/2021 16:22
Publication number: ELQ-33616-6
View all versions & Certificate
Monte Carlo Analysis (without macros) Excel Model
This post explains how to run full Monte Carlo analysis in Excel without macros
Further information
Perform sensitivity analysis in situations of uncertainty or limited input data
Financial modelling in situations involving uncertainty
None
Reviews
4.8 / 5 (78 votes)- 83%
- 13%
- 4%
- -
- -
Write a review
- COOLthis is so cool
- Great, InstructiveVery instructive, there are great examples by distributions
People using this Best Practice also downloaded
Unlocking Full Potential of Excel Data Tables (Part 2)
This is a sequel to my 'Unlocking Full Potential of Excel Data Tables (Part 1)' publication1,169Discussadd_shopping_cart$15.00
by Andrei Okhlopkov
Descriptive statistics for grouped (weighted) data
Calculating key statistics measures (average, median, standard deviation, skewness etc.) for grouped or weighted data1,314Discussadd_shopping_cartfree
by Andrei Okhlopkov
Unlocking Full Potential of Excel Data Tables (Part 1)
This publication explains an advanced method of using data tables for sensitivity analysis in financial models6,881Discussadd_shopping_cartfree
by Andrei Okhlopkov
Guiding principles of Financial Modelling
The publication explains the general principles of building financial models with practical examples4,698Discussadd_shopping_cartfree
by Andrei Okhlopkov
Sensitivity analysis Excel Model with indifference curves
An Excel utility to draw indifference curves for two inputs in a financial model1,9561add_shopping_cartfree
by Andrei Okhlopkov
Sensitivity analysis Excel Model with indifference curves (unlocked)
An Excel utility (unlocked) to draw indifference curves for two inputs in a financial model352Discussadd_shopping_cart$10.00
by Andrei Okhlopkov
Scatter Plot Matrix in Excel (12x12 panels) with Correlation Matrix
Excel template to build a Scatterplot Matrix—with up to 12 variables—in one chart for ease of analysis and formatting.47Discussadd_shopping_cart$25.00
by Hicham Bou Habib
Simulate the number of Corona virus infections
Simulate Corona virus infection rates324Discussadd_shopping_cartfree
by Gary Knott
Monte Carlo Simulation Excel Model
Monte Carlo Model generated in excel with explanatory article included to guide you block-by-block through the code.1,363Discussadd_shopping_cartfree
by Frank Gary
Investment Return Monte Carlo Simulation Excel Model Template
Model to calculate the expected value and return of an investment over an investment period using Monte Carlo simulation2,1882add_shopping_cart$30.00
by Projectify
More Best Practices from Andrei Okhlopkov
Unlocking Full Potential of Excel Data Tables (Part 2)
This is a sequel to my 'Unlocking Full Potential of Excel Data Tables (Part 1)' publication1,169Discussadd_shopping_cart$15.00
by Andrei Okhlopkov
Price-Volume-Mix Analysis
A comprehensive full-scope model to analyse revenue and profit changes1,207Discussadd_shopping_cart$50.00
by Andrei Okhlopkov
Inventory Analysis and Management
Inventory balance movements, days inventory outstanding (DIO), turnover, seasonality, aging analysis, planning orders4052add_shopping_cart$20.00
by Andrei Okhlopkov
Van Westendorp’s Pricing Method
Determines optimal and acceptable price points, plots demand curves, estimates revenue maximisation points235Discussadd_shopping_cart$12.00
by Andrei Okhlopkov
Profit Distribution and Carried Interest Waterfall
A template illustrating various setups of profit distribution between the JV partners with a carried interest waterfall1,1652add_shopping_cart$30.00
by Andrei Okhlopkov
Measuring and Modeling Seasonality
A set of very practical tools to analyse seasonal fluctuations299Discussadd_shopping_cart$25.00
by Andrei Okhlopkov
Due Diligence Tools and Methods
A suite of best practices to perform financial and commercial due diligence515Discussadd_shopping_cart$150.00
by Andrei Okhlopkov
Analyzing Sales and Product Performance
Tools to analyse sales, portfolio performance, seasonal variations, price-volume-mix factors1,080Discussadd_shopping_cart$19.00
by Andrei Okhlopkov
Cap Table with Derivative Instruments, Investor Returns and Charts
Advanced multiple round startup cap table56Discussadd_shopping_cart$35.00
by Andrei Okhlopkov
Interim Budgeting (Reforecasting)
Tools to depict the mid-year status, estimate if the outlook looks reasonable and assess the accuracy of past forecasts299Discussadd_shopping_cart$20.00
by Andrei Okhlopkov
Cap Table with Investor Returns and Charts
Cap table with 5 investment rounds for up to 10 investors366Discussadd_shopping_cart$25.00
by Andrei Okhlopkov
Horizontal Waterfall Chart
A smart and efficient chart with detailed step-by-step instructions176Discussadd_shopping_cart$7.00
by Andrei Okhlopkov
Discussion feed for Monte Carlo Analysis (without Macros) Excel Model
The user community and author are here to help. Go ahead!
- Andrei, how do you deal with correlations among inputs without VBA? I understand that one need e.g. Cholesky decomposition to transform uncorrelated to correlated inputs? Alexei
- Hi Alexei -
If you look at "Indexation and interest" of the file, to make two correlated arrays I take numbers from an array and "mix" it with random numbers from an independent array. This "mixing" take place in column H (titled "Correlated") and the formula is:
Random_number x Corr_coeff + New_random_number x SqRoot(1 - Corr_coeff^2)
This formula essentially "decomposes" the definition of linear correlation. No need for VBA or complex matrices...
Andrei- Thank you Andrei. It seems to me that it is possible to have only two correlated inputs w/out VBA. For 3 and more one needs Cholesky decomp or copulas?
- Hi Alexei,
I think we can decompose the covariance matrix in Excel as generate multiple correlations without VBA but the formulas will be more complex. By the way, the correlation formula I am using is a special case of Cholesky decomposition for two elements.
Andrei
