用Excel判断某款理财产品是否值得购买(上)

编按:

Excel提供了丰富的财务函数,可以将原本复杂的计算过程变得简单,方便大家核算投资、贷款,判定某投资、贷款是否合理。下面让我们来一起看看。

一、FV()函数:计算当前投资在未来产生多大收益
问1:
你2020年8月一次性借给朋友100万,对方承诺给你每年6%的利息,利滚利,10年满期后归还。到期你连本带利一共收回多少钱?
问2:
你从2020年8月起10年内每月初借给你朋友1万,对方承诺给你每年6%的利息,利滚利,10年期满后归还,到期你连本带利一共能收回多少钱?
这类问题就是求当前投资的未来收益。如果利率是固定的,分期投资的金额也是固定的,则可以用FV函数快速计算出未来的收益,即终值。
该函数语法结构:FV(rate,nper,pmt,[PV],[type])
l  rate:必需的参数,表示固定利率。通常用年表示利率,如果是按月利率,则利率应为年利率/12,如果指定为负数,返回错误值“#NUM!”。
l  nper:必需的参数,表示总的付款期数。如果利率是年利率,这是就用年数;如果利率是月利率,这里就用月数。也就是rate和nper要对应。如果指定为负数,返回错误值“#NUM!”。
l  pmt:必须的参数,各期固定支付的金额。如果省略pmt,或为0,则必须包含后面的pv参数。
l  pv:可选参数,表示投资开始的一次性支出金额。如果省略pv,或为0,则必须包括pmt参数。
l  type:可选参数,表示支付是期初还是期末,期末为0,期初为1。
下面我们就来看看前面的两个问题的收入各是多少。
答1:
显然这是个一次性投资,所以pmt(每期支付金额)是0,pv(一次性支出金额)是-100万(因为是支出所以是负数)。一次性支付,到期还款,支付类型type为1。到期后的收益=FV(B1,B2,B3,B4,B5)=1,790,847.7
答2:
这是一个分期投资,月利率=6%/12=0.5%,期限=10*12=120,pmt(每期支付金额)是-1万,pv(一次性支出金额)是0。每月月初支付,所以支付类型type是1。到期后的收益=FV(B1,B2,B3,B4,B5)=1,646,987.44。
二、PV()函数:计算未来的一笔收益需要投入多少
问3:
你想把钱借给朋友来获取利润。朋友承诺给你每年6%的利息,利滚利,10年满期后一次性给你1,790,847.7元。那现在你应该一次性拿出多少钱给朋友?
问4:
你朋友向你借钱。承诺给你每年6%的利息,采用每月月初支付1万元(包含了本金和利息)的方式向你还款,一共还10年。那你现在需要一次性拿多少钱给朋友是合理的?
这两个问题可以看做是前述问题1、2的逆向问题:根据未来收益反算当前需要的投入金额。
固定利率、每期获利固定,在Excel中可以用PV函数计算出当前需要一次性投入多少,即现值。
函数语法结构:PV(rate,nper,pmt,[fv],[type])。
rate,nper,pmt,type参数与fv()函数代表意义一致。剩下的fv为可选参数,是投资到期的最末一笔收益。如果省略fv,则表示其值为0,这时必须包括pmt(每期收入金额)参数。
答3:
收益会在投资的最后一次性收回。所以fv(到期最后收益)为1,790,847.7元,pmt(每期收入金额)为0。因为是到期一次性收本金利息,所以类型是0(其实,这个时候不管是0还是1,结果都一样)。你一次性借出的金额=FV(B1,B2,B3,B4,B5)=-1,000,000。因为是支出,所以是负数。
答4:
收益会在120月内每月等额获得,并且没有最后的一次性收入。所以fv(到期最后收益)为0,pmt(每期收入金额)为10,000。因为是每月月初获得收入,所以类型是1。你应该一次性借出的金额=FV(B1,B2,B3,B4,B5)=-905,238.21
同样的收益条件,如果你朋友让你出借95万,那你就亏了;如果你朋友让出借85万,那你就多赚了。
在问4的基础上,如果朋友答应除每月支付1万外,最后还会一次性给你10万。那你该借给朋友多少呢?用PV函数很容易算出应该支付约96万。
有朋友可能会投资购买年金,如果是每期等额返回金额给你,就类似问题4,可以用PV函数计算是否值得购买。譬如,如果根据对方提供的收益计划算出来到期整个年金的现值(就是你为获得收益当前应该支出的金额)是30,000元,而对方报价是35,000元,那就不值得购买。
三、RATE()函数:利率判断贷款或投资是否划算 
问5:
公司老板为一个项目计划向某机构贷款100,000元,机构同意贷款,要求公司每月还款2,600元,在4年内还完整个贷款。这笔贷款划算吗?
问6:
银行向你推销理财产品。要求你第一次向银行存入3,500元,以后每月都存入银行600元,连续存10年,最终银行一次性给你100,000元。这款理财产品适合买吗?
毫无疑问,只要算出利率,然后用计算出的利率与市场上同类贷款或产品的利率进行一番比较,就知道是否划算。
在贷款(投资)期限固定、每期还款(投资)额固定、贷款总额(投资总收入)固定的情况下,计算贷款(投资)利率可以用RATE函数。
RATE函数语法结构:RATE(nper,pmt,pv,[fv],[type], [guess])。
此处nper,pmt,pv,fv,type参数和前面FV、PV函数的参数意义一致。guess参数为可选参数,表示一个预估的利率,通常省略。只有当RATE函数结果不收敛,返回错误值“#NUM!”的时候,才设置此值。
答5:
nper(贷款期数)是4*12=48个月,pmt(每期还款金额)是-2,600元,pv(贷款金额)是10万。由于是每月末还款,所以type(类型)为缺省(0)。用RATE函数计算得出贷款的月利率为0.94%。
年利率为0.94*12=11.28%。2020年企业银行贷款3~5年期年利率为4.75%,即便上浮,应该不超过8.1%。如果不是无法向银行贷款,不是亟需用这100,000元,你可以建议老板不要贷这笔款。
答6:
这是生活中常见的理财问题。nper(投资期数)是10*12=120个月,pmt(每期投资金额)是-600元,pv(第一次一次性存入金额)是-3,500元,fv(到期收益)为100,000元。因为是每月初投入,type选1。经过RATE函数计算,该理财产品的月利率为0.43%,年利率为0.43*12=5.16%。
年利率为5.16%,考虑到平均通货膨胀率3%左右,实际利率只有2%,因此不建议购买。当然,如果没有其他投资渠道,该理财产品又比单纯存银行收益要高,可以购买。
四、NPER()函数:计算还款或者投资时长 
问7:
你想购房,计划向银行贷款80万元。打算以等额本息方法分期偿还,根据家庭收入情况,你每月最多可偿还3,500元,银行年利率为5.39%,这笔贷款需要还多久?
问8:
你打算做一笔投资。投资条件是首笔投资5万元,每月初追加投资5,000元,投资汇报月利率是0.82%。如果你想最终获得45万的受益,你需要投资多久?
这种利率固定,每月还款或者投入金额固定,贷款总金额或投资总收益固定的情况,求还款或投资期数,可以使用NPER()函数。
该函数语法结构:NPER(rate,pmt,pv,[fv],[type])。rate,pmt,pv,fv,type和上面的参数代表意义一致。
答7:
这是很常见的贷款买房的问题。rate(月利率)等于5.39%/12=0.45% ;pmt(每期还款金额)是-3,500(支付为负)元,pv(贷款总额)是800,000元,fv(到期余额)为0,每个月月末还款,type为0或缺省。在B5单元格输入函数公式=NPER(B1,B2,B3,B4,0),回车确定,得到的是错误值。
这是为什么?
是你想得太美!贷款80万,月利息0.45%,第一月的利息就有3,600元,而你只还3,500元,这样下来永远也还不完。银行能借给你吗?
假如你每月能还5,500元,则还款月数约236月。
答8:
rate(月利率)为0.82%,pmt(每月投入)-5,000元,pv(首笔投入)-50,000,fv(未来收益)45,0000,type为1。输入公式=(B1,B2,B3,B4,1),得到需要持续投入约58月。
扫二维码免费学Excel等视频

让工作提速百倍的「Excel极速贯通班」

(0)

相关推荐