股票期权二叉树定价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 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效果如下：