用EXCEL模拟房贷还款,轻松选定理财策略

摘要：面对高企的房价和频频出台的调控措施，这个“房贷还款模拟器”以列表方式给出了购房者在还贷过程所关心的全部信息，而且还可以利用EXCEL内置图表工具进行数据分析，帮购房者轻松选定最佳还款策略。

主题词：EXCEL；模拟；策略

面对高企的房价，国家频频出台严厉调控措施抑制房价过快增长。在各种调控措施面前，购房者怎么还贷才更划算?为此，笔者用EXCEL设计了一个“房贷还款模拟器”，以列表方式给出了购房者在还贷过程所关心的全部信息，真实再现了完整的还款过程。模拟器除能准确计算月还款额外。还可模拟加／降息、取消优惠、提前还款等事件对还款计划的影响，并可以利用EXCEL内置图表工具进行数据分析，帮购房者轻松选定最佳的还款理财策略。

1　设定模拟事件

模拟器所要模拟的贷款事件假定为：购房者于某年3月向银行贷款30万，期限10年，年利率0.0594(即5.94％)，折扣优惠为0.7(即利率7折)；该购房者于当年4月开始第一次还款，还款方式为“等额本息”。

2　几个关键环节的设计与实现

(1)月还款额的计算

银行给出的公式为：月还款额=[本金×月利率×(1+月利率)^贷款期数]／[(1+月利率)^贷款期数-1]。公式中的“本金”和“贷款期数”其实可以更准确地定义为“剩余本金”和“剩余贷款期数”，此两者均可由上一期的还款数据递推出来；公式中的“月利率”为折扣优惠后的实际利率。EXCEL中的power()函数可以用于幂计算，其用法为power(底数，指数)。

(2)计算方法的设计

模拟器中设置“月份”和“期数”栏。其中，“月份”栏与现实中的真实月份相对应，便于用户按月查对各月还款情况；“期数”栏中，第一个还款月对应的那一期为第1期；第1个还款月之前的状态为第0期。

首先，在EXCEL中输入以下的表格样式，并将第0期行作为还款前的初始状态，依次填入月还款额、月还利息、月还本金等初始数据。如下图：

其次，在第1期行各单元格中输入相应计算公式。具体内容见下表：

输入完成后，得到的各类信息如下图：

最后，选取第1期行(不选“月份”和“期数”两个单元格)，将鼠标移至选中区域右下角，待鼠标变为黑色十字后，按下鼠标左键并向下拖动，直至最后一期所在行。模拟器的还款计算便宣告完成。如下图：

3　对还款过程进行模拟

只需改变本模拟器相应月份所在行中对应信息项的内容，模拟器便会自动将变化后的情况反映到后续还款计划中。下面举几个例子来说明如何模拟现实中的利率调整、提前还款、贷款期限延长，缩短等情形。

例1：在购房者贷款当年的9月份，央行宣布加息0.5个百分点。

由于加息是在次年1月份开始执行，故只需将次年1月份的“年利率”增加0.005(0.5％，即0.5个百分点)，也就是将J12单元格的内容改为“=J11+0.005”即可。模拟结果显示：加息后，购房者在还款期满时总共向

银行支付利息72394.73元，比加息前的67191.88元多出了5202.85元。

例2：购房者在次年3月份提前还款10万元。

只需将次年3月份的“剩余本金”减少10万元，也就是将H14单元格的内容改为“=H13-E14-100000”即可。模拟结果显示：提前还款后，购房者在还款期满时总共向银行支付利息50555.57元。比不提前还款少支付21839.16元。

例3：申请缩短还款期限／折扣优惠政策调整。

提前还款后，购房者一般有两种选择：一是保持月还款额基本不变，缩短还款期限；二是保持还款期限不变，减少每月还款额。按照现行政策规定，如果选择缩短还款期限，银行会与购房者重新签订合同。取消原有利率折扣优惠并按新的折扣政策执行；如果选择保持还款期限不变，银行会仍按原合同约定的利率优惠执行。这两种方案究竟哪种更划算呢?

首先对“保持月还款额基本不变，缩短还款期限”的方案进行模拟：将3月份的“剩余期数”减少3年，同时将“折扣优惠”调整为1，也就是将114单元格的内容改为“=13-1-36”，将K15单元格的内容改为“1”。得到的结果是：缩短还款期限后，购房者的月还款额为2943.31元，与提前还款前的3106.80元相比基本持平，购房者在还款期满时总共向银行支付利息49263.61元。再对“保持还款期限不变”的方案进行模拟：购房者的月还款额为1978.66元，比原来减少1128.14元，降低了36.3％，购房者在还款期满时总共向银行支付利息50555.57元。

对比两次模拟结果可知：购房者在这两种方案中总共向银行支付的利息几乎没有差别(相差不足3％)，但购房者在缩短还款期限后的月还款压力并未明显减轻(相差约5％)。显然，按原合同还款对购房者而言更为有利。

4　运用图表分析还款数据

利用EXCEL自带的图表工具，可以方便地对各种还款数据进行分析统计，模拟数据的任何变化都能在图表中实时显示，能轻松实现数据可视化，让枯燥的数据变得生动起来，让模拟结果更加直观。如下图所示：