現金流量型態

EXCEL有五大財務函數，分別為FV、PV、PMT、RATE、NPER，而這五個函數之間都有相互關聯。整體概念如下：FV、PV及PMT分別描述一組現金流量的大小及發生時間點，PV是發生在期初的單筆現金流量，FV是發生在期末的單筆現金流量，PMT則是發生在每一期的重複性現金流量。而NPER函數描述這現金流量的期數。因貨幣具有時間價值，RATE函數是描述這些現金流量之間的報酬率關係。

公式

這一組現金流量相互之間，維持一個平衡及對等的關係，其關係式如下所示，只要知道其中四個參數，當然就可以計算出剩下的那一個。

要學會如何活用這五大函數，只要學會如何描述一組現金流量，也就是說只要將所碰到的財務問題，用現金流量的型態呈現出來，然後用這些函數的參數輸入，就可以得到所要的答案。不過這五個財務函數並不是每一種現金流量的型態都可適用，必須符合下列條件的現金流量才可以使用：

• PV：單筆金額，只有在期初發生一次
• FV：單筆金額，只有在期末發生一次
• PMT：每一期都會發生的年金，不可以某些期數有，某些期數沒有，而且金額大小都必須一致

PV、FV及PMT都可以是正值或負值，分別代表現金流量之方向，水平軸以下的為負值，代表是現金流出。水平軸以上的為正值，代表是現金流入。再加上期數NPER，以及適用的報酬率RATE，共同組合成這五大函數。

期初年金

PMT所發生的時間點，有期初及期末之分，都在每一期末的就稱為普通年金，type = 0、也是預設值。發生在每一期的期初，就稱為期初年金，type的值必須設為1。現金流量如下圖所示：

回目錄

未來值函數FV

FV函數主要用來計算未來值，只要將希望得到的現金流量之金額大小及發生的時間點，以rate, nper, pmt, pv, type參數描述，就可以得到未來值的金額，格式如下：

FV(rate, nper, pmt, [pv], [type])

參數	意義
rate	報酬率
nper	期數
pmt	每一期之金額
[pv]	期初單筆金額
[type]	Pmt的發生時點 0：期末(預設值) 1：期初

單筆借款

James跟朋友借一筆10萬元的金額，雙方同意以年利率10%計息，借期2年以複利計算，請問到期後James該還朋友多少錢？

FV(rate, nper, pmt, [pv], [type])

=FV(10%, 2, 0, 100000) = -121,000

零存整付之定存

Lisa每月於期初均存入銀行一萬元，年利率2%，每月計算複利一次，請問一年後可以拿回多少錢？

FV(rate, nper, pmt, [pv], [type])
= FV(2%/12, 12, -10000, 0 , 1)
= 121,308

Lisa每月拿出10,000元(現金流出 pmt = -10,000)，而且是期初拿出(type = 1)，所以期末時(FV)當然要拿回121,308(現金流入)，所以當然是正值了。

退休規劃

Michael現年35歲，現有資產200萬元，預計每年可結餘30萬元，若將現有資產200萬及每年結餘30萬均投入5%報酬率的商品，請問60歲退休時可拿回多少錢？

FV(rate, nper, pmt, [pv], [type])
=FV(5%, 25, -300000, -2000000) = 21,090,840

這是單筆(pv)及年金(pmt)都是負值的例子，期初時拿出200萬元(pv=-2,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期末當然是要拿回21,090,840元，這帳才會平衡。

貸款餘額

Peter有一筆100萬元的10年期貸款，年利率10%，每月支付13,215.074元，請問於第5年底貸款餘額為多少？

FV(rate, nper, pmt, [pv], [type])

=FV(10%/12, 12*5, -13215.074,1000000)
= -621,972

這也是單筆(pv)加年金(pmt)的例子，只是pv為正值，pmt為負值。Peter於期初時拿入100萬元(pv = 1,000,000)，每月繳納13,215.074(pmt = -13,215.074)，到第5年時，pv及pmt兩相平衡後尚差-62,1972，也就是說期末時還必須拿出621,972，這筆帳才會平衡，代表期末貸款餘額尚差這金額。

同樣一個公式，如果將期數nper由5年(12*5)改為10年(12*10)，FV一定會等於0，代表這貸款還清了。

=FV(10%/12, 12*10, -13215.074,1000000)
= 0

 

回目錄

現值函數PV

PV函數主要用來計算現值，只要將希望得到的現金流量之金額大小及發生的時間點，以rate, nper, pmt, fv, type參數描述，就可以得到應該有的現值，格式如下：

PV(rate, nper, pmt, [fv], [type])

參數	意義
rate	報酬率
nper	期數
pmt	每一期之金額
[Fv]	期末單筆金額
[type]	Pmt的發生時點 0：期末(預設值) 1：期初

單筆借款

James想跟朋友借一筆款項，雙方同意以年利率10%計息，借期2年、一年複利一次，到期還款10萬元。請問James可以借到多少錢？

PV(rate, nper, pmt, [fv], [type])
=PV(10%, 2, 0, -100000) = 82,645

以James角度來看，因為是借款，期末有一筆現金10萬元流出James，所以fv = -100000。算出來的答案是82,645，代表James應該拿到借款(現金流入)82,645，這筆帳才會平衡。

銀行貸款

Lisa於每月底必須繳交貸款本息一萬元，年利率2%、每月計算複利一次、期限為一年。請問Lisa跟銀行貸了多少錢？

PV(rate, nper, pmt, [fv], [type])

= PV(2%/12, 12, -10000) 
= 118,710

Lisa每個月底拿出10,000元(所以是年金式的現金流出 pmt = -10,000)，那麼期初(PV)當然要拿到118,710的銀行撥款(現金流入)，這樣才划算。

退休規劃

Michael現年35歲，預計60歲退休，目前『每年』可結餘30萬元，均投入5%報酬率的商品。希望退休時可擁有2,000萬的退休金，請問Michael現在必須已經擁有多少存款才有辦法達到這目標？

PV(rate, nper, pmt, [fv], [type])
=PV(5%, 25, -300000, 20000000) = -1,677,872

這是單筆(fv)為正值、年金(pmt)為負值的例子，希望期末時拿到2,000萬元(fv=20,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期初必須拿出-1,677,872元，這帳才會平衡。也就是尚欠1,677,872元。
如果完全相同的一個例子，但是每年結餘由30萬元變成50萬元，結果又是不一樣了：

=PV(5%, 25, -500000, 20000000) = 1,140,917

現值由負轉正喔，每年結餘30萬(pmt = -300,000)時，還缺約168萬。可是當每年結餘增價為50萬時(pmt = -500,000)，就多了約114萬。當pv=0的那個pmt，就是剛好所需要的每年結餘，一定是介於30萬~50萬之間，答案是-419,049。

貸款餘額

Peter有一筆10年期貸款，年利率10%，每月支付13,215.07元，第5年底貸款餘額還剩621,972元，請問Peter貸了多少錢？

PV(rate, nper, pmt, [fv], [type])
=PV(10%/12, 12*5, -13215.07, -621972)
= 1,000,000

這也是單筆(fv)加年金(pmt)的例子，只是fv、pmt均為負值。Peter每月繳納13,215.07(pmt = -13215.07)，到第5年時，還必須拿出62萬多(fv = -621972)，也就是說期初時貸款1,000,000，這筆帳才會平衡。

 

回目錄

年金函數PMT

PMT函數主要用來計算年金的金額，只要將希望得到的現金流量之金額大小及發生的時間點，以rate, nper, pv, fv, type參數描述，就可以得到年金的金額，格式如下：

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

參數	意義
rate	報酬率
nper	期數
pv	期初單筆金額
[fv]	期末單筆金額
[type]	Pmt的發生時點 0：期末(預設值) 1：期初

存錢買車

Jeff預計5年後買一輛新車60萬元，若目前年利率3%，從現在起於每月底需存多少錢。

以這例子每期為一個月，pv=0，nper =12*5，rate = 3%/12，fv =600,000。

PMT(rate, nper, pv, [fv], [type])
=PMT(3%/12, 12*5, 0, 600000) 
= -9,281

也就是每月須拿出9,281元

房屋貸款

Susan向銀行貸款100萬元，利率5%、期限20年，本息均攤請問月繳款多少元？

這例子相當於一個月為一期，總共有240期(NPER=240)，每期利率=5%/12，Susan期初跟銀行拿了100萬(pv=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於零(fv=0)？

PMT(rate, nper, pv, [fv], [type])
=PMT(5%/12,240,1000000)
=-6,600

也就是每月得繳本息6,600元

房屋貸款2

Susan向銀行貸款100萬元，利率5%，到第3年底(36期)時，尚有餘額905,717，問Susan每月繳款的金額是多少元？

這例子相當於一個月為一期，總共有36期(NPER=36)，每期利率=5%/12，Susan期初跟銀行拿了100萬(pv=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於905,717(fv=-905,717)？

PMT(rate, nper, pv, [fv], [type])
=PMT(5%/12,36,1000000,-905717)
=-6,600

退休規劃

Peter現有存款200萬，希望15年後退休可達1,500萬，若Peter的投資報酬率每年有8%，每年需要另存多少錢？

存款投資200萬(pv=-2,000,000)，每年為一期，總共有15期(nper=15)，每期報酬率為8%(RATE=8%)，15年後拿回1500萬(fv=15,000,000)，那麼每年還需投資：

PMT(rate, nper, pv, [fv], [type])
=PMT(8%,15,-2000000,15000000)
=-318,784

 也就是每年還得拿出31.8萬元去投資，15年後連同期初那200萬元的單筆投資，總共可得1,500萬元。

 

回目錄

期數函數NPER

NPER函數可以用來計算所需要的期數，只要將希望得到的現金流量之金額大小及發生的時間點，以rate, pmt, pv, fv, type參數描述，就可以得到應該有的期數，格式如下：

NPER(rate, pmt, pv, [fv], [type])

參數	意義
rate	報酬率
pmt	每一期之金額
pv	期初單筆金額
[fv]	期末單筆金額
[type]	Pmt的發生時點 0：期末(預設值) 1：期初

貸款規劃

Queena買了一間房子，希望跟銀行貸款300萬元、利率2.2%，每月有能力繳本息30,000元，請問要多久可以繳清貸款？

以這例子每月為一期，pv=3,000,000，rate =2.2%/12，pmt=-30000，fv = 0。

NPER(rate, pmt, pv, [fv], [type])
=NPER(2.2%/12, -30000, 3000000, 0)
= 110.6

也就是需要110個月才能繳清貸款，相當於不到十年就還完了。

退休規劃

Pete目前30歲，擁有存款200萬元，每年底可結餘30萬元，均投資年報酬率8%的商品，希望退休時可以擁有1,500萬元，那麼幾歲可以退休？

以這例子每年為一期，pv=-2,000,000，pmt=-300,000，rate =8%，fv =15,000,000。

NPER(rate, pmt, pv, [fv], [type])
=NPER(8%, -300000, -2000000, 15000000)
=15.4

哇~~~真好，Peter大約15年後、45歲就可退休了。

提早償還貸款

Susan向銀行貸款100萬元，利率3%、期限20年，每月本息攤還5,546元。已經繳了5年，還剩本金餘額803,088元，目前Susan剛好有一筆業務獎金30萬元進帳，想提早還款。Susan希望往後每月還是繳相同的錢，多久以後可以還清貸款？

以這例子每月為一期，目前本金還有503,088(pv=803,088-300,000)，年利率3%(rate=3%/12)，每期繳款5,546(pmt=-5,546)，期末還清(fv = 0)。

NPER(rate, pmt, pv, [fv], [type])
=NPER(3%/12, -5546, 803088-300000)
=103

也就是103個月後(8年7個月)，即可還清貸款。

 

回目錄

報酬率函數RATE

RATE函數可以用來計算報酬率或利率，只要將現金流量之金額大小及發生的時間點，以nper, pmt, pv, fv, type參數描述，就可以得到投資報酬率的值，格式如下：

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

參數	意義
nper	期數
pmt	每一期之金額
pv	期初單筆金額
[fv]	期末單筆金額
[type]	Pmt的發生時點 0：期末(預設值) 1：期初
[guess]	猜測報酬率可能落點 因為RATE是用代入法求得答案，先假設一個報酬率(預設值10%)，然後代入公式，再看看是否符合。如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少報酬率，看哪一個方向較為接近，然後往該方向前進。然後一直反覆這過程，直到找答案為止。只是若答案離預設值太遠，在 20次反覆運算之後，依然無法收斂到0.0000001以內時，就會傳回錯誤值 #NUM!。這時使用者就必須更改Guess參數，然後重新搜尋一次。依據我個人經驗，如果計算的是月利率的話，最好guess參數設定為1%，比較不會出錯。

基金年化報酬率(一)

Peter於10年前，以10萬元買了一個基金，現在該基金淨值15萬元，請問這樣相當於多少的年報酬率？

以這例子每年為一期，pv=-100,000，nper =10，pmt=0，fv =150,000。

RATE(nper, pmt, pv, [fv], [type], [guess])
=RATE(10, 0, -100000, 150000)
= 4.14%

基金年化報酬率(二)

Peter於10年前，以10萬元買了一個基金，而且每月底定期定額2,000元買相同之基金，現在該基金淨值65萬元，請問這樣相當於多少的年報酬率？

以這例子每月為一期，pv=-100,000，pmt= -2,000，nper =120，fv =650,000。

RATE(nper, pmt, pv, [fv], [type], [guess])
=RATE(120, -2000, -100000, 650000, 0, 1%)*12
= 9.4%

因為期數是每月一期， RATE所傳回的是『月報酬率』，所以年報酬率必須再乘上12。

房屋貸款

Susan向銀行貸款100萬元，期限20年，每月本息攤還6,600元，問這貸款年利率是多少？

以這例子每月為一期，總共240期(nper =240)，期初拿到100萬(pv=1,000,000)，每期繳款6,600(pmt=-6,600)，期末還清fv = 0)。

RATE(nper, pmt, pv, [fv], [type], [guess])
=RATE(240, -6600, 1000000, 0, 0, 1%)*12
= 5.0%

 

回目錄

內部報酬率函數IRR

一項投資案一定會有現金流量產生，只要列出這項投資的現金流量表，就可以計算出整體投資的報酬率。EXCEL提供了這相當好用的IRR函數，只要輸入現金流量，就會計算出投資報酬率。雖然RATE函數也可以計算報酬率，但是每一期金額都不等的現金流量，無法計算。但是IRR函數，幾乎任何形式的現金流量都可以計算報酬率。

IRR函數的參數

IRR(value, [guess])

參數	意義
value	現金流量： 必須至少包含一個正數和一個負數，以計算內部報酬率
[guess]	猜測報酬率可能落點 因為IRR是用代入法求得答案，先假設一個報酬率(預設值10%)，然後代入公式，再看看是否符合。如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少報酬率，看哪一個方向較為接近，然後往該方向前進。然後一直反覆這過程，直到找答案為止。只是若答案離預設值太遠，在 20次反覆運算之後，依然無法收斂到0.0000001以內時，就會傳回錯誤值 #NUM!。這時使用者就必須更改Guess參數，然後重新搜尋一次。依據我個人經驗，如果計算的是月利率的話，最好guess參數設定為1%，比較不會出錯。

 

定存股範例

有一定存股，第一年拿出61,020元買股票一張，第二年拿回3,580配息，第三年拿回4,686配息，第四年拿回5,098配息，第五年拿回91,916配息及股票賣回金額，整個現金流量如下圖所示。如何將這樣的現金流量敘述給IRR函數知道呢？有兩種方式，第一種方法是用大括弧將現金流量括起來，當中每一期的現金流量再用逗點分隔。

=IRR({-61020, 3580, 4686, 5098, 91916})
=15.8%

現金流量也可以放在儲存格裡，然後將範圍放入IRR的參數即可。如下圖所示，儲存格B2:B6就是現金流量的範圍。

儲蓄險利率試算

有一個六年期的養老保險，繳費期間是前三年、每一年初繳30,250，到第六年時領回100,000元，這樣相當於多少的利率？

首先將現金流量畫出來，因為前三年為繳費，屬於現金流出所以是負值，第六年底時現金拿回來，所以是正值。現金流量圖如下所示，所以年化投資報酬率為：

=IRR({-30250, -30250, -30250, 0, 0, 0,100000})
=1.96%

期初或期末

IRR的現金流量因為每一期只能填入一筆金額，但是必須要有期初或期末之分，而且前後有一致性。以儲蓄險為例，前三期的保險費是發生在期初，但是第6年的金額是發生在期末。

如果要以期初為基準，那麼第6年底的金額就必須當成第7年初發生。也就是第n期的期末，等於第n+1期的期初。所以1,2,3期初支出30,250元，4,5,6期無現金流，第7期領回10萬元。

相同道理，如果以期末為準，那麼前三期的發生在期初的金額，都要往前挪一期，所以是0,1,2期的期末支出30,250元，3,4,5期無現金流，第6期領回10萬元。但是不論期初或期末，現金流量都是：

{-30250, -30250, -30250, 0, 0, 0,100000}

一般人常犯的錯誤是期初、期末不分，所以就會出現一期的差異，常看到有人將上述的現金流量，錯寫成下列的樣子，當中少了一個零。

{-30250, -30250, -30250, 0, 0, 100000}

沒有現金流量的期數

如果沒有現金流量發生的期數，現金流量的值必須填0，不可以忽略掉，否則IRR函數會算錯期數。以儲蓄險為例，如果沒有現金流量的期數不填0，公式將成為：

=IRR({-30250, -30250, -30250, 100000})
=4.93%

這時IRR會誤認為總共只有4期，認為第4期就會拿回10萬元，所以就錯的。這情況在直接使用儲存格輸入時，較容易發生錯誤，容易把空格當成0。例如下圖的E欄，3, 4, 5期的現金流量是空的，讀者很容易認為IRR應該是0，結果IRR卻會直接跳過，誤認為只有4期，答案當然就錯掉。