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

1、2008 年 3 月中 国 管 理 信 息 化Mar., 2008第 11 卷第 6 期China Management InformationizationVol.11, No.6基于 Excel 的等额还款与等本还款计算模型许长荣( 上海农林职业技术学院, 上海 201600) 摘 要 等额还款与等本还款是按揭贷款中两种最常见的还款方式, 笔者构建了基于 Excel 的等额还款与等本还款计算模型, 模型由贷款参数与数据区域两部分组成。通过该模型可以非常容易得到贷款周期内各期需要偿还的本金、利息, 总还款额, 提前还贷时的还款额, 贷款利率变动时的还款额等重要信息, 有助于事前制定合理的按揭

2、贷款计划。 关键词 等额还款; 等本还款; 计算模型; Excel 中图分类号F232; F275.1 文献标识码A 文章编号1673- 0194( 2008) 06- 0041- 031引言在现代社会中, 随着居民收入水平的提高以及消费观念的转变, 按揭贷款购房、购车应运而生。按揭贷款的还款方式有两种等额还款法与等本还款法。二者的主要区别在于等本还款法在整个贷款周期内支付的利息总和相对较少, 提前还贷时“合算”,但主要缺点是前期还贷压力很大。对于按揭者而言, 不仅需要知道上述区别, 还要把它们的区别量化。况且我国目前正处于一个加息周期中, 有比较明确的加息预期, 按揭者希望在贷款前进行敏感性

3、分析以了解利率在目前基础上每上升若干基点, 还款额将增加多少, 这样有利于做出合适的贷款计划。面对诸如此类的问题, 笔者构建了基于 Excel 的等额还款与等本还款模型, 能提供按揭者需要的各种数据, 比如: 每期的还贷额,每期偿还的本金、利息, 在某一期提前还贷时需要偿还的金额等。当贷款利率等因素发生变动时, 只需在模型中的贷款参数栏填入变动后的数值, 上述各种按揭者需要的数据将自动更新, 使用起来非常便利。2 模型的构建2. 1 等额还款模型等额还款模型包括两部分, 一是贷款参数部分, 由贷款年利率、贷款月利率、贷款期限、贷款金额 4 个参数组成。其中贷款月利率等于贷款年利率除以 12,

4、由系统以公式的方式产生计算值, 勿以人工方式填入, 以防在除不尽时产生比较大的计算误差, 其余的三要素则以手工填入。二是各种还款数据区域, 包括实际期次、计算期次、期初尚欠的贷款本金、本期需偿还的利息、截至本期末累计偿还的利息, 本期需偿还的本金, 截至本期末累计偿还的本金、本期还款之和、本期末尚欠的贷款本金等 9 个要素。此模型构建完毕后可以达到如下效果: 在贷款参数中更换不同的数值, 数据区域将自动更新。下面本文以一个实例来说明如何设计等额还款计算模型: 小王利用公积金按揭贷款 收稿日期 2007- 09- 17 作者简介 许长荣( 1972- ) , 男, 江西万安人, 上海农林职业技术

5、学院讲师, 主要从事会计电算化、财务管理、统计方面的教学与研究。购房, 贷款金额 200 000 元, 年利率 5.04%, 贷款期限 15 年。表 1等额还款模型2. 1. 1 要素间的钩稽关系要素 1实际期次。用来反映实际的还款时间, 即第几个月。本要素在“计算期次”的基础上根据公式产生。要素 2计算期次。用来计算“本期还款之和”、“本期需偿还的本金”、“本期需偿还的利息”等所需要的参数。本栏目的数据由人工利用 Excel 的填充功能产生, 非由公式计算生成。要素 3期初尚欠的贷款本金。第一期初尚欠的贷款本金等于总贷款金额, 以后各期期初尚欠的金额等于上期期末尚欠的贷款本金。要素 4本期需

6、偿还的利息。根据计息的基本原理,每期末的应付利息等于当期期初的贷款本金乘以当期的贷款利率。所以表 1 的 D7 单元格的公式为“=C7*$D$2”。或者直接用 Excel 财务函数 IPMT, 在 D7 单元格填入公式“= IPMT($D$2, B7, $D$3, - $D$4) ”。值得注意的是 Excel 财务函数涉及资金流入与流出的问题, 资金流入用正数表示, 资金流出用负数表示, 本文在贷款金额 D4 前加上负号则保证计算出来的还款金额是正数, 否则计算结果将以负数显示。要素 5截至本期末累计偿还的利息。第一期利息的累计值等于第一期的实际值, 以后各期利息的累计值等于上期的累计值加当期

7、的实际值。要素 6本期需偿还的本金。在等额还款中每期需要偿还的本金采用倒推的方式, 即本期需偿还的本金=本CHINA MANAGEMENT INFORMATIONIZATION/ 41财务管理信息化期还款之和- 本期需偿还的利息。所以表 1 的 F7 单元格的公式为“= H7- D7”。或者直接用 Excel 财务函数 PPMT, 在F7 单元格填入公式“=PPMT( $D$2, B7, $D$3, - $D$4) ”。要素 7截至本期末累计偿还的本金。参照要素 5。要素 8本期还款之和。等额还款从实质上讲是年金, 根据资金时间价值的基本原理, 每期的还款额等于贷款金额除以相应的年金现值系数(

8、P/A, i, n) 或者乘以资本回收系数(A/P, i, n) 。(A/P, i, n)= i1- (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本期末尚欠的贷款本金。本要素实质上就是按揭者在本期提前还贷所需向银行支付的金额, 也是计算下期利息的基础。它的计算可以用这样两种方式, 一是:期初尚欠的本金-

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

10、- ( 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。至此, 要素之间的钩稽关系已经描述清楚, 只要把各公式填充到底即可。值得注意的是, 按照从左到右的顺

11、序填充各公式, 填充过程中个别栏目会出现都为 0 的情况,当把表 1 的各栏目都填充完毕后就会出现正确结果。此外, 在运用上述各公式时一定要注意绝对引用与相对引用的问题, 不能弄错。本模型也可以用来解决组合贷款 ( 公积金贷款+商业贷款) 条件下各要素的计算, 只需要仿照表 1 再设计一张表即可, 见表 2。另外, 由于表格较大, 为了方便查阅, 可以定位于 C7 单元格, 然后选择“窗口”菜单中的“冻结窗格”功能。2. 2 等本还款模型2. 2. 1 要素间的钩稽关系模型的结构与表 1 完全一样, 要素间的钩稽关系与等额还款方式基本一致, 这里不再重复。两种还款方式的区表 2组合贷款条件下的

12、模型结构( 表头部分)别在于“本期需偿还的本金”、“本期还款之和”这两个要素。等本还款方式下, 本期需偿还的本金等于贷款总额除以期限, 然后在此基础上加本期需偿还的利息得到本期还款之和。等额还款方式下则根据年金原理先计算出每期的还款之和, 然后以本期还款之和扣除当期应偿还的利息倒推当期需偿还的本金。2. 2. 2 公式列表与填充综上所述, 表 3 第一期( 1) ( 9) 各栏的公式依次为:A7=B7 ( B 列的数据由人工利用 Excel 的填充功能产生, 非由公式计算生成)C7= $D$4; D7=C7*$D$2; E7= D7; F7=$D$4/$D$3; G7= F7; H7=D7+F

13、7; I7=C7- F7。表 3 第二期 ( 3) 、(5) 、(7) 各栏的公式依次为: C8=I7;E8=E7+D8; G8=G7+F8。要素之间的钩稽关系描述清楚后,把各公式填充到底即可, 结果见表 3。表 3等本还款模型2. 3 两种还款方式比较2. 3. 1 等额偿还法的前期还款压力小比较表 1 与表 3 明显可以发现这一点, 直到第 79 期,等本还款法下的每月还款金额才下降到 1 587.11 元, 接近等额还款下的每月 1 585.76 元。从第 80 期开始等本偿还方式的还款压力低于等额还款方式, 越往后压力越小。2. 3. 2 在整个贷款周期内, 等额还款方式支付的利息多在

14、 15 年的贷款期内 , 等本偿还方式共支付利息76 020.00 元, 等额偿还方式共支付利息 85 436.4 元, 后者相对前者多支付了 12.39% 。2. 3. 3 提前还贷时, 等本偿还方式相对经济如果按揭者在第五年末 ( 第 60 期) 选择提前偿还贷款, 等额方式下需偿还给银行 149 232.14 元( 表 1 的第( 9) 栏) , 等本方式下需偿还给银行 133 333.33 元, 后者相对前者少付 10.65% 。3 贷款参数发生变化时模型的运用是指当按揭者开始还款一段时间后, 遇到诸如国家调整贷款利率、提前偿还了部分贷款等情况, 如何运用上述模型自动产生下一期的各种数

15、据。解决这类问题的基本思想42 / CHINA MANAGEMENT INFORMATIONIZATION2008 年 3 月中 国 管 理 信 息 化Mar., 2008第 11 卷第 6 期China Management InformationizationVol.11, No.6陕西省上市公司资本结构现状分析王燕妮( 西安电子科技大学 经济管理学院, 西安 710071) 摘 要 本文从总资产负债率、行业资产负债率、债务总额与地区生产总值对比等 3 个方面对陕西省上市公司的资本结构现状加以分析, 结果表明, 陕西省上市公司资产负债率总体偏低、存在明显的行业差异、存在比全国上市公司平均水

16、平更强的股权融资偏好。 关键词 上市公司; 资本结构; 资产负债率 中图分类号F275 文献标识码A 文章编号1673- 0194( 2008) 06- 0043- 03企业融资由外部融资和内部融资组成, 内部融资主要来源于留存收益, 外部融资包括股权融资和债务融资。由 收稿日期 2007- 10- 27 基金项目 陕西省自然科学基金项目( 2004G07) ; 陕西省社会科学基金项目( 05D007S) 。 作者简介 王燕妮( 1975- ) , 女, 陕西商州人, 西安电子科技大学经济管理学院副教授, 研究方向: 公司财务与投资。于在企业外部融资中, 股权融资和债务融资在资本成本、净收益、

17、税收以及对企业经营决策的影响方面存在很大不同, 两者不同的组合形成不同的资本结构。资本结构不仅影响企业的资本成本和企业总体价值, 而且影响企业的治理结构, 进而影响一个国家或地区的总体经济增长和稳定。陕西省位于中国西部, 是联结西北、西南、华北、华中!是: 把贷款参数变动后的还款计算当作一笔新的按揭贷款式的修改简单快捷。来对待, 在模型的贷款参数栏中输入新的参数值, 模型会自 表 4 提前部分还贷及利率变动后的还款表动更新数据。本文假定国家在第 60 期( 第五年末) 调整贷款利率至 5.58%, 新的利率从第 61 期开始执行, 按揭者为此还在第 60 期提前偿还了贷款 30 000 元。以

18、等额还款为例说明如下: 从表 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, 并把表 14小结中的第 127186 行全部选中并删除, 即保留原模型的第 7 用 Excel 构建按揭贷款计算模型简单易行, 它可以向 126 行共 120 期的数据 ( 要注意原有数据的备份与保存) 。 按揭各方提供各种重要的数据。从上面的论述中可以发此时模型自动更新了数据, 更新后的结果见表 4。现, “本期还款之和”、“本期需偿还的本金”、“本期需偿还的需要说明的是, 为了使累计的数据具有连续性, 本文更 利息”是模型中最关键的 3 个因素。在等额还款模型中, 按改了 E7、G7 的计算公式