股票期权二叉树定价-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 = 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 希望是本无所谓有，无所谓无的。这正如地上的路；其实地上本没有路，走的人多了，也便成了路。