Jan 10

Using Excel to simulate standard normal random variable - 9 min. tutorial

by David Harper, CFA, FRM, CIPM

FRM | CFA |

image

We commonly model asset returns under the idea they are normally distributed. In this tutorial, I explain how we can do this with the following Excel function: = NORMSINV((RAND()). The 'S' indicates a standard normal distribution; by definition, 'standard' refers to a distribution with zero mean and one (1.0) standard deviation. Alternatively, we can use =NORMINV(RAND(),0,1) to achieve the same effect. Further:

  • We could randomize other distributions; e.g., I briefly show the randomized lognormal (which is non-normal and would be used to model price levels where the returns are normally distributed. Don't forget: if returns are normal, levels are lognormal.)
  • At the end, I use the random trials to simulate stock price movements. This qualifies as a very simple Monte Carlo simulation!

If you would like to look at the XLS, you can download it here.

Comments

  1. jyothi 11 Jan 2008

    David

    An excellent intro. A quick question.

    Under what conditions can a random samples drawn from a std normal distribution be equated with a monte carlo simulation. In your presentation this issue is blurred.

    A MC simulation for a stock price would need to follow the GBM model which apart from mu and sigma also has Sqrt (time) variable. The assumption here in this presentation is that if the unit of time is taken as 1, then Delta(t) and Sqrt(Delta(t)) would be unity and the above random samples would correspond to MC simulations

    Correct?

    Thanks as always for excellent stuff

    Jyothi

  2. jyothi 12 Jan 2008

    David

    Another quick one.

    What is the difference between this and bootstrapping and MC simulations.

    An excellent site for VBA programmes, such as the cholesky decomposition that you demostrated elsewhere can be found at

    http://www.anthony-vba.kefra.com/index.htm

    Thanks

  3. Chris 15 Feb 2008

    MC simulations generate event outcomes based on defined probability distributions.  Bootstrapping is random sampling based on an existing dataset/outcomes.

Leave a Comment