Ｅｘｃｅｌ“模拟运算表”工具在长期贷款分析中的应用

杨桂梅

摘要:在信息化的时代，人们越来越意识到利用信息化工具在提高工作效率、创造更大效益等方面所发挥的重要作用。本文写作的目的仅是抛砖引玉，希望越来越多的人们都能利用现代化的工具为其所用，更好地工作和生活。

关键词:Excel的两种“模拟运算表”;长期贷款分析;应用

目前，无论是企业还是个人都有可能需要长期贷款。进行长期贷款理性的分析是必要的。长期贷款分析主要是对贷款的金额、贷款的利率、贷款的期限和归还期限等因素进行分析。我们经常要观察某一个因素变动或某两个因素的变动对其他因素的影响，Excel就提供了“模拟运算表”工具来解决这类问题。

“模拟运算表”就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。

一、单变量模拟运算表

单变量模拟运算表就是基于输入一个变量，用它来测试对公式计算结果的影响。

创建单变量模拟运算表，所设计的运算表应当是：其输入数值被排列在一列中或一行中。使用的公式必须引用输入单元格（输入单元格：在该单元格中，源于数据表的输入值将被替换。工作表中的任何单元格都可用作输入单元格。尽管输入单元格不必是数据表的一部分，但是数据表中的公式必须引用输入单元格）。如果数据表是列方向的，在第一个数值的上一行且位于数值列右边的单元格中键入公式。如果数据表是行方向的，在第一个数值左边一列且位于数值行下方的单元格中键入公式。

例1：某企业向银行贷款10000元，期限5年，用“模拟运算表”工具来测试不同的利率对月还款额的影响，步骤如下：

1.设计单变量模拟运算表结构，利率设置为列变量，所以设置的公式单元格B7是位于变量值所在列的右侧，并且高于第一个变量单元格A8一行。左侧单元格A7无任何作用。如图1所示。

2.在公式单元格B7中计算利率为4%时月还款额，输入公式“B7=PMT(B5/12,B4,B1)”。

3.选取包括公式和需要进行模拟运算的单元格区域A7:B16。

4.单击“数据”菜单，选择“模拟运算表”项，弹出“模拟运算表”对话框，如图2所示。

5.由于本例中引用的是列数据，故在“输入引用列的单元格”中输入“$B$5”。单击“确定”按钮，即得到单变量模拟运算结果表，如图3所示。

二、双变量模拟运算表

双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响。

用一个简单的算式z=3x+4y+1来看：要求当x等于从1到4间的所有整数，而y为1到7间所有整数时所有z的值，用模拟运算表做：首先排好x与y的位置，然后在下面的单元格中建立一个公式，在公式所在行的右边和下面分别输入两个变量的变化值，这里我们在行上为x，列上为y，然后选中这个方形的区域，选择“数据”菜单中的“模拟运算表”命令，打开“模拟运算表”对话框，将“输入引用行的单元格”选择为公式中x的数值所在单元格，“输入引用列的单元格”选择为公式中y的数值所在的单元格。如图4所示。

单击“确定”按钮，就可以看到运算的结果了。如果公式发生了改变，只要修改公式单元格中的公式就可以了，模拟运算表的排列位置及计算结果如图5所示。

例2：某企业打算向银行申请长期借款进行项目投资，财务人员考虑了两种还款方式想从中选择其一。基本条件是：用6年时间，借款金额在100万元—500万元，利率在5%—12%之间，采用到期一次还本付息还款方式或采用每年等额还本付息方式。分析哪种还款方式最为有利？用“双变量模拟运算表”工具来测试不同还款方式对还款额的影响，步骤如下：

1. 设计双变量模拟运算表结构，如图6所示。

2.在单元格D4中计算借款金额为100万元，借款期限为6年，借款利率为5%的条件下到期一次还本付息的金额，输入公式“D4=FV(C4,B4,,A4)”。

3.选取包括公式和需要进行模拟运算的单元格区域D4:I12。

4.单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，在【输入引用行的单元格】中输入“$A$4”，在【输入引用列的单元格】中输入“$C$4”，如图7所示。

5.单击【确定】按钮，即得到到期一次还本付息双变量模拟运算表的结果，如图8所示。

6.按照上述（2）到（5）的步骤进行每年等额还本付息双变量模拟的运算，计算结果如图9所示。

7.从图9中可以看到，将借款200万元，利率为7%，还款期为6年的到期一次还本付息和每年等额还本付息还款方式相比较，前者6年还款总额为300万元，后者6年还款总额为252万元，由此得出每年等额还本付息比到期一次还本付息还款方式要少负债很多。

例3：林某准备按揭贷款买房，他手中有资金150000元，其收入为每月3600元。现在他想选择一套125平方米，单价为4000元/平方米的房子，假设首付款为房款的30%，贷款年利率为5%，准备20年还完。现在对以下两种情况进行购房分析：

第一，如果房子的价格、还款年限不变，林某只能每月支付2000元的贷款，用单变量模拟运算表确定林某能购买的合适的住房面积。

第二，如果房子的价格不变，而面积和贷款的还款年限可以变动，用双变量模拟运算表确定合适的面积和贷款还款年限。

分析如下：

（1）建立单变量模拟运算表如图10所示，在B7单元格中使用函数B7=PMT(B5/12,B6*12,B2*B3-B4)计算出每月应付款数额。再选中A7：B21，使用“模拟运算表”，计算出不同面积每月应付款数额。从计算结果分析：林某每月支付2000元的贷款，只能选择面积为105平方米及以下的房子。

（2）建立双变量模拟运算表如图11所示，在B7单元格中使用函数B7=PMT(B5/12,B6*12,B2*B3-B4)计算出每月应付款数额。再选中B7：M21，使用“双变量模拟运算表”，计算出不同面积、不同年限的每月应付款数额。从计算结果分析：林某每月支付2000元的贷款，可以选择面积和年限为表中有底色显示的房子。