iPad iBook teaches how to build financial simulators in Excel

4 mins read

iPad iBook teaches how to build financial simulations in Excel.

Building Monte Carlo Simulation iBook, our latest iBook on Monte Carlo Simulators in Excel is now live on the Apple iBook store. With 136 pages, 8 video lectures and 5 review sessions the iBook reviews the process of building Monte Carlo Simulators in Excel to manage risk and price options as well as walk through convergence techniques to speed up your simulators. You can purchase the our latest iBook in the financial models series on the iBook store.

The iBook uses our award winning approach to teaching complex financial topics by combining video lessons with step by step model building instructions and interactive review sessions that allow you to dissect and disassemble complex models using your iPad. Models reviewed within the iBook include:

  • Financial Risk Management.
  • Commodity price simulation.
  • Option Pricing.
  • Interest Rate Simulators.

Monte Carlo Simulation is a numerical method that is used in a wide range of applications in finance, space exploration, energy, engineering, etc. In finance it has been used to price complex derivatives that do not have ready closed form solutions; determine profitability distributions for various hedging strategies; simulate interest rate term structures; understand various problems being analyzed such as decoding option price sensitivities (Greeks) or the risk adjusted probabilities of the Black Scholes formula; quantify the risk of illiquid or complex financial products, etc.

Like all analysis tools, simulation allows us to understand problems we are trying to analyze. Sometimes we can use this tool to help our audiences better understand solutions and recommendations. Sometimes the primary beneficiary is the person building the model, because by definition structuring and sculpting the model brings us closer to the problem and its probable solution. For example, Monte Carlo models can take something as basic and chaotic as a return series for crude oil and, produce something as powerful and elegant as a profitability distribution for a hedging strategy – which becomes useful when you have a board presentation and need something intuitive and powerful to show.

There are two primary uses of Monte Carlo Simulation that we cover in this book – one (the more popular use) is option pricing, the second (unpopular by a notch), is risk management.

What is Monte Carlo Simulation?

A simulation is an experiment. How will people react when you ring their doorbell and run? Short of actually ringing the doorbell and running do you have any other options? But sometimes ringing doorbells and running is not possible in an academic or professional setting.

An easy compromise is a coin toss. In case of heads they will open the door, in case of tails they won’t. In case of heads they will scream and shout and curse, in case of tails they will quietly shake their heads and close the door. In case of heads the wrath of God will strike you down, in case of tails clouds will part, angels will sing and you will receive manna from heaven.

Over the last two centuries the science around coin tossing has been refined to mathematical poetry. All coin tosses are not created equal. Some lead to symmetry and are treated as fair. Others are clearly biased and are treated as skewed. Some come with memory (your result is dependent on the history of coin tosses), while others start afresh with each toss (as pure as driven snow).

So toss a coin a hundred times and if it is fair, you will travel the distance from the Binomial distribution all the way up to the Normal distribution. Toss it without memory and you can throw in the waiting game along with the Poisson analysis. Want a bit of a skew introduce the Lognormal, the Gamma and the Beta. The choice is yours but the results are very different.

The graph that follows shows the result from a short simulation using some of the above-mentioned distributions. While not all distributions are equal, you can clearly see that some are more equal than others.

Figure 1: Simulated results using various distributions

The art of using a coin toss to simulate the results of an experiment is one form of simulation. Inspired by the ancient tradition of counting tosses and the close relationship between coin tosses, cards and gambling the science is called Monte Carlo simulation.

What is a Monte Carlo Simulator?

A Monte Carlo simulator at its heart is a simple coin tossing machine. Depending on the tool used to build the machine (the choice of distribution) the simulator will behave in a certain fashion (symmetric, asymmetric, normal and skewed, with thin tails or long fat tails).

MS EXCEL comes with its own built-in Monte Carlo (MC) simulator. The simplest and easiest is the function called RAND(). The graph above was generated using the RAND() function embedded within function calls to the Normal, Lognormal, Beta and Gamma distributions in MS EXCEL.

The choice of the underlying distribution is one of the primary factors in the model. The other driver is the model itself.

Simulation – The process or generator function

Each MC simulation model comes with certain behavioral assumptions. These assumptions are based on process or what Nicholas Nassim Taleb (NNT) calls the generator function. One of the objectives of the simulation is to understand how the model interacts with the assumptions and how that interaction changes the distribution of simulated results.

Check it out on the iBook store now.