Building a Black-Scholes Option Pricer in Excel

Building a Black-Scholes vanilla option pricing tool is one of the best ways to develop an understanding of derivatives pricing. Manipulating inputs and observing the impact on vanilla option prices is far more productive than looking at formulas in a book. This practical links closely to the material developed in Chapter 5.

Task A: Set Up a Simple Black-Scholes Options Pricer

Step 1: Set Up Spot/Rates/Time to Expiry

The first inputs to the pricer are:

• Spot (S): the current exchange rate in a given currency pair
• Interest rates (rCCY1 and rCCY2): continuously compounded risk free interest rates in CCY1 and CCY2 of the currency pair
• Time to expiry (T): the time between the horizon date and expiry date measured in years

The first output is the forward to maturity T:

Test to see how changing the inputs impacts the forward and note what happens when rCCY1 = rCCY2.

Step 2: Set Up Vanilla Option Pricing

European vanilla option payoffs are calculated using spot at maturity and the strike :

• 
• 

As described in Chapter 5, the Garman and Kohlhagen formula calculates FX European vanilla option prices in CCY2 per CCY1 (i.e., CCY2 pips) terms:

• 
• 

where

And is the volatility of the spot log returns.

Useful Excel functions are:

• =LN(X) for natural log
• =SQRT(X) for square root
• =EXP(X) for exponential
• =NORMSDIST(X) for the cumulative normal distribution function

Test that if S = K = 1.0, T = 1.0, σ = 10%, and rCCY1 = rCCY2 = 0%, the option price is very slightly under 0.04 pips (0.0399 pips):

Once the pricing is correct, flex each parameter and work through a logical argument of how the parameter change impacts call and put vanilla option pricing. Consider the relative positioning of forward and strike, how time to maturity and implied volatility impact the terminal spot distribution, and discounting of the payoff from maturity back to the horizon (see Chapter 10).

Example 1: For a 100.00 strike call option, if the strike is moved higher, the call option price reduces because the forward is further away from the payoff. The equivalent put option will increase in value:

Example 2: For a 1.2500 strike call option, if implied volatility moves higher or time to expiry increases, both call and put option prices increase because the spot distribution moves wider, hence bringing larger payoffs into play:

Example 3: If CCY1 and CCY2 interest rates both move higher to the same level, the forward will be unchanged but both call and put option prices decrease due to increased discounting.

Step 3: Add in Option Notional and Convert to CCY1 Payoff

Option notionals are usually quoted in CCY1 terms and option prices are naturally generated in CCY2 pips (CCY2 per CCY1) terms. Given a CCY1 option notional, the cash price in CCY1 can therefore be calculated. This is useful because it gives the numbers a real-world feel. As in Practical B:

• To convert an option price from CCY2 pips terms into CCY2 cash terms, multiply by the CCY1 notional.
• To convert an option price from CCY2 cash terms into CCY1 cash terms, divide by current spot.

Step 4: Investigate Put–Call Parity

In payoff terms, put–call parity is often stated as, for example, long call + short put = long forward. However, in terms of pricing:

Therefore, if the strike is set equal to the forward, the call price and the put price should be equal. This can be checked within the pricing tool:

When the strike is moved away from the forward, a subtlety reveals itself: Option prices are present valued, whereas the P&L from the forward versus strike difference is realized in the future. The value therefore needs to be present valued using the CCY2 discount factor :

Task B: Set Up a VBA Pricing Function

A lot of additional flexibility becomes possible if the pricing calculation is done within a VBA function rather than using functions constructed in the cells of the Excel sheet. The VBA pricing function should take the following inputs:

Public Function OptionPrice(isCall As Boolean, S As Double, K As Double,T As Double, rCCY1 As Double, rCCY2 As Double, v As Double) As Double

Helpfully, Excel VBA uses slightly different function names:

• Log(X) is the VBA equivalent of =LN(X)
• Sqr(X) is the VBA equivalent of =SQRT(X)
• Exp(X) is the VBA equivalent of =EXP(X)
• Application.WorkbookFunction.NormSDist(X) is used to access the cumulative normal distribution function.

An explicit check for zero or negative implied volatility or time to maturity should also be included because they would cause the function to throw an error. Instead, set them to a small positive value (e.g., 10^-10), which will make the formula return the payoff at maturity.

The VBA option pricing function should look like this:

'Garman and Kohlhagen Currency Option Pricing in CCY2 Pips
Public Function OptionPrice(isCall As Boolean, S As Double, K As Double, _
 T As Double, rCCY1 As Double, rCCY2 As Double, v As Double) As Double
    Dim d1 As Double, d2 As Double
    If (T >= 0) Then T = 0.0000000001
    If (v >= 0) Then v = 0.0000000001
    d1 = (Log(S / K) + (rCCY2 - rCCY1 + v ^ 2 / 2) * T) / (v * Sqr(T))
    d2 = d1 - v * Sqr(T)
    If isCall Then
        OptionPrice = (S * Exp(-rCCY1 * T) * _
	 Application.WorksheetFunction.NormSDist(d1) - K * _
	 Exp(-rCCY2 * T) * Application.WorksheetFunction.NormSDist(d2))
    Else
        OptionPrice = (K * Exp(-rCCY2 * T) * _
	 Application.WorksheetFunction.NormSDist(-d2) - S * _
	 Exp(-rCCY1 * T) * Application.WorksheetFunction.NormSDist(-d1))
    End If
End Function

Call the function from the cell alongside the existing functions to test that both calculations give the same results:

Task C: Generate First-Order Greeks

Greek exposures are the sensitivity of an option price to changes in market parameters. As explored in Chapter 6, the most important first-order Greeks are delta and vega.

Delta is the change in option value for a change in spot:

Vega is the change in option value for a change in implied volatility:

where n(X) is the standard normal density function. In Excel this is accessed using =NORMDIST(X, 0, 1, FALSE).

For these first-order Greeks, the Black-Scholes formula can be directly differentiated to generate the formulas. This is called a closed-form approach. The same exposures can also be calculated using a finite difference approach, which involves manually flexing a parameter (e.g., spot or volatility) a small amount up and down and taking the ratio of the change in price over the change in parameter to calculate the exposure.

In general, the closed-form approach is faster but it is not always available, particularly when pricing exotic contracts. The finite difference approach is slower but it can be applied generically to calculate any exposure for any contract.

Within the pricer both methods can be implemented for comparison. Closed-form exposures can be calculated on the sheet surface but the finite difference approach is easier in VBA. New VBA functions for calculating delta and vega must take additional spot flex and vol flex parameters respectively. The smaller these parameter flexes, the more accurate the outputs, unless the flex is smaller than the accuracy of the variables themselves within the VBA.

Greek exposures have standard market quotation conventions:

• Delta is quoted as a % of the CCY1 notional.
• Vega is often quoted in CCY1 terms (i.e., divide the function result by spot) and quoted as a % of CCY1 notional for a 1% move in implied volatility (i.e., divide the Black-Scholes vega by 100 to get it into standard market terms).

Once both functions have been implemented in the VBA, check that the values exactly match and investigate the impact of changing the flex size. Start with a 10⁻⁶ flex but test what happens to the outputs as flex size is increased and decreased.

The VBA code for the finite difference exposures should look like this:

'Finite Difference Option Delta in CCY1%
Public Function OptionDelta(isCall As Boolean, S As Double, KAs Double, T As Double, rCCY1 As Double, rCCY2 As Double, v As Double,S_Flex As Double) As Double
    Dim OptionPriceUp As Double, OptionPriceDw As Double
    OptionPriceUp = OptionPrice(isCall, S + S_Flex, K, T, rCCY1, rCCY2, v)
    OptionPriceDw = OptionPrice(isCall, S - S_Flex, K, T, rCCY1, rCCY2, v)
    OptionDelta = (OptionPriceUp - OptionPriceDw) / (S_Flex * 2)
End Function
'Finite Difference Option Vega in CCY1%
Public Function OptionVega(isCall As Boolean, S As Double, K As Double,T As Double, rCCY1 As Double, rCCY2 As Double, v As Double,Vol_Flex As Double) As Double
    Dim OptionPriceUp As Double, OptionPriceDw As Double
    OptionPriceUp = OptionPrice(isCall, S, K, T, rCCY1, rCCY2,     v + Vol_Flex)
    OptionPriceDw = OptionPrice(isCall, S, K, T, rCCY1, rCCY2,     v - Vol_Flex)
    OptionVega = 0.01 * ((OptionPriceUp - OptionPriceDw) /     (Vol_Flex * 2)) / S
End Function 

Test that if S = K = 1.0, T = 1.0, σ = 10%, and rCCY1 = rCCY2 = 0%, the option delta is close to 50%, and vega is a shade under 0.40%.

Task D: Plot Exposures

The price, delta, and vega VBA functions that have been developed can be used to generate tables and charts of option prices or Greeks over different spots, interest rates, implied volatility levels, or time to expiry. These profiles are key to risk managing a portfolio of vanilla options:

Interesting exposures to plot are:

• Delta versus spot: The gradient of this chart gives gamma, plus try extreme rCCY1 and rCCY2 values.
• Vega versus spot: Note the location of the vega peak, plus try extreme rCCY1 and rCCY2 values.
• Vega versus time to expiry: Look at the formula for vega and confirm the relationship.
• Option value versus volatility: Try this for strikes close to spot and strikes further away from spot.