第10章-贷款购房方案.ppt

1、第10章贷款购房方案,第十章贷款购房方案,案例说明,知识点,案例制作,1,2,3,4,知识拓展,15.1 案例说明,本章将通过一个实例介绍中文版Excel 2003在个人贷款数据处理方面的应用，其中包括PMT函数的使用、模拟运算表 、单变量求解等方面的知识及相关操作。,一、案例分析,日常生活中，人们越来越多地同银行的存贷业务打交道，如住房贷款、汽车贷款、教育贷款及个人储蓄等，但很多人对某一贷款的月偿还金额计算或利息计算往往感到束手无策。 随着银行信贷业务的广泛开展，贷款购房成为多数家庭购房时选择的方案。那么如何根据自己的还款能力制定一个切实可行的购房贷款计划尤为重要。,二、设计思路,怎样制定一

2、个合理的借贷方案呢？ 我们可以利用Excel的PMT函数及双变量模拟运算表做一个购房贷款方案表，它包含在“还款期数”和“贷款本金”两个参数同时变化的情况下“贷款的每期（月）偿还额”，从中我们可以很容易选择出适合自己的一套方案来。还可使用单变量求解根据个人偿还能力来推算出可贷款上限。,10.2 知识点,一、 PMT函数 PMT函数是基于固定利率及等额分期付款方式，返回贷款的每期付款额。 PMT函数的格式为：PMT（rate，nper，pv，fv，type），返回值为“投资或贷款的每期（月）偿还额”。 参数Rate必选，是Double类型，指定每一期的贷款利率。例如，如果有一笔贷款年百分比率（AP

3、R）为百分之十且按月付款，则每一期的利率为0.1/12或0.0083。,参数Nper必选，Integer类型，指定一笔贷款的还款期数。如果对一笔为期四年的汽车贷款选择按月付款，则贷款共有412（或48）个付款期。参数Pv必选，Double类型，现值或一系列未来付款的当前值的累积和，也称为本金。例如，贷款买汽车时，向贷方所借贷的金额为将来每月偿付给贷方的现值。参数Fv可选，是Variant类型，指定在付清贷款后所希望的未来值或现金结存。例如，贷款的未来值在贷款付清后为0元。但是，如果想要在8年间存下50000元作为子女教育基金，那么50000元为未来值。如果省略的话，缺省值为0。参数Type可选

4、，Integer类型，指定各期的付款时间是在期初还是期末，如果贷款是在贷款周期结束时到期，请使用0；如果贷款是在周期开始时到期，则请使用1；如果省略的话，缺省值为0。,所谓模拟运算表是对Excel工作表中的一个单元格区域的数据进行模拟计算，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。根据分析计算公式中参数的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。,二、模拟运算表,方案是一组命令的组成部分，这些命令有时也被称作假设分析工具。 （假设分析：该过程通过更改单元格中的值来查看这些更改对工作表中

5、公式结果的影响。例如，更改分期支付表中的利率可以调整支付金额） 方案是一组由Excel 保存在工作表中并可进行自动替换的值。可以使用方案来预测工作表模型的输出结果，还可以在工作表中创建并保存不同的数值组，然后切换到任何新方案以查看不同的结果。可以为每个方案定义多达32个可变单元格，也就是说对一个模型可以使用多达32个变量来进行模拟分析。,三、方案与方案管理,单变量求解是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决许多财务管理中设计一个变量的求解问题。 例如，假设目前银行的贷款年利率是6%，小豆希望可以在20年内还完所有的贷款，而且目前的偿还能力每月

6、可负担2000元，那么可贷款的上限是多少呢？ 利用单变量求解工具进行计算的具体步骤： 1、首先设计计算表格，如图10-1所示。,四、单变量求解,图10-1计算表格,2、在B1中输入公式=PMT(B2/12,B312,B4)，计算每期偿还金额。 3、在Excel的“数据工具”菜单中选择【假设分析】，然后单击【单变量求解】，打开“单变量求解”对话框，如图10-2所示。,图10-2单变量求解对话框,4、在“目标单元格”文本框输入“$B$1”，在“目标值”文本框中输入“-2000”，在“可变单元格”文本框中输入“$B$4”。,5、单击【确定】按钮, 系统自动进行运算，运算完毕后弹出“单变量求解状态”对

7、话框，如图10-3所示，同时计算结果显示在工作表上。,6、按下【确定】，则最终的计算结果如图10-4所示。可见，这样的条件大约可贷27万左右。,表格建立与数据输入,A,模拟运算表的计算,B,方案设计与管理器使用,C,贷款年限选择,D,10.3 案例制作,一、表格建立与数据输入,步骤如下： 1、新建Excel工作簿，打开一张工作表，在B2单元格输入年利率6%，在C2单元格输入借贷期数5年（此单元格将被设置为列变量），在D2单元格输入房价600000（此单元格将被设置为行变量 2、在C5I5区域输入不同房价，在B6B10区域输入不同按揭年数的月份数。 3、在B5单元格建立公式：PMT（B2/12，

8、C2*12，D2），回车确认，即可在B5单元格得到房价60万元5年按揭的月供金额。结果如图10-5所示。,图10-5贷款购房模拟运算表1,二、模拟运算表的计算,步骤如下： 1、选取区域B5I10，建立模拟运算表。选择【数据】【模拟运算表】命令，打开“数据表”对话框。 2、分别指定$D$2为“引用行的单元格”（即行变量），$C$2为“引用列的单元格”（即列变量），单击【确定】按钮，随后，在C6I10区域便显示不同还款期限、不同房价的房屋月供金额，如图10-7所示。例如：F6单元格的数值表示50万元房价、15年按揭的月供金额。,图10-7贷款购房模拟运算表2,三、方案设计与方案管理器的使用,1、创

9、建方案 当建立了完整的数据表后，就可以使用“方案”命令对其进行模拟分析了。其执行步骤如下： (1) 在Excel的【数据工具】菜单中选择【假设分析】，然后单击【方案管理器】。 (2) 按下【添加】按钮。出现一个“添加方案”对话框。 (3) 在“方案名”框中键入方案名。在“可变单元格”框中键入单元格的引用，在这里我们输入“B7:B10”。对保护项进行选择。按下【确定】按钮。就会进入到如图10-8所示的对话框。,图10-8方案变量值对话框,(4) 编辑每个可变单元格的值，在输入过程中要使用【Tab】键在各输入框中进行切换。如果我们再建立附加的方案，可以选择【添加】按钮重新进入到【添加方案】对话框中

10、。 重复输入全部的方案。当输入完所有的方案后，按下【确定】按钮。 (5) 选择【关闭】按钮，完成该项工作。,2、显示方案 设定了各种模拟方案后，就可以随时察看模拟的结果。操作步骤如下： (1) 在【数据工具】菜单中选择【假设分析】，然后单击【方案管理器】，出现“方案管理器”对话框。 (2) 在“方案管理器”列表框中，选定要显示的方案。 (3) 按下【显示】按钮。选定方案中可变单元格的值出现在工作表的可变单元格中，同时工作表重新计算，以反映模拟的结果。 (4) 重复显示其它方案，最后按下【关闭】按钮。,四、 贷款年限选择,Excel中可用单变量求解方式计算最长借贷年限，以方便借贷者选择合适的借贷

11、年限。 例如，假设小豆以6%的年利率向银行借贷600000元，而且他目前的偿还能力每月可负担4000元，那么他最多能借款多少年呢？,先设计计算表，在B1中输入借款额600000，B2中输入年利率6%，B4中任意输入一个正数（比如1），再在B3中输入公式“=PMT(B2/12,B4*12,-B1)”，计算每月的等额偿还额。打开“单变量求解”对话框，在“目标单元格”文本框输入“$B$3”，在“目标值”文本框输入“4000”，在“可变单元格”输入“$B$4”。单击【确定】。则系统自动进行运算，最后结果如图10-9所示。可见，小豆最多可借款23年多。,15.4 知识拓展,单变量与双变量模拟运算比较,方

12、案总结与方案保护,A,B,一、单变量与双变量模拟运算的比较,双变量模拟运算表与单变量模拟运算表最大区别是前者受两个变量的同时影响，而后者仅受一个变量的影响。 创建双变量模拟运算表的步骤与创建单变量模拟运算表的步骤相差不大，如果已经掌握创建单变量模拟运算表的方法，则创建双变量模拟运算表就很简单了。 创建双变量模拟运算表时只是在“模拟运算表”对话框中在“输入引用列的单元格”编辑框中和“输入引用行的单元格”编辑框中均需为单元格键入引用，其他与创建单变量模拟运算表的步骤基本相同。,二、方案总结与方案保护,1、方案总结 （1）在【数据】选项卡上的【数据工具】组中，单击【假设分析】，然后单击【方案管理器】。 （2）单击【摘要】。 （3）单击【方案摘要】或【方案数据透视表】。 （4）在【结果单元格】框中，输入单元格的引用，该单元格为方案用于更改结果的单元格。多个引用需用逗号分隔开,注意：在生成方案总结报告时不一定需要结果单元格，而在生成方案数据透视表时则一定需要！,2、方案保护 在创建方案时在“添加方案”对话框中有一个“保护项”，如图所示。在“保护项