双变量模拟运算表在筹资模型中的应用及其注意事项

摘 要：筹资是一项重要的财务管理活动，充分高效率的获取资金对个人和企业来说都是至关重要的。本文通过案例说明利用Excel双变量模拟运算表建立个人房貸筹资决策模型，以及如何防范在建立模型的过程中容易出现的问题，以提高筹资决策效率。

关键词：筹资模型；双变量模拟运算表；等额本息还款

一、引言

随着我国房贷款利率的不断调整，越来越多的消费者在购买房屋时优先考虑贷款购房，但如何根据自己的实际情况来选择合理的贷款方案是困扰大家的难题。首先，大家应该清楚目前的还款方式主要有等额本息还款和等额本金还款两种，而对于不准备提前偿还的贷款的人来说，会首先选择等额本息还款方式，本文就是针对这种还款方式下进行的贷款决策。本文借助Excel提供的PMT函数及双变量模拟运算表来进行房贷决策，具有一定实用价值。

二、PMT（）函数简介

PMT（）是Excel提供的在固定利率下，计算贷款的等额分期偿还额。其参数构成为PMT（rate，nper，pv，fv，type）。Rate表示期利率，当年利率为6%时，如果按年偿还，则期利率为6%；如果按月偿还，则期利率为6%/12。Nper表示总付款期，如一笔20年的贷款，按年付款总期数为100年；按月付款，则总基数为10*12。

Pv表示现值，即目前的贷款总额。Fv表示付清贷款后的未来值，因为我们通常计算现在贷款，每期偿还金额，而很少用现在的贷款未来值来计算每期偿还金额，所以此参数通常省略，缺省值为0。Type表示年金类型，先付年金为1，后会年金为0，缺省值为0。

此函数需要注意的是，现金的流入用正数表示，现金流出用负数表示；并且Pv和Fv两个参数是必选其一，当然也可两者都选。

三、双变量模拟运算表简介

模型y=f（x1，x2，x3，......，xn-1，xn）中，一共有n个自变量，模拟运算表就是假定在其余自变量固定不变，仅一个或者两个自变量在一定范围内非连续变动，在假设一个自变量有m个取值，另一个自变量有n个取值，则函数y有m×n个计算结果。双变量模拟运算表可在计算公式固定的情况下，快速求出参数值的变化对计算结果的影响，并将所有计算结果同时显示在一个表中，通过列表的方式直观显示，便于查看和比较。

一个自变量的变化对目标函数的影响分析，是单变量模拟运算表，两个自变量变化对目标函数的影响分析，是双变量模拟运算表。

四、案例

假设某人想通过向银行借款购房，房产总价125万元，首付20%，即需要借款100万元。他可以选择公积金借款或商业借款，不同的借款方式下依据年限的长短不同，利率也有所不同，2015年最新的数据是：公积金借款年限5年以下（含5年）的年利率为3.75%，5年～30年的年利率为4.25%；商业借款年限1年以内（含1年）的年利率为5.60%，1年～5年（含5年）的年利率为6.00%，5年～30年的年利率为6.15%。

由于收入的限制，此人每月还款额最高不能超过8000元，但也不想低于6000元。因为考虑到自己的收入相对稳定，不会提前偿还贷款，他计划采用等额本息还款法，按月还本付息，请给出其可选择的贷款方案。

五、筹资基本模型建立

新建计算机财务管理工作薄，在sheet1工作表上建立借款筹资决策模型，如图1。其中，左侧的基本数据区显示目前借款的不同类型及对应的年利率。首先，选择“视图”菜单下的子菜单“工具栏”，打开“窗体”对话框，单元格F4的位置插入“组合框”窗体，F7单元格的右侧插入“微调项”，在“组合框”位置单击右键，选择“设置控件格式”，在微调按钮位置，位置单击右键，选择“设置控件格式”。

通过这样设置“组合框”，就达到了可以在下拉选项中选择所需要的借款类型，并且在D4单元格显示所选中的类型在数据源区域处于第几行，比如我们选择的是5年以下公积金借款，对应的D4单元格返回数据1。

“微调项”被这样设置后，F7单元格中借款年限不用手工输入，只要点击微调按钮就自动增减一年，借款年限限定在1～30之间，但同时要注意，年限范围应与借款利率相匹配。

通过窗体的使用，大大提高输入效率，同时避免了手工输入的失误。F7单元格需要根据用户选择的借款类型和借款年限自动显示借款年利率。

在F6单元格输入函数如下：=INDEX（C5：C9，D4），可以实现借款利率随着借款类型的变化而发生相应的变化，比如：5年以下公积金借款其利率为3.75%。

在单元格F8中输入函数如下：=ABS（PMT（F6/12，F7*12，F5，）），参数中利率为月利率，总期数为对应的月份数，可以计算等额本息还款方式下，每个月的还款额，用ABS（）函数求出正值使整个页面看起来整齐。

基本模型设计结束，每月偿还金额与上面的因素形成了动态链接，可以更改模型中的任意一个数据或多个数据，包括通过下拉框选择借款类型，随之而变动的借款年率，可以通过微调按钮更改借款年限，也可以根据所需要借款的金额来输入相应的借款本金，这些数据的变化都会使每个月的还款额发生变化，使决策快速化，用户可以根据自己的实际能力选择筹资方式。

六、利用双变量模拟运算表设计筹资决策模型

首先，在D13：H14区域内输入对应的借款类型和借款年限。在D15：H15区域中输入公式，比如D15中输入=C5，通过公式的使用，可以实现数据的动态链接，当基本数据区的数据发生变化时，此区域的数据随之变化。

其次，在C16：C45区域中通过输入序列的方式输入借款年限1-30年。

再次，在此表的交叉单元格C15中输入函数=ABS（PMT（F6/12，F7*12，F5）），表示每个月应偿还的金额，使用ABS（）函数，是为了把负数调整为正数，使整个界面看起来清晰。

最后，选择整张要放入模拟运算表内容的单元格区域C15：H45，从“数据”菜单下选择“模拟运算表”，出现如图5所示的模拟运算表对话框，在“输入引用行的单元格”中选择$F$6，在“输入引用列的单元格”中选择$F$7。表示是用表中的第15行中各种可能的利率数据来替换F6的值，用表中C列各种可能的年限数据来替换F7的值，点确定，此时双变量模拟运算表操作就已完成。此时要注意输入引用的行和输入引用的列所要替换的单元格不要选反。

因为在表中表示利率的第15行中的利率是与基本数据形成动态链接的，所以当政策变更使利率发生变化时，只要更改基本数据区的对应利率即可。模拟运算表完成之后，当交叉单元格中公式中引用的数据发生变化时，模拟运算表中的数据也会随之变化，但有些数据只影响交叉单元格的数据变化（比如：基本模型中借款利率和借款年限的变化），而有些数据的变化则影响整张模拟表中的数据（比如基本数据区的借款利率、基本模型区的借款本金的变化）。

决策者可以根据已经做好的双变量模拟运算表来进行筹资决策，依据个人能否进行公积金借款，进行借款方式的选择，再依据个人每个月的偿还能力选择合适的偿还金额范围，选择偿还金额所对应的借款年限。

七、使用双变量模拟运算表要注意的几个问题

（1）运算表中的所有结果都是一样的数据。因为你在使用模拟运算表中输入引用的行或输入引用的列时，没有使用交叉单元格式公式所使用的单元格地址。

（2）运算表中的年限不用使用年来显示，而是折算成月来显示。这是因为在基本模型区设置了总付款期数，在输入引用的列时用的是总付款期这一单元格，所以，只能用此列中的数据替换总付款期数。

（3）基本数据区的内容很多，把模拟运算表放入不同的工作表中来显示。在这种情况下，如果更改基本數据区中的其他因素，模拟运算表并不会相应发生变化。这是因为模拟运算表中输入引用的行或输入引用的列的单元格是不可以在不同的工作表中的，只能引用同一个工作表中的单元格。

（4）在修改模拟运算表时，经常出现“不能更改模拟运算表的某一部分”的警告语，而且让你无法继续操作，这时应该是处于编辑状态，只需要点中公式编辑左侧的×号，取消修改即可。

参考文献：

[1]蒋秀莲，宋言东等.利用excel双变量模拟运算表进行购房贷款决策[J].会计之友，2007（6）上.

[2]张瑞君.计算机财务管理[M].北京：中国人民大学出版社，2007年.

[3]谷增军.excel模拟运算表在财务分析中的应用[J].财会月刊，2010（1）.

作者简介：陈艳杰（1975.07- ），女，江苏徐州人，作者单位：徐州工程学院，讲师，研究方向：财务管理