在Excel中计算复利,居然一个函数全搞定!!!

 

今天,我和大家聊聊复利的计算。首先,我们应该先了解下复利和单利的区别。

 

你手上有2万块钱,在银行存了10年的定期,年利率按照3.5%计算,10年到期后按照20000*3.5%*10计算你应得的利息,这种计算方式就叫单利。也就是说,单利是指一笔钱无论存多久,都只有最初的本金在计算利息。

 

相比之下,假如还是2万块钱,年利率还是3.5%,还是存10年,第一年的利息是20000*3.5%,第二年初的本金变成了20000+20000*3.5%,第二年的利息就变成了(20000+20000*3.5%)*3.5%,第三年初的本金变成了20000+20000*3.5%+(20000+20000*3.5%)*3.5%,依次类推,这种计算方式就叫复利。也就是说,复利是指把上一期的利息也作为下一期的本金来计算。

 

上面介绍的就是单利和复利的概念。

 

来到Excel,如下图所示,我们可以完全照搬上面介绍的计算方式,用公式计算每个年度的本金、利息,然后在D4单元格将本金和利息相加求出对应的每个年度末的本息和。

当然我们也可以直接在E4单元格输入公式
=$B$1*(1+$B$2)^A4
然后填充下来直接求出复利下每个年度末的本息和,如下所示
上述介绍的是只在期初投入了本金,后期都不再追加投入本金的情况下计算复利。
现在我们把问题升级,假设第一年投入2万元,第二年又投入了2万元,然后每年都等额追加投入2万元。这时候的复利应该如何计算呢?
我们还是跟上面的分析一样,先用最浅显易懂的方式来计算:
第一年末的本息和是:20000*(1+3.5%)
第二年末的本息和是:20000*(1+3.5%)^2+20000*(1+3.5%)
第三年末的本息和是:20000*(1+3.5%)^3+20000*(1+3.5%)^2+20000*(1+3.5%)
第四年末的本息和是:20000*(1+3.5%)^4+20000*(1+3.5%)^3+20000*(1+3.5%)^2+20000*(1+3.5%)
以此类推第十年末的本息和是:20000*(1+3.5%)^10+20000*(1+3.5%)^9+20000*(1+3.5%)^8+20000*(1+3.5%)^7+…+20000*(1+3.5%)^2+20000*(1+3.5%)
看到上面的分析,如果要在Excel中求等额追加下的10年复利本息和,需要写这么长的公式,而且还不方便用一个公式直接拖拉计算。
实际上,Excel中还内置了一个函数,可以直接计算上述两种复利。
1.计算不追加的复利本息和
直接在F4单元格输入函数公式
=FV($B$2,A4,,-$B$1)
下拉即可
如下所示,结果和用公式=$B$1*(1+$B$2)^A4计算的一模一样。
2.计算等额追加下的复利本息和
直接在F4单元格中输入公式
=FV($B$2,A4,-$B$1,,1)
下拉就可以了,如下所示
同样的一个函数,通过参数的变化,就解决了两个看似繁琐的复利计算,真的是非常神奇。
接下来,我给大家解释下这其中的原理,FV函数的的语法如下
FV(rate,nper,pmt,[pv],[type])
FV函数不仅可以用于计算投资收益,还可以用于计算贷款支出。
当FV函数用于计算投资收益时,第一个参数rate表示利率,第二个参数nper表示总期数,第三个参数pmt表示每期都要投入的钱,第四个参数表示最初投入的钱,第五个参数type表示计算收益的方式是在期初还是期末,0表示在期初,1表示在期末,默认是0。
通过这样的解释,当我们在计算不追加的复利本息和时,函数公式
=FV($B$2,A4,0,-$B$1)
的第一个参数是年利率,第二个参数是对应的投资年份,第三个参数省略,因为只在期初投入了本金,不是每个年度都投入本金,第四个参数是期初投入的本金,这里用负数表示求的是投资收益,而不是贷款支出。
当我们在计算计算等额追加的复利本息和时,函数公式
=FV($B$2,A4,-$B$1,,1)
的第一个参数是年利率,第二个参数是对应的投资年份,第三个参数是每个年度都等额追加的本金,第四个参数省略,表示不是只在期初投入了本金,第五个参数指定是在每个年度的末计算复利本息和。
通过上述的介绍,相信您会对复利的概念和复利的计算有个更深入的了解。
爱因斯坦说过
复利是世界上第八大奇迹,它的威力甚至超过了原子弹
在我们的生活中,复利的例子随处可见,你如果去接高利贷,对方往往是用复利给你计算利息,如果我们有买年金或者理财险,保险计算收益也是用复利的方式。
还等什么呢?赶紧打开Excel算算投入多少钱才能复利到1个亿吧:)
       

发表评论