Our course on Building Monte Carlo Simulators in Excel and related available-for-sale excel examples for Commodities, Currencies and Equities provide the groundwork for this EXCEL model. The model is an extension of the work done on this site as well as by us as part of our risk management practice.

where zt is a random sample from a normal distribution with mean zero and standard deviation of 1. In EXCEL zt is obtained by normally scaling the random numbers generated using the RAND() function, i.e. NORMINV(RAND()).

The traditional Monte Carlo simulation model assumes that the underlying return distribution is normal. However, the question arises – “Does the normal distribution truly reflect how price returns actually evolve over time?” Let us consider the price return series for Gold. We have the following information and parameters available to us:

Figure 1: Parameters and inputs for the Monte Carlo simulation model

The spot price for gold on 14-March-2011 was 1,422.25. The one year US Treasury Yield curve rate on this date was 0.22% which we have taken as a proxy to the risk free rate. We have assumed a convenience yield of 0%. The average annualized volatility of actual price returns over the 365 trading day period from 15-March-2011 to 28-September-2012 was 25%.

Using the Monte Carlo simulation model with the Black Scholes Terminal price formula we simulate prices for the next 365 trading days. Note that µ is replaced in the formula above by the risk free rate less the convenience yield. The total period being considered is 365 days (=N) and the time step, t, is a daily time step, i.e. 1/365 expressed in years.

Actual price series against one scenario of the resulting simulated price series is given in the graph below:

Figure 2: Actual Gold spot price series versus one scenario of MC simulated price series using the original approach

In this scenario, at first glance, the simulated price series seems to be fairly consistent with the actual gold spot price series. However consider the histograms of price return series below. The histograms plot the frequency distribution of the return series:

Figure 3: Actual Gold spot price return series histogram

Figure 4: Simulated Gold spot price return series histogram using the original MC approach under one scenario

Actual returns show a much wider dispersion of returns and much longer tails than their normally simulated counterparts.

In another scenario, the prices series are themselves very divergent:

Figure 5: Actual Gold spot price series versus another scenario of MC simulated price series using the original approach

There is nearly a USD 700 difference between the actual and simulated price on day 365. And once again the normally simulated series do not capture the actual extreme tail events as can be since in the figure below:

Figure 6: Simulated Gold spot price return series histogram using the original MC approach under another scenario

Average terminal price at day 365 over the following 25 scenarios show a difference of USD 300 between the actual price and the simulated price.

Figure 8: Average Terminal prices across 25 scenarios using the original MC simulation approach

In this chapter we present an alternative method for simulating the price series to reconcile some of the difference that we see between actual historical and simulated returns. However the important point to note here is that this method is a form of calibration of simulated results to historical returns. In reality there is no guarantee that the future will pan out in a similar manner as it had done in the past. This is a necessary caveat or qualification that needs to be made if this model is used. If the future turns out differently from what has actually happened over the historical period of study being considered, results and decisions made on those results, may not be retain they validity.

In the alternate method, the z-scores are determined from the actual historical return series rather than by applying the inverse of the standard normal distribution function in EXCEL to the random numbers generated using the RAND() function, i.e. NORMINV(RAND()).

To see how this is done let us once again look at the terminal price formula:

By rearranging the equation we have zt = (ln(St/St-1) – (r-q-o.5σ2)t)/σ√t.

Note that we have replaced µ with the risk free rate less the convenience yield, i.e. r-q, and S0 with St-1. The latter has been done because the simulated price path is determined using an iterative process where prices for that day, t, are derived using the previous day’s (t-1) simulated price.

The natural log of the ratio of successive prices, ln(St/St-1) is the daily price return at time t. If we replace this price return with the actual daily historical return, then zt will in effect be a derived z-score applicable to that historical return.

Each derived zt will be assigned an index number. This index number will be a multiple of 1/number of time steps, in this instance, 1/365. Why 1 in the numerator? Because the range between the minimum and maximum numbers in the uniform random series is 1. The first zt will be assigned 1/365, the second 2/365, the third 3/365 and so on.

In a variation to this method, the actual return series is then ordered in ascending ordered, smallest to largest and then assigned index numbers as above.

Random numbers are generated as in the original Monte Carlo simulation construction as discussed earlier, i.e. using EXCEL’s RAND() function. However instead of normally scaling the random number, the random number is taken to stand for a particular index number. Using EXCEL’s VLOOKUP functionality the corresponding derived zt is then selected.

The rest of the model works in a similar manner to the traditional MC simulator, i.e.

It calculates a path of prices, St, for each time step up to and including the Terminal price at the end of the specified duration, T.

It calculates an Average Terminal Price from the results generated for the 25 scenarios

It plots the path of prices & histogram of the distribution of returns for each scenario. By pressing the function key, F9, new scenarios are graphically displayed.

Figure 9: Monte Carlo Simulation model using Historical Returns

The scenarios generated under this alternative approach produce results that are more in line with the actual historical returns as can be seen from the price graph as well as the return histograms below.

Figure 10: Actual Gold spot price series versus one scenario of MC simulated price series using the historical returns approach

Though there is variation in results from one scenario to the next these deviations on average are much less pronounced as compared to the original Monte Carlo simulation model. In our model, across the 25 scenarios captured in the Data Table, the average of terminal prices converges more closely to the actual spot price of gold on 28-September-2012:

Figure 12: Average Terminal prices across 25 scenarios using the MC simulation with historical returns approach

Our MC simulator with Historical returns model for simulating commodity prices is now available for sale. To purchase the Excel file visit the Computational Finance section of our Finance Course Store.

About the authorJawwad Farid

Jawwad Farid has been building and implementing risk models and back office systems since August 1998. Working with clients on four continents he helps bankers, board members and regulators take a market relevant approach to risk management. He is the author of Models at Work and Option Greeks Primer, both published by Palgrave Macmillan.Jawwad is a Fellow Society of Actuaries, (FSA, Schaumburg, IL), he holds an MBA from Columbia Business School and is a computer science graduate from (NUCES FAST). He is an adjunct faculty member at the SP Jain Global School of Management in Dubai and Singapore where he teaches Risk Management, Derivative Pricing and Entrepreneurship.