VBA code for "A Course in Derivative Securities"

0关注
0粉丝

硕士

已卖：1150份资源

硕士生

48%

还不是VIP/贵宾

-

0%

威望: 0 级
论坛币: 134480 个
通用积分: 1.6600
学术水平: 3 点
热心指数: 3 点
信用等级: 2 点
经验: 1085 点
帖子: 57
精华: 0
在线时间: 140 小时
注册时间: 2009-8-4
最后登录: 2017-6-15

楼主

wangdaji 发表于 2010-3-4 09:52:35 |AI写论文

是否 +2 论坛币

k人参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

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