精品财务知识E五大财务函数汇整.docx
- 文档编号:28870694
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:22
- 大小:129.10KB
精品财务知识E五大财务函数汇整.docx
《精品财务知识E五大财务函数汇整.docx》由会员分享,可在线阅读,更多相关《精品财务知识E五大财务函数汇整.docx(22页珍藏版)》请在冰豆网上搜索。
精品财务知识E五大财务函数汇整
Excel五大財務函數彙整
日期:
2011-01-19
目錄
現金流量型態
未來值函數-FV
現值函數-PV
年金函數-PMT
期數函數-NPER
報酬率函數-RATE
內部報酬率函數-IRR
現金流量型態
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期,答案當然就錯掉。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 精品 财务 知识 函数