长期借款敏感性分析模型代码.doc

Function sjpd() As Boolean If IsNumeric(Cells(4, 2) = False Then MsgBox 请在借款额中输入数字 sjpd = False Exit Function Else If Val(Cells(4, 2) = 0 Then MsgBox 借款金额必须大于零 sjpd = False Exit Function End If End If If IsNumeric(Cells(5, 2) = False Then MsgBox 请在期限中输入整数 sjpd = False Exit Function Else If Val(Cells(5, 2) = 0 Then MsgBox 期限必须大于零 sjpd = False Exit Function End If End If If IsNumeric(Cells(6, 2) = False Then MsgBox 请在利率中输入数字 sjpd = False Exit Function Else If Val(Cells(6, 2) 0 Then Sheet1.ChartObjects.Delete End If Range(B4:B7).ClearContents Range(Cells(4, 2), Cells(6, 2) = 0 Cells(7, 2) = 1 Cells(9, 3) = 0End SubSub jsfx() Dim jzlv As Double Dim bhlv As Double Dim max1 As Double Dim max2 As Double Dim max3 As Double Dim max4 As Double Dim maxx As Double Dim zqs As Integer Dim i As Integer Dim aa As String If sjpd = False Then Exit Sub End If jzlv = Cells(6, 2) bhlv = Cells(9, 2) zqs = Cells(5, 2) * Cells(7, 2) 判断最后一列的字母符号 If zqs = 24 Then aa = Chr(66 + zqs) Else If zqs = 50 Then aa = a & Chr(66 + zqs - 26) Else If zqs = 76 Then aa = b & Chr(66 + zqs - 52) Else If zqs = 102 Then aa = c & Chr(66 + zqs - 78) Else MsgBox (本模型最长还款期数为102期!) End End If End If End If End If 设置表中的期间数 For i = 1 To zqs Cells(12, i + 2) = i Next 设置单元格的对齐方式 Range(Cells(12, 2), Cells(12, zqs + 2).HorizontalAlignment = xlCenter 利用循环语句，计算每期的本金和利息 For i = 1 To zqs Cells(13, i + 2) = =-IPMT(b6/b7, & i & ,b5*b7,b4) Cells(14, i + 2) = =-IPMT(b9/b7, & i & ,b5*b7,b4) Cells(15, i + 2) = =-PPMT(b6/b7, & i & ,b5*b7,b4) Cells(16, i + 2) = =-PPMT(b9/b7, & i & ,b5*b7,b4) Next Range(Cells(13, 3), Cells(16, zqs + 2).NumberFormat = 0.00 max1 = Cells(13, 3) max2 = Cells(14, 3) max3 = Cells(15, zqs + 2) max4 = Cells(16, zqs + 2) maxx = Application.WorksheetFunction.Max(max1, max2, max3, max4) Range(Cells(13, 3), Cells(16, zqs + 2).Select Charts.Add aa = c12: & aa & 16 ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SetSourceData Source:=Sheets(sheet1).Range(aa), PlotBy:=xlRows ActiveChart.SeriesCollection(1).Name = =基准利率情况下的利息 ActiveChart.SeriesCollection(2).Name = =变化利率情况下的利息 ActiveChart.SeriesCollection(3).Name = =基准利率情况下的本金 ActiveChart.SeriesCollection(4).Name = =变化利率情况下的本金 ActiveChart.Location where:=xlLocationAsObject, Name:=sheet1 With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = 期次 .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = 利息或本金 End With ActiveChart.Axes(xlCategory).Select With ActiveChart.Axes(xlCategory) .MinimumScale = 0 .MaximumScale = zqs + 1 .MinorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = 0 .MaximumScale = Int(maxx) .MinorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With Selection.TickLabels.NumberFormatLocal = 0 ActiveChart.SeriesCollection(1).Select Selection.Border.ColorIndex = 0 With Selection .MarkerBackgroundColorIndex = 0 .MarkerForegroundColorIndex = 0 .MarkerStyle = xlDiamond End With ActiveChart.SeriesCollection(2).Select Selection.Border.ColorIndex = 1 With Selection .MarkerBackgroundColorIndex = 1 .MarkerForegroundColorIndex = 1 .MarkerStyle = xlSquare End With ActiveChart.SeriesCollection(3).Select Selection.Border.ColorIndex = 3 With Selection .MarkerBackgroundColorIndex = 3 .MarkerForegroundColorIndex = 3 .MarkerStyle = xlTriangle End With ActiveChart.SeriesCollection(4).Select Selection.Border.ColorIndex = 4 With Selection .MarkerBack