Excel Models
Finite Difference Methods
The finite difference method, beyond finance, is actively used in physics and engineering fields, such as:
- 🌡️ In heat transfer equations (Heat equation)
- 🌊 In fluid dynamics (Navier–Stokes equations)
- ⚡ In electromagnetism
- 🧠 In neuronal diffusion
- 🏗️ In structural mechanics
The method involves breaking down a continuous differential equation into a system of finite difference equations and then solving them.
In the case of option pricing, a matrix is created where one dimension represents the possible underlying asset prices and the other represents the remaining time to contract expiration. The option’s value is calculated at the intersections of these two dimensions.
The method has two types: Implicit and Explicit.
Implicit Finite Difference Method
Let’s consider an example. Suppose we want to calculate the price of a 5-month American PUT option with a strike price of $50.
First, to construct the matrix, we need to decide how many steps to divide both price and time. We first take a maximum price (Smax), at which the value of the Put option is almost zero. In our example, this is $100. Then, we determine the number of steps for both time and price. Ultimately, we get the following initial data:
Next, the boundaries of the matrix are set, where option prices are defined using relatively simple formulas.
Then, coefficients are calculated, followed by filling the matrix cells. Since the formulas are circular, the corresponding function in Excel must be enabled.
The need for a circular formula arises because neighboring cells in the matrix depend on each other. In other words, the system of equations is solved for the entire row simultaneously.
The order of calculation matters, because, like in binomial trees, the recalculation is done from the far end (closest to expiration) backward in time. (Note that the time axis represents the remaining time until contract expiration.) In our example, the first cell is calculated from the lower-left corner, then its neighboring right-hand cells, and then the upper rows. In other words, the formula is first dragged to the right, then up the row.
The final result is the option price, which is shown at the intersection of the $50 column and the 5-month row: $4.10.
It is important to note that American options allow early exercise, which the method accounts for during calculations.
Additionally, the result’s accuracy depends on the resolution of the matrix. Since this is a discrete solution of a continuous integral, values converge as the matrix is divided into sufficiently small cells.
For this reason, the control variate technique is useful. For example, in our case, the same matrix yields a European option price of $3.91, while the Black-Scholes formula gives $4.07. The difference represents the expected error between the calculated and the “true” value. This gives:
4.10 + (4.07 − 3.91) = $4.26
Explicit Finite Difference Method
The explicit method differs from the implicit method in that it is direct algebra—there are no iterations. Therefore, it requires less computational power, but it does not always converge (the model may “explode”). Dependencies between cells are different: each cell depends on the neighbors in the row below.
It can be shown that this method is equivalent to a trinomial tree solution in matrix form.
P.S. For more efficient calculations, it is better to take the logarithm of prices in the matrix (i.e., replace S with Ln(S)), which adjusts the formulas accordingly (not implemented in Excel here).
P.P.S. There are also variations of the method, such as the Crank–Nicolson method, which represents a combination of the implicit and explicit versions. Formula transformations are performed in the respective cells.
Excel files:
Source:
Options, Futures & Other Derivatives, John C. Hull