用Excel学理财(5):分期付款

作者: 郑一文 | 来源:发表于2017-06-07 23:54 被阅读266次

    你打算买房,目测资金缺口100万元。现银行贷款年利率(基准利率)4.90%,你准备贷款100万元、等额本息分期付款、30年还清。那么房贷月供得要多少钱?

    这个问题,除了网络各式各样的计算器,还可以用Excel一个公式来解答。

    因为是按月还款,这里得先确定每月还款的利率。根据银行年利率4.90%,房贷月利率就是4.9%除以12等于0.4083%,还款期数是年数30乘以每年12个月即是360期,每次是期初支付。

    在Excel列个表格,一个公式就计算出来。

    如果熟练一点,只要拿到年利率和还款年数的数据,也可以一步计算出来。

    因为是计算按月的分期付款额,所以输入利率和期数必须以月为单位。如果采用的是年利率、年数,计算时相应改为月利率、月期数就可以了。


    这里引用一个Excel函数,PMT函数

    PMT(Returns the periodic payment for an annuity),也有人认为是payment。翻译过来就是在固定利率及等额分期付款的情况下,计算贷款、年金的分期付款额。

    PMT函数语法:PMT(rate, nper,pv, [fv], [type])。这里包括以下参数:

    Rate:贷款利率。

    Nper:该项贷款的付款总数。

    Pv:本金。

    Fv:可忽略不填。一般假设其值为0(零),意思是起始放贷本金和后续的全部分期付款的终值总和是0。(这句话看不懂没关系,以后有篇文章专门解释取值正负的问题。)

    Type:各期的付款时间是在期初还是期末。期初填“1”,期末填“0”或忽略不填。

    这个函数简单好用。不过要注意,一定要贷款利率和期数是对应的。如果要计算按季度的分期付款,那么就要弄清楚季度利率和季度期数。同样道理,按周、按月、按年分期付款,也是如此操作。


    下面来验证一下,文章开头那个例子。为了让童鞋更易理解,这里稍稍作了调整。同样是还款100万、年利率4.9、分30年等额本息每月还款,只不过是期末支付。意思是从第一笔月供开始,每一次还款里面都有偿还利息和本金。这样调整之后,每月还款额为PMT(4.9%/12,30*12,-1000000,,)= 5307元。

    我们列个表格,看看每月还款5307元、还足30年,是否能还清贷款。(表格隐藏第6期至第355期的还款情况。)

    从这个表格可以看到,每月还款额包括偿还的利息和本金。刚开始等额本息的月供主要用于偿还利息,随着贷款本金逐渐减少,后期以偿还本金为主。一直还款到360期,这系列还款才把100万贷款本金全部还清。

    有兴趣的童鞋,可以算一下,未来360期还款额,以0.4083%月利率贴现计算现值,看看是否等于100万。(要用之前学过的PV函数来计算,而不是简单粗暴把每期月供金额加起来哦。)


    PMT函数除了可以计算贷款分期付款问题,还可以计算储蓄问题。

    例如,我想在1年时间存下一笔家庭旅游基金,用于未来一家人出国旅游。我设定目标为2万元,打算每月存入一笔钱、放进年化收益率8%的理财产品里,等存够了就取出来。那么,从这个月初开始,每个月我得存多少钱?

    我列一个Excel表格,看看用PMT函数怎么计算这个问题。

    咦,好像跟文章开头那个例子有点不一样哦。怎么B2格跑到后面去了呢?回忆一下PMT函数的语法,PMT(rate, nper, pv, [fv], [type])。我这次要存2万块钱,是一系列每月存款在1年后的总价值,也就是“终值”(FV)。因为我是每月存款、放进理财坐等赚利息,所以我不需要2万元除以12个月每个月存1667元,而是每个月存1596元、存够12个月在未来收割2万元。这个存款目标,是终值,应当放在函数第四个参数位置。

    不相信的童鞋不要着急,下面我列表展示这个存款方案,最终肯定可以存够2万元。


    总结

    1.等额本息的分期付款问题,可用PMT函数解决。

    2.PMT函数语法:PMT(rate,

    nper, pv, [fv], [type])。不论按周、按月、按季度还是按年计算,参数利率、期数必须相对应。

    3.PMT函数还可以解决等额储蓄额的问题。不过储蓄目标值要视为终值,填写在函数第四个位置。

    作业

    小A刚刚步入职场,她打算工作5年后报读MBA课程。假设MBA课程在未来5年都不会调整学费,学费是每年10万元、总共2年,学费需要每年年初支付。小A打算从今年起每个月从薪水里扣20%来存这笔学费。小A的劳动合同上面写得很清楚,第一年试用期月薪是3000元,转正后头三年的月薪分别为3500、4000和5000元,到了第五年达到小主管级别月薪可望涨至10000元。

    小A找到一款靠谱的中长期理财产品,年化收益率为8%,可接受每月小额存款。

    (1)按照小A的计划,她可否在工作第五年末存够读MBA的所有学费。

    (2)小A发现她无法存够学费,但她依然想读MBA。小A打算MBA学费全额贷款,贷款利率为6%,贷款自入学时发放,头两年(读书期间)免息、期间可选择暂时不还,贷款要求五年内全部还清。小A算了一下,她读书期间没工作收入、暂时不还,等MBA毕业后工作才开始每月等额本息偿还贷款。那么,小A最低还款额是多少?

    备注:本课示例和答案的EXCEL表格,可在百度网盘http://pan.baidu.com/s/1miE4Y7Y

    下载,或在百度网盘搜索用户“ppathome39”《用EXCEL学理财-示例及作业(更新到第5课)》。

    相关文章

      网友评论

        本文标题:用Excel学理财(5):分期付款

        本文链接:https://www.haomeiwen.com/subject/iuqzfxtx.html