基于Excel的房贷还款模型构建与分析.docVIP

基于Excel的房贷还款模型构建与分析 　　[摘 要]房贷使用何种还款方式及利弊差异，大多“准房奴”无所适从。文章首先对常见房贷还款方式进行介绍，再结合Excel财务函数功能构建了还款模型，不仅从细节上对每期还款数据进行了计算，还从整体上进行了展示 [关键词]还款模型；Excel；财务函数 10 13939/j cnki zgsc 2015 51 154 1 引 言 “有房才有家，有房才有根。”是绝大多数国人对于买房而不租房过日子的论调。房价如此的高，贷款买房成为必然。贷款方式多种多样，如何选择，如何计算又成为买房路上的拦路虎。Excel是数据处理最有效的工具，使用Excel构建还款模型、展示结果、分析成因就显得顺理成章。房贷还款方式主要分为等额本金、等额本息和公积金自由三种还款方式 2 贷款方式及对应Excel函数介绍 2 1 房贷还款方式概述及差异比较 等额本金、等额本息、公积金自由三种还款方式各有优缺点，如表1所示 表1 三种还款方式差异比较 []特点[]缺点[]适用人群 等额本金[]本金保持相同，利息逐月递减，月还款数递减[]贷款人起初还款压力较大，但是随时间的推移每月还款数也越来越少[]适合有一定经济基础，能承担前期较大还款压力，且有提前还款计划的借款人 等额本息[]本金逐月递增，利息逐月递减，月还款数不变[]支出利息较多，还款初期利息占月供款的大部分，随本金逐渐返还，本金占每月供款的比重增加[]每月归还相同的款项，方便安排收支，适合经济条件不允许前期还款投入过大，收入处于较稳定状态的借款人 公积金自由[]只要不低于最低还款额，借款人可以自由设定每月的还款金额[]还款人一直按最低还款额进行还款，最后一期还款额数目较大，通常为贷款数额的10%～20%[]适用于公积金贷款且收入逐年递增的中青年人群，月还款额调整较频繁 2 2 Excel财务函数介绍 Excel中包含多种类型函数，其中财务函数可进行常见财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值，功能十分强大。结合实例，函数PMT、IPMT、PPMT分别用于计算等额本息还款额、利息、本金 3 还款模型构建 3 1 案例 某人拟购置一处房产，需从银行贷款80万元，公积金月贷款利率为：3 54167‰，贷款30年，试构建还款模型，比较等额本金、等额本息及自由还款方式的差异，从总还款额、支付的利息及每期还款额等几个方面说明 3 2 构建模型 模型如下图所示，在进行公式录入前先来介绍几个公式： 支付的利息=期初余额×贷款利率 本期还款额=支付的利息+偿还的本金 期末余额=期初余额-偿还的本金 下一期期初余额=上一期期末余额 基于上述公式，期初余额已知，则支付的利息确定，根据不同还款方式确定本期还款额或偿还的本金即可确定其余所有项目 等额本金偿还方式，偿还的本金=贷款额/总付款期数；等额本息偿还方式，本期还款额=PMT（$B$4，$B$6，-$B$3），第三个参数pv为负值，支付的利息=IPMT（$B$4，A377，$B$6，-$B$3），偿还的本金=PPMT（$B$4，A377，$B$6，-$B$3）；自由还款方式，最低还款额为合同规定的最低值，案例设定为3641元，其余项可由公式计算得到 房贷还款模型图 4 结果与分析 根据房贷还款模型对案例进行计算，结果如下图、表2所示。显见，自由还款方式所要支付的利息最多，且最后一期需还￥217，413 46；等额本金方式最初还款额为￥5，055 56，逐年递减，最后一期为￥2，230 09；等额本息方式每期还款额固定为￥3，935 52 5 小 结 通过使用Excel函数，构建了房贷还款模型，充分展示了三种房贷还款方式的差异。模型实现了计算过程自动化，只需输入贷款额、利率、年限即可由此计算出三种还款方式的每期数值。模型既从细节上展示了每期的还款额、利息和本金，又从整体上给出了三种方式的利弊差异 总的来说，模型的构建阐明了三种房贷还款方式的特点及适用对象，为无所适从的“准房奴”提供了详尽的数据支持 表2 三种还款方式对比表 还款方式还款总额支付的利息偿还的本金 等额本金￥1，311，417 15￥511，417 15￥800，000 00 等额本息￥1，416，787 56￥616，787 56￥800，000 00 自由还款￥1，524，532 46￥724，532 46￥800，000 00 参考文献： [1]王海林 Excel财务管理建模与应用[M].北京：电子工业出版社，2014 [2]张山风，