利用EXCLE建立分期还款明细分析模型1.doc

利用Excel建立分期偿还贷款明细分析模型【摘要】 现实生活中,人们需要根据自己的收入水平来选择贷款方式和规模等,一旦贷款方式选定之后,就迫切需要了解每月该偿还多少贷款,到目前为止累计偿还了多少,还剩下多少贷款要还,这正是本文要解决的问题。【关键词】 Excel;分期偿还贷款;模型会计之友2007年第6期上刊登了蒋秀莲、宋言东等同志的利用Excel双变量模拟运算表进行购房贷款决策(以下简称“蒋文”)一文,笔者认为,该文实用价值颇大,利用 “蒋文”中的模型,根据不同房价、不同期限来选择房贷方式,解决了不同收入层次的人们的房贷选择问题。但是贷款方案一旦确定,人们更想知道的信息是到一定时间为止共偿还了多少贷款,如果提前还贷,还需向银行支付多少贷款。本文在“蒋文”的基础上以案例方式对分期偿还贷款进行了明细分析。一、案例的提出2006年12月1日,甲从中国银行申请住房商业贷款15万元,贷款年限15年,采用等额本息付款方式按月偿还贷款,其间中国银行利率变化了四次:2006年利率为3.5127%,2007年利率为6.156%,2008年利率为6.9977%,2009年利率为4.4254%(注:前三年的利率在基准利率上打九折,2009年的利率在基准利率上打七折)。问:1.每年的月偿还额为多少元?2.至2009年底,甲共偿还多少贷款?其中本金多少?利息多少?如果这时想提前偿还贷款,还需向银行支付多少元?二、模型的建立由于知道现值(150 000元),利率(变动利率)和期限(15年),求每年支付的金额(年金),可以用时间价值函数中的年金函数(PMT)、年金中的本金函数(PPMT)和年金中的利息函数(IPMT)来分别计算月偿还额、月偿还额中的本金和利息,最后再用Excel中的常用计算函数求出累计偿还额、累计偿还本金和利息,以及剩余贷款金额。(一)建立分期偿还贷款分析模型表创建一个新的工作簿,将其命名为“贷款偿还分析表”,在工作簿中选择一工作表,并将该工作表重命名为“分期偿还贷款明细分析表”,在此工作表上建立“分期偿还贷款分析模型”,如表1:各项目的公式设置如下:总付款期数:C8=C6*C7每期偿还金额:C9=ABS(PMT(C5/C7,C8,C4)该模型建立之后,每期偿还金额与贷款金额、贷款年利率、贷款年限、每年还款期数等因素之间建立了动态链接,可以通过直接输入数据的方式改变贷款金额、贷款年利率、贷款年限、每年还款期数中的任意一个或几个因素的值,来观察每期偿还金额的变化,选择一种当前能力所及的固定偿还金额进行贷款。“蒋文”是把贷款金额和利率变化的多种结果综合显示到一张表上,便于决策,而本文讨论的重点不是决策,而是决策后需要了解的相关信息。为了便于贷款额的明细分析,文中只显示一种结果,表1显示的结果就是案例中2006年的月偿还额1 073.26元。如果要求以后年度的月偿还额,得用下面的模型。(二)建立分期偿还贷款双变量分析表在“蒋文”中,由于是对贷款进行决策,所以其选用的双变量为“利率”和“贷款额”。在实际中,一旦决策确定,贷款深受利率和期限的影响,贷款期限的长短可能影响其贷款利率,贷款利率的变动又对贷款分析产生较大的影响。为了观察两个因素不同组合下的每期偿还额,需要借助模拟运算表建立双变量分析表来达到目的,本文选用的双变量为“利率”和“期限”。1.目标函数的输入在行与列的交叉单元A13输入目标函数:= ABS(PMT(C5/C7,C8,C4),设置一个双变量数据表,用13行的各种总付款期数替换第一个变量(行变量C8,即总付款期数)的值,并且用A列的各种年利率替换第二个变量(列变量C5,即年利率)的值,与此对应的每期偿还金额将放在第14行下面,A列右方的单元区域中,如表2:2.双变量分析表值的计算选择A13至F21单元区域,在菜单栏“数据”下选择“模拟运算表”,在“输入引用行的单元格”中输入$C$8,在“输入引用列的单元格”中输入$C$5,确定,即可看到不同利率、不同期限下的月偿还额,如表3所示:通过该模型,可以观察两个因素不同组合下的贷款分析的结果。当贷款各因素的值发生变化时,只需改变第一变量或第二变量所在行和列的值或其他因素的值,系统就会自动重新计算双变量分析表中的所有值。在案例中 ,多种不同利率下15期贷款月偿还额对应的值为第D列,则甲2006年12月应还的贷款额为1 073.26元,2007年每月应还的贷款额为1 278.46元。2008年每月应还的贷款额为1 348.05元,2009年每月应还的贷款额为1 141.78元。如果以后利率有变化,只需调整第A列A17以下单元格的利率则可以观察到不同的月偿还额。此时,案例中的第一个问题已解决。(三)建立分期偿还贷款明细分析表在分期偿还贷款双变量分析表中,可以观察到不同期限和利率组合下的月偿还额,但如果想知道目前还了多少贷款,还剩多少贷款没还清,则需要通过明细分析表来实现。在本案例中,由于中国银行2006年至2009年利率变动了四次,所以在计算每期偿还本金和利息时需分别计算,公式设置如下:1.还款额(第J列)公式的设置2006年每月还款额J3=D14;2007年每月还款额J4=$D$15,利用自动填充功能将公式复制到J5至J15单元格中; 2008年每月还款额J16=$D$16,利用自动填充功能将公式复制到J7至J27单元格中;2009年每月还款额J28=$D$17,利用自动填充功能将公式复制到J29至J39单元格中。2.偿还本金(第K列)公式的设置2006年每月偿还本金K3= -PPMT($A$14/12,I3,180,$C$4);2007年每月偿还本金K4= -PPMT($A$15/12,I4,180,$C$4),利用自动填充功能将公式复制到K5至K15单元格中;2008年每月偿还本金K16= -PPMT($A$16/12,I16,180,$C$4),利用自动填充功能将公式复制到J17至J27单元格中;2009年每月偿还本金K28= -PPMT($A$17/12,I28,180,$C$4),利用自动填充功能将公式复制到J29至J39单元格中。3.偿还利息(第L列)公式的设置2006年每月偿还本金L3= -IPMT($A$14/12,I3,180,$C$4);2007年每月偿还本金L4= -IPMT($A$15/12,I4,180,$C$4),利用自动填充功能将公式复制到L5至L15单元格中;2008年每月偿还本金L16= -IPMT($A$16/12,I16,180,$C$4),利用自动填充功能将公式复制到L17至L27单元格中;2009年每月偿还本金L28= -IPMT($A$17/12,I28,180,$C$4),用“填充柄”将公式复制到L29至L39单元格中。4.剩余贷款金额(第M列)公式的设置2006年12月剩余贷款金额:M3=$C$4-K3;2007年后每月还款后剩余贷款金额:M4=M3-K4,利用自动填充功能将公式复制到M5至M39单元格中;合计:J40=SUM(J3:J39),利用自动填充功能将公式复制到K40至L40单元格中。5.剩余贷款金额(第41行)公式的设置M41=C4-K40,这个金额应该与M39相等。如表4(本表已作优化处理):至此,可以计算出甲在三年多中,累计偿还贷款额为46 292.75元,其中累计偿还本金21 178.76元,累计支付利息25 113.99元,如果在12月底想提前偿还贷款,此时还应支付给银行128 821.24元。三、模型的应用以上三个模型是紧密联系的,前一个模型的数据直接影响后面模型的结果,所以当对贷款进行明细分析时,以上三个模型都会运用到,但是具体运用到各种情况中时操作又有不同。假定在利率变动的情况下,讨论几种情况:一是贷款金额变动,期限不变,比如本文的案例,如果贷款20万元,其它条件不变,这时只要把图表中的贷款金额C4由150 000改成200 000万元即可得到想要的结果;二是贷款金额不变,期限变动,比如本文的案例,如果贷款期限为10年,其它条件不变,则需要改表1和表4,在表1中,把贷款年限C6由15改成10,此时要注意表2不要改,但是取值时要注意的是对应10年的月偿还额,即第C列,而不是本文开头案例中的第D列,由于取值列数有改变,所以在表4中公式需要稍作变动,月偿还额第J列的公式中需要把D改为C,比如J3=C14,J4-J15=$C$14,月偿还本金和月偿还利息中由于年限由15年变成10年,所以需要把第K列和第L列公式的期数180改成120,比如K3=-PPMT($A$14/12,I3,120,$C$4),L3=-IPMT($A$14/12,I3,120,$C$4),这样就可得到想要的结果。一般情况下,选择了贷款后,除了利率会变动外,总的偿还期限不会变化,随着分期偿还期限的增加,只要在第40行下面增加相应的行,利用“填充柄”功能则可扩充表4中的结果,如果利率2009年后再有变化,只需修改表2中A17单元格以下的利率,在表4中修改利率变化月份第J列的公式即可得到想要的结果。四、结束语在当今社会中,投资机会无处不在,如何选择一种合理的贷款结构来筹集资金,进行理性的投资,显得颇为重要。本文的模型在实际中具有很好的实用价值,不仅可以随时了解每期还贷金额里包含的利息额、累计偿还额等相关信息,而且可以随时确定实际利率,通过与投资的预期收益率相比较,据此作出是提前还贷还是另选其它投资项目的决定,从而作出理性的投资决策。