办公自动化第5章+制作贷款模拟运算表.ppt

1、第5章 制作企业贷款模拟运算表,5.1 单变量模拟运算表,由于公司业务发展，需要购置新设备，而购置的资金需要￥800000元，准备用银行贷款买下设备，然后在今后10年中按月进行分期偿还。公司领导应知道在不同银行利率下，每个月公司需要偿还银行的贷款金额是多少。,5.1.1创建单变量模拟运算表,根据规定，单变量模拟运算表必须包括输入值和相应的结果值，运算表的输入值要在一列或一行中。若输入值在一行，则称为行引用；若输入值在一列，则称为列引用。 步骤1 创建工作表。建立基本的运算工作表，输入数据，如图所示。,步骤2 设置数据格式。将标题行“贷款偿还模拟运算表”合并居中，“本金”列和“每月偿还”列设置为

2、货币型，并保留2位小数，“利率”列设置为“百分比”型，并将“Sheet1”工作表标签重命名为“单变量模拟”，如图所示。完成设置后以“企业贷款模拟运算.xls”为文件名保存在指定位置。,5.1.2运用财务函数,在创建单变量模拟运算表时要使用财务函数PMT（）。PMT（）函数是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 语法为：PMT(rate，nper，pv，fv，type) Rate：贷款利率。 Nper：总投资或贷款期，即该项投资或贷款的付款总数。 Pv：当前值，或一系列未来付款的当前值的累积和，也称为本金。 Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省略

3、fv，则假设其值为零，也就是一笔贷款的未来值为零。 Type：数字 0 或 1，0指期末，1指期初，用以指定各期的付款时间是在期初还是期末。默认值为0。,步骤1 创建运算公式。选定单元格D3，在其中输入公式：=PMT（C3/12，B3*12，-A3），按“Enter”回车键确认公式输入并计算出“每月偿还”的数值。如图所示。,步骤2 将输入公式“=PMT（C3/12，B3*12，-A3）”中的“B3”和“A3”相对地址转换为“$B$3”和“$A$3”绝对地址，即输入公式“=PMT（C3/12，$B$3*12，-$A$3）”，用填充柄填充“每月偿还”列数据，完成效果如图所示。,单元格引用有3种方式

4、：相对引用、绝对引用和混合引用。 相对引用：公式中的相对单元格引用（例如 C3）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。 绝对引用：单元格中的绝对单元格引用（例如 $B$3）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。 相对引用和绝对引用的转换：在编辑栏中，选择公式或需要转换成绝对地址的部分，按下F4键，即可将选中内容自动进行相对地址和绝

5、对地址的转换了，可多按几次F4键，实现不同的地址转换效果。,5.2 利用单变量求解逆算利率,利用Excel中的单变量求解可对利率进行逆运算，即根据企业的偿还能力来计算其能够承受的银行贷款利率。 根据企业自身的情况，假设企业可负担的每月贷款偿还金额为￥10000，则从图6-4的计算结果中发现企业可接受的银行利率为8.05% 9.00%，超出此范围，企业财务就会出现危机。 为更好使用贷款，在Excel 2003中可使用单变量求解功能实现公司在贷款期限上的选择。,步骤1 切换至“Sheet2”工作表，输入相关数据，并设置数据格式，并将工作表标签重命名为“逆算利率”，如图所示。,步骤2 选定单元格B7

6、，在其中输入公式：=PMT（B6/12，B4*12，-B3），按“Enter”回车键确认公式输入。 步骤3 选择“工具”|“单变量求解”命令，如图所示，弹出“单变量求解”对话框。,步骤4 在“单变量求解”对话框中的“目标单元格”文本框中输入“可接受月偿还金额”对应的单元格，按“F4”键将其转换为单元格绝对地址，在“目标值”文本框中输入可承受的月偿还预期金额“10000”，在“可变单元格”文本框中输入“可接受利率”对应的单元格$B$6，如图所示。,步骤5 单击“确定”按钮，弹出“单变量求解状态”对话框，求得一个解，如左图所示。 步骤6 单击“确定”按钮，返回工作表，则数据工作表中对应单元格中就出

7、现了求解结果，然后再将该工作表重命名为“逆算利率”，如右图所示。,“单变量求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用“单变量求解”功能。当进行单变量求解时，Microsoft Excel 会不断改变特定单元格中的值，直到依赖于此单元格的公式返回所需的结果为止。,5.3 双变量模拟运算表的运用,模拟运算表实质上只是为简化某些数值变化对最终结果的影响而建立的一个数据表。单变量模拟运算表中包含一个可变化的数值，如“单变量模拟”表中的“利率”。而在实际的银行贷款中，年限也是一个可变量，当把利率与年限均作为变量对对应

8、的月偿还金额进行查看时，就需要建立双变量模拟运算表来实现。,5.3.1创建“逆算利率”工作表,步骤1 创建工作表。在“Sheet3”工作表中输对应数据，并将其工作表标签重命名为“双变量模拟运算表”，如左图所示。 步骤2 创建计算公式。选择E3单元格，在其中输入公式：=PMT（B4/12，B3*12，-B2），如右图所示。,步骤3 选定E3：J18单元格区域，然后再选择“数据”|“模拟运算表”命令，如图所示。,步骤4 在弹出的“模拟运算表”对话框中，在“输入引用行的单元格”文本框中输入“年限”变量所在的单元格地址“$B$3”，在“输入引用列的单元格”文本框输入“利率”变量所在的单元格地址“$B$

9、4”，如图所示。引用的单元格地址必须是绝对引用。,步骤5 单击“确定”按钮，返回工作表，即可得到将利率和年限同时为变量的贷款偿还模拟运算表的计算结果，并将其的数据格式设置为“贷币型”，小数点保留2位。如图所示。,从计算结果可以看到，使用双变量模拟运算表进行计算之后的结果也保存在数组中，但它们不是以常量的形式存在的，是以二维区域数组形式出现的。单击E3：J18单元格区域中任一单元格，则在编辑栏中出现“=表(B3,B4)”，其中“B3”为引用行变量的单元格地址，“B4”为引用列变量的单元格地址。 在双变量模拟运算表中输入公式，必须输入到包含两组输入值的行和列相交的单元格中。,5.3.2将模拟运算结

10、果转换为常量,Excel 2003提供了两类数组：区域数组和常量数组。区域数组主要是用来存储使用同一公式的数据，且这些数据都放置在工作表的一个矩形域中；常量数组用来存储一组用于某一公式参量的常量。 使用模拟运算表进行计算之后的结果有时并不是用常量形式存在的，若用户需要，可以将这些运算结果转换为常量数组保存起来。,步骤1 先插入一新工作表“Sheet4”，再选定“双变量模拟运算表”中的计算结果区域E3：J18，右键单击选定区域，从弹出的快捷菜单中单击“复制”命令。然后在工作表“Sheet4”中选择要粘贴到的位置，右键单击，再从弹出的快捷菜单中单击“选择性粘贴”命令，打开如图所示的“选择性粘贴”对话框，从对话框的“粘贴”选项组选中“数值”单选按钮，其余保持系统默认设置。,步骤3 将工作表“Sheet4”标签名重命名为“数据常量”。 步骤4 选择“格式”|“工作表”|“隐藏”命令，将“数据常量”工作表隐藏起来，