论文部分内容阅读
摘要:面对高企的房价和频频出台的调控措施,这个“房贷还款模拟器”以列表方式给出了购房者在還贷过程所关心的全部信息,而且还可以利用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自带的图表工具,可以方便地对各种还款数据进行分析统计,模拟数据的任何变化都能在图表中实时显示,能轻松实现数据可视化,让枯燥的数据变得生动起来,让模拟结果更加直观。如下图所示:
主题词: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自带的图表工具,可以方便地对各种还款数据进行分析统计,模拟数据的任何变化都能在图表中实时显示,能轻松实现数据可视化,让枯燥的数据变得生动起来,让模拟结果更加直观。如下图所示: