Monte Carlo Simulator Using Historical Returns – Simulating Commodity Prices

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.

The simulated prices generated in the above articles and excel examples were based on the Black Schole’s Terminal Price formula:

St=S0*exp[(r-q-0.5?2)t+??tzt]

Where S0 is the spot price at time zero,

r is the risk free rate

q is the convenience yield

? is the annualized volatility in the commodity’s price

t is the duration since time zero

and zt is a random sample from a normal distribution with mean zero and standard deviation of 1.

zt was obtained in these models by normally scaling the random numbers generated using Excel’s RAND() function, i.e. NORMINV(RAND()).

Our Power tweaked Monte Carlo Simulator with Historical Returns model, presented in MS Excel worksheet format, provides an alternative for zt values. We start of first by obtaining the price data time series for the required commodities whose price we will be simulating. For example our excel worksheet simulates prices respectively for Gold and Crude oil (WTI). The data used in the model was obtained from the following sources:

  • Gold Spot Price Data from www.onlygold.com for the period January 2005 to August 2011. This price data represents the London P.M. fix spot price which is the price at which the world’s largest size gold purchases and sales are accomplished on any given day. The price is expressed in USD for 1 troy ounce of Gold.
  • West Texas Intermediate, Cushing, OK, Spot Prices, expressed in USD for 1 barrel of Crude Oil from eia.gov for the period January 2001 to August 2011.

A daily return series is generated from the given price data series by taking the natural log of the ratio of successive prices, i.e. ln(Pt/Pt-1) where Pi is the price on date i. Each daily return is then assigned an index number ranging in order from 1 to however many daily returns have been calculated in the series.

In a variation to this method, the return series is calculated as above. It is then ordered in descending ordered, largest to smallest. The ordered series rather than the original return series is assigned index numbers.

Random numbers are generated as in the original Monte Carlo simulation construction discussed in our earlier posts and courses, i.e. using Excel’s RAND() function. However instead of normally scaling the random number, it is adjusted (multiplication by 10000 and rounded nearest whole number) and taken to stand for a particular index number. For example if a random number of 0.1045678 is generated, it will be multiplied by 10,000 and then rounded to the nearest whole number to arrive at an index number of 1044. zt is taken as the historical daily return assigned to this index number scaled by the length of time step, expressed in days, used. For example if the daily return at index number 1044 was 0.01% and the length of the time step was 36.5 days, zt would be 0.365%.

The rest of the model works in a similar manner to the earlier models already shared on-line for free and in excel examples on sale in our finance course store. That is,

  • 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 uses 10 time steps.
  • It uses Excel’s DATA Table functionality to generate Terminal Prices for 25 different scenarios
  • It calculates an Average Terminal Price from the results generated for the 25 scenarios

In addition this model also plots the path of prices for each scenario. By pressing the F9 key new scenarios are graphically displayed.

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.

 





Premium Courses