Excel財務函數補充說明

0731

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財務函數的應用，會有更一層的了解，相望大家會喜歡。





http://oursogo.com/home.php?mod= ... ward=1&id=28861



↑我日記有圖表顯示

期貨大昌莊渝程

先收下了~謝謝分享~對於EXCEL的函數有些真的是不太親民

dududu1019

真是專業~~

應該是校園中的精英份子

josman1234

專業的 太厲害了 讚讚讚