Friday, October 14, 2011

Lognormal and Normal Distributions

This is a quick and technical post.  I received a question in the comments of a post mentioning about normal and lognormal distributions:

I have been doing my own research into drawdowns using monte carlo simulations based on a normal distribution. To generate returns I have been using the following formula: =NORMINV(RAND(),$C$1,$D$1. I would like to have a look at my results when basing returns on a lognormal distribution.

How would I go about entering this in excel as just replacing NORMINV with LOGINV yields very unrealistic results.


Just realised the formula in my question refers to the cells. C1 and D1 have the mean and standard deviation for the distribution.

Here is my answer:

There are a couple more steps.

Let me call the mean A1, and the standard deviation A2. These are what you can use for NORMINV

For the lognormal make sure these are defined in decimal form. i.e. a 5% return with 20% standard deviation would be .05 and .2

You need to calculate 3 more cells:

A3=LN(1+A1)
A4=SQRT(LN(A2^2+EXP(2*A3))-2*A3)
A5=A3-0.5*A4^2

Then you can use:

=LOGINV(RAND(),$A$5,$A$4)-1

The results shouldn't be too different, but an advantage of lognormal distributions is that they prevent you from having a return of less than
-100%. This could sometimes happen with volatile returns for the normal distribution.