论文部分内容阅读
［摘 要］ 等额还款与等本还款是按揭贷款中两种最常见的还款方式，笔者构建了基于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）”。从逻辑上讲这是等额还款方式下的首要因素。
要素9——本期末尚欠的贷款本金。本要素实质上就是按揭者在本期提前还贷所需向银行支付的金额，也是计算下期利息的基础。它的计算可以用这样两种方式，一是：期初尚欠的本金－本期需偿还的本金；二是：公积金贷款金额－截至本期累计偿还的本金。因此在表1的I7单元格填入公式“=C7－F7”或者“=$D$4－G7”。
2. 1. 2公式列表与填充
综上所述，表1第一期（1）~（9）各栏的公式依次为：
A7=B7（B列的数据由人工利用Excel的填充功能产生，非由公式计算生成）
C7=$D$4；D7=C7*$D$2 或者 D7=IPMT（$D$2，B7， $D$3， －$D$4）；
E7= D7；F7=H7－D7或者F7=PPMT（$D$2，B7，$D$3，
－$D$4）；G7=F7；
H7=$D$2/（1－（1 $D$2） ^ （-$D$3））*$D$4 或者H7=PMT（$D$2，$D$3，－$D$4）；
I7=C7－F7。
由于存在“已偿还利息累计、已偿还本金累计以及本期期初尚欠的贷款本金等于上期期末尚欠的贷款本金”的钩稽关系，暂时还不能把各公式往下填充，需要在表1第二期的部分栏目填写反映上述关系的公式后才能把完整的要素之间的钩稽关系全部描述出来。表1第二期（3）、（5）、（7）栏的公式依次为：C8=I7；E8=E7 D8；G8=G7 F8。
至此，要素之间的钩稽关系已经描述清楚，只要把各公式填充到底即可。值得注意的是，按照从左到右的顺序填充各公式，填充过程中个别栏目会出现都为0的情况，当把表1的各栏目都填充完毕后就会出现正确结果。此外，在运用上述各公式时一定要注意绝对引用与相对引用的问题，不能弄错。
本模型也可以用来解决组合贷款（公积金贷款 商业贷款）条件下各要素的计算，只需要仿照表1再设计一张表即可，见表2。
另外，由于表格较大，为了方便查阅，可以定位于C7单元格，然后选择“窗口”菜单中的“冻结窗格”功能。
2. 2等本还款模型
2. 2. 1要素间的钩稽关系
模型的结构与表1完全一样，要素间的钩稽关系与等额还款方式基本一致，这里不再重复。两种还款方式的区别在于“本期需偿还的本金”、“本期还款之和”这两个要素。等本还款方式下，本期需偿还的本金等于贷款总额除以期限，然后在此基础上加本期需偿还的利息得到本期还款之和。等额还款方式下则根据年金原理先计算出每期的还款之和，然后以本期还款之和扣除当期应偿还的利息倒推当期需偿还的本金。
2. 3两种还款方式比较
2. 3. 1等额偿还法的前期还款压力小
比较表1与表3明显可以发现这一点，直到第79期，等本还款法下的每月还款金额才下降到1 587.11元，接近等额还款下的每月1 585.76元。从第80期开始等本偿还方式的还款压力低于等额还款方式，越往后压力越小。
2. 3. 2在整个贷款周期内，等额还款方式支付的利息多
在15年的贷款期内，等本偿还方式共支付利息
76 020.00元，等额偿还方式共支付利息85 436.4元，后者相对前者多支付了12.39% 。
2. 3. 3提前还贷时，等本偿还方式相对经济
如果按揭者在第五年末（第60期）选择提前偿还贷款，等额方式下需偿还给银行149 232.14元（表1的第（9）栏），等本方式下需偿还给银行133 333.33元，后者相对前者少付10.65% 。
3 贷款参数发生变化时模型的运用
是指当按揭者开始还款一段时间后，遇到诸如国家调整贷款利率、提前偿还了部分贷款等情况，如何运用上述模型自动产生下一期的各种数据。解决这类问题的基本思想是：把贷款参数变动后的还款计算当作一笔新的按揭贷款来对待，在模型的贷款参数栏中输入新的参数值，模型会自动更新数据。本文假定国家在第60期（第五年末）调整贷款利率至5.58%，新的利率从第61期开始执行，按揭者为此还在第60期提前偿还了贷款30 000元。以等额还款为例说明如下：从表1中可以看出，第60期末尚欠贷款本金149 232.14元，提前偿还30 000元后仍欠119 232.14元。可以把它看成一笔新的按揭贷款，本金119 232.14元，利率5.58%，期限120期（180－60）。在表1的贷款参数栏的D1、D3、D4单元格分别输入5.58%、120、119 232.14，并把表1中的第127~186行全部选中并删除，即保留原模型的第7~126行共120期的数据（要注意原有数据的备份与保存）。此时模型自动更新了数据，更新后的结果见表4。
需要说明的是，为了使累计的数据具有连续性，本文更改了E7、G7的计算公式，否则“截至本期末累计偿还的利息”、 “截至本期末累计偿还的本金”两栏将重新开始累计。E7单元格的公式改为E7=D7 44 377.61（44 377.61是指截至第60期已累计偿还的利息，见表1），G7单元格的公式由原来的改为G7=F7 50 767.86（50 767.86是指截止第60期已累计偿还的本金，见表1），这两个公式修改好即可，不需要再往下填充。此外，为了使实际期次反映真实情况，把A7单元格的公式改为“=B7 60”并往下填充，由此不难理解为什么在模型中设计“实际期次”与“计算期次”这样两个项目。由于本模型不是计算机编程，在发生贷款要素变动时需要对原有的部分公式稍加修改，这是其不尽人意之处，尽管公式的修改简单快捷。
4 小 结
用Excel构建按揭贷款计算模型简单易行，它可以向按揭各方提供各种重要的数据。从上面的论述中可以发现，“本期还款之和”、“本期需偿还的本金”、“本期需偿还的利息”是模型中最关键的3个因素。在等额还款模型中，按照“确定每期还款额”、“确定当期应付利息”、“倒推当期还本金额”的顺序来进行。而在等本还款模型中则按照“确定每期还款本金”、“确定当期应付利息”、“确定当期还款额”的顺序来进行。当发生贷款要素变动时，只需对原模型稍作修改便可以自动产生新贷款参数下的各种数据。
主要参考文献
［1］ 史玉磊等. Excel函数与图表实用大全［M］. 北京：电子工业出版社，2007.
［2］ 宋燕，刘丹. Excel实用财务系统设计［M］. 北京：电子工业出版社，2007.
［关键词］ 等额还款；等本还款；计算模型；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）”。从逻辑上讲这是等额还款方式下的首要因素。
要素9——本期末尚欠的贷款本金。本要素实质上就是按揭者在本期提前还贷所需向银行支付的金额，也是计算下期利息的基础。它的计算可以用这样两种方式，一是：期初尚欠的本金－本期需偿还的本金；二是：公积金贷款金额－截至本期累计偿还的本金。因此在表1的I7单元格填入公式“=C7－F7”或者“=$D$4－G7”。
2. 1. 2公式列表与填充
综上所述，表1第一期（1）~（9）各栏的公式依次为：
A7=B7（B列的数据由人工利用Excel的填充功能产生，非由公式计算生成）
C7=$D$4；D7=C7*$D$2 或者 D7=IPMT（$D$2，B7， $D$3， －$D$4）；
E7= D7；F7=H7－D7或者F7=PPMT（$D$2，B7，$D$3，
－$D$4）；G7=F7；
H7=$D$2/（1－（1 $D$2） ^ （-$D$3））*$D$4 或者H7=PMT（$D$2，$D$3，－$D$4）；
I7=C7－F7。
由于存在“已偿还利息累计、已偿还本金累计以及本期期初尚欠的贷款本金等于上期期末尚欠的贷款本金”的钩稽关系，暂时还不能把各公式往下填充，需要在表1第二期的部分栏目填写反映上述关系的公式后才能把完整的要素之间的钩稽关系全部描述出来。表1第二期（3）、（5）、（7）栏的公式依次为：C8=I7；E8=E7 D8；G8=G7 F8。
至此，要素之间的钩稽关系已经描述清楚，只要把各公式填充到底即可。值得注意的是，按照从左到右的顺序填充各公式，填充过程中个别栏目会出现都为0的情况，当把表1的各栏目都填充完毕后就会出现正确结果。此外，在运用上述各公式时一定要注意绝对引用与相对引用的问题，不能弄错。
本模型也可以用来解决组合贷款（公积金贷款 商业贷款）条件下各要素的计算，只需要仿照表1再设计一张表即可，见表2。
另外，由于表格较大，为了方便查阅，可以定位于C7单元格，然后选择“窗口”菜单中的“冻结窗格”功能。
2. 2等本还款模型
2. 2. 1要素间的钩稽关系
模型的结构与表1完全一样，要素间的钩稽关系与等额还款方式基本一致，这里不再重复。两种还款方式的区别在于“本期需偿还的本金”、“本期还款之和”这两个要素。等本还款方式下，本期需偿还的本金等于贷款总额除以期限，然后在此基础上加本期需偿还的利息得到本期还款之和。等额还款方式下则根据年金原理先计算出每期的还款之和，然后以本期还款之和扣除当期应偿还的利息倒推当期需偿还的本金。
2. 3两种还款方式比较
2. 3. 1等额偿还法的前期还款压力小
比较表1与表3明显可以发现这一点，直到第79期，等本还款法下的每月还款金额才下降到1 587.11元，接近等额还款下的每月1 585.76元。从第80期开始等本偿还方式的还款压力低于等额还款方式，越往后压力越小。
2. 3. 2在整个贷款周期内，等额还款方式支付的利息多
在15年的贷款期内，等本偿还方式共支付利息
76 020.00元，等额偿还方式共支付利息85 436.4元，后者相对前者多支付了12.39% 。
2. 3. 3提前还贷时，等本偿还方式相对经济
如果按揭者在第五年末（第60期）选择提前偿还贷款，等额方式下需偿还给银行149 232.14元（表1的第（9）栏），等本方式下需偿还给银行133 333.33元，后者相对前者少付10.65% 。
3 贷款参数发生变化时模型的运用
是指当按揭者开始还款一段时间后，遇到诸如国家调整贷款利率、提前偿还了部分贷款等情况，如何运用上述模型自动产生下一期的各种数据。解决这类问题的基本思想是：把贷款参数变动后的还款计算当作一笔新的按揭贷款来对待，在模型的贷款参数栏中输入新的参数值，模型会自动更新数据。本文假定国家在第60期（第五年末）调整贷款利率至5.58%，新的利率从第61期开始执行，按揭者为此还在第60期提前偿还了贷款30 000元。以等额还款为例说明如下：从表1中可以看出，第60期末尚欠贷款本金149 232.14元，提前偿还30 000元后仍欠119 232.14元。可以把它看成一笔新的按揭贷款，本金119 232.14元，利率5.58%，期限120期（180－60）。在表1的贷款参数栏的D1、D3、D4单元格分别输入5.58%、120、119 232.14，并把表1中的第127~186行全部选中并删除，即保留原模型的第7~126行共120期的数据（要注意原有数据的备份与保存）。此时模型自动更新了数据，更新后的结果见表4。
需要说明的是，为了使累计的数据具有连续性，本文更改了E7、G7的计算公式，否则“截至本期末累计偿还的利息”、 “截至本期末累计偿还的本金”两栏将重新开始累计。E7单元格的公式改为E7=D7 44 377.61（44 377.61是指截至第60期已累计偿还的利息，见表1），G7单元格的公式由原来的改为G7=F7 50 767.86（50 767.86是指截止第60期已累计偿还的本金，见表1），这两个公式修改好即可，不需要再往下填充。此外，为了使实际期次反映真实情况，把A7单元格的公式改为“=B7 60”并往下填充，由此不难理解为什么在模型中设计“实际期次”与“计算期次”这样两个项目。由于本模型不是计算机编程，在发生贷款要素变动时需要对原有的部分公式稍加修改，这是其不尽人意之处，尽管公式的修改简单快捷。
4 小 结
用Excel构建按揭贷款计算模型简单易行，它可以向按揭各方提供各种重要的数据。从上面的论述中可以发现，“本期还款之和”、“本期需偿还的本金”、“本期需偿还的利息”是模型中最关键的3个因素。在等额还款模型中，按照“确定每期还款额”、“确定当期应付利息”、“倒推当期还本金额”的顺序来进行。而在等本还款模型中则按照“确定每期还款本金”、“确定当期应付利息”、“确定当期还款额”的顺序来进行。当发生贷款要素变动时，只需对原模型稍作修改便可以自动产生新贷款参数下的各种数据。
主要参考文献
［1］ 史玉磊等. Excel函数与图表实用大全［M］. 北京：电子工业出版社，2007.
［2］ 宋燕，刘丹. Excel实用财务系统设计［M］. 北京：电子工业出版社，2007.