股票期权二叉树定价-excel-VBA程序.docx

《股票期权二叉树定价-excel-VBA程序.docx》由会员分享，可在线阅读，更多相关《股票期权二叉树定价-excel-VBA程序.docx（6页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、股票期权二叉树定价-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

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 T

3、o nSet rr1 = Union( Range(D & (5 + i), rr1)Nextrr1.SelectWith Selection . HorizontalAlignment = xlGeneral . VerticalAlignment = xlBottom . WrapText = False .Orientation = 0 . AddIndent = False . IndentLevel = 0 . ShrinkToFit = False . ReadingOrder = xlContext . MergeCells = True End With 设置格式 居中With

4、 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 nSet rr2 = Union( Range(D

5、 & (n + 9 + i), rr2)Nextrr2.SelectWith Selection . HorizontalAlignment = xlGeneral . VerticalAlignment = xlBottom . WrapText = False .Orientation = 0 . AddIndent = False . IndentLevel = 0 . ShrinkToFit = False . ReadingOrder = xlContext . MergeCells = True End With 设置格式 居中With Selection . Horizontal

6、Alignment = 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 nWorksheets( 1).Cells(1, 5 + i) = iNext 时间

7、（年）Worksheets( 1).Cells(2, 5) = 0For i = 1 To nWorksheets( 1).Cells(2, 5 + i) = t / (12 * n) * iNext 上行乘数u = Exp (s * (t / (12 * n) 0.5)Worksheets( 1).Cells(3, 5) = u 下行乘数d = 1 / uWorksheets( 1).Cells(4, 5) = d 股票价格For i = 1 To n + 1Worksheets( 1).Cells(4 + i, 4 + i) = 50 * d (i - 1)NextFor i = 1 To

8、 nFor j = i To nWorksheets( 1).Cells(4 + i, 5 + j) = Worksheets(1).Cells(4 + i, 4 + j) * uNextNext 执行价格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 T

9、o n + 1q = Worksheets( 1).Cells(4 + i, 5 + n) - xIf q 0 ThenWorksheets( 1).Cells(n + 8 + i, 5 + n) = qElseWorksheets( 1).Cells(n + 8 + i, 5 + n) = 0End IfNext 由后往前推各期的价值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 ThenWorksheets( 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)ElseWorksheets( 1).Cells(n + 8 + jj , 4 + ii) = 0End IfNextNextEnd Sub效果如 下：