股票期权二叉树定价 excel VBA程序
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) = 0For i = 1 To n
Worksheets(1).Cells(1, 5 + i) = iNext
'权权;年,
Worksheets(1).Cells(2, 5) = 0For 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) = pWorksheets(1).Cells(n + 8, 5 + n) = 1 - p'权入期权价格
'最后一期的期权价权
For i = 1 To n + 1
q = Worksheets(1).Cells(4 + i, 5 + n) - xIf q > 0 Then
Worksheets(1).Cells(n + 8 + i, 5 + n) = qElse
Worksheets(1).Cells(n + 8 + i, 5 + n) = 0End 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) = 0End If
Next
Next
End Sub
效果如下,