文档介绍：利用Excel建立分期偿还贷款明细分析模型
【摘要】现实生活中,人们需要根据自己的收入水平来选择贷款方式和规模等,一旦贷款方式选定之后,就迫切需要了解每月该偿还多少贷款,到目前为止累计偿还了多少,还剩下多少贷款要还,这正是本文要解决的问题。
【关键词】 Excel;分期偿还贷款;模型
《会计之友》2007年第6期上刊登了蒋秀莲、宋言东等同志的《利用Excel双变量模拟运算表进行购房贷款决策》(以下简称“蒋文”)一文,笔者认为,该文实用价值颇大,利用“蒋文”中的模型,根据不同房价、不同期限来选择房贷方式,解决了不同收入层次的人们的房贷选择问题。但是贷款方案一旦确定,人们更想知道的信息是到一定时间为止共偿还了多少贷款,如果提前还贷,还需向银行支付多少贷款。本文在“蒋文”的基础上以案例方式对分期偿还贷款进行了明细分析。
一、案例的提出
2006年12月1日,甲从中国银行申请住房商业贷款15万元,贷款年限15年,采用等额本息付款方式按月偿还贷款,其间中国银行利率变化了四次:%,%,%,%(注:前三年的利率在基准利率上打九折,2009年的利率在基准利率上打七折)。问:
?
,甲共偿还多少贷款?其中本金多少?利息多少?如果这时想提前偿还贷款,还需向银行支付多少元?
二、模型的建立
由于知道现值(150 000元),利率(变动利率)和期限(15年),求每年支付的金额(年金),可以用时间价值函数中的年金函数(PMT)、年金中的本金函数(PPMT)和年金中的利息函数(IPMT)来分别计算月偿还额、月偿还额中的本金和利息,最后再用Excel中的常用计算函数求出累计偿还额、累计偿还本金和利息,以及剩余贷款金额。
(一)建立分期偿还贷款分析模型表
创建一个新的工作簿,将其命名为“贷款偿还分析表”,在工作簿中选择一工作表,并将该工作表重命名为
“分期偿还贷款明细分析表”,在此工作表上建立“分期偿还贷款分析模型”,如表1:
各项目的公式设置如下:
总付款期数:C8=C6*C7
每期偿还金额:C9==ABS(PMT(C5/C7,C8,C4))
该模型建立之后,每期偿还金额与贷款金额、贷款年利率、贷款年限、每年还款期数等因素之间建立了动态链接,可以通过直接输入数据的方式改变贷款金额、贷款年利率、贷款年限、每年还款期数中的任意一个或几个因素的值,来观察每期偿还金额的变化,选择一种当前能力所及的固定偿还金额进行贷款。“蒋文”是把贷款金额和利率变化的多种结果综合显示到一张表上,便于决策,而本文讨论的重点不是决策,而是决策后需要了解的相关信息。为了便于贷款额的明细分析,文中只显示一种结果,表1显示的结果就是案例中2006年的月偿还额1 。如果要求以后年度的月偿还额,得用下面的模型。
(二)建立分期偿还贷款双变量分析表
在“蒋文”中,由于是对贷款进行决策,所以其选用的双变量为“利率”和“贷款额”。在实际中,一旦决策确定,贷款深受利率和期限的影响,贷款期限的长短可能影响其贷款利率,贷款利率的变动又对贷款分析产生较大的影响。为了观察两个因素不同组合下的每期偿还额,需要借助模拟运算表建立双变量分析