基于Excel的等额本息提前还贷分析

吴敏

摘要：等额本金还款与等额本息还款是按揭贷款中两种最常见的还款方式，建立了基于Excel的等额本息还款计算模型。本模型由贷款参数区域、数据计算区域和结果统计区域三部分组成。通过该模型可以直观得出不同期数提前还款情况下贷款周期内各期需要偿还的本金、利息，总还款额，贷款利率变动时的还款额等重要信息，有助于事前制定合理的还款计划。

关键词：提前还贷；Excel；等额本息还款法

中图分类号：F83

文献标识码：A

文章编号：16723198（2015）27-011801

现代社会中，居民的消费观念不断转变，按揭购物、分期付款等经济现象不断普遍。目前按揭贷款的还款方式有两种——等额本金还款法与等额本息还款法。二者的主要区别在于等额本金还款法在整个贷款周期内支付的利息总和相对较少而前期还贷压力较大，等额本息还款法正好相反。采用提前还贷的等额本息还款方式，不但能减轻前期还款压力，还能够在资金灵活的情况下减少还款利息。

1 模型的构建

1.1 模型构建的基本内容

等额还款模型包括三部分：一是贷款参数部分，由贷款总额、贷款月利率、贷款期限和等额本息月还款额共4个参数组成。其中贷款期限单位为年，默认每年12期；等额本息月还款额根据其它三个参数计算得出。如图1所示。

本文以一个实例来说明：小方利用公积金按揭贷款购房，贷款金额40万元，年利率4.50%，贷款期限20年，首次还款时间为2013年6月。其中假设2014年6月提前还贷12万，2015年6月提前还贷10万。

二是各种还款数据区域，分“总还款期不变”和“月还款额不变”两种情况。两种情况下分别提供日期、期数、提前还款额、月初剩余还款额、月还款额、本金额、利息额和利息本金比共8个要素。

三是结果统计区域，统计分析在等额本息还款方式中不提前还款、提前还款期不变和提前还款月还款额不变三种情况下的总还款情况。如图2所示。此模型构建完毕后可以达到如下效果：在贷款参数中更换不同的数值，还款数据区域和结果统计区域将自动更新。

1.2 模型构建要素分析

要素l——日期。用来反映实际的还款时间。本要素由人工利用Excel的填充功能产生。

要素2——期数。用来计算“月还款额”、“本金额”、“利息额”等所需要的参数。本栏目的数据由人工利用Excel的填充功能产生。

要素3——提前还款额。用来填写本期提前还款的数额。根据具体提前还款情况或者计划由用户进行填写，否者不填！

要素4——月初剩余还款额。第一期“月初剩余还款额”等于总贷款金额，通过读取贷款参数生成，以后各期的“月初剩余还款额”等于上期月初剩余还款额减去上期本金额获得。即：本月月初剩余还款额一上月月初剩余还款额一上月月换本金额。

要素5——月还款额。根据资金时间价值的基本原理，每期的还款额等于贷款金额除以相应的年金现值系数（P/A，i，n）或者乘以资本回收系数（A/P，i，n）。（A/P，i，n）=i÷[1一（1+i）-n，i为贷款利率，n为贷款期限。本文直接用Excel财务函数PMT，在“总还款期不变”情况下K3单元格公式为“=PMT（贷款年利率/12，（贷款期*12+1-B3），-J3）”；在“月还款额不变”情况下R3单元格公式为“一IF（Q3>+月还款额，（+月还款额+P3），MAX（O，Q3》”，添加IF判断是否已经还款完成。

要素6——本金额。在等额还款中每期需要偿还的本金采用倒推的方式，即本金额一月还款额一利息额。所以表1的L7单元格的公式为“=K7-M7”，或者直接用Excel财务函数PPMT。

要素7——利息额。根据计息的基本原理，每期的应付利息等于上期剩余贷款额乘以当期的贷款利率。所以图2的T3单元格的公式为“=Q3*+贷款年利率/12”。或者直接用Excel财务函数IPMT。

要素8——利息本金比。通过利息本金比为每期的还款资金情况进行分析。利息本金比一利息额/本金额。

1.3 公式列表与填充

至此，要素之间的钩稽关系已经描述清楚，只要把各公式填充到底即可。值得注意的是，按照从左到右的顺序填充各公式，填充过程中个别栏目会出现都为O的情况，当把图2的各栏目都填充完毕后就会出现正确结果。

本模型也可以用来解决组合贷款（公积金贷款十商业贷款）条件下各要素的计算，只更改图1的利率，即贷款年利率=（商业贷款额*商贷利率+公积金贷款额*公积金贷款利率）/总贷款额。

2 对提前还款决策的分析

对要素7进行分析可以发现，利息的产生是由上期剩余贷款额和当期的利率共同作用的，所以说贷款中产生的利息都是由于对当期的资金占用而生成的，只与上期剩余贷款额和贷款利率有关，与具体的时间、期数等无关等无关；

提前还款引起的利息减少也是由于持续占用的资金减少造成的，与具体的提前还款时间无关，如：“等额本息房贷类型的客户，已经到了还贷中期不宜提前还贷”的说法是不严谨的，该说法的理由是：在等额本息房贷产品的设计当中，银行为了迅速收回房贷资金，到了还贷中期时，大部分的房贷本金都已经还完，剩下的房贷利息支出较少，这个时候再提前还贷意义不大。实际上每次的还款不但有本金，也有利息。

决定是否提前还款应该主要考虑闲置资金对于自身的价值，如果有高于贷款利率收益的投资渠道，则可以放弃提前还款，当然还要综合考虑闲置资金的周转期及资金的灵活性。如：股票、基金或理财产品等的募集期、手续费和到账时间等。

CPI和PPI宏观经济数据对提前还款的决策没有直接影响，其影响主要通过利率的预期变化来实现。

3 结论

用Excel构建基于等额本息的提前还款模型简单明了，它可以向按揭各方提供各种重要的数据。通过该模型设定不同的贷款参数，就可以得到每期的总还款额、每期的本金额、每期的利息额等数据，通过利息本金比得出当期的还款资金分布。同时录入自身提前还款情况还可以得出提前还款后的每期还款情况，并与没有提前还款的情况进行比较分析，为提前还款的决策提供参考。通过对还款过程中各要素的分析，得出：对个体而言，减少利息支出的有效方法就是减少对资金的占有时间或占有总额，影响提前还款决策的主要因素主要有个体投资渠道收益和当期及以后利率的对比。