VBA code for <A Course in Derivative Securities>



XLS file
Most useful learning material for entry level quant using excel to model derivatives.

Yep, I admit it's ridiciously expensive, so obviously it's not for ordinary users.





Coding example: charpter5:

Option Explicit
Function European_Call_MC(S, K, r, sigma, q, T, M)
'
' Inputs are S = initial stock price
'            K = strike price
'            r = risk-free rate
'            sigma = volatility
'            q = dividend yield
'            T = time to maturity
'            M = number of simulations
'
' This outputs the row vector (call value, delta method 1, delta method 2)
'
Dim LogS0, drift, SigSqrT, UpChange, DownChange, SumCall, SumCallChange
Dim SumPathwise, LogS, LogSd, LogSu, CallV, CallVu, CallVd, i, Delta1, Delta2
LogS0 = Log(S)
drift = (r - q - 0.5 * sigma * sigma) * T
SigSqrT = sigma * Sqr(T)
UpChange = Log(1.01)
DownChange = Log(0.99)
SumCall = 0
SumCallChange = 0
SumPathwise = 0
For i = 1 To M
    LogS = LogS0 + drift + SigSqrT * RandN()         ' simulated log S(T)
    CallV = Application.Max(0, Exp(LogS) - K)        ' corresponding call value
    SumCall = SumCall + CallV                        ' total of call values
    LogSu = LogS + UpChange                          ' simulated log Su(T)
    CallVu = Application.Max(0, Exp(LogSu) - K)      ' corresponding call value
    LogSd = LogS + DownChange                        ' simulated log Sd(T)
    CallVd = Application.Max(0, Exp(LogSd) - K)      ' corresponding call value
    SumCallChange = SumCallChange + CallVu - CallVd  ' differences of call values
    If Exp(LogS) > K Then
        SumPathwise = SumPathwise + Exp(LogS) / S    ' total for pathwise delta
    End If
Next i
CallV = Exp(-r * T) * SumCall / M                      ' Monte Carlo call
Delta1 = Exp(-r * T) * SumCallChange / (M * 0.02 * S)  ' Monte Carlo delta 1
Delta2 = Exp(-r * T) * SumPathwise / M                 ' Monte Carlo delta 2
European_Call_MC = Array(CallV, Delta1, Delta2)
End Function
Function Eur_Call_GARCH_MC(S, K, r, sigma0, q, T, N, kappa, theta, lambda, M)
'
' Inputs are S = initial stock price
'            K = strike price
'            r = risk-free rate
'            sigma0 = initial volatility
'            q = dividend yield
'            T = time to maturity
'            N = number of time periods
'            kappa = GARCH parameter
'            theta = GARCH parameter
'            lambda = GARCH parameter
'            M = number of simulations
'
' This returns the row vector (call value, standard error).
'
Dim dt, Sqrdt, a, b, c, LogS0, SumCall, SumCallSq, LogS, sigma
Dim Y, CallV, StdError, i, j
dt = T / N
Sqrdt = Sqr(dt)
a = kappa * theta                ' GARCH parameter
b = (1 - kappa) * lambda         ' GARCH parameter
c = (1 - kappa) * (1 - lambda)   ' GARCH parameter
LogS0 = Log(S)                   ' store log stock price
SumCall = 0                      ' initialize running total of call values
SumCallSq = 0                    ' initialize running total of squared values
For i = 1 To M
    LogS = LogS0                 ' initialize log stock price
    sigma = sigma0               ' initialize volatility
    For j = 1 To N               ' generate path of log stock price and volatility
        Y = sigma * RandN()
        LogS = LogS + (r - q - 0.5 * sigma * sigma) * dt + Sqrdt * Y
        sigma = Sqr(a + b * Y ^ 2 + c * sigma ^ 2)  ' update vol
    Next j
    CallV = Application.Max(0, Exp(LogS) - K)       ' date-T option value