Excel 財務函數 · =RATE( nper, pmt, pv, fv, type, guess) 19. 基金年化報酬率 (二)...

Excel 財務函數

Stanley HsiaoSep 14, 2010

2財務函數的公式

pv x (1+rate)nper + pmt x (1+ rate x type) x (1+rate)nper -1rate

+ fv = 0

單筆終值年金終值

期初或期末

五大財務函數3

pv

fv

1 2 3 4 5 6

pmt pmt pmt pmt pmt pmt

type = 0 (預設)


type = 1

nper(總期數)

FV、PV 、NPER、RATE、PMT

pmt必須全期都一致

允許之現金流量4

不允許之現金流量5

全期之pmt金額必須都一樣

全期之pmt金額必須都一樣

不可全部都同方向

不可全部都同方向

6FV函數之概念圖

pv

fv

1 2 3 4 5 6


type = 0 (預設)


type = 1

nper(總期數)

7FV函數及參數

=FV( rate, nper, pmt, pv, type)

參數意義必要參數

rate 每期之利率必要

nper 期數必要

pmt 年金之每期金額必要

pv 單筆之期初金額選項

type年金發生於期初或期末0：期末(預設)1：期初

選項


8單筆借款

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

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

以James角度來看，因為是借款，期初有一筆現金10萬元流入James，所以pv = 100,000。答案-121,000，代表James必須拿出(現金流出)121,000還朋友，這筆帳才會平衡。


9零存整付之定存

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

=FV(2%/12, 12, -10000, 0 , 1) = 121,308

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

10PV函數之概念圖

pv

fv

1 2 3 4 5 6


type = 0 (預設)


type = 1

nper(總期數)

11PV函數及參數

=PV( rate, nper, pmt, fv, type)



nper 期數必要

pmt 年金之每期金額必要

fv 單筆之期末金額選項


選項

=PV( rate, nper, pmt, fv, type)

12銀行貸款

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

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

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

13PMT函數之概念圖

pv

fv

1 2 3 4 5 6


type = 0 (預設)


type = 1

nper(總期數)

14PMT函數及參數

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



nper 期數必要

pv 單筆之期初金額必要

fv 單筆之期末金額選項


選項

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

15房屋貸款

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

=PMT( 5%/12, 240, 1000000)= -6,600

16RATE函數之概念圖

pv

fv

1 2 3 4 5 6


type = 0 (預設)


type = 1

nper(總期數)

17RATE函數及參數

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

參數意義必要參數nper 期數必要pmt 年金之每期金額必要pv 單筆之期初金額必要fv 單筆之期末金額選項

type 期初或期末0：期末(預設)1：期初

選項

guess 猜測利率可能之落點選項

18Guess 參數

RATE函數是用『目標搜尋』的方式找答案，過程如下：

1. 預設一個起始rate的值(10%)，然後代入下圖公式，看結果和「0」有多少誤差。

2. 如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少rate的值，看哪一個方向代入公式最接近「0」，然後往該方向前進。

3. 反覆過程「2」直到找答案為止。

pv x (1+rate)nper + pmt x (1+ rate x type) x (1+rate)nper -1rate

+ fv = 0

Guess參數是目標搜尋的起始值

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

19基金年化報酬率(二)

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

=RATE(120, -2000, -100000, 650000, 0, 1%)*12

= 9.4%

20NPER函數之概念圖

pv

fv

1 2 3 4 5 6


type = 0 (預設)


type = 1

nper(總期數)

21NPER函數及參數

=NPER( rate, pmt, pv, fv, type)

參數意義必要參數rate 每期之利率必要pmt 年金之每期金額必要pv 單筆之期初金額必要fv 單筆之期末金額選項

type 年金發生於期初或期末0：期末(預設)1：期初

選項

=NPER( rate, pmt, pv, fv, type)

22貸款規劃

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

= 110.6

23提早償還貸款

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

=NPER(3%/12, -5546, 803088 -300000)

=103

24現金流量反推報酬率

投資行為

報酬率(rate)

現金流量產生

推論

推論工具：IRR、XIRR

25IRR函數及參數

=IRR( values, guess)


value 現金流量必要

guess *猜測IRR最可能的落點選項

+Value-0(1+rate)0

Value-1(1+rate)1

Value-2(1+rate)2

Value-n(1+rate)n+ + = 0+

26IRR的guess參數

+Value-0(1+rate)0

Value-1(1+rate)1

Value-2(1+rate)2

Value-n(1+rate)n+ + = 0+

RATE函數是用『目標搜尋』的方式找答案，過程如下：

1. 預設一個起始rate的值(10%)，然後代入下圖公式，看結果和「0」有多少誤差。

2. 如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少rate的值，看哪一個方向代入公式最接近「0」，然後往該方向前進。

3. 反覆過程「2」直到找答案為止。

Guess參數是目標搜尋的起始值

27IRR計算結果是「期」利率

年初投入100元，每「年」底拿回利息7元，且12 「年」底拿回本金100元=IRR({-100, 7,7,7,7,7,7,7,7,7,7,7,107}) = 7% (每年)

年初投入100元，每「月」底拿回利息7元，且12 「月」底拿回本金100元=IRR({-100, 7,7,7,7,7,7,7,7,7,7,7,107}) = 7% (每年)

年利率 = 7%*12 = 84%

公式完全一樣，可是意義卻不一樣

28期初與期末

郵局6年期吉利保險，每年「初」繳保費161,568元（1%折扣後），第6年「底」拿回100萬元，相當年利率多少？

期末期初

0 1 -161,568

1 2 -161,568

2 3 -161,568

3 4 -161,568

4 5 -161,568

5 6 -161,568

6 7 1,000,000

0.888%

0.888%

年利率(IRR)

期數現金流量

=IRR(C3:C9)

=RATE(6, -161568, 0, 1000000, 1)

29中間「空格」不計現金流量

第一年初投入100元，第七年底拿回200元，年化報酬率為多少？

期數1 -100 -100 2 03 04 05 06 07 08 200 200

報酬率 100.0% 10.4%

現金流量

=IRR(C2:C9)

中間有無空格，答案迥然不同

30範圍不相連的表示方式

年保費生存金現金流量保額報酬率

0 100,000 100,000- 100,000

1 2,000 2,000 100,000 0.00% =IRR(($D$2:D2,E3))

2 2,000 2,000 100,000 1.00% =IRR(($D$2:D3,E4))

3 2,000 2,000 100,000 1.34% =IRR(($D$2:D4,E5))

4 2,000 2,000 100,000 1.51% =IRR(($D$2:D5,E6))

5 2,000 2,000 100,000 1.61% =IRR(($D$2:D6,E7))

6 2,000 2,000 100,000 1.68% =IRR(($D$2:D7,E8))

31IRR與Rate之差別

RATE

IRR

pv

cf0

pmt

cf1

cf2cf3

cf4

cf5

1 2 3 4 5

1 2 3 4 5

32郵局六年吉利保險

報酬率 0.385% =IRR(B4:B10)

年度現金流量

0 -164,439

1 -164,439

2 -164,439

3 -164,439

4 -164,439

5 -164,439

6 1,000,000

=RATE(6, -164439,0,1000000,1)= 0.385%

33NPV

NPV(rate,value1,value2, ...)


rate 折算現值的利率必要

Value1 現金流量1 必要Value2… 現金流量2 選項

+Value-3(1+rate)3

Value-1(1+rate)1

Value-2(1+rate)2

Value-n(1+rate)n+ +NPV = +

34投資決策

一項投資案，預估未來可盈餘發回金額如下：

第一年：250,000第二年：300,000第三年：430,000第四年：625,000

若投資報酬率要求20%，投資金額多少以內划算？0 -966,917

1 250,000

2 300,000

3 430,000

4 625,000

=-NPV(20%,B2:B5)

35XIRR

XIRR(values ,dates, [guess])


values 現金流量必要

dates 現金流量發生日期必要guess 搜尋的起始點選項

∑=

−

+=

N

jdd

jj

rate

P

1 3651

)1(0

計算出來的利率是以日複利的實質利率，非名目利率

XIRR範例36

2009/4/24以每股59.5、買入中華電2張(2,000股)2010/8/12中華電每股配息4.06元。2009/6/26以每股50.9、買入四維航運1張(1,000股)2010/8/9四維航運每股配息4元。

2010/10/8，中華電以每股71.8賣出，四維航以每股39.6賣出，求總體投資報酬率。

報酬率 10.50% =XIRR(B4:B8,A4:A8

日期現金流量備註2009/4/24 -119,000 買入中華電2張2009/6/26 -50,900 買入四維航1張

2010/8/9 4,000 四維航配息4元2010/8/12 8,120 中華電配息4.06元2010/10/8 183,200 全部賣出

37MIRR

MIRR(values, finance_rate, reinvest_rate)


values 為含有各期現金流量數值的陣列或儲存格參照必要

finance_rate 投入資金的融資利率必要reinvest_rate 轉投資報酬率必要

1))1(*])[,()1(*])[,(( 1

1

−++− −n

n

fratenegativevaluesfrateNPVrratepositivevaluerrateNPV

38

謝謝

網站：http://www.masterhsiao.com.tw

Email: [email protected]

http://www.masterhsiao.com.tw/

mailto:[email protected]

Excel 財務函數 · =RATE( nper, pmt, pv, fv, type, guess) 19. 基金年化報酬率 (二)...

Documents

Transcript of Excel 財務函數 · =RATE( nper, pmt, pv, fv, type, guess) 19. 基金年化報酬率 (二)...