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

Sub期权定价()DimiAsLong'将输入的参数的值赋给相应的变量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)=买入期权价格'合并相应单元格Setrr1=Range(D5)Fori=1TonSetrr1=Union(Range(D&(5+i)),rr1)Nextrr1.SelectWithSelection.HorizontalAlignment=xlGeneral.VerticalAlignment=xlBottom.WrapText=False.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWith'设置格式居中WithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=False.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWith'合并相应单元格Setrr2=Range(D&(n+9))Fori=1TonSetrr2=Union(Range(D&(n+9+i)),rr2)Nextrr2.SelectWithSelection.HorizontalAlignment=xlGeneral.VerticalAlignment=xlBottom.WrapText=False.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWith'设置格式居中WithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=False.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWith'计算表格相应内容'期数Worksheets(1).Cells(1,5)=0Fori=1TonWorksheets(1).Cells(1,5+i)=iNext'时间（年）Worksheets(1).Cells(2,5)=0Fori=1TonWorksheets(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'股票价格Fori=1Ton+1Worksheets(1).Cells(4+i,4+i)=50*d^(i-1)NextFori=1TonForj=iTonWorksheets(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'买入期权价格'最后一期的期权价值Fori=1Ton+1q=Worksheets(1).Cells(4+i,5+n)-xIfq0ThenWorksheets(1).Cells(n+8+i,5+n)=qElseWorksheets(1).Cells(n+8+i,5+n)=0EndIfNext'由后往前推各期的价值Forii=nTo1Step-1'列Forjj=1Toii'行IfWorksheets(1).Cells(n+8+jj,4+ii+1)0OrWorksheets(1).Cells(n+8+jj+1,4+ii+1)0ThenWorksheets(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)=0EndIfNextNextEndSub效果如下：