Excel財務函數補充說明

日期:2015-03-12

許多人面對Excel財務函數的應用,往往不知道TYPE變數該填0(期末)還是1(期初),為解決使用者這方面困擾,本文針對TYPE變數做了更詳細的說明。

FV、 PV、PMT、NPER、RATE是Excel很重要的5個財務函數,函數的名稱其實也可以是變數,因為這5個變數依貨幣時間價值得到一個方程式,只要知道其中4個變數,就可以得到另一個變數的解。函數名稱乾脆就用求解的變數命名。例如預估某項投資的未來淨值就用FV函數,那麼就得知道PV、PMT、NPER、RATE這些變數。或者求貸款專案的每月本息繳款金額,可用PMT函數,那麼其他4個變數就必須是已知。

舉一個典型的應用範例,期初投入10萬元(PV),且每隔一個月再投入1萬元(PMT),於年平均報酬率5.0% (RATE)的基金,經過5年(NPER)後,期末淨值(FV)會是多少?這例子中PV、PMT、NPER、RATE都是已知,所以就可以用FV函數求解。

圖一

圖一為這專案的現金流量圖,Excel這5個函數都是以一期為計算單位,至於一期多久,可以自行依需求制訂,例如一年、半年或一個月。因為本例的現金流量最小期間是一個月,所以必須以一個月為一期,而期間為5年,所以總期數NPER為60。

至於每個月再投入的1萬元,並沒有明白的敘述投入時間點,是每一期的期初或是期末,而這兩種型態PMT的每一筆現金流量,剛好都差了1期,所以必須精準描述,否則算出來的結果是不正確的。

PMT有多少筆金額跟NPER息息相關,PMT為-1萬元(負值為現金流出)、NPER為60個月,代表著每一月都會有支付1萬元,總共有60萬。但這樣描述還不夠,必須加入TYPE變數才完整,這60萬投入的時間點,是每一期的期初還是期末呢,是由TYPE決定的,0代表期末(預設值)而1代表期初。圖一左邊期末,右邊是期初。表一列出了兩種模式的計算公式及結果。

表一

Type 公式
FV(rate, nper, pmt, [pv], [type])
結果
0:期末 =FV(5%/12, 60, -10000, -100000, 0)

=FV(5%/12, 60, -10000, -100000)
808,396.70
1:期初 =FV(5%/12, 60, -10000, -100000, 1) 811,230.28

要特別注意,期初不是月初,期末也不是月底。一期的起始點是以專案生效日起算,也是第一期的期初,一期的結束點為期末,而每一期的期末就是下一期的期初,例如第1期末等於第2期初,第10期初也等於第9期末。有時候也會以契約生效日為第一期的期初,例如銀行貸款或保險。

銀行貸款之每月繳款金額

用範例來解說更清楚,有一銀行貸款專案,貸款金額100萬元,年利率5.0%,分36月本息平均攤還,每月繳款金額為多少?

本金及利息是撥款之後起算,每一個月後繳交一次。所以銀行撥款日也是第一期的期初。若撥款日為2/10,那麼第一期末就是3/10,第二期末為4/10依此類推。現金流量如圖二所示:

圖二

因為這應用要求解的是每月繳款(PMT),所以用PMT函數來計算,公式如下:

=PMT(rate, nper, pv, [fv], [type])

中括弧的fv及type若為0可以省略。

此專案最小間距為一個月,所以每月為一期,撥款日開始生效,所以每月本息的繳款時間點都落在期末,所以type必須設定為0,因為fv及type都是預設值0,所以可以省略。因此公式如下:

=PMT(5%/12, 36, -1000000)
=29,971

六年期儲蓄險之利率計算

另一個應用是100萬六年期儲蓄險,年繳保費15萬4,876元,這保險相當於銀行多少的年利率?儲蓄險的現金流量如圖三所示:

圖三

要求解的是利率(RATE),所以用Excel的RATE函數,其他四個變數FV、 PV、PMT、NPER必須已知才行,RATE函數格式如下:

=RATE(nper, pmt, pv, [fv], [type], [guess])

契約開始生效時就是第一期的期初,第一期保費於投保時就得交付,所以屬於期初年金,TYPE必需設為1。現金流量的最短間距為一年,所以每一年為一期,也就是NPER等於6。年利率計算結果如下:

=RATE(6, -154876, 0, 1000000, 1)
=2.1%

透過範例的解說,相信讀者對Excel財務函數的應用,會有更一層的了解,相望大家會喜歡。



怪老子新文章《Excel財務函數補充說明》

Posted by 怪老子理財 on 2015年3月12日

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