凤凰平台注册开户_凤凰彩票app下载安装_凤凰彩票投注网

热门关键词: 凤凰平台注册开户,凤凰彩票app下载安装,凤凰彩票投注网

excel表格怎么利用PMT公式,分期付款

作者: 凤凰彩票投注网  发布:2019-11-17

问题:excel表格怎么使用PMT公式?

商业贷款,都会清晰地告诉你这笔贷款每月还款额、总共要还多少期。

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

回答:

但假如你每年最多能还款X元,在申请贷款之前你得考虑还清贷款需要多长时间。又比如,背负房贷的你想向银行申请提高还款额,这时候你得考虑得多长时间承受这样的月供。

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

pmt函数是excel提供的财务函数之一,其用途是:基于固定利率及等额分期付款方式,返回贷款的每期还款额,注释为:=PMT(利率,支付总期数,现值,[终值],[是否期初支付])

Excel有一款函数,可以解决上述问题。

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

根据pmt函数制作房贷还款计划表

先来看一个简单的例子。

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

(1)建立如下图所示的excel表,分别输入相应公式,并下拉填充。C3单元格中为pmt公式计算在固定利率下,贷款的等额分期偿还额,利率和支付总期数应相互对应,保持一致。

假设今年年初你因为做生意从亲戚那里借了10万元,利率10%。亲戚许诺,你只需要每年年终时归还当年的利息,如有能力时再偿还本金。你估算一下,每年最多可以拿出1.5万元用于还款。在接受这笔借款之前,你需要知道偿还这笔钱需要多久?

图片 1

图片 2

用NPER函数可以计算出来。

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

(2)10年120个月,剩余本金变为0,房贷还清啦!

图片 3

图片 4

图片 5

用NPER函数算出来,需要11.53年。后面再解释这个函数怎么用。我们先来验证一下这个结果是否正确。

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

图片 6


将每年产生的利息和还款额列表。上图显示,到了第12年,剩余的本金7344元和利息734元,低于还款额15000元。也即是说,还款需时11年,但不足12年。

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

这个验证结果,跟用NPER函数计算的一致。

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

NPER函数,total number of

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

periods,意思是“总期数”。NPER取自number ofperiods缩写。这个函数的语法是(rate,pmt,pv,[fv],[type])。具有下列参数:

Rate:贷款利率。

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

Pv:本金。

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

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

Rate利率。

Pmt各期所应支付的金额。

Pv现值,或一系列未来付款的当前值的累积和。

Fv选填。終值,或在最后一次付款后希望得到的现金余额。例如,一笔贷款的终值即为0。

Type选填。数字0或1,用以指定各期的付款时间是在期初还是期末。

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

从参数的含义可以看出,NPER函数是在固定利率及等额分期付款方式下,计算某项贷款/投资的总期数。


像上面的偿还亲戚的借贷,最终贷款是要还清、终值为0。如果最终只是偿还一部分贷款、将终值定为某个数,一样可以用NPER函数计算总期数。

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

在房贷提前还款问题上,用NPER函数计算还款时间就有意义。

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

虽然很多理财专家提倡不要提前还款,因为随着时间推移、货币会贬值,偿还月供的压力会越来越小。但实际上,很多人还是会耐不住性子想早点还清贷款。

图片 7

比方说你在5年前买了房子,总贷款30万元、共30年还清。为了简化计算,这里展示每年等额本息还款,银行贷款利率6%。

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

图片 8

图片 9

还记得之前介绍的等额分期付款的公式吗?用PMT函数,计算出30年等额本息还款额。

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

因为只还了5年,所以上图仅列出头5年的利息和账户金额。这时候,你的房贷本金剩下27.8万元。你现在的经济能力提升了,你想向银行申请每年偿还5万元,那么还需要还多少年?


图片 10

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

经计算,还需要还款约7年。

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

这里解释一下,为什么函数里面会有负值。贷款是现金流入你的账户,但还款是从你的口袋流走,现金的流向是相反的。因此,每期还款额和贷款总额必须有一个是负值。至于哪个取负值,视乎计算者是从哪个角度看待问题。从还款者角度,计算公式就是上述写法。

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

之前介绍的FV函数可以一步计算5年后剩余待偿还本金。具体算法可参照本课作业。

图片 11

实际房贷还款,是采用每月还款。要计算多久还清,一样可以用NPER函数。只不过要将利率改写成月利率,还款期数按月计算。

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

总结:

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

1.计算一笔贷款需要多久时间还清,可用NPER函数。

图片 12

2.NPER函数的语法是(rate,pmt,pv,[fv],[type])。在计算贷款问题上,一般取终值为0。


作业:

总结

你打算买一部新车,车身价为15万元。车行提供全额贷款,优惠利率低至0.6%/月。你评估自己的还款能力,每个月最多能用3000元换车贷。

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

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

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

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

如果买车的15万元采用车行贷款,你需要还款多少年(注意先计算还款总月数,再转换成年数。)

作业

按上述方式还了12个月后,你看中另一款新车。你打算申请提高每月还款额为5000元,看能不能2年内还清。

小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表格,可在百度网盘

备注:本课示例和答案的EXCEL表格,可在百度网盘

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

图片 13

图片 14

本文由凤凰平台注册开户发布于凤凰彩票投注网,转载请注明出处:excel表格怎么利用PMT公式,分期付款

关键词: