Monte Carlo Simulation Data Table Help - Financial Model

[hmd3d]

Monte Carlo Sims Help - Financial Model

I built a model to project and compare the returns of multiple real estate investments each under different scenarios. The model is not small (3.5 MB) with many volatile formulas (quickest way to consolidate each of the historical cash flow inputs from different sources i.e. indirect formulas, sumproduct, etc.) but a file of this size should be able to handle a data table of Monte Carlo sims. I have a deterministic/stochastic toggle to switch from a single set of input parameters to a range of random variables in order to quantify and compare risk adjusted returns across the different investments/scenarios. However, the 10,000 Monte Carlo simulations, which I am trying to produce via a data table, generates identical outputs. With the stochastic toggle on, I have set calculations to automatic (except data tables) and calculated via f9; I’ve tried various vba subroutines that calculate the workbook (turns off screen updating etc. and all the common procedures that speed up excel calcs); and I reduced the simulations to 1,000 but none of these fix the issue to produce the desired stochastic outputs. I was able to get a few hundred different outputs by hitting f9 rapidly in succession for about 5 minutes straight, but the stochastic outputs never update through the entire data table. I really don’t think the file size is too large for this one data table (I have 4 other small single-variable data tables elsewhere in the workbook ~6 outputs each which all work correctly). Any assistance or even creative ideas for a workaround would be most appreciated. Happy to share more details/example with anyone that may be able and willing to assist. Thanks in advance!

 

[KRice]

I'm curious about your approach. Does each row of your sheet (where the simulations are managed) represent one scenario?...meaning you define various distributions for your input variables, and on each row, you generate random values belonging to each of those distributions? Could you perhaps offer an example of how you set up one or two of those stochastic variables, as well as a description of the intended distributions for each?

 

[hmd3d]

So there are two basic scenarios that flow simultaneously through the entire model. One assumes that the existing office property investment which was recently vacated is held with an additional capital outlay to re-lease the property. The other scenario assumes the property is sold, and the proceeds fund the equity of a new investment. I have setup static input variables for each of the scenarios assumptions, with the stochastic toggle to alternate the model between the static input variables and random value inputs based on predefined distributions of each of those variables. Conceptually, you will likely have higher returns in the first scenario, since there is value creation in taking the risk to own and invest additional money into a vacant building, compared to selling a vacant building at a lower price, and reinvesting those proceeds into a stabilized new investment through a market transaction. The idea is that the monte carlo will allow me to highlight the difference in the risk-adjusted returns, and quantify the difference in risk per unit of return between the two scenarios. I have layered the stochastic toggle into many of the input variables, so that when it is turned on, the random values are incorporated based on the user-defined distributions of the respective input variable. The inputs are hard-coded on one tab, and there is a separate monte carlo tab, with separate selected inputs for the monte carlo variables. The mean of each monte carlo variable is set by default to equal the corresponding static input on the assumptions tab. Then the user inputs standard deviation, min, and max for each monte carlo variable. I have also incorporated a random walk, to change the monte carlo variables year-to-year. When the toggle is turned off, the monte carlo variables equal the static inputs, and when turned on, the rand() function is applied in a random walk of each variable in each year of the analysis. The model calculations (cash flow projections in two different tabs - one for each scenario - with IRR's and other return metric outputs) are performed based upon the monte carlo tab's inputs, which will equal the static inputs when the stochastic toggle is off, and the randomly distributed variables when the toggle is on. The monte carlo data table is intended to be setup as a single column for each simulation, with the output equal to the unlevered IRR.

 

[hmd3d]

I'm curious about your approach. Does each row of your sheet (where the simulations are managed) represent one scenario?...meaning you define various distributions for your input variables, and on each row, you generate random values belonging to each of those distributions? Could you perhaps offer an example of how you set up one or two of those stochastic variables, as well as a description of the intended distributions for each?

Is there a way to share an example with you? I could not find a pm option on this forum. I can post it here, but would prefer to send it directly/securely. Thank you!!

 

[KRice]

I'm not aware of a PM option and believe that it runs contrary to the forum rules. If it would help, you could use the XL2BB Excel add-in available here to extract just portions of your worksheets and uplad them into your post. Basically, you'd select a block of cells involved in your computations and run the add-in to convert it to code that is pasted from the clipboard into your post.