(仅供参考)Excel VBA 金融工程 Section 2 - 期权定价

梦特卡罗期权价格计算

欧洲看涨不派息股票期权

我会将之前介绍到技巧应用在不派息的欧洲看涨期权上。分别是控制変量方法CMcEuropeanCall()程序- 请参考源代码OP02，对偶変量法AMcEuropeanCall()程序-请参考源代码OP03及原模拟McEuropeanCall()程序- 请参考源代码OP04，。

当分别输入资料{S0, K, r, σ, T, n}，而经由读取程序readData() -请参考源代码OP01后。而readData()程序会分别将资料放到以上三个不同方法的程序里，最后结果会输回其期权现价及标准误差到readData()程序。在全部程序里，随机抽样数ɛ会由StdNormNum()函数产生，并由此根据(FOP.16)带出抽様的到期价格S T(ɛ)。就对偶変量法，在相同的随机数上加上负数即可S T(-ɛ)。相同的数值在不同函数，引用不同公式(FOP.18), (FOP.19) 及(FOP.21)里运作后评估。根据(FOP.2),其均值及方差的现值可将每个抽祥数分别加起来及将其倍乘即可,其可以一个小回路程序由1到n项。可以更简单的将(FOP.2)的方差计算用以下方式表达

S2=1

n+1∑g

n

i=1

2(x

i

)-n

n−1

m2(FOP.25)

此两个流程都可以在估算期权现价时得出其均值。在控制变量法，一定要时刻记者将S0包括在均值估算上当为控制变量(FOP.20)的解决方法。在评估过程里的标准误差可以根据在方差计算时的附加因子1/√n所找出。

Sub readData()

Dim assetPrice As Double: assetPrice = Cells(2, 2)

Dim strike As Double: strike = Cells(3, 2)

Dim maturity As Double: maturity = Cells(4, 2)

Dim riskFree As Double: riskFree = Cells(5, 2)

Dim sigma As Double: sigma = Cells(6, 2)

Dim nsample As Long: nsample = Cells(7, 2)

Dim optionPrice As Double

Dim stdEr As Double

seed = 5678

Call McEuropeanCall(assetPrice, strike, riskFree, sigma, maturity, nsample, optionPrice, stdEr)

Cells(9, 2) = optionPrice

Cells(10, 2) = stdEr

Call CMcEuropeanCall(assetPrice, strike, riskFree, sigma, maturity, nsample, optionPrice, stdEr)

Cells(9, 3) = optionPrice

Cells(10, 3) = stdEr

Call AMcEuropeanCall(assetPrice, strike, riskFree, sigma, maturity, nsample, optionPrice, stdEr)

Cells(9, 4) = optionPrice

Cells(10, 4) = stdEr

End Sub

源代码OP01: 读取基本期权资料程序

Sub CMcEuropeanCall(assetPrice As Double, strike As Double, riskFree As Double, sigma As Double, maturity As Double, nsample As Long, ByRef optionPrice As Double, ByRef stdEr As Double)

Dim sum01 As Double, sum02 As Double, Vnum As Double

Dim mean As Double, Vd As Double, Vs As Long

Dim sT As Double, fT As Double, pV As Double

sum01 = 0

sum02 = 0

For Vs = 1 To nsample

Vnum = StdNormNum()

sT = assetPrice * Exp((riskFree - 0.5 * sigma ^ 2) * maturity + sigma * Sqr(maturity) * Vnum)

fT = CallPayoff(strike, sT) - sT

pV = Exp(-riskFree * maturity) * fT

sum01 = sum01 + pV

sum02 = sum02 + pV * pV

Next Vs

mean = sum01 / nsample

Vd = Sqr(sum02 / (nsample - 1) - (nsample / (nsample - 1)) * mean ^ 2)

optionPrice = assetPrice + mean

stdEr = Vd / Sqr(nsample)

End Sub

源代码OP02: 欧州看涨期权控制変量法梦特卡罗模拟程序

Sub AMcEuropeanCall(assetPrice As Double, strike As Double, riskFree As Double, sigma As Double, maturity As Double, nsample As Long, ByRef optionPrice As Double, ByRef stdEr As Double)

Dim sum01 As Double, sum02 As Double, Vnum As Double

Dim mean As Double, Vd As Double, Vs As Long

Dim sT As Double, sTa As Double, fT As Double, pV As Double

sum01 = 0

sum02 = 0

For Vs = 1 To nsample

Vnum = StdNormNum()

sT = assetPrice * Exp((riskFree - 0.5 * sigma ^ 2) * maturity + sigma * Sqr(maturity) * Vnum)

sTa = assetPrice * Exp((riskFree - 0.5 * sigma ^ 2) * maturity + sigma * Sqr(maturity) * (-Vnum))

fT = (CallPayoff(strike, sT) + CallPayoff(strike, sTa)) / 2

pV = Exp(-riskFree * maturity) * fT

sum01 = sum01 + pV

sum02 = sum02 + pV * pV

Next Vs

mean = sum01 / nsample

Vd = Sqr(sum02 / (nsample - 1) - (nsample / (nsample - 1)) * mean ^ 2)

optionPrice = mean

stdEr = Vd / Sqr(nsample)

End Sub

源代码OP03: 欧州看涨期权对偶変量法梦特卡罗模拟程序

Sub McEuropeanCall(assetPrice As Double, strike As Double, riskFree As Double, sigma As Double, maturity As Double, nsample As Long, ByRef optionPrice As Double, ByRef stdEr As Double)

Dim sum01 As Double, sum02 As Double, Vnum As Double

Dim mean As Double, Vd As Double, Vs As Long

Dim sT As Double, fT As Double, pV As Double

sum01 = 0

sum02 = 0

For Vs = 1 To nsample

Vnum = StdNormNum()

sT = assetPrice * Exp((riskFree - 0.5 * sigma ^ 2) * maturity + sigma * Sqr(maturity) * Vnum)

fT = CallPayoff(strike, sT)

pV = Exp(-riskFree * maturity) * fT

sum01 = sum01 + pV

sum02 = sum02 + pV * pV

Next Vs

mean = sum01 / nsample

Vd = Sqr(sum02 / (nsample - 1) - (nsample / (nsample - 1)) * mean ^ 2)

optionPrice = mean

stdEr = Vd / Sqr(nsample)

End Sub

源代码OP04: 欧州看涨期权原梦特卡罗模拟程序

Public seed As Long

Function StdNormNum() As Double

Dim v1 As Double, v2 As Double, w As Double, fac As Double

Dim snnUse As Double

Static flagSave As Integer: If IsEmpty(flagSave) Then flagSave = 0

Static snnSave As Double

If (flagSave = 0) Then

NewTrial:

v1 = 2# * ran0() - 1#

v2 = 2# * ran0() - 1#

w = v1 ^ 2 + v2 ^ 2

If (w >= 1#) Then GoTo NewTrial

fac = Sqr(-2# * Log(w) / w)

snnSave = fac * v1

snnUse = fac * v2

flagSave = 1

Else

snnUse = snnSave

flagSave = 0

End If

StdNormNum = snnUse

End Function