股票期权二叉树定价excelVBA程序.docx

Sub 期权定价()

Dim i As Long

'将输入的参数的值赋给相应的变量

s0 = Worksheets(1).Cells(1, 2)
x = Worksheets(1).Cells(2, 2)
r = Worksheets(1).Cells(3, 2)
s = Worksheets(1).Cells(4, 2)
t = Worksheets(1).Cells(5, 2)
n = Worksheets(1).Cells(6, 2)

'生成表格

Worksheets(1).Cells(1, 4) = "期数"
Worksheets(1).Cells(2, 4) = "时间（年）"
Worksheets(1).Cells(3, 4) = "上行乘数"
Worksheets(1).Cells(4, 4) = "下行乘数"
Worksheets(1).Cells(5, 4) = "股票价格"
Worksheets(1).Cells(n + 6, 4) = "执行价格"
Worksheets(1).Cells(n + 7, 4) = "上行概率"
Worksheets(1).Cells(n + 8, 4) = "下行概率"
Worksheets(1).Cells(n + 9, 4) = "买入期权价格"

'合并相应单元格
Set rr1 = Range("D5")
For i = 1 To n
Set rr1 = Union(Range("D" & (5 + i)), rr1)
Next
rr1.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

'设置格式 居中
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

'合并相应单元格
Set rr2 = Range("D" & (n + 9))
For i = 1 To n
Set rr2 = Union(Range("D" & (n + 9 + i)), rr2)
Next
rr2.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

'设置格式 居中
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

'计算表格相应内容
'期数
Worksheets(1).Cells(1, 5) = 0
For i = 1 To n
Worksheets(1).Cells(1, 5 + i) = i
Next

'时间（年）
Worksheets(1).Cells(2, 5) = 0
For i = 1 To n
Worksheets(1).Cells(2, 5 + i) = t / (12 * n) * i
Next

'上行乘数
u = Exp(s * (t / (12 * n)) ^ 0.5)
Worksheets(1).Cells(3, 5) = u

'下行乘数
d = 1 / u
Worksheets(1).Cells(4, 5) = d

'股票价格
For i = 1 To n + 1
Worksheets(1).Cells(4 + i, 4 + i) = 50 * d ^ (i - 1)
Next

For i = 1 To n
For j = i To n
Worksheets(1).Cells(4 + i, 5 + j) = Worksheets(1).Cells(4 + i, 4 + j) * u
Next
Next

'执行价格
Worksheets(1).Cells(n + 6, 5 + n) = x

'上行概率、下行概率
p = ((r * t) / (12 * n) + 1 - d) / (u - d)
Worksheets(1).Cells(n + 7, 5 + n) = p
Worksheets(1).Cells(n + 8, 5 + n) = 1 - p

'买入期权价格
'最后一期的期权价值
For i = 1 To n + 1
q = Worksheets(1).Cells(4 + i, 5 + n) - x
If q > 0 Then
Worksheets(1).Cells(n + 8 + i, 5 + n) = q
Else
Worksheets(1).Cells(n + 8 + i, 5 + n) = 0
End If
Next

'由后往前推各期的价值
For ii = n To 1 Step -1 '列
For jj = 1 To ii '行
If Worksheets(1).Cells(n + 8 + jj, 4 + ii + 1) > 0 Or Worksheets(1).Cells(n + 8 + jj + 1, 4 + ii + 1) > 0 Then
Worksheets(1).Cells(n + 8 + jj, 4 + ii) = (p * Worksheets(1).Cells(n + 8 + jj, 4 + ii + 1) + (1 - p) * Worksheets(1).Cells(n + 8 + jj + 1, 4 + ii + 1)) / (1 + (r / 12) * (t / n))
Else
Worksheets(1).Cells(n + 8 + jj, 4 + ii) = 0
End If
Next
Next

End Sub

效果如下：