Random Number Generation tool with lognormal distribution

[Benjaminmin]

Hi all,

I am writing to ask if anyone could help show me how I can use the Random Number Generation analysis tool to generate numbers based on a LogNormal distribution (which is not one of the option) based on a mean and variance I have?

To be specific, what I am trying to accomplish is the following task:

Simulate the 10 000 realizations of the underlying asset at maturity ST (T = 90 days). To do this, you need to determine the underlying’s distribution at T in the riskneutral world. ST is lognormally distributed, which is equivalent to saying that ln(ST) is normally distributed. Furthermore, they show that the mean and variance of ln(ST) are given by:
E*( lnST ) = lnS + rT - s ²T/2 and var*( lnST ) = s ²T


Use Excel’s Random Number Generation analysis tool to generate the numbers (This tool is part of the Analysis ToolPak in Excel)

If anyone can help I would be awesomely grateful!

Cheers
Ben

 

[sijpie]

Of those 10 I belong to the 1st...


Anyway to your question. No I don't know how to do it. but the following functions might help get you to a solution. The URL in the function is no longer working, so I had to figure out how to use the functions to my need (monte-Carlo simulations with skewed distributions)

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> RandSkew(fAlpha <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>, _<br>    <SPAN style="color:#00007F">Optional</SPAN> fLocation <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN> = 0, _<br>    <SPAN style="color:#00007F">Optional</SPAN> fScale <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN> = 1, _<br>    <SPAN style="color:#00007F">Optional</SPAN> bVolatile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">False</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>     <br>     <SPAN style="color:#007F00">' shg 2008-0919</SPAN><br>     <SPAN style="color:#007F00">' http://azzalini.stat.unipd.it/SN/</SPAN><br>     <SPAN style="color:#007F00">' http://azzalini.stat.unipd.it/SN/faq-r.html</SPAN><br>     <SPAN style="color:#007F00">' Returns a random variable with skewed distribution</SPAN><br>     <SPAN style="color:#007F00">'       fAlpha      = skew</SPAN><br>     <SPAN style="color:#007F00">'       fLocation   = location</SPAN><br>     <SPAN style="color:#007F00">'       fScale > 0  = scale</SPAN><br>     <br>    <SPAN style="color:#00007F">Dim</SPAN> sigma   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> afRN()  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> u0      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> v       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> u1      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>     <br>    <SPAN style="color:#00007F">If</SPAN> bVolatile <SPAN style="color:#00007F">Then</SPAN> Application.Volatile<br><SPAN style="color:#007F00">'    Randomize (Timer)  ' done in calling program</SPAN><br>     <br>    sigma = fAlpha / Sqr(1 + fAlpha ^ 2)<br>     <br>    afRN = RandNorm()<br>    u0 = afRN(1)<br>    v = afRN(2)<br>    u1 = sigma * u0 + Sqr(1 - sigma ^ 2) * v<br>     <br>    RandSkew = IIf(u0 >= 0, u1, -u1) * fScale + fLocation<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br> <br><SPAN style="color:#00007F">Function</SPAN> RandNorm(<SPAN style="color:#00007F">Optional</SPAN> Mean <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN> = 0!, _<br>    <SPAN style="color:#00007F">Optional</SPAN> Dev <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN> = 1!, _<br>    <SPAN style="color:#00007F">Optional</SPAN> bVolatile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">False</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>()<br>     <SPAN style="color:#007F00">' shg 1999-1103</SPAN><br>     <SPAN style="color:#007F00">' Returns a pair of random deviates (Singles) with the specified</SPAN><br>     <SPAN style="color:#007F00">' mean and deviation</SPAN><br>     <br>     <SPAN style="color:#007F00">' Orders of magnitude faster than =NORMINV(RAND(), Mean, StdDev)</SPAN><br>     <br>     <SPAN style="color:#007F00">' Box-Muller Polar Method</SPAN><br>     <SPAN style="color:#007F00">' Donald Knuth, The Art of Computer Programming,</SPAN><br>     <SPAN style="color:#007F00">' Vol 2, Seminumerical Algorithms, p. 117</SPAN><br>     <br>    <SPAN style="color:#00007F">Dim</SPAN> af(1 <SPAN style="color:#00007F">To</SPAN> 2) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> y       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> w       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>     <br>    <SPAN style="color:#00007F">If</SPAN> bVolatile <SPAN style="color:#00007F">Then</SPAN> Application.Volatile<br>     <br>    <SPAN style="color:#00007F">Do</SPAN><br>        x = 2! * Rnd - 1!<br>        y = 2! * Rnd - 1!<br>        w = x ^ 2 + y ^ 2<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> w < 1!<br>     <br>    w = Sqr((-2! * <SPAN style="color:#00007F">CSng</SPAN>(Log(w))) / w)<br>    af(1) = Dev * x * w + Mean<br>    af(2) = Dev * y * w + Mean<br>    RandNorm = af<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>    </FONT>

 

[Benjaminmin]

Hey, Sorry for the super late reply, I was sure I had already replied.

Thank you very much for your help - I got it working, much appreciated!!