I am supposed to model daily stock prices with a normal inverse gauss distribution in excel. I feel like I am misssing some basics because I cant transform the information from the academic papers into an excel formula. Does anyone have any experience with this distribution? How do I go from the PDF to the St = So * exp (X) formula?
Add a comment
|
1 Answer
$\begingroup$
$\endgroup$
Note: In your text you refer to the stock prices and using a normal inverse gaussian. This would correspond to a normal model. However the formula you write suggests a log-normal (Black-Scholes) like model (not sure what X is), i.e. using a normal inverse gaussian for the stock returns.
For Excel: The spreadsheet at http://www.christian-fries.de/finmath/spreadsheets/ does a Monte-Carlo Simulation of a Black-Scholes model and the corresponding risk neutral valuation of a derivative. To convert the log-normal process to a normal one: convert the Euler scheme
=D18+$C$6*D18*$C$8+$C$7*D18*NORMINV(RANDOM();0;1)*SQRT($C$8)
you have
=D18+$C$6*$C$8+$C$7*NORMINV(RANDOM();0;1)*SQRT($C$8)
where this is cell E18 and
- D18 denotes the previous cell / realization of the stock
- \$C\$6 is the drift r
- \$C\$8 is the time step dt
- \$C\$7 is the volatility $\sigma$
and each row then corresponds to a Monte-Carlo path. (Remark: In the normal case case, the Euler scheme is the exact solution. In the log normal case it is much better to use the Euler scheme for log(S), i.e. $S(t+\Delta t) = S(t) * \exp(r \Delta t - 0.5 \sigma \Delta t + \Delta W(t))$ - I assume you can guess the corresponding Excel formula.
