分年本息Excel动态规划滚动条实现

1.Excel借款本息规划函数

Excel提供了进行借款本息的规划函数，主要有分期等额还款本金PPMT、分期等额还款利息IPMT、分期等额还款PMT、普通还款利息ISPMT、阶段累积还款本金Cumprinc、阶段累积还款利息Cumipmt等函数。前三者是最常用的函数，前两者的函数公式如下：

＝PPMT(rate，per，nper，pv，fv，type)

＝IPMT(rate，per，nper，pv，fv，type)

式中，per表示计算利息的期次，介于1和付息总次数nper之间；其他参数的含义见上述分期等额还款函数PMT的说明。

2.Excel数组公式

Excel中可用数组公式来解决大量单元公式计算方法相同的录入。操作方法是，选定计算方法相同的单元区域，即包含使用数组公式的区域，选定后此区域中第1个单元格为活动单元格、其他单元格将反白显示；再键入公式，其公式将只显示于活动单元格中；在该区域仍处于选定状态时，同时按下键盘上的“Ctrl＋Shift＋Enter”组合键，则该公式将填充于所选区域的全部单元格之中。

区域数据录入后，选定其中的任意单元格，在上部编辑框显示的公式中，两端均有大括号“{}”，表示这是数组公式。对于数组公式不能单独修改、删除，只能在选定整个数组区域后进行整体修改、删除。同时按下键盘上的“Ctrl＋Enter”两个组合键，也能将公式填充于所选的单元区域的全部单元格之中，但它们不是数组公式，即公式两端没有大括号。

3.Excel滚动条设计

Excel中可用滚动条按钮、微调项等窗体工具，进行区连续间值的设计。

某公司准备向银行借入5年期的长期借款，借款额为2万～300万元，每年末等额还本付息；由于借款本金影响财务状况、借款利息影响经营成果，公司总经理要求财务部长对这5年借款本息进行规划；经财务部长调查，银行借款的年利率可能在3%～20%之间。请完成这项长期借款还本付息的规划。

在Excel中完成的还本付息规划表如图4-12所示。单击B2单元格中滚动条的左右箭头，则C2单元格中的借款总额随之增减10 000；单击滚动条中的空白滑槽，则C2单元格中的借款总额随之增减100 000；拖动滚动条中的滑块，则借款总额随拖动的幅度而变动；它们的变动幅度均为20 000～3 000 000。

图4-12　借款还本付息滚动条规划

单击E2单元格中滚动条的左右箭头，则F2单元格中的利率随之增减1%；单击滚动条中的空白滑槽，则利率随之增减2%；变动幅度均为3%～20%。

随借款总额、利率的变化，表中各年还本、付息、剩余本金等也随之变动。

（1）录入除C2、F2、A4至F10单元区域以外的数值、文字，合并A1至F1单元格区域。

（2）设计第1个滚动条及借款总额的代码取值，方法如下：

a.插入滚动条。单击窗体工具（表单控件）的滚动条按钮，鼠标变“＋”状时，在B2单元格中拖动至适当大小后释放鼠标，插入一个滚动条按钮。

b.精确设计滚动条的大小。在滚动条处于编辑状态下，通过拖动其调节柄，可以调整滚动条的大小，但不精确。精确设计方法：右击滚动条，选择“设置控件格式”命令，进入设置控件格式对话框；单击“大小”卡片，如图4-13（a）所示，在其中录入该按钮的高度、宽度值。

图4-13　滚动条大小（a）与控制（b）的设置(www.xing528.com)

c.代码值设计。单击设置控件格式“控制”卡片，如图4-13（b）所示，在最小值中录入“200”；最大值中录入“30 000”；步长中录入“100”；页步长中录入“1 000”；单元格链接中键入“＄B＄2”（绝对引用B2单元格）；勾选“三维阴影”复选框。

单击“确定”按钮后，在B2单元格右下角（或左下角）出现设计代码“200”，它为控件格式设置对话框中的最小值。

单击该滚动条的左右箭头时，B2单元格的值将增加或减少100，这是控件的步长在起作用；单击滚动条的滑槽时，B2单元格的值将增加或减少1 000，这是控件的页步长在起作用；它们的变动范围均为200～30 000，这是控件的最大值与最小值在起作用。

d.在C2单元格录入“＝B2∗100”，以将设计代码值中的最小值、最大值转换为20 000、3 000 000。

e.隐藏设计代码。将B2单元格设置为居中对齐，以将设计代码隐藏于滚动条后部；或在编辑状态下拖长滚动条，将设计代码置于滚动条的后部。（3）设计第2个滚动条及年利率，方法如下：a.设计滚动条。在E2单元格中拖动一个适当的滚动条，右击滚动条，选择“设置控件格式”命令；键入最小值“3”，最大值“20”，步长“1”，页步长“2”，单元格链接“＄E＄2”；勾选“三维阴影”复选框。

b.在F2单元格键入“＝E2/100”，单击格式工具栏中的按钮，从而将滚动条在E2单元格中产生的设计代码转换为用百分数表示。再隐藏E2单元格中的设计代码。

（4）在A4至A10单元区域录入年度数值及文字，其中“0”值表示第1年初。在E4中录入“＝C2”，即第1年初剩余本金为借款总额。

（5）用PMT函数及数组公式定义“各年还本付息总额”，方法如下：

a.选定B5至B9单元格区域，单击插入函数按钮，选择“财务”类中的PMT函数，进入PMT函数参数对话框。

b.在利率参数rate中键入引用的单元格“F2”；期数参数nper中键入引用的单元格“A9”；现值参数pv中键入引用的单元格“-C2”；终值参数fv中键入“0”；时点参数type中键入“0”或省略（期末付款）。

c.按下“Ctrl＋Shift＋Enter”组合键（注意：不要单击“函数参数”对话框的“确定”按钮），工作表的编辑框中将显示其数组公式为“{＝PMT（F2，A9，-C2，0，0）}”，在B5至B9单元区域自动计算出各年相等的还本付息总额（年金）。

（6）用数组公式定义“各年付利息”，方法如下：

a.选定C5至C9单元格区域，单击插入函数按钮，选择“财务”类中的IPMT函数，进入“IPMT函数参数”对话框，如图4-14（a）所示。

图4-14　IPMT函数（a）与PPMT函数（b）对话框

b.在该对话框的利率参数rate中键入引用的单元格“F2”；在计息期次参数per中键入引用的单元格区域“A5：A9”；总期数参数nper中键入引用的单元格“A9”；现值参数pv中键入引用的单元格“-C2”；终值参数fv中键入“0”；时点参数type中键入“0”（期末付款）。

注意：type参数，应单击该对话框右部的滚动条后再录入。

c.同时按下键盘的“Ctrl＋Shift＋Enter”组合键，编辑框中显示的数组公式为“{＝IPMT（F2，A5：A9，A9，-C2，0，0）}”，同时计算出各年付利息额，显示于C5至C9单元区域。

（7）用数组公式定义“各年还本金”。选定D5至D9单元区域，单击插入函数按钮，选择“财务”类中的PPMT函数，进入“PPMT函数参数”对话框；PPMT函数与IPMT函数的参数完全相同，键入的参数也与IPMT完全一致，如图4-14（b）所示。

同时按下“Ctrl＋Shift＋Enter”组合键，工作表编辑框中显示的数组公式为“{＝PPMT（F2，A5：A9，A9，-C2，0，0）}”，同时计算出各年还本额，显示于D5至D9单元区域。

（8）用数组公式定义“剩余本金”。选定E5至E9单元区域；键入“＝”，用鼠标选定E4至E8单元区域；再键入“-”，用鼠标选定D5至D9单元区域；同时按下“Ctrl＋Shift＋Enter”组合键，完成各年剩余本金的计算；其数组公式为“{＝E4：E8-D5：D9}”。

（9）在B10单元格中输入公式“＝SUM（B5：B9）”，自动填充C10、D10单元公式。

也可不用数组公式而用自动填充法键入公式，方法是：

（1）在B5单元格中键入公式“＝PMT（＄F＄2，＄A＄9，-＄C＄2，0，0）”，在C5单元格（利息）键入“＝IPMT（＄F＄2，A5，＄A＄9，-＄C＄2，0，0）”，D5单元格键入“＝PPMT（＄F＄2，A5，＄A＄9，-＄C＄2，0，0）”，E5单元格中键入公式“＝E4-D5”。

（2）选定B5至E5单元区域，将鼠标指针指向E5单元格下部的填充柄，待光标变为“”状时，向下拖动到第9行，Excel将自动填充B6至E9单元区域的公式。