实训五财务函数的使用——制作存贷款预算表.pdf

实训五财务函数的使用——制作存贷款预算表一、情景描述良好的理财手段是保证生活质量的便捷途径，存贷款及投资预算和方案选择能帮助我们合理地理财不论我们是贷款，还是参加银行的零存整取或是投资，Excel 中提供的财务函数能帮助我们快速计算出按照既定方案需要付出的代价或是可以获取的回报在这个实训中， 首先我们需要学习如何根据既定方案计算简单的贷款代价和存款（投资）回报然后我们就可以调整方案的细节（如存贷款年限、考虑利率的变化有等因素），决定我们的理财方案下图是根据每月存款额、年利率及存款期限计算出的最终存款额：图 4-5-1 存款计算表1 根据每月存款额、年利率、存款期限及期望值计算出的现值存入数额：图 4-5-2 存款计算表2 根据每月存款额、年利率、存款期限计算出的税后本息总额：图 4-5-3 存款计算表3根据贷款总额、年利率及贷款期限计算出的每月还款额：图 4-5-4 还款计算表1 根据一项已经购买了的保险年金的月底回报、投资收益率、投资年限计算出的该份保险年金的价值，然后与购买金额对比看是否合算：图 4-5-5 投资预算表当每月存款额与存款期限固定不变时，不同的年利率对应的存款总额：图 4-5-6 零存整取单变量模拟运算表当存款期限为10年不变，不同每月存款额及年利率对应的存款总额：图 4-5-7 零存整取双变量模拟运算表当贷款总额与贷款期限固定时不同的年利率对应的月偿还额，或是贷款总额与年利率固定时不同的贷款年限对应的月偿还额，或是贷款年限10 年不变时不同的贷款总额及年利率对应的月偿还额：图 4-5-8 贷款单变量及双变量模拟运算表二、任务分析为了顺利完成本次工作任务，我们除了需要巩固复习函数的基本编辑与使用等基本知识外，还需要具备以下知识。

对基本存贷款及投资方式的了解及相应的计算方法财务函数 FV、PV 及 PMT 的使用单变量模拟运算的计算方法双变量模拟运算的计算方法三、任务分解将本次工作任务分为以下5 个子任务子任务 1 存款预算子任务 2 贷款预算子任务 3 投资预算子任务 4 存款方案选择子任务 5 贷款方案选择四、任务指导打开 EXLX05.xls 文件，在相应工作表中根据存贷款方案完成预算1、存款预算：（1）某人每月存款3000 元，共累计存款20 年（ 240 个月），已知存款利率为2.50% ，计算最终存款额，结果添加货币符号“ ￥” 并保留 2 位小数步骤 1：在 C6 单元格中插入FV 函数，参数如下图所示：图 4-5-9 存款计算表1 的 FV 函数的相关参数及结果注意条件中的每月存款额应为负值，否则计算结果将为负值步骤 2：单击结果单元格，选择“ 格式 ”—“单元格 ” 命令，在 “ 数字 ” 选项卡中单击 “ 货币 ”分类，设置小数位数为22）某人预算在10 年后使存款数额达到300000 元，现在每月存入2000 元，年利率为4.125% ，计算其开始应存入银行的现值数额，结果添加货币符号“ ￥ ” 并保留 2 位小数。

提示： 在 C14 单元格中插入PV 函数，参数如下图所示：图 4-5-10 存款计算表2 的 PV 函数的相关参数及结果设置货币类型与小数位数的方法与上相同，不再赘述3）某人参加银行的零存整取储蓄，每月存入1500 元，年利率为3% ，请在相应单元格用函数公式计算出2年期满后的减除利息税后的本息总和,设置单元格格式例如为：￥1500.22注 :利息税为6%, 必须使用公式,取 2 位小数位数）步骤 1： 在 C18：C20 单元格分别输入相应的参数3%、24、-1500；步骤 2： 在 C21 单元格输入函数，参加下列提示：提示： 税后本息总和 =本息总和 -利息税利息税 =利息 * 利率利息 =本息总和 -本金即：税后本息总和=本息总和 -（本息总和 -本金） *利率根据上述公式，在C21 单元格输入如下所示的公式即可：=FV(C18/12,C19,C20)-(FV(C18/12,C19,C20)-C19*(-C20))*6% 注意公式中的本金计算，引用C20 单元格内容应添加负号2、贷款预算某人向银行贷款100000 元，贷款年限为10 年，已知贷款年利率为8% ，在 C6 单元格中利用函数计算出“每月应还款”，结果保留2 位小数。

提示： 在 C6 单元格中输入PMT 函数，参数如下图所示：图 4-5-11 贷款计算表的PMT 函数的相关参数及结果3、投资预算假设要购买一项保险年金，该保险可以在今后20 年内，每月末回报￥600.此项年金的购买成本为80000，假定投资回报率为8% 该项投资合算吗？提示： 在 C7 单元格输入PV 函数： =PV(C4/12,C5*12,C3) 计算结果为￥-71,732.58 即总收益率为￥ -71,732.58，而购买成本为80000 元，因此该项投资不合算4、存款方案选择（1）已知一项零存整取方案的每月存款额为1500 元，存款期限为120 个月，请根据年利率的不同计算得到的存款总额步骤 1：在 C7 单元格中输入FV 函数： =FV(B7/12,C5,-C4) ；步骤 2：选择 B7：C11 单元格区域，选择“ 数据 ”—“ 模拟运算表 ” 命令，在 “ 输入引用列单元格 ” 后面输入框中单击，选择B7 单元格，单击确定结果如图4-5-6 所示2）已知一项零存整取方案的存款期限为120 个月，请根据年利率的不同和月存款额的不同计算得到的存款总额步骤 1：在 B15 单元格输入FV 函数： =FV(B22/12,120,G15) ；步骤 2：选择 B15：F21 单元格区域，选择“ 数据 ”—“模拟运算表 ” 命令，在 “ 输入引用行单元格 ” 后面输入框中单击，选择G15 单元格，单击确定；在“ 输入引用列单元格” 后面输入框中单击，选择B22 单元格，单击确定。

结果如图4-5-7 所示5、贷款方案选择（1）已知一项贷款方案的贷款额为100000 元，贷款期限为120 个月，请根据年利率的不同计算月还款额步骤 1：在 C7 单元格中输入FV 函数： =PMT(B7/12,C5,C4) ；步骤 2：选择 B7：C11 单元格区域，选择“ 数据 ”—“ 模拟运算表 ” 命令，在 “ 输入引用列单元格 ” 后面输入框中单击，选择C7 单元格，单击确定结果如图4-5-8 左上所示2）已知一项贷款方案的贷款金额为6000000 元，年利率为6% ，请根据贷款期限的不同计算月偿还额步骤 1：在 F7 单元格输入PMT 函数： =PMT(F5/12,E7*12,F4) ；步骤 2：选择 E7：F11 单元格区域，选择“ 数据 ”—“ 模拟运算表 ” 命令，在 “ 输入引用列单元格 ” 后面输入框中单击，选择F7 单元格，单击确定结果如图4-5-8 右上所示3）已知一项贷款方案的贷款期限为120 个月，请根据贷款额的不同和年利率的不同计算月偿还额步骤 1：在 B15 单元格输入PMT 函数： =PMT(G15/12,120,B23) ；步骤 2：选择 B15：F22 单元格区域，选择“ 数据 ”—“ 模拟运算表 ” 命令，在 “ 输入引用行单元格 ” 后面输入框中单击，选择 G15 单元格， 在“ 输入引用列单元格” 后面输入框中单击，选择 B23 单元格，单击确定。

结果如图4-5-8 下所示五、实训小结在本次实训中我们将制作存贷款预算表这项工作分解为5 个子任务， 通过先行学习如何根据既定方案计算简单的贷款代价和存款及投资回报，然后通过调整方案的细节（如存贷款年限、考虑利率的变化有等因素），决定我们的理财方案，完成了本次任务通过这次实训， 巩固复习了公式和函数的基本编辑及货币类数字的格式设置等知识，同时也学习了三大财务函数FV、PMT 及 PV 的使用、单变量及双变量模拟运算等新知识，对理财方面的基本常识也增进了了解六、课后拓展练习仿照这次实训的内容，作以下尝试：为你自己或者你宿舍的同学建立零存整取预算表，了解一下当下的1 年或 2 年的零存整取利率，把每个月的零花钱适当的存起来，看看最后的你的存折上能有多少钱？看看你身边是否有人准备或已经贷款买房，根据他们的贷款总额与贷款期限，及当下的贷款利率为他们计算一下他们每月需要偿还银行多少钱？假如你需要为你的父母购买一份保险，去了解一下保险的投资回报率及购买成本，根据预定的每月回报金额，计算一下购买该份保险是否合算？。