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

-精选财经经济类资料- -最新财经经济资料-感谢阅读- 1 双变量模拟运算表在筹资模型中的 应用及其注意事项 摘 要：筹资是一项重要的财务 管理活动，充分高效率的获取资金对个 人和企业来说都是至关重要的。本文通 过案例说明利用 Excel 双变量模拟运算 表建立个人房J 筹资决策模型，以及 如何防范在建立模型的过程中容易出现 的问题，以提高筹资决策效率。 中国论文网 /3/view-12942441.htm 关键词：筹资模型；双变量模拟 运算表；等额本息还款 一、引言 随着我国房贷款利率的不断调整， 越来越多的消费者在购买房屋时优先考 虑贷款购房，但如何根据自己的实际情 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 2 况来选择合理的贷款方案是困扰大家的 难题。首先，大家应该清楚目前的还款 方式主要有等额本息还款和等额本金还 款两种，而对于不准备提前偿还的贷款 的人来说，会首先选择等额本息还款方 式，本文就是针对这种还款方式下进行 的贷款决策。本文借助 Excel 提供的 PMT 函数及双变量模拟运算表来进行 房贷决策，具有一定实用价值。 二、PMT （）函数简介 PMT（）是 Excel 提供的在固定 利率下，计算贷款的等额分期偿还额。 其参数构成为 PMT（rate，nper ，pv，fv ，type ） 。Rate 表示期利率，当年利率为 6%时，如果 按年偿还，则期利率为 6%；如果按月 偿还，则期利率为 6%/12。Nper 表示总 付款期，如一笔 20 年的贷款，按年付 款总期数为 100 年；按月付款，则总基 数为 10*12。 Pv 表示现值，即目前的贷款总 额。Fv 表示付清贷款后的未来值，因 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 3 为我们通常计算现在贷款，每期偿还金 额，而很少用现在的贷款未来值来计算 每期偿还金额，所以此参数通常省略， 缺省值为 0。Type 表示年金类型，先付 年金为 1，后会年金为 0，缺省值为 0。 此函数需要注意的是，现金的流 入用正数表示，现金流出用负数表示； 并且 Pv 和 Fv 两个参数是必选其一，当 然也可两者都选。 三、双变量模拟运算表简介 模型 y=f（x1，x2 ，x3， xn-1，xn）中， 一共有 n 个自变量，模拟运算表就是假 定在其余自变量固定不变，仅一个或者 两个自变量在一定范围内非连续变动， 在假设一个自变量有 m 个取值，另一个 自变量有 n 个取值，则函数 y 有 mn 个计算结果。双变量模拟运算表可在计 算公式固定的情况下，快速求出参数值 的变化对计算结果的影响，并将所有计 算结果同时显示在一个表中，通过列表 的方式直观显示，便于查看和比较。 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 4 一个自变量的变化对目标函数的 影响分析，是单变量模拟运算表，两个 自变量变化对目标函数的影响分析，是 双变量模拟运算表。 四、案例 假设某人想通过向银行借款购房， 房产总价 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 元。因为考虑到自己的收入相 对稳定，不会提前偿还贷款，他计划采 用等额本息还款法，按月还本付息，请 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 5 给出其可选择的贷款方案。 五、筹资基本模型建立 新建计算机财务管理工作薄，在 sheet1 工作表上建立借款筹资决策模型， 如图 1。其中，左侧的基本数据区显示 目前借款的不同类型及对应的年利率。 首先，选择“ 视图” 菜单下的子菜单 “工 具栏”，打开 “窗体”对话框，单元格 F4 的位置插入“ 组合框” 窗体， F7 单元格的 右侧插入“微调项 ”，在“组合框”位置单 击右键，选择“ 设置控件格式 ”，在微调 按钮位置，位置单击右键，选择“设置 控件格式”。 通过这样设置“ 组合框 ”，就达到 了可以在下拉选项中选择所需要的借款 类型，并且在 D4 单元格显示所选中的 类型在数据源区域处于第几行，比如我 们选择的是 5 年以下公积金借款，对应 的 D4 单元格返回数据 1。 “微调项 ”被这样设置后， F7 单元 格中借款年限不用手工输入，只要点击 微调按钮就自动增减一年，借款年限限 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 6 定在 130 之间，但同时要注意，年限 范围应与借款利率相匹配。 通过窗体的使用，大大提高输入 效率，同时避免了手工输入的失误。F7 单元格需要根据用户选择的借款类型和 借款年限自动显示借款年利率。 在 F6 单元格输入函数如下： =INDEX（C5：C9，D4） ，可以实现借 款利率随着借款类型的变化而发生相应 的变化，比如：5 年以下公积金借款其 利率为 3.75%。 在单元格 F8 中输入函数如下： =ABS（PMT（F6/12，F7*12，F5， ） ） ， 参数中利率为月利率，总期数为对应的 月份数，可以计算等额本息还款方式下， 每个月的还款额，用 ABS（）函数求出 正值使整个页面看起来整齐。 基本模型设计结束，每月偿还金 额与上面的因素形成了动态链接，可以 更改模型中的任意一个数据或多个数据， 包括通过下拉框选择借款类型，随之而 变动的借款年率，可以通过微调按钮更 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 7 改借款年限，也可以根据所需要借款的 金额来输入相应的借款本金，这些数据 的变化都会使每个月的还款额发生变化， 使决策快速化，用户可以根据自己的实 际能力选择筹资方式。 六、利用双变量模拟运算表设计 筹资决策模型 首先，在 D13：H14 区域内输入 对应的借款类型和借款年限。在 D15：H15 区域中输入公式，比如 D15 中输入=C5，通过公式的使用，可以实 现数据的动态链接，当基本数据区的数 据发生变化时，此区域的数据随之变化。 其次，在 C16：C45 区域中通过 输入序列的方式输入借款年限 1-30 年。 再次，在此表的交叉单元格 C15 中输入函数 =ABS（PMT（F6/12，F7*12，F5） ） ， 表示每个月应偿还的金额，使用 ABS（）函数，是为了把负数调整为正 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 8 数，使整个界面看起来清晰。 最后，选择整张要放入模拟运算表内容 的单元格区域 C15：H45，从“数据”菜 单下选择“模拟运算表 ”，出现如图 5 所 示的模拟运算表对话框，在“输入引用 行的单元格” 中选择$F$6，在“输入引用 列的单元格” 中选择$F$7。表示是用表 中的第 15 行中各种可能的利率数据来 替换 F6 的值，用表中 C 列各种可能的 年限数据来替换 F7 的值，点确定，此 时双变量模拟运算表操作就已完成。此 时要注意输入引用的行和输入引用的列 所要替换的单元格不要选反。 因为在表中表示利率的第 15 行 中的利率是与基本数据形成动态链接的， 所以当政策变更使利率发生变化时，只 要更改基本数据区的对应利率即可。模 拟运算表完成之后，当交叉单元格中公 式中引用的数据发生变化时，模拟运算 表中的数据也会随之变化，但有些数据 只影响交叉单元格的数据变化（比如： 基本模型中借款利率和借款年限的变化） -精选财经经济类资料- -最新财经经济资料-感谢阅读- 9 ，而有些数据的变化则影响整张模拟表 中的数据（比如基本数据区的借款利率、 基本模型区的借款本金的变化） 。 决策者可以根据已经做好的双变 量模拟运算表来进行筹资决策，依据个 人能否进行公积金借款，进行借款方式 的选择，再依据个人每个月的偿还能力 选择合适的偿还金额范围，选择偿还金 额所对应的借款年限。 七、使用双变量模拟运算表要注 意的几个问题 （1）运算表中的所有结果都是 一样的数据。因为你在使用模拟运算表 中输入引用的行或输入引用的列时，没 有使用交叉单元格式公式所使用的单元 格地址。 （2）运算表中的年限不用使用 年来显示，而是折算成月来显示。这是 因为在基本模型区设置了总付款期数， 在输入引用的列时用的是总付款期这一 单元格，所以，只能用此列中的数据替 换总付款期数。 -精选财经经济类资料- -最新财经经济资料-感谢阅读- 10 （3）基本数据区的内容很多， 把模拟运算表放入不同的工作