How to Conduct a Monte Carlo Analysis in Excel, What The Heck is a Monte Carlo Analysis?

In the following blog post, I will walk through how you can conduct a Monte Carlo Analysis of returns in Excel. I will use a problem that I call the stock options problem, which walks through two scenarios, one which shows what will happen if you take the stock options on a risk adjusted basis, versus what will happen if you take the more cautious option, the risk free rate on a CD, in cash, right now. A Monte Carlo Analysis is essentially just a simulation of the two scenarios, and of which one works out better a higher percentage of the time. And on that note, let’s look at how to conduct a Monte Carlo Analysis in Excel, and of how this analysis is one of the best Excel tricks to know for a Financial Analyst. For additional Financial Analyst tips and tricks, be sure to subscribe to our blog or comment down below with your thoughts and opinions on the article, and we’ll get back to you within one business day with a response. And to those in wealth management, unfortunately no, Excel tricks are not something on the Series 7 Exam, although they totally should be.

Other popular Financial Analysis Tricks include the following:

Profit and Loss Statement Analysis

Balance Sheet Mock Ups

Trial Balance Sheets

Cash flow projections

Discounting Future Cash Flows

DCF Valuations

Financial Statement Analysis

Condensing 10-K Reports Into Excel Spreadsheets

Excel Filtering

Excel Pivot Tables

Confidence Interval Analysis

And a host of other Financial Analyst tips and tricks, subscribe for more info!

What is a Monte Carlo Analysis, and How to Conduct a Monte Carlo Analysis in Excel The Right Way

So, the gist of a Monte Carlo analysis is that you are projecting what will happen between two outcomes. If you’ll look at the image just above this subheading, or the image that was on the front page of my blog beside this blog post, this is the gist of a Monte Carlo Analysis that projects several possible return outcomes for two scenarios. The question was below:

Build a Model for two scenarios:

Scenario 1: $5,000 Cash Received Today

Scenario 2: 10,000 stock options, giving you the right to buy the company’s stock 3 years from now at $30 per share. Current share price is $28, and the expected return is 3% per year with a standard deviation of 5%.

I turned it into a more realistic scenario, and said that basically money now is worth more than money later, because of the Time Value of Money (TMV), so $5,000 today isn’t really $5,000, it’s more like $5,000 at the risk free rate of interest, which is say 3%. So say $5,000 now is actually $5796.37, which is $5,000, at 3% for 5 years. This is a riskless option, and for the most part, if say you had this in a money market account or a liquid CD, then you can have complete liquidity and access to your money.

My projection above showed that the stock options choice, which is hte right to buy 10,000 shares of stock 3 years from now at $30 per share, only pays out if the stock moves up. The returns show the following, in a randomly generated model:

What this model basically shows, is that the stock options option, well….is not that great. In 9/10 returns, it barely cracks 3% per year, and out of 3 returns, it only wins 33% of the time. For something with a ton of risk and almost no liquidity, this is a horrible option, and so the risk free, cash based option is much much better. This was a class project from my Master’s Degree in Finance awhile back, and I believe I got something like an 80% on it (it was too real world for Academia;) To generate a list of returns, I used the RAND function in Excel, which gave me a random sample of something like 15 or 20 returns to choose from, of which I took the top 9 and averaged them into 3 different scenarios. And that my friends, is how you conduct a Monte Carlo Analysis in Excel.

Final Thoughts on How to Conduct a Monte Carlo Analysis in Excel, And For What Scenarios This is Best

A Monte Carlo Analysis is best used for cash flow projections, for discounting cash flows, and for plotting possible comparison scenarios. The Options vs. Raw Cash scenario is actually a perfect example for how to use the Monte Carlo analysis in Excel, in that it is a financial problem, is very real world based, and contains raw numbers. You would not be able to do this with something like how much do you spend on Cucumbers vs. Apples, and it would have no bearing and wouldn’t be nearly as interesting. This is perfect for any cash flow, business, or equity market comparisons, and I would highly recommend having this Financial Analysis example in your tool belt if you are looking at pursuing being a Financial Analyst for a career. Subscribe for more details and information and for daily blog posts, and comment down below with any thoughts on the article, thanks for reading!

Cheers!

*Inflation Hedging.com

Sources:

https://finance.fandom.com/wiki/Firm_Foundation_Theory
https://finance.fandom.com/wiki/Castle-in-the-Air_Theory
https://money.cnn.com/data/markets/

Disclaimer: The opinions and documentation contained within this article and on this blog are the sole property of inflationhedging.com and are not to be copyrighted or reproduced in any manner, else legal action within the rights of the United States legal code could be use to obtain recompense. All articles and blog posts are the sole opinions of the writers of the blog, and are not necessarily in line with what exactly will work for you, you should consult a CPA, Tax Professional, or Financial Professional to determine what exact financial needs are in line with your interests. Also, from time to time, certain links on this website will be used to generate affiliate commissions, in order to support the health and growth of our website, health and business.