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