1 / 38

Excel 財務函數

Excel 財務函數. Stanley Hsiao Sep 14, 2010. 期初或期末. (1+rate) nper -1. pv x (1+rate) nper + pmt x (1+ rate x type) x. + fv = 0. rate. 年金終值. 單筆終值. 財務函數的公式. 五大財務函數. FV 、 PV 、 NPER 、 RATE 、 PMT. fv. type = 0 ( 預設 ). type = 1. pmt. pmt. pmt. pmt. pmt. pmt. pmt. pmt. pmt.

raoul
Download Presentation

Excel 財務函數

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel 財務函數 Stanley Hsiao Sep 14, 2010

  2. 期初或期末 (1+rate)nper -1 pv x (1+rate)nper+ pmt x(1+ rate x type) x + fv = 0 rate 年金終值 單筆終值 財務函數的公式

  3. 五大財務函數 FV、PV 、NPER、RATE、PMT fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv pmt必須全期都一致

  4. 允許之現金流量

  5. 不允許之現金流量 全期之pmt金額必須都一樣 不可全部都同方向 全期之pmt金額必須都一樣 不可全部都同方向

  6. FV函數之概念圖 fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv

  7. FV函數及參數 =FV( rate, nper, pmt, pv, type)

  8. 單筆借款 James跟朋友借一筆10萬元的金額，雙方同意以年利率10%計息，借期2年以複利計算，請問到期後James 該還朋友多少錢？ =FV(10%, 2, 0, 100000) = -121,000 以James角度來看，因為是借款，期初有一筆現金10萬元流入James，所以pv = 100,000。答案-121,000，代表James必須拿出(現金流出)121,000還朋友，這筆帳才會平衡。 =FV( rate, nper, pmt, pv, type)

  9. 零存整付之定存 Lisa每月於期初均存入銀行一萬元，年利率2%，每月計算複利一次，請問一年後可以拿回多少錢？ =FV(2%/12, 12, -10000, 0 , 1) = 121,308 Lisa每月拿出10,000元(現金流出 pmt = -10,000)，而且是期初拿出(type = 1)，所以期末時(FV)當然要拿回121,308(現金流入)，所以當然是正值了。 =FV( rate, nper, pmt, pv, type)

  10. PV函數之概念圖 fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv

  11. PV函數及參數 =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的銀行撥款，這樣才划算。 =PV( rate, nper, pmt, fv, type)

  13. PMT函數之概念圖 fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv

  14. PMT函數及參數 =PMT( rate, nper, pv, fv, type)

  15. 房屋貸款 Susan向銀行貸款100萬元，利率5%、期限20年，本息均攤請問月繳款多少元？ =PMT( 5%/12, 240, 1000000)= -6,600 =PMT( rate, nper, pv, fv, type)

  16. RATE函數之概念圖 fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv

  17. RATE函數及參數 =RATE( nper, pmt, pv, fv, type, guess)

  18. (1+rate)nper -1 pv x (1+rate)nper+ pmt x(1+ rate x type) x + fv = 0 rate Guess 參數 Guess參數是目標搜尋的起始值 • RATE函數是用『目標搜尋』的方式找答案，過程如下： • 預設一個起始rate的值(10%)，然後代入下圖公式，看結果和「0」有多少誤差。 • 如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少rate的值，看哪一個方向代入公式最接近「0」，然後往該方向前進 。 • 反覆過程「2」直到找答案為止。

  19. 基金年化報酬率(二) Peter於10年前，以10萬元買了一個基金，而且每月定期定額2,000元買相同之基金，現在該基金淨值65萬元，請問這樣相當於多少的年報酬率？ =RATE(120, -2000, -100000, 650000, 0, 1%)*12 = 9.4% =RATE( nper, pmt, pv, fv, type, guess)

  20. NPER函數之概念圖 fv type = 0 (預設) type = 1 pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt pmt 4 1 2 3 5 6 nper(總期數) pv

  21. NPER函數及參數 =NPER( rate, pmt, pv, fv, type)

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

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

  24. 現金流量 產生 投資行為 報酬率(rate) 推論 現金流量反推報酬率 推論工具：IRR、XIRR

  25. Value-n Value-1 Value-2 Value-0 + + + + = 0 (1+rate)n (1+rate)1 (1+rate)2 (1+rate)0 IRR函數及參數 =IRR( values, guess)

  26. Value-n Value-1 Value-2 Value-0 + + + + = 0 (1+rate)n (1+rate)1 (1+rate)2 (1+rate)0 IRR的guess參數 Guess參數是目標搜尋的起始值 • RATE函數是用『目標搜尋』的方式找答案，過程如下： • 預設一個起始rate的值(10%)，然後代入下圖公式，看結果和「0」有多少誤差。 • 如果誤差在容許範圍內，該值就是答案，否則就試著增加或減少rate的值，看哪一個方向代入公式最接近「0」，然後往該方向前進 。 • 反覆過程「2」直到找答案為止。

  27. 年初投入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% IRR計算結果是「期」利率 公式完全一樣，可是意義卻不一樣

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

  29. 中間「空格」不計現金流量 第一年初投入100元，第七年底拿回200元，年化報酬率為多少？ 中間有無空格，答案迥然不同

  30. 範圍不相連的表示方式

  31. IRR與Rate之差別 pmt 1 2 3 4 5 RATE pv cf2 cf4 cf3 cf5 cf1 1 2 3 4 5 IRR cf0

  32. 郵局六年吉利保險 =RATE(6, -164439,0,1000000,1)= 0.385%

  33. NPV NPV(rate,value1,value2, ...) Value-3 Value-n Value-1 Value-2 NPV = + + + + (1+rate)3 (1+rate)n (1+rate)1 (1+rate)2

  34. 投資決策 一項投資案，預估未來可盈餘發回金額如下： 第一年：250,000第二年：300,000第三年：430,000第四年：625,000 若投資報酬率要求20%，投資金額多少以內划算？

  35. XIRR XIRR(values ,dates, [guess]) 計算出來的利率是以日複利的實質利率，非名目利率

  36. XIRR範例 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賣出，求總體投資報酬率。

  37. MIRR MIRR(values,finance_rate,reinvest_rate)

  38. 謝謝 網站：http://www.masterhsiao.com.tw Email: stanley@masterhsiao.com.tw

More Related