实验 2 房屋按揭现金的实际计算

1. 实验2 房屋按揭现金的实际计算

2. 试验目的： • 运用房地产投资等相关知识，使用Excel的内部函数及相关功能，掌握房屋按揭现金的实际计算，以帮助我们进行房地产投资分析。

3. 实验内容与要求： • 使用PMT函数等Excel相关内部函数及计算功能，在房屋按揭现金的实际计算中做出合理的投资项目决策。 • 实验工具：Excel。

4. 一、不同复利间隔期利率的转换 • 在我们筹资和借贷活动中，经常遇到这种情况：给定年利率，但是计息周期是半年一次或按季或月计算复利。那么实际的年利率与给定的年利率(称为名义年利率)必然不同。

5. 如计算实际月利率了rEM时，由于一年中按月利率rEM计算12次复利和与按rm/m计算m 次复利的值(名义年利率)是相等的，即(1十 rm/m)m = (1 十rEM)12。则实际月利率rEM的计算公式为: • rEM = (1十rm/m)m/12 -1

6. 特别当m=1时，实际月利率可由年利率r求得： • rEM=(1+r)1/2-1

7. 在Excel中有幂函数POWER可以利用。以上讨论的复利计算，其计息周期都有一定的时间间隔，我们称为间断复利。当复利的时间间隔趋于0，即上式中的m→∞时，则为连续复利，此时 • rEM=

8. 二、PMT函数 • PMT函数是年金函数，在已知期数、利率及现值或终值的条件下，返回年金，即投资(或贷款)的每期付款额(包括本金和利息)。其表达式为： • PMT(rate, nper, pv, fv, type)

9. 三、实际分析

10. 1.等额摊还法。 • 房屋、耐用消费品抵押贷款的分期支付一般是按月等额偿还，但是按年或半年计复利。在金融市场上资金借贷一般使用名义年利率作为利率标价。因此，为计算抵押贷款月等额偿还额，必须先计算出实际月利率，然后按等额摊还方式分摊到每个月支付。

11. 【例2.1】消费者为购买住房而向银行申请住房抵押贷款，以10000元为单位，10年内按月分期等额偿还，若年利率为7%，每半年计复利一次，计算每月的等额偿还额是多少？

14. 2.等本金还款法。 • 与等额摊还法相比，等本金还款法每月的还款额是不一样的，但还款额中所包含的本金偿还是一样的。因此，随着剩余还款期数的减少以及本金的偿还，每月偿还的利息在不断减少，导致每月的还款额也在不断减少，见表2-3：

16. 3.美国的ARM

17. 【例2.2】我们仍然使用例2.1的数据，但前5年的年利率我们按照1%计算，当然并不是银行发善心降低了利率，而是出于扩大市场的需要，提供前5年按揭支付较低的贷款品种。【例2.2】我们仍然使用例2.1的数据，但前5年的年利率我们按照1%计算，当然并不是银行发善心降低了利率，而是出于扩大市场的需要，提供前5年按揭支付较低的贷款品种。

18. ①将前面等额摊还法的工作表复制粘贴过来，见表2-5。在E1:F1两个单元格里约定了前5年计算依据的年利率为1%；在E2:F2两个单元格里约定了最高贷款限额为20%；在E3:F3两个单元格里计算了前5年支付按揭依据的实际月利率为0.08%，这通过在F3单元格里输入计算公式：“= ROUND(POWER(1+Fl/B3, B3/B2) -1, 4)”而完成；在E4: F4两个单元格里计算了前5年实际月还款额为87.43元，这通过在F4单元里输入计算公式：“= ROUND(PMT(F3, D3,-D2), 2)”而完成。

20. ②接下来，在F5单元格里输入：“实际月还款额”，在F6单元格里输入：“=$F$4”；在G5单元格里输入：“实际月还本金”，在G6单元格里输入：“= F6-D6”。这里的含义是：虽然前5年还款额计算依据的年利率是1%，但实际计算每月应还利息仍然是按照前面等额摊还法的算法进行的。而且，虽然实际月还款由115.90元下降到87.43元，但利息的偿还是不会变的，变化的只是本金的偿还降低了速度，由原来每月偿还57.90、58.07元等下降到每月偿还29.43、29.60元等。与原来的等额摊还法相比，相当于在月初本金的序列9942.10、9883.86元等等里面追加了少偿还的本金(E列与G列的差) 28.47元，结果上9970.57、9940.97元等等(见B7、B8等等)。

21. ③5年后的还款需要重新设定，见表2-6。此时，ARM已还本金2104.80元，这通过在I66单元格里输入：“= SUM(G6: G65)”计算而得，而正常情况下此时已还本金4140.89元。此时，ARM未归还的本金为：7895.20元，这通过在K63单元格里输入：“=B66”计算而得，而正常情况下此时未归还的本金只有5859.ll元。我们把表头复制粘贴在H62: K65区域，只将I65单元格里的“贷款年限”改为5年；将K63单元格里的“贷款本金”由10000元改为7895.20元；将K64单元格里的“总还款期数”由120个月修改为60个，此时，K65单元格里的“月还款额”重设为156. 19元，这通过在K65单元格里输入计算公式 “= ROUND(PMT (K62, K64,-K63), 2)”计算可得。

22. ④将ARM的F66单元格里的计算公式：“=$F$4”改为：“=$K$65”，引进了新的还款额，与原来的月还款额87.43元相比，月还款额上涨了78.65%。月还利息仍然与前面的例子相同，不同的是大大加快了偿还本金的速度。虽然ARM改变了原来等额摊还法偿还本金的速度与节奏，但这两个月还款现金流的现值之和是几乎一致的。见表2-7中的H126与I126两个单元格，他们就分别是ARM与等额摊还法的月还款额的现值之和。为了得到这个事实的验证，我们在G列与H列之间插入2个新列，并在H6、I6单元格里分别输入：“=F6/(1+$D$1)^A6”、“=C6/(1+$D$1)^A6”，然后在把鼠标放在H6、I6单元格的右角并出现十字星光标时分别双击生成H、I两列。最后再在H126、I126单元格里分别输入：“=SUM(H6:H125)”、“=SUM(I6:I125)”即可得到两种情况下的月还款现值之和。

23. 【例2.3】如果我们把上面的例子中前5年的月还款额再下降为25元时(30元时都不会触发还款重设)，分析一下在5年期到来之前触动120%的最高贷款限额时的还款设。

24. ①取消E1:F1区域中的“前5年利率”E3:F3区域中的“前5年实际月利率”，并将“前5年实际月还款”硬性规定为25元，见表2-8。此时， G列的实际月还本金出现负数（Negative Amortization），这将影响B列表示的下一期月初本金的数额不再是像前面的例子一样下降而是上升了。当第53期还款结束之后，第54期的月初本金数就会超过本金10000元的120%，此时就需要重新设定还款额了，见表2-9。

27. ②此时，K66单元格里：“=SUM(G6:G58)”的已还本金是负数：-2040.74元，这笔本金累加到原来的本金10000元上就是B59单元格里的数字。此时，我们将M64单元格里表示“总还款期数”的60个月改为67个月， M65单元格里表示月还款额的156.19元自动改为217.40元。可见上原来等额摊还法中的月还款额87.43元相比，月还款额上涨的更多，涨幅达到148.66%,这充分说明了ARM月还款额在还款重设之后的陡然增加，见表2-10。

29. ③最后1期的剩余本金只有216.20元，利息为1.25元，因此，只要归还217.45元即可。此时，还款重设后的月还款额217.40元少还0.05元。与等额摊还法中月还款现金流的现值之和9999.79元相比，第二份ARM的月还款现金流的现值之和为：9999.96元，相差0.17元，误差是非常小的(见表2-11)。