EXCEL RATE函数详解

EXCEL RATE函数详解目 录EXCEL函数详解1EXCEL函数-RATE利率1RATE与其他函数之关系2公式2现金流向2PV、PMT相同流向3PV、PMT流向相反4Excel RATE函数5RATE的参数6范例6基金年化报酬率(一)6基金年化报酬率(二)7房屋贷款8EXCEL函数-RATE利率我们生活周遭充满了跟利率或报酬率相关的例子，举凡房屋贷款、基金报酬率以及储蓄险，全部都跟报酬率有关。例如房屋贷款100万、20年期，银行跟我说每月得缴款6,600元，那么年利率到底是多少呢？投资基金也是另一个例子：3年前买了一只股票基金，现在有20%的报酬率，那相当于每年几趴的报酬率呢？Excel提供了一个全能的函数，可以立即得到答案。RATE与其他函数之关系了解RATE函数之前，得先了解RATE跟其他函数之间的关系。Excel提供了五个非常实用的财务函数：PV(现值)、PMT(年金)、FV(未来值或终值)、RATE(利率)、NPER(期数。每个函数相互之间是息息相关的，也可以说连成一体。只要知道任何中四个，便可求出另外的那一个。那么函数之间的关系又是如何？是如何互动的。从观念上来看，这几个函数架构了一个平衡系统，可以用一个跷跷板的概念(如下图)清楚表达出来。跷跷板的左边有刻度，NPER就是分成几段的意思，RATE决定了每一刻度的长度。那么跷跷板位置又如何分配呢？左边给PV(单笔现值)及PMT(年金的每期金额)，右边就是FV(未来值或终值)。当期数愈多、RATE愈大，代表左边长度愈长，也就是杠杆愈大。当然这杠杆代表着复利的意义。只要长度够长，小小的PV反应在跷跷板右边的力量就很大了。整个概念就是：RATE及NPER决定左边跷跷板的刻度及长度，当PV、PMT及FV坐上去时必须维持平衡。公式PV、PMT、FV、RATE、NPER之间的关系，若以数学公式表达如下：现金流向PV、PMT以及FV的现金流向都是有方向性的，现金流出以及现金流入。在图上是以箭头向上及向下来表示，只是箭头方向在跷跷板两边刚好相反，如下表：箭头向下现金流入现金流出箭头向上现金流出现金流入这又是何原因，跷跷板左边箭头往下代表现金流入，此时反应在跷跷板右边会往上跷，若是要跷跷板平衡，右边就必须有往下的力量，也就是现金流出(左边现金流入，右边就应该是现金流出才会两边平衡)。所以跷跷板右边箭头往下代表现金流出，箭头往上代表现金流入。也就是箭头方向位于跷跷板两边刚好流向相反。PV、PMT相同流向接着看看PV及PMT的方向。PV、PMT方向若相同，两者力量是相加的，反应在跷跷板右边的力量当然也是相加。只是当PV及PMT都往上时，那跷跷板是相反的，那就请读者运动运动，倒立着看这图了。PV、PMT流向相反PV、PMT方向若相反，两者力量是相减的，反应在跷跷板右边的力量当然也是变小了。而且不只FV会比PV、PMT任一个还小，方向还得看哪个大。当然当FV等于零时，代表PV及PMT刚好力量两相抵消，刚好是平衡的。Excel RATE函数有了这些基本观念，接下来就简单了。RATE函数就是传回：已知的期数(NPER)的情况下，多少的期利率会使得PV、PMT及FV相互得到平衡。RATE的参数RATE函数的参数定义如下：=RATE(nper, pmt, pv,fv, type, guess)下表列出了参数的意义，前面三项参数 nper、pmt、pv，是一定要有的参数不可以省略，后面3项fv、type以及guess是可有可无。nper期数必要pmt年金之每期金额必要pv单笔之期初金额必要fv单笔之期末金额选项type年金发生于期初或期末0：期末(预设)1：期初选项guess猜测利率可能之落点选项type参数说明年金是发生于期末(默认值)或期初，若没有输入，RATE函数会采用默认值 type = 0(期末)。fv是期末终值(默认值 = 0)，至于guess这参数就得费点功夫说明。guess真是个有趣的参数，RATE函数的任务不就是要解出报酬率的值吗，怎会要我们自己猜测报酬率的落点呢？这不是很奇怪吗，Excel计算功能那么强，难道RATE函数无法直接解出来？没错，如果看看上面的公式，当PV、PMT、FV、NPER为已知时，看如何直接解出RATE的值？这就难了，还好虽然无法直接求解，Excel使用代入逼近法，先假设一个可能的rate(默认值为10%)，然后代入上面式子看看是否吻合，如果不是就变动rate的值，然后慢慢逼近、反复计算，直到误差小于 0.00001 为止。如果真正的解和默认值差距过远，运算超过20次还是无法求得答案，RATE 函数会传回错误值 #NUM!。这时使用者就必须使用较接近的 guess 值，然后再试一次。所以guess参数只是RATE函数开始寻找答案的起始点而已，跟找到的答案是无关。例如下面范例基金年化报酬率下面三个RATE公式，其他参数都一样，但是guess都不同，结果答案却都一样是4.14%。=RATE(10, 0, -100000, 150000, 0,10%)=RATE(10, 0, -100000, 150000, 0,8%)=RATE(10, 0, -100000, 150000, 0,4%)范例基金年化报酬率(一)Peter于10年前，以10万元买了一个基金，现在该基金净值15万元，请问这样相当于多少的年报酬率？以这例子每年为一期，PV=-100,000，NPER =10，PMT=0，FV =150,000。=RATE(10, 0, -100000, 150000, 0, 10%)= 4.14%基金年化报酬率(二)Peter于10年前，以10万元买了一个基金，而且每月定期定额2,000元买相同之基金，现在该基金净值65万元，请问这样相当于多少的年报酬率？以这例子每月为一期，PV=-100,000，PMT=-2,000，NPER =120，FV =650,000。=RATE(120, -2000, -100000, 650000, 0, 1%)*12=9.4%注：因为这例子每月为一期，所以RATE是传回月报酬率，年报酬率必须再乘上12。房屋贷款Susan向银行贷款100万元，期限20年，每月本息摊还6,600元，问这贷款年