Originally published: 28/03/2019 08:15
Last version published: 15/03/2020 15:38
Publication number: ELQ-33616-3
View all versions & Certificate
Last version published: 15/03/2020 15:38
Publication number: ELQ-33616-3
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 (64 votes)- 81%
- 14%
- 5%
- -
- -
Write a review
- 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)' publication856Discussadd_shopping_cart$15.00by Andrei Okhlopkov
Descriptive statistics for grouped (weighted) data
Calculating key statistics measures (average, median, standard deviation, skewness etc.) for grouped or weighted data1,039Discussadd_shopping_cartfreeUnlocking Full Potential of Excel Data Tables (Part 1)
This publication explains an advanced method of using data tables for sensitivity analysis in financial models5,590Discussadd_shopping_cartfreeGuiding principles of Financial Modelling
The publication explains the general principles of building financial models with practical examples3,709Discussadd_shopping_cartfreeSensitivity analysis Excel Model with indifference curves
An Excel utility to draw indifference curves for two inputs in a financial model1,6101add_shopping_cartfreeSensitivity analysis Excel Model with indifference curves (unlocked)
An Excel utility (unlocked) to draw indifference curves for two inputs in a financial model257Discussadd_shopping_cart$10.00Simulate the number of Corona virus infections
Simulate Corona virus infection rates230Discussadd_shopping_cartfreeby 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,019Discussadd_shopping_cartfreeby Frank Gary
Analysing data in Excel
A set of techniques for working with data in Excel1,946Discussadd_shopping_cartfreeBasic Simulator in Excel - Piston Engine
This is the most basic simulator model of a piston engine built on Excel VBA Framework.463Discussadd_shopping_cartfreeby Kamal C Bharakhda
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)' publication856Discussadd_shopping_cart$15.00Profit Distribution and Carried Interest Waterfall
A template illustrating various setups of profit distribution between the JV partners with a carried interest waterfall242Discussadd_shopping_cart$30.00Horizontal Waterfall Chart
A smart and efficient chart with detailed step-by-step instructions50Discussadd_shopping_cart$7.00Controlling Outlook from Excel (full version)
Explains how to programmatically link Excel to Outlook156Discussadd_shopping_cart$15.00Analyzing Sales and Product Performance
Tools to analyse sales, portfolio performance, seasonal variations, price-volume-mix factors834Discussadd_shopping_cart$19.00Price-Volume-Mix Analysis
Tools and methods to calculate and depict price, volume and mix effects257Discussadd_shopping_cart$39.00Inventory Analysis and Management
Inventory balance movements, days inventory outstanding (DIO), turnover, seasonality, aging analysis, planning orders109Discussadd_shopping_cart$20.00VBA Interface Collection (full version)
A set of Excel VBA macros aimed at enhancing user interface130Discussadd_shopping_cart$20.00Interim Budgeting (Reforecasting)
Tools to depict the mid-year status, estimate if the outlook looks reasonable and assess the accuracy of past forecasts118Discussadd_shopping_cart$20.00Sensitivity analysis Excel Model with indifference curves (unlocked)
An Excel utility (unlocked) to draw indifference curves for two inputs in a financial model257Discussadd_shopping_cart$10.00Charts and Dashboards in Excel (full version)
Explains the basics of designing professional charts in Excel with practical examples (full version)265Discussadd_shopping_cart$20.00A Chart Showing Three Dimensions of Data
A professionally-designed chart which can show data in three measurements28Discussadd_shopping_cart$5.00
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
