房贷月供分析表模拟运算结果

1．问题的提出

模拟运算表（Data Tables）：指用于观察和分析在某个（或者某两个）初始变量发生变化时，多个导出值随之变化的具体情况的工具。追踪单个初始变量发生变化后所带来的新影响的运算表，称为单变量模拟运算表；追踪两个变量发生变化后所带来的新影响的运算表，称为双变量模拟运算表。模拟运算表可以在一张表中对比不同的初始数据所带来的不同结果数据的差异，为决策提供更好的依据。

购买房产时，一般的初始条件有“总房价”“首付比例”“贷款期限”“贷款利率”；关键的导出数据包括“首付额”“贷款额”“月供额度”“总还款额”“利息总额”。这些数据都是购房者在决策过程中需要考虑的重要因素。这里，导出数据与初始条件之间的关系为：

首付额＝总房价×首付比例

贷款额＝总房价－首付额

月供额度＝PMT（月贷款利率,贷款月数,贷款额）。PMT( )函数是Excel提供的根据固定利率和固定贷款额计算还款额的函数，参数中“贷款额”应以负值代入。

总还款额＝月供额度×贷款月数

利息总额＝总还款额－贷款额

图17-47　房屋贷款分析表

基于这些初始条件和数据关系，可以得到如图17-47所示的“房屋贷款分析表”（注意：这里的分析只对贷款过程进行了计算，没有包含购房过程中所产生的税费等其他费用）。

根据上述数据关系建立了结果数据和输入数据之间的关系，通常可以利用图17-47所示的分析表，通过改变输入数据来获得不同的结果。但是，我们很快就发现，这个分析表虽然能够动态获得结果数据，却不能反映在某个输入数据不同时，各个结果数据间的对比情况。Excel用于数据分析的模拟运算表正是解决这一问题的实用工具。

2．模拟运算表的建立

模拟数据表分为两个部分：数据模型表和模拟运算表。其中，数据模型表反映了输入数据与结果数据之间的关系，而模拟运算表得出了当某一输入数据（放在可变单元格中的自变量）连续变化时，各输出数据的情况。模拟运算表的布局如图17-48所示。操作步骤为：

图17-48　模拟运算表布局

操作步骤

【Step 1】 建立数据模型。在某一Excel工作表中的适当区域中列出问题涉及的输入数据，并在适当区域（例如下方）列出问题涉及的结果数据，然后用适当的运算公式和函数关系将输入数据和结果数据关联起来。显然，这一步骤就是通过一套数据来建立问题的模型。这里的模型一般就是如何由输入数据获得结果数据。(www.xing528.com)

实 用 技 巧

建立数据模型时，结果数据的各单元格对输入数据的引用最好采用绝对引用。这样，在建立模拟运算表时，即可以自由地将结果数据的单元格复制到对应的单元格中，而不会导致由于位置的变化而造成引用位置的改变。

【Step 2】 设置可变单元格（自变量）。在数据模型区域附近，例如其左侧某列（图中为E列）或者某行，列出某一可以变化的数据，如“数据k”（自变量）；列出（或算出）可变单元格（自变量）按某种规律形成的一系列变化值的序列。

注意：可变单元格（自变量）的变化值序列可以用公式生成，即在下一个单元格输入上一单元格的值再加上一个变动量，然后拖动填充柄填充到各单元格中。例如，在图17-48中的E5单元格中输入“=E4＋变化量”，然后向下填充，最终获得所需变化范围的序列。

【Step 3】 安排模拟运算表。将数据模型中的“结果k”“结果k+1”……的计算结果单元格（例如图17-48中的C9、C10等单元格）复制并粘贴到模拟运算表可变单元格旁边的单元格中，例如图17-48中的F3、G3等单元格。这一操作就是构造数据模型中各个结果随“数据k”变化的二维表。

【Step 4】 生成模拟运算表。选中在【Step 3】中构造出来的整个模拟表框架，例如选中图17-48中的E3:I12区域，单击“数据”选项卡—“预测”组—“模拟分析” 按钮下的“模拟运算表”选项，弹出如图17-49所示的“模拟运算表”对话框。如果在【Step 2】中将可变单元格设置在了某一列中，则在“输入引用列的单元格”输入框中选择或输入数据模型中的可变单元格（自变量）。例如，如果需要观察“数据2”的变化所带来的影响，则如图17-49所示，选择（或输入）“$C$5”；类似地，如果在【Step 2】中将可变单元格设置在某一行中，则在“输入引用行的单元格”中选择或输入数据模型中的可变单元格（自变量）。最后，单击“确定”按钮，即会基于可变单元格的变化，生成各个结果数据的变化情况。

图17-49　确定模拟运算表的引用列

图17-50是按照上述步骤生成的房贷分析模拟运算表。可以看到，这是基于“贷款利率”变动所得到的分析表，其中的“贷款额” 与“贷款利率”无关，所以不会发生变化，而“月还贷额”“总还贷额”和“利息总额”都随“贷款利率”的不同而发生变化。

在实际应用中，有时还可能需要考虑由“首期比例”“贷款期数（月）”的不同所带来的影响。

图17-50　房贷模拟运算表

图17-50所示的分析表以及“首期比例”或“贷款期数（月）”生成的分析表参见本书提供的样例文档“房贷数据表—单变量模拟运算表.xlsx”。

温馨提示

使用模拟运算表分析问题时，首先需要准确地建模，即明确结果数据与输入数据之间的关系。其次，要建立好模拟运算表的结构。最后，需要清楚“可变单元格”是对应到“引用行”还是“引用列”。

用鼠标点击模拟运算表生成数据后的单元格，我们会在编辑框中发现模拟运算表生成数据的单元格实际上就是我们在前面介绍过的“多单元格数组公式”，其公式为“{=TABLE(, C7)}”。

可以看到，模拟分析表是动态的，在改变模型中的任何一个输入量后，模拟运算表的所有单元格都会同步发生变化，从而能更好地支持用户的商业决策。