基于Excel等额还款与等本还款计算模型.docVIP

基于Excel等额还款与等本还款计算模型 　　［摘 要］ 等额还款与等本还款是按揭贷款中两种最常见的还款方式，笔者构建了基于Excel的等额还款与等本还款计算模型，模型由贷款参数与数据区域两部分组成。通过该模型可以非常容易得到贷款周期内各期需要偿还的本金、利息，总还款额，提前还贷时的还款额，贷款利率变动时的还款额等重要信息，有助于事前制定合理的按揭贷款计划。 　　［关键词］ 等额还款；等本还款；计算模型；Excel 　　［中图分类号］F232；F275.1［文献标识码］A［文章编号］1673-0194（2008）06-0041-03 　　 　　1 引 言 　　 　　在现代社会中，随着居民收入水平的提高以及消费观念的转变，按揭贷款购房、购车应运而生。按揭贷款的还款方式有两种――等额还款法与等本还款法。二者的主要区别在于等本还款法在整个贷款周期内支付的利息总和相对较少，提前还贷时“合算”，但主要缺点是前期还贷压力很大。对于按揭者而言，不仅需要知道上述区别，还要把它们的区别量化。况且我国目前正处于一个加息周期中，有比较明确的加息预期，按揭者希望在贷款前进行敏感性分析以了解利率在目前基础上每上升若干基点，还款额将增加多少，这样有利于做出合适的贷款计划。面对诸如此类的问题，笔者构建了基于Excel的等额还款与等本还款模型，能提供按揭者需要的各种数据，比如：每期的还贷额，每期偿还的本金、利息，在某一期提前还贷时需要偿还的金额等。当贷款利率等因素发生变动时，只需在模型中的贷款参数栏填入变动后的数值，上述各种按揭者需要的数据将自动更新，使用起来非常便利。 　　 　　2 模型的构建 　　 　　2. 1等额还款模型 　　等额还款模型包括两部分，一是贷款参数部分，由贷款年利率、贷款月利率、贷款期限、贷款金额4个参数组成。其中贷款月利率等于贷款年利率除以12，由系统以公式的方式产生计算值，勿以人工方式填入，以防在除不尽时产生比较大的计算误差，其余的三要素则以手工填入。二是各种还款数据区域，包括实际期次、计算期次、期初尚欠的贷款本金、本期需偿还的利息、截至本期末累计偿还的利息，本期需偿还的本金，截至本期末累计偿还的本金、本期还款之和、本期末尚欠的贷款本金等9个要素。此模型构建完毕后可以达到如下效果：在贷款参数中更换不同的数值，数据区域将自动更新。下面本文以一个实例来说明如何设计等额还款计算模型：小王利用公积金按揭贷款购房，贷款金额200 000元，年利率5.04%，贷款期限15年。 　　 　　2. 1. 1要素间的钩稽关系 　　要素1――实际期次。用来反映实际的还款时间，即第几个月。本要素在“计算期次”的基础上根据公式产生。 　　要素2――计算期次。用来计算“本期还款之和”、“本期需偿还的本金”、“ 本期需偿还的利息”等所需要的参数。本栏目的数据由人工利用Excel的填充功能产生，非由公式计算生成。 　　要素3――期初尚欠的贷款本金。第一期初尚欠的贷款本金等于总贷款金额，以后各期期初尚欠的金额等于上期期末尚欠的贷款本金。 　　要素4――本期需偿还的利息。根据计息的基本原理，每期末的应付利息等于当期期初的贷款本金乘以当期的贷款利率。所以表1的D7单元格的公式为“=C7*$D$2”。或者直接用Excel财务函数IPMT，在D7单元格填入公式“=IPMT（$D$2，B7，$D$3，－$D$4）”。值得注意的是Excel财务函数涉及资金流入与流出的问题，资金流入用正数表示，资金流出用负数表示，本文在贷款金额D4前加上负号则保证计算出来的还款金额是正数，否则计算结果将以负数显示。 　　要素5――截至本期末累计偿还的利息。第一期利息的累计值等于第一期的实际值，以后各期利息的累计值等于上期的累计值加当期的实际值。 　　要素6――本期需偿还的本金。在等额还款中每期需要偿还的本金采用倒推的方式，即本期需偿还的本金=本期还款之和－本期需偿还的利息。所以表1的F7单元格的公式为“= H7－D7”。或者直接用Excel财务函数PPMT，在F7单元格填入公式“=PPMT（$D$2，B7，$D$3，－$D$4）”。 　　要素7――截至本期末累计偿还的本金。参照要素5。 　　要素8――本期还款之和。等额还款从实质上讲是年金，根据资金时间价值的基本原理，每期的还款额等于贷款金额除以相应的年金现值系数（P/A，i，n）或者乘以资本回收系数（A/P，i，n）。（A/P，i，n）= i÷[1－（1+i）-n]，i为贷款利率，n为贷款期限。于是可以在表1的H7单元格填入公式“=$D$2/（1－（1+$D$2） ^ （-$D$3））*$D$4”。或者直接用Excel财务函数PMT，在H7单元格填入公式“=PMT（$D$2，$D$3，－$D$4）”。