基于Excel的通用按揭还款模型研究

罗小兰

(南京金肯职业技术学院，江苏 南京 211156)

基于Excel的通用按揭还款模型研究

罗小兰

(南京金肯职业技术学院，江苏 南京 211156)

随着时代发展和大众消费心理的变迁，“借钱消费、分期付款”模式逐渐被越来越多的人所接受，按揭还款在住房、汽车等耐用品消费中渐成主流。非财会专业的社会大众很难区分等额本息、等额本金之类术语的含义，遑论数字背后基于货币时间价值的内在逻辑。即使是专业人士，面对利率调整、还款期限、提前还本等变化，快速便捷计算出每期还款额也并非易事。本文介绍一种方法，利用普遍使用的Excel电子表格构建一个本金余额、剩余期限、贷款利率都可变的通用模型，基本涵盖现实生活中可能出现的各种情形。

按揭还款；通用模型；多变量；excel

Excel是功能丰富、普遍使用的电子表格，提供了一系列财务函数帮助人们解决实际问题，理清思路后，使用相关函数建立逻辑关系就能成功构建模型。

一、准备

1.按揭分期的理论基础。按揭(Mortgage)是指以房地产等实物资产或有价证券、契约等作抵押，获得银行贷款并依合同分期付清本息，贷款还清后银行归还抵押物。分期付款的形式一般有两种：(1)等额本息。等额本息的特点是在还款期内每期(月)的还款本息之和固定，适合每月资金流较为稳定的人群(如工薪阶层)，每期还款额x的计算公式为：

x=Aβ(1+β)m(1+β)m-1

其中A为贷款总额，β为月利率，m为总还款期数；(2)等额本金。即每期按相等的金额偿还贷款本金，利息按剩余本金计算并逐月结清，两者合计即为每期的还款额，由于占用银行资金在时间、金额上较等额本息方式少，能够减少利息支出，每期还款额x的计算公式为：

x=Am+Amβ

其中A为贷款总额，m为总还款期数，Am为第m期本金余额，β为月利率。

2.Excel中相关财务函数介绍。虽然有公式，在计算每月还款额时仍然很复杂繁琐，计算等额本息Excel提供了PMT、PPMT、IPMT函数，分别用来计算每期的还款额、每期本金、每期利息，以PMT函数为例：

X=PMT(rate,nper,pv,fv,type)

其中，rate为每期利率，nper为总期数，pv为本金，fv为终值(此处可省略)，type为类型，默认值为0(普通年金)。PPMT、IPMT用法类似，可参阅Excel帮助。

二、思路

考虑贴近生活实际，本金余额、利率、剩余期限可能在任何一期发生变动，为保证模型的通用性和灵活性，采用逐期处理的方式来进行处理，分别计算每期偿还本金、利息、本金余额。具体步骤为：(1)计算本金余额；(2)根据本期利率和剩余还款期限分别计算本期偿还本金、利息；(3)下一期以此类推迭代计算。

三、实际操作

1.表格设计。(1)初始值。主要变量有：贷款总额；年利率(%)，计算中除以12得月利率；还款期限(年)，计算中乘以12得总期数；还款方式，方便输入避免出错可选菜单“数据-数据有效性-设置-有效性条件”，在“允许”中选择“序列”，并在下方文本框中输入“等额本息，等额本金”(见图1、图2)。

图1

图2

(2)表格项目。时间，表示实际年月份；期数，表示当期在总期数中的位置；剩余期数，表示预期还款时间；减少期数，提前还款时可约定减少还款期；利率，一般每年初调整为最近利率水平；本金余额，表示当前占用银行资金额度；提前还本，表示当期提前还款额；(图3)

图3

应付本金；应付利息；应付本息经计算为负值，表示资金的流出。(图4)

2.逻辑关系及公式编写

(1)本金余额。第1期直接引用初始值，本例中公式为“=N3”；第2期以后本金余额=上期本金余额-上期已还本金-上期提前还本额，如第2期公式为“=F2+H2+G2”(还款为负值，故用加号)。

(2)剩余期数。第1期末时，剩余期数为总期数减1，公式为“=N5*12-1”，第2期后可能有缩短还款期，公式为“=C2-D2-1”。

图4

(3)应还本金。根据还款方式的不同，应还本金应用不同的公式，等额本息时本期应还本金为“=PPMT(E2/1200,1,C2+1,F2)”，等额本金时每期本金为“=-F2/(C2+1)”。为减少人工干预，使用IF函数自动判断，公式为：“=IF($N$2=‘等额本金’,-F2/(C2+1),PPMT(E2/1200,1,C2+1,F2))”。

(4)应还利息。同上，使用IF函数自动判断当前选择的还款方式，公式为：“=IF($N$2=‘等额本金’,-F2*E2/1200,IPMT(E2/1200,1,C2+1,F2))”。

(5)应还本息。即每期的本金与利息之和，可用PMT函数计算，也可直接相加。

3.注意事项及技巧

Excel电子表格的一大优势是可以复制公式到其他单元格，电子表格会按照对应的逻辑关系自动匹配，为我们减少了相当的工作量。在操作中需要注意的有：(1)财务函数计算结果根据资金的流向不同结果为正值或负值，与其他数据计算时需要注意；(2)Excel中默认为相对应用，必要时可用类似“$A$1”的形式表示绝对引用；(3)如缩短还款期限，原末期单元格公式无意义，会显示错误值，可用IFERROR、ISNA等函数调整。

四、实例

以贷款100万元20年，年利率5.65，分别采用等额本金、等额本息方式，模拟提前还款、缩短还款期情形测试该模型均计算正确，如有需要还可进一步完善实现组合贷款试算。(图5-1、图5-2)

图5-1

图5-2