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

股票期权二叉树定价-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 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 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" & (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 . 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 nWorksheets( 1).Cells(1, 5 + i) = iNext 时间（年）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 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 To 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效果如 下：