第5章 制作贷款模拟运算表

5.4 加速工作表的运算速度
在EXCEL中，可以通过设置加快包括模拟运算数据在内的 工作表的计算速度，减少重新计算的时间，提高工作效率。 途径如下：工具-选项-重新计算选项卡，在“计算”选项中 单击“除模拟运算表外，自动重算”单选按钮，如下图所示。 完成该设置，则下次更新若不涉及模拟运算表中的数据，这 个设置就会起作用。
从计算结果可以看到，使用双变量模 拟运算表进行计算之后的结果也保存在数 组中，但它们不是以常量的形式存在的， 是以二维区域数组形式出现的。单击E3： J18单元格区域中任一单元格，则在编辑 栏中出现{“=表(B3,B4)”}，其中“B3”为 引用行变量的单元格地址，“B4”为引用 列变量的单元格地址。 在双变量模拟运算表中输入公式，必 须输入到包含两组输入值的行和列相交的 单元格中。
步骤1 先插入一新工作表 “Sheet4”，再选定“双变量模拟 运算表”中的计算结果区域E3： J18，右键单击选定区域，从弹出的 快捷菜单中单击“复制”命令。然 后在工作表“Sheet4”中选择要粘 贴到的位置，右键单击，再从弹出 的快捷菜单中单击“选择性粘贴” 命令，打开如图所示的“选择性粘 贴”对话框，从对话框的“粘贴” 选项组选中“数值”单选按钮，其 余保持系统默认设置。
步骤3：在“图表选项”界面，设置“图表标题”为贷款偿 还，分类（X）轴为利率，数值(Y)轴设置为“月偿还金额”， 其他默认。
若对设计图表不满意，可选中图表中任意空白区 域，右击，快捷菜单中选择“图表选项”等对图 表进行操作。还可对图表的源数据、图表类型等 进行更改。 如果不需要模拟运算表，或者需要对其中数据进 行重新设置时，需对模拟运算表进行清除操作。 由于其中数据时数组类型，故不能单独删除个别 结果，而只能对整个数组区域进行操作。选中单 元格，编辑-删除-内容。或者右击-清除内容。
5.7使用模板方案
Excel中为用户提供了多种电子方案表格模板，包 括报价单、报销单、考勤记录、投资收益测算器 等，如果要使用这些模板，步骤如下： 文件-新建命令，打开“新工作簿”窗格，单击窗 口右边“本机上的模板”链接，打开“模板”对 话框后选择“电子方案表格”选项卡，再选择所 需模板即可。如图所示。如果本机没有合适的模 板，则可通过“office online 模板”下载合适的模 板来进行使用。
步骤4 在“单变量求解”对话框中的“目标单元格” 文本框中输入“可接受月偿还金额”对应的单元格， 按“F4”键将其转换为单元格绝对地址，在“目标值” 文本框中输入可承受的月偿还预期金额“10000”，在 “可变单元格”文本框中输入“可接受利率”对应的 单元格$B$6，如图所示。
步骤5 单击“确定”按钮，弹出“单变量求解状态” 对话框，求得一个解，如左图所示。 步骤6 单击“确定”按钮，返回工作表，则数据工 作表中对应单元格中就出现了求解结果，然后再将 该工作表重命名为“逆算利率”，如右图所示。
附：小数点快速输入（自动定位）
此操作使用范围为整篇文档，当需输入小数点时 可以设置，不需要时需将此设置去掉方能输入正 确的数据。如果自己输入的数据已自带小数，则 不受此规则限制。意义上是将所有单元格中的数 据调整为原数据的的10-n 大小。如果需录入小数 点后数据位数不同，则此规定无效，同样需自己 手动输入。 方法：工具-选项-编辑，选中“自动设置小数点” 复选框，再设置“位数”微调框中需要显示的小 数点后面的位数即可。设置完成之后，在某个单 位中输入某个整数，回车，则该单元格中的数字 自动变为相应的小数。
5.1 单变量模拟运算表
由于公司业务发展，需要购置新设备，而购 置的资金需要￥800000元，准备用银行贷款买 下设备，然后在今后10年中按月进行分期偿还。 公司领导应知道在不同银行利率下，每个月公 司需要偿还银行的贷款金额是多少。 （此公式 适用的为等额本息的还法，即每个月的所还利息 和本金的总额是固定不变的。如果采用等额本金 的还法，则每个月的本金不变，利息变。）
5.5 将双变量模拟运算表转化为直观的图表 步骤1：设置图表类型，选中需显示的数据区域，即E4:J19 单元格。选择“插入图表”命令，打开“图表向导”对话框， 选择“图表类型”下拉列表中的折线图，如下图所示。
步骤2：设置“图表数据源”对话框，先在“数据区域”选项卡， 设置系列产生在“列”，再切换至“系列”选项卡，选择“系列” 下拉列表中的“系列1”，再将其名称改为“8年”，“值”对话框 选中表中的8年对应的还款额所在单元格区域，在“分类（X）轴标 志”对话框中选中8年对应的利率所在单元格区域。
步骤1 创建运算公式。选定单元格D3，在其中输 入公式：=PMT（C3/12，B3*12，-A3），按 “Enter”回车键确认公式输入并计算出“每月偿还” 的数值。如图所示。（图中百分数数据可通过单元 格格式-数据-百分数-2位小数进行设置，则输入时 只需输入数据即可得到百分数。）
步骤2 将输入公式“=PMT（C3/12，B3*12，-A3）” 中的“B3”和“A3”相对地址转换为“$B$3”和“$A$3” 绝对地址，即输入公式“=PMT（C3/12，$B$3*12，$A$3）”，用填充柄填充“每月偿还”列数据，完成 效果如图所示。
在创建单变量模拟运算表时要使用财务函数PMT（）。PMT（）函 数是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款 额。 语法为：PMT(rate，nper，pv，fv，type) Rate：贷款利率。 Nper：总投资或贷款期，即该项投资或贷款的付款总数。 Pv：当前值，或一系列未来付款的当前值的累积和，也称为本金。 如果此处为贷款值，则为负数。 Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省 略 fv，则假设其值为零，也就是一笔贷款的未来值为零。 Type：数字 0 或 1，0指期末，1指期初，用以指定各期的付款时间 是在期初还是期末。默认值为0。
“单变量求解”是一组命令的组成 部分，这些命令有时也称作假设分析工 具。如果已知单个公式的预期结果，而 用于确定此公式结果的输入值未知，则 可使用“单变量求解”功能。当进行单 变量求解时，Microsoft Excel 会不断 改变特定单元格中的值，直到依赖于此 单元格的公式返回所需的结果为止。
5.3 双变量模拟运算表的运用
步骤1 创建工作表。在“Sheet3”工作表中输对应数据，并 将其工作表标签重命名为“双变量模拟运算表”，如左图所 示。 步骤2 创建计算公式。选择E3单元格，在其中输入公式：
=PMT（B4/12，B3*12，-B2），如右图所示。
步骤3 选定E3：J18单元格区域，然后再选择 “数据”|“模拟运算表”命令，如图所示。
模拟运算表实质上只是为简化某 些数值变化对最终结果的影响而建立 的一个数据表。单变量模拟运算表中 包含一个可变化的数值，如“单变量 模拟”表中的“利率”。而在实际的 银行贷款中，年限也是一个可变量， 当把利率与年限均作为变量对对应的 月偿还金额进行查看时，就需要建立 双变量模拟运算表来实现。
5.3.1 创建“逆算利率”工作表
第5章 制作企业贷款模拟运算表
模拟运算表是一个单元格区域，它可以显 示一个或者多个公式中替换不同值时的结 果，主要用来考察一个或者两个重要决策 变量的变动对于分析结果的影响。单变量 模拟运算表中，用户可以对一个变量输入 不同值来查看该变量对一个或多个公式的 影响。双变量模拟运算表中，用户可以对 两个变量输入不同值来查看它们对公式的 影响。
5.1.2 运用财务函数
财务函数可以进行一般的财务计算，如确定贷 款额支付额、投资的未来值或者净值，以及 债券或者息票的价值。这些财务函数大体上 可分为4类：投资计算函数、折旧计算函数、 偿还率计算函数、债券及其他金融函数。它 们为财务分析提供了极大的白努力。常见参 数包括如下几个：未来值（fv）期间数 （nper）付款（pmt）现值（pv）利率 （rate）类型 （type）日计数基准类型 （basis）
已知还款能力求还款期限
使用函数nper(rate,pmt,pv,[fv],[type]),基于 固定利率及等额分期付款方式，返回某项 投资的总期数。
5.2 利用单变量求解逆算利率
利用Excel中的单变量求解可对利率进行逆运 算，即根据企业的偿还能力来计算其能够承受的银 行贷款利率。 根据企业自身的情况，假设企业可负担的每月 贷款偿还金额为￥10000，则从图5-5的计算结果 中发现企业可接受的银行利率为8.05% ~9.00%， 超出此范围，企业财务就会出现危机。 为更好使用贷款，在Excel 2003中可使用单变 量求解功能实现公司在贷款期限上的选择。
单元格引用有3种方式：相对引用、绝对引用和混合引用。 相对引用：公式中的相对单元格引用（例如 C3）是基于包 含公式和单元格引用的单元格的相对位置。如果公式所在 单元格的位置改变，引用也随之改变。如果多行或多列地 复制公式，引用会自动调整。默认情况下，新公式使用相 对引用。 绝对引用：单元格中的绝对单元格引用（例如 $B$3）总 是在指定位置引用单元格。如果公式所在单元格的位置改 变，绝对引用保持不变。如果多行或多列地复制公式，绝 对引用将不作调整。默认情况下，新公式使用相对引用， 需要将它们转换为绝对引用。 相对引用和绝对引用的转换：在编辑栏中，选择公式或需 要转换成绝对地址的部分，按下F4键，即可将选中内容自 动进行相对地址和绝对地址的转换了，可多按几次F4键， 实现不同的地址转换效果。
步骤2 设置数据格式。将标题 行“贷款偿还模拟运算表”合 并居中，“本金”列和“每月 偿还”列设置为货币型，并保 留2位小数，“利率”列设置 为“百分比”型，并将 “Sheet1”工作表标签重命名 为“单变量模拟”，如图所示。 完成设置后以“企业贷款模拟 运算.xls”为文件名保存在指定 位置。
步骤3 将工作表“Sheet4”标签名重命名为“数 据常量”。 步骤4 选择“格式”|“工作表”|“隐藏”命令， 将“数据常量”工作表隐藏起来，此时，在工 作表标签位置上，“数据常量”工作表被隐藏 了。 若要显示“数据常量”工作表，则再选择选 择“格式”|“工作表”|“取消隐藏”命令，从弹 出的“取消隐藏”对话框“取消隐藏工作表” 列表框中选要取消隐藏的工作表即可。
5.3.2 将模拟运算结果转换为常量
Excel 2003提供了两类数组：区域数 组和常量数组。区域数组主要是用来存储 使用同一公式的数据，且这些数据都放置 在工作表的一个矩形域中；常量数组用来 存储一组用于某一公式参量的常量。 使用模拟运算表进行计算之后的结果 有时并不是用常量形式存在的，若用户需 要，可以将这些运算结果转换为常量数组 保存起来。
步骤4 在弹出的“模拟运算表”对话框中，在 “输入引用行的单元格”文本框中输入“年限” 变量所在的单元格地址“$B$3”，在“输入引用 列的单元格”文本框输入“利率”变量所在的单 元格地址“$B$4”，如图所示。引用的单元格地 址必须是绝对引用。
步骤5 单击“确定”按钮，返回工作表，即可得到将 利率和年限同时为变量的贷款偿还模拟运算表的计算 结果，并将其的数据格式设置为“货币型”，小数点 保留2位。如图所示。
ห้องสมุดไป่ตู้
步骤1 切换至“Sheet2”工作表，输入相关数 据，并设置数据格式，并将工作表标签重命名 为“逆算利率”，如图所示。
步骤2 选定单元格B7，在其中输入公式：=PMT （B6/12，B4*12，-B3），按“Enter”回车键确认公 式输入。 步骤3 选择“工具”|“单变量求解”命令，如图所示， 弹出“单变量求解”对话框。
5.1.1 创建单变量模拟运算表
根据规定，单变量模拟运算表必须包括输入值和 相应的结果值，运算表的输入值要在一列或一行中。 若输入值在一行，则称为行引用；若输入值在一列， 则称为列引用。（使用单变量模拟运算表，意味着表 中只有一列数据在发生变化，在此处即只有银行利率 不同。） 步骤1 创建工作表。建立基本的运算工作表，输入数 据，如图所示。