用Excel計算成長型年金

日期:2015-04-03

Excel 的FV及PV財務函數,是以輸入之參數描述現金流量,每種參數都只有一個,對pv或fv單筆金額的參數不會有問題,但pmt參數因為屬於年金,每一期都有現金流量,所以每一期的pmt都必須一樣才適用。而且,pmt參數和nper(期數)及type參數一起才能完整描述現金流量。

例如某財務應用的現金流量如圖一所示,總共有5期,在每一期的期末都有10,000元收入。所以這財務應用的現金流量參數設定如下:

pmt=10000、nper=5、type=0

雖然在計算PV及FV時,每一期的pmt都必須一樣才可以使用。但是當pmt的現金流量以等比例(g)往上成長或往下衰退時也可以使用,只是報酬率比需使用有效報酬率(r-g)/(1+g),其中r為報酬率。除了報酬率必須更換之外,對於不同型態的pmt年金(期初或期末),會有些微的調整。對於想進一步了解為何這樣的讀者,本文也有PV公式的推導,FV的部分就留給讀者自行練習。不喜歡數學的投資者,使用下表整理好的公式即可。

表一:年金成長型態的PV及FV公式應用 (pmt年金、r報酬率、g成長率、nper期數)

函數 期末(type=0) 期初(type=1) 備註
PV =PV((r-g)/(1+g), nper, pmt)/(1+g) =PV((r-g)/(1+g), nper, pmt, 0, 1) fv=0
FV =FV((r-g)/(1+g), nper, pmt) *(1+g)^(nper-1) =FV((r-g)/(1+g), nper, pmt, 0, 1) *(1+g)^nper pv=0

範例一:退休金

Peter預計60歲退休,退休後生活費預估每年60萬元,假設退休後報酬率每年6.0%,通貨膨漲率2.0%,要準備多少退休金才足以使用至100歲?(參考投影片http://goo.gl/4KqNhK第3~4頁)

現金流量總共有40筆(61~100歲),生活費不會每年固定60萬,而是每年以2.0%的通貨膨漲率成長。那麼退休準備金所需金額,就是未來所有現金流量的現值,可使用PV函數計算,但必須使用實際報酬率(6%-2%)/(1+2%),且因為屬於期末年金,所以PV函數的數值還必須除上(1+g),所以得準備1,177萬9,943元才足夠,公式如下:

=PV((6%-2%)/(1+2%), 40, 600000)/(1+2%)
=-1,177萬9,943元 (負值代表現金流出)

更精確一點,年初及年末的生活費應該不一樣,因為前後剛好差了一年的通膨,所以最好的方式就是用年中的金額,也就是多了半期的通貨膨漲率才更精準,就是起始值為60萬乘上(1+2%)^0.5,所以:

=PV((6%-2%)/(1+2%), 40, 600000*(1+2%)^0.5)/(1+2%)
=-1,189萬7,160元 (負值代表現金流出)

範例二:定期不定額投資

投資全球已開發市場股票型基金,第一年底投入金額12萬,且金額每年以3%成長,若平均投資報酬率為10%,10年後的基金淨值應該是多少?

=FV((10%-3%)/(1+3%),10,-120000)*(1+3%)^9
=214萬2,559元

公式推導

PV函數:期末年金

圖一:PMT現金流量,每期金額相同,發生於期末

Excel的PV函數對期末年金PMT(type=0)的計算公式如下:

圖二:PMT現金流量,每期成長率g,發生於期末

成長型PMT(type=0)使用PV函數公式推導:

PV函數:期初年金

圖三:PMT現金流量,每期金額相同,發生於期初

Excel的PV函數對期初年金PMT(type=1)的計算公式如下:

圖四:PMT現金流量,每期成長率g,發生於期初

成長型PMT(type=1)使用PV函數公式推導:



怪老子新文章《用Excel計算成長型年金》投資全球已開發市場股票型基金,第一年底投入金額12萬,且金額每年以3%成長,若平均投資報酬率為10%,10年後的基金淨值應該是多少?...http://www.masterhsiao.com.tw/CatExcel/Growth/Growth.php

Posted by 怪老子理財 on 2015年4月7日

■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。