虽然姐姐我很穷，但是我一直梦想有一个Big House，大大的房间，落地窗，游泳池……
打住，别做白日梦了，还是好好学习Excel吧。这些年身边越来越多的人选择做房奴，姐姐这期就带大家用Excel来算算月供多少吧。
1、使用“模拟运算表”进行贷款方案设计
许多人在买房贷款时需要考虑总共贷多少、贷多长时间、每个月还多少，这种情况下我们通常会使用Excel中的模拟运算表工具。
2016版“模拟运算表”位置
现假设总贷款50万，贷款年限30年，按照等额还款方式，利用PMT函数计算月供金额。
PMT(Rate, Nper, Pv, Fv, Type)。
PMT（贷款利率，付款总期数，现值（本金），余值，付款时间是期初还是期末（0代表后付，可省略，1代表先付））
公式=PMT(C2/12,B2*12,-A2)
条件已知，计算月供
当贷款总额为50万，期限30年，利率为4.90%时，月供金额为2653.63元。（大家能接受不？）但是，实际情况往往更复杂。
在真正买房的时候，为了综合衡量自身的还款能力，选择合适的贷款方案，我们需要考虑不同贷款年限和不同贷款金额的月供变动情况。
假定首付后能够接受的贷款总额在20万-80万之间，贷款期限在10年-30年的之间，那么在不同贷款总额、不同贷款期限条件下，月供多少呢？
首先，以月还款金额A5为基准单元格，生成一组从20万-80万以5万为间隔的纵向等差序列，再生成一组10-30以2为间隔的横向等差序列。
构建模拟运算区域
选定A5:L18单元格区域，数据-模拟分析-模拟运算表，【输入引用行的单元格】中输入$B$2，【输入引用列的单元格】中输入$A$2，单击确定。
注意：模拟运算表的行是“贷款期限”这组变量，因此“引用行”单元格中引用了“贷款期限”这个变量所在的单元格B2；
模拟运算表的列是“贷款总额”这组变量，因此“引用列”单元格中引用了“贷款总额”这个变量所在的单元格A2。
最终结果
在生成的模拟运算表区域利用条件格式-色阶进行单元格处理，绿色、黄色、红色分别代表较低、中等、较高的月供金额，这样我们在买房的时候就能根据自身的承受能力进行决策了。
月供数据分析
以目前的工资水平来看，罂粟姐姐能够接受的就是20万30年，可是这样首付的压力很大啊，瞬间悲伤逆流成河~
2、还款方式是等额本息还是等额本金？
在1中我们计算的前提是使用了等额本息的还款方式，其实在真正买房贷款时，有两种还款方式，一种是等额本息，就是每期本息之和为一个固定金额；第二种是等额本金，即每期偿还的本金相等，利息随着要偿还的本金越来越少而递减。
假设罂粟姐姐要买房啦，价值100万，首付30万，贷款70万，贷款30年，年利率4.90%，现有等额本息和等额本金两种还款方式，我应该选择哪一种呢？
等额本息还款方式：
偿还本金=PPMT($C$2/12,A7,$B$2*12,-$A$2)
偿还利息=IPMT($C$2/12,A7,$B$2*12,-$A$2)
本息合计=PMT($C$2/12,$B$2*12,-$A$2)或者=偿还本金+偿还利息
等额本金偿还方式：
偿还本金=$A$2/360
偿还利息=上期余额*利率
本息合计=偿还本金+偿还利息
计算结果
采用等额本息还款方式30年下来总共偿还金额为1,337,431元，采用等额本金还款方式30年下来总共还款金额为1,215,929元。
由于本金都是70万，所以可以得出等额本息比等额本金多付利息：121,502元。
好吓人啊，30年整整多了12万！
我们再借用图表对月供金额进行分析。
等额本金方式因为利息不断减少，所以月还款额不断地降低，但是前期月还款压力比较大，等额本息前期的压力相对较小。
从人民币贬值的角度来看，等额本息每个月还款固定，从现值的角度来讲是逐步下降的，而等额本金初期还款多，现值高，可能不利于投资。
所以如果是买房自住，尽量选择等额本金，毕竟前期压力大点儿没啥，后期越来越少，总利息也少很多；如果是投资的话，尽量选择等额本息，既能缓解创业前期的资金压力，又能体现钱的时间价值。
各位要买房的亲们，你们准备好（钱）了吗？
总结：
（1）认识“模拟运算表”这个不常用，但是很强大的假设工具。
（2）学会一些最基础的财务函数。
当然，贴心的罂粟姐姐还是为大家准备了源文件哦，计算结果都在里面，可以直接套用。
网友评论