This guide offers an Excel spreadsheet and tutorial that explores the importance of modeling jump diffusion when predicting option prices.

The Black-Scholes model for option pricing assumes that asset prices are described by geometric Brownian motion, with constant volatility. This means that local asset price move by small amounts (unless the volatility is unrealistically high).

However, in reality the implied volatility of an option is different with respect to various strike prices (give the same asset and expiration date). This is known as a volatility smile or volatility skew (depending on the shape of the volatility profile).

Merton (1976) suggested that volatility skews and smiles could be modeled by introducing Poisson jumps to the volatility in the Black-Scholes model. Essentially, this means that asset prices follow a Brownian drift, with the occasional large spike in asset price. This gives the fat-tailed returns distributions which are observed in reality

Jump processes are particularly important in describing option close to maturity.

Merton’s model introduces two new factors to standard Black-Scholes framework.

- mean number of jumps per time period (usually a year)
- jump size

The call and put prices for a European option under a Merton jump process are defined by these equations

where call_{BS} and put_{BS} are the call and put prices predicted by the standard Black Scholes model (using the adjusted volatility σ_{j}), T the maturity time, σ is the volatility, λ is the number of jumps per year, and γ is the mean jump size.

λ and γ are usually calibrated to historical market data (a similar concept to that of implied volatility).

## Excel for European Options Under Jump Diffusion

This Excel spreadsheet implements Merton’s classic model for pricing European options under the influence of jump diffusion.

These equations are those described earlier in the article and are in VBA. The number of iterations (the summation variable i in the equations above) has to be 170 or less, otherwise a numeric overflow results.

**Download Excel Spreadsheet to Price European Options Under Jump Diffusion**

Hi,

May I ask if this spreadsheet is purchaseable or comes in unprotected format, as I would like to understand how this is implemented in VBA code?

I am currently completing an assessment whereby I need to implement Excel solutions for BSM model.

Thank you in advance for any feedback