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 = Ran ge("D5")
For i = 1 To n
Set rr1 = Union(Range("D" & (5 + i)), rr1) Next
rr1.Select
With Selecti on
.Horiz on talAlig nment = xlGe
neral .VerticalAlig nment =
xlBottom .WrapText = False
.Orie ntati on = 0
.Addl ndent = False
」nden tLevel = 0
.Shri nkToFit = False .Readi ngOrder =
xlC on text .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)) A 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 A (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 The n
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 The n
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
效果如下:
A B C D E F G H I J K L 1股累现,价屍50期数0 123456
2执行价格1T52. Q8时间f年｝0 0. 083338a 1666670.250. 3333330,4166670.5
3年无风险利率0. 04上行乘数 1.124606
4监析■瀬动率C际准差｝0. 4068下行乘數0. 8892
5劃期时间｛梦少个月〉650 56. 2303263. 2369771.116779. 9782980. 94409101.1517
6划分朗数644.465056. 2303263. 2369771. 116779.97929
739. 5338444. 465056. 2303263.23657
8脸票忻格35.15349的.533S4也4650
g31.2594835, 1534939.53384 1027, 7950531.25348 1124. 71586
12执行桥格52. 08
13上行祉率0. 4B4835 140. 515165
15 5. 294956 3. 51705113. 261S419. S35G28. 243763B. 0371149.07169
16 2.296B164. 1067137.16050112. 050S19. 2097227.69329
170. 608334 1.258906 2.605Z25-39132211.15697
13买入期釈价格0000 19000 2000
210
22
23
24 1 11
25。