办公自动化技术与应用 教学课件 作者 李岚 第5章 制作贷款模拟运算表.pptVIP

第5章 制作企业贷款模拟运算表 5.1 单变量模拟运算表 由于公司业务发展，需要购置新设备，而购置的资金需要￥800000元，准备用银行贷款买下设备，然后在今后10年中按月进行分期偿还。公司领导应知道在不同银行利率下，每个月公司需要偿还银行的贷款金额是多少。 5.1.1　创建单变量模拟运算表 根据规定，单变量模拟运算表必须包括输入值和相应的结果值，运算表的输入值要在一列或一行中。若输入值在一行，则称为行引用；若输入值在一列，则称为列引用。 步骤1 创建工作表。建立基本的运算工作表，输入数据，如图所示。 步骤2 设置数据格式。将标题行“贷款偿还模拟运算表”合并居中，“本金”列和“每月偿还”列设置为货币型，并保留2位小数，“利率”列设置为“百分比”型，并将“Sheet1”工作表标签重命名为“单变量模拟”，如图所示。完成设置后以“企业贷款模拟运算.xls”为文件名保存在指定位置。 5.1.2　运用财务函数 在创建单变量模拟运算表时要使用财务函数PMT（）。PMT（）函数是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 语法为：PMT(rate，nper，pv，fv，type) Rate：贷款利率。 Nper：总投资或贷款期，即该项投资或贷款的付款总数。 Pv：当前值，或一系列未来付款的当前值的累积和，也称为本金。 Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省略 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种方式：相对引用、绝对引用和混合引用。 相对引用：公式中的相对单元格引用（例如 C3）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。 绝对引用：单元格中的绝对单元格引用（例如 $B$3）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。 相对引用和绝对引用的转换：在编辑栏中，选择公式或需要转换成绝对地址的部分，按下F4键，即可将选中内容自动进行相对地址和绝对地址的转换了，可多按几次F4键，实现不同的地址转换效果。 5.2 利用单变量求解逆算利率 利用Excel中的单变量求解可对利率进行逆运算，即根据企业的偿还能力来计算其能够承受的银行贷款利率。 根据企业自身的情况，假设企业可负担的每月贷款偿还金额为￥10000，则从图6-4的计算结果中发现企业可接受的银行利率为8.05% ~9.00%，超出此范围，企业财务就会出现危机。 为更好使用贷款，在Excel 2003中可使用单变量求解功能实现公司在贷款期限上的选择。 步骤1 切换至“Sheet2”工作表，输入相关数据，并设置数据格式，并将工作表标签重命名为“逆算利率”，如图所示。 步骤2 选定单元格B7，在其中输入公式：=PMT（B6/12，B4*12，-B3），按“Enter”回车键确认公式输入。 步骤3 选择“工具”|“单变量求解”命令，如图所示，弹出“单变量求解”对话框。 步骤4 在“单变量求解”对话框中的“目标单元格”文本框中输入“可接受月偿还金额”对应的单元格，按“F4”键将其转换为单元格绝对地址，在“目标值”文本框中输入可承受的月偿还预期金额“10000”，在“可变单元格”文本框中输入“可接受利率”对应的单元格$B$6，如图所示。 步骤5 单击“确定”按钮，弹出“单变量求解状态”对话框，求得一个解，如左图所示。 步骤6 单击“确定”按钮，返回工作表，则数据工作表中对应单元格中就出现了求解结果，然后再将该工作表重命名为“逆算利率”，如右图所示。 “单变量求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用“单变量求解”功能。当进行单变量求解时，Microsof