Skip to main content

Log Normal Distribution

Where do you meet this distribution?​

  • Finance, Economics : Change of stock price

Shape of Distribution​

Basic Properties​

  • Two parameters M,SM, S are required (How can you get these).

    S>0S>0

  • Continuous distribution defined on semi-bounded range x>0x>0

  • This distribution is always asymmetric.

Probability​

AB
1DataDescription
20.5Value for which you want the distribution
30.1Value of parameter M
42Value of parameter S
5FormulaDescription (Result)
6=NTLOGNORMDIST(A2,A3,A4,TRUE)Cumulative distribution function for the terms above
7=NTLOGNORMDIST(A2,A3,A4,FALSE)Probability density function for the terms above

Sample distribution

Quantile​

AB
1DataDescription
20.7Probability associated with the distribution
30.1Value of parameter M
42Value of parameter S
5FormulaDescription (Result)
6=NTLOGNORMINV(A2,A3,A4)Inverse of the cumulative distribution function for the terms above

Characteristics​

Mean -- Where is the "center" of the distribution? (Definition)​

  • Mean of the distribution is given as

    mωm\sqrt{\omega}

    where

    m=exp⁡(M),;ω=exp⁡(S2)m=\exp(M),;\omega=\exp(S^2)

  • How to compute this on Excel

AB
1DataDescription
20.1Value of parameter M
32Value of parameter S
4FormulaDescription (Result)
5=NTLOGNORMMEAN(A2,A3)Mean of the distribution for the terms above

Standard Deviation -- How wide does the distribution spread? (Definition)​

  • Variance of the distribution is given as

    m2ω(ω−1)m^2\omega(\omega-1)

    where

    m=exp⁡(M),;ω=exp⁡(S2)m=\exp(M),;\omega=\exp(S^2)

    Standard Deviation is a positive square root of Variance.

  • How to compute this on Excel

AB
1DataDescription
20.1Value of parameter M
32Value of parameter S
4FormulaDescription (Result)
5=NTLOGNORMSTDEV(A2,A3)Standard deviation of the distribution for the terms above

Skewness -- Which side is the distribution distorted into? (Definition)​

  • Skewness of the distribution is given as

    (ω+2)ω−1(\omega+2)\sqrt{\omega-1}

    where

    ω=exp⁡(S2)\omega=\exp(S^2)

  • How to compute this on Excel

AB
1DataDescription
20.1Value of parameter M
32Value of parameter S
4FormulaDescription (Result)
5=NTLOGNORMSKEW(A2,A3)Skewness of the distribution for the terms above

Kurtosis -- Sharp or Dull, consequently Fat Tail or Thin Tail (Definition)​

  • Kurtosis of the distribution is given as

    ω4+2ω3+3ω2−6\omega^4+2\omega^3+3\omega^2-6

    where

    ω=exp⁡(S2)\omega=\exp(S^2)

  • How to compute this on Excel

AB
1DataDescription
20.1Value of parameter M
32Value of parameter S
4FormulaDescription (Result)
5=NTLOGNORMKURT(A2,A3)Kurtosis of the distribution for the terms above

Random Numbers​

  • Random number x is generated by inverse function method, which is for uniform random U,

    x=exp⁡[SΦ−1(U)+M]x=\exp\left[S\Phi^{-1}(U)+M\right]

    where\

    Φ(⋅)\Phi(\cdot)

  • How to generate random numbers on Excel.

AB
1DataDescription
20.1Value of parameter M
32Value of parameter S
4FormulaDescription (Result)
5=NTRANDLOGNORM(100,A2,A3,0)100 log normal deviates based on Mersenne-Twister algorithm for which the parameters above

Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A7:A106 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER.

NtRand Functions​

Reference​