Bitcoin Madness: How to Simulate Bitcoin Prices in Google Sheets

You know the scenario...

Bitcoin had another huge increase, but you missed the opportunity. You wanted to get in, but your gut instinct told you no. And rightfully so...no one knows where the price is going to go. What if you invested, and it had another 20% loss? These sort of price movements are common in the volatile world of cryptocurrencies.

Seriously...how far can this Bitcoin price really go?

BITCOIN IS A VOLATILE BEAST

Risk analysis must be a part of every decision you make.

You are constantly faced with uncertainty, ambiguity, and variability. Variability, in the case of Bitcoin, unlike anything we have ever seen before. And even though we have unprecedented access to information, we can’t accurately predict the future.

Luckily, we have methods that enable you to see all the possible outcomes of your decisions, and assess the impact of risk.

WHERE TO START?

Running simulations can prepare us for the worst.

Monte Carlo simulation (also known as the Monte Carlo Method) allows for better decision making under uncertainty.

One of the most common ways to estimate risk is the use of a Monte Carlo simulation (MCS). From Investopedia:

For example, to calculate the value at risk (VaR) of a portfolio, we can run a Monte Carlo simulation that attempts to predict the worst likely loss for a portfolio given a confidence interval over a specified time horizon - we always need to specify two conditions for VaR: confidence and horizon. (For related reading, see The Uses And Limits Of Volatility and Introduction To Value At Risk (VAR) - Part 1 and Part 2.)

A MCS can be run with many different models. Our own process will be:

1. Specify a model (for here, we will use geometric Brownian motion)
2. Get historical daily bitcoin prices
3. Calculate daily returns
4. Name the daily return range
5. Summary statistics
6. Simulate a year
7. Simulate a year many times
8. Multi-year summary statistics
9. Quick analysis of results

STEP 1. WTF IS GEOMETRIC BROWNIAN MOTION?

The geometric Brownian motion (GBM) is a statistical method that is used heavily in the forecasting of stock prices. The reason the process is so attractive for this is because of the following:

• The change in price over one period of time is unrelated to the change in price over a disjoint period of time.
• The change in log(price) over any period of time is normally distributed with a distribution depending only on the length of the period.
• Samples of the distribution are continuous, with probability 100%.

The GBM is technically a Markov process, which is a fancy way of saying "A random process whose future probabilities are determined by its most recent values." Said another way, past price information is already incorporated and the next price movement is "conditionally independent" of past price movements.

Math geeks have a habit of making things infinitely more complicated than they have to be. I will do my best to make this as simple as possible.

The formula for GBM is as follows:

Where:

• B is the bitcoin price
• m or "mu" is the expected return
• s or "sigma" is the standard deviation of returns
• t is time
• e or "epsilon" is the random variable

This formula can be broken down into two very important terms: "drift" and "shock".

For each time period, our model assumes the price will "drift" up by the expected return. But the drift will be shocked (added or subtracted) by a random shock. The random shock will be the standard deviation "s" multiplied by a random number "e". This is simply a way of scaling the standard deviation.

STEP 1A. THE THUNDER GOD ELI5

The ELI5 version: The thunder god Zeus is a great god. A just god.

But Zeus is subject to wild mood swings.

Every day Zeus can shoot his magic lightning into the price of Bitcoin, and cause it to go up or down.

Some days he is in such a good mood, that he shocks the price up by a random amount. On other days, he is in such a poor mood that he shocks the price down for opposing him.

And thus, we have the essence of GBM: a series of steps with an expected upward drift, where each step is hit with a plus/minus shock (which is a function of the stock's standard deviation).

STEP 2. HISTORICAL DAILY BITCOIN PRICES

Copy the raw data scores from coinmarketcap. Paste the data into your own spreadsheet.

For this exercise, your columns will be: Time, Open, Close, High, Low, Volume.

Want to automatically pull in Bitcoin prices? Use the Spreadstreet Google Sheets Add-in.

STEP 3. CALCULATE DAILY RETURNS

Calculate daily returns from the "Close" price. in H2 put the formula:

=LN(C2/B2)

Drag it all the way down to the end of the prices to fill the entire Returns column

STEP 4. NAME THE DAILY RETURNS RANGE

Create a named range from the returns column, called returns, to make our life easier. Highlight all the data in column H, i.e. cells H1:H1000, then click on the menu Data > Named ranges… and call the range returns:

STEP 5. SUMMARY STATISTICS

Set up a small summary table with the close, daily volatility, annual volatility, daily drift, annual drift, and mean drift of our population. The formulas are:

In K1, enter:

=C2

and name it close.

In K2, enter:

=STDEV(returns)

and name it dailyVolatility

In K3, enter:

=dailyVolatility*SQRT(365)

and name it annualVolatility

In K4, enter:

=AVERAGE(returns)

and name it dailyDrift

In K5, enter:

=dailyDrift*365

and name it annualDrift

In K6, enter:

=dailyDrift-0.5*dailyVolatility^2

and name it meanDrift

STEP 6. SIMULATE A YEAR

Setup the yearly simulation table with Time, Normdist, Log Return, and Simulated Price

Time

In J12 put 0, and in J13 put:

=J12+1

Drag it all the way down to your preferred forecast timeframe. Here I simulated a year (365 days), so I copied down to J377

Normdist

Let’s set up the normal distribution curve values.

Google Sheets has a formula NORMDIST which calculates the value of the normal distribution function for a given value, mean and standard deviation. Since we ascribe to the random walk theory, we want to use a mean of 0, and a standard deviation of 1.

In K13, put the formula:

=NORMINV(RAND(),0,1)

Drag it all the way down to K377 to fill the whole Normdist column:

Log Return

To get the percentage of daily stock movement, we will calculate log return.

In L13, put the formula:

=meanDrift+dailyVolatility*K13

Copy the formula all the way down to L377:

Simulated Price

Now to the real meat. Let's calculate the simulated Bitcoin price.

In M12 put the Close price, and in M13, put:

=M12*EXP(L13)

Copy the formula all the way down to M377:

Forecasted Bitcoin price for one year

Let's see what the pricing data looks like.

Select from M12 to M377, then Insert - Chart and select line chart:

We have now successfully completed one simulation. And depending on your results, they could look normal...or downright crazy.

STEP 7. SIMULATE A YEAR MANY TIMES

We completed one simulation, but we want to run many different trials.

Create a scenario tab, setup a table to simulate 1,000 different one-year trials. In A3 to A1003, put the numbers 1 through 1000.

In B3, put the formula:

=Close*EXP((annualDrift-0.5*annualVolatility^2)+annualVolatility*norminv(rand(),0,1))

Copy the formula down all the way. Name this range "scores":

STEP 8. MULTI-YEAR SUMMARY STATISTICS

Set up a small summary table with the mean, median, standard deviation, min, max, and range of our new population. The formulas are:

=AVERAGE(scores)

=STDEVP(scores)

=MIN(scores)

=MAX(scores)

=E6-E5

STEP 9. QUICK ANALYSIS OF RESULTS

My results will look different than yours (due to the random nature of NORMDIST and the time you pulled the Bitcoin prices). But let's take a look at the results:

Mean 27,147.09
Median 16,097.74
St. Dev 37,243.84
Min 556.60
Max 479,586
Range 479,029

3sd $1,486
2sd $3,005
1sd $5,850
Current $16,098
1sd $43,896
2sd $81,998
3sd $190,129

How to read: We can be 95% certain that the price of Bitcoin will fall between $3,005, and $81,998 in one year.

Wait really? Should I buy? No, this is not telling you to buy. This should be one tool of many to help you in your buying and risk decisions.

CONCLUSION

You now know how to complete a geometric Brownian motion analysis of Bitcoin prices. Congratulations!

Good statistical analysis methods can be scary, but they don't have to be. Here we covered off on a great method for estimating future Bitcoin prices, which can also be applied to other cryptocurrencies.

With this new tool in place, you can be confident in your risk analysis methods by seeing all the possible outcomes of your decisions, and assess the impact of risk.

Deliberate. Analytical. Intelligent.

WANT YOUR OWN COPY?

RELATED POSTS

High-Flyers and Shitcoins: What I Learned from Analyzing CoinMarketCap Data in Google Sheets

7 Smart Ethereum Price Prediction Methods for HODL’ers