1 / 45

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.

cosima
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. 退休規劃 Michael現年35歲，現有資產200萬元，預計每年可結餘30萬元，若將現有資產200萬及每年結餘30萬均投入5%報酬率的商品，請問60歲退休時可拿回多少錢？ =FV(5%, 25, -300000, -2000000) = 21,090,840 期初時拿出200萬元(pv=-2,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期末當然是要拿回21,090,840元，這帳才會平衡。 =FV( rate, nper, pmt, pv, type)

  11. 貸款餘額 Peter有一筆100萬元的10年期貸款，年利率10%，每月支付13,215.074元，請問於第5年底貸款餘額為多少？ =FV(10%/12, 12*5, -13215.074,1000000) = -621,972 Peter於期初時拿入100萬元(pv = 1,000,000)，每月繳納13,215.074(pmt = -13,215.074)，到第5年時，pv及pmt兩相平衡後尚差-62,1972，也就是說期末時還必須拿出621,972，這筆帳才會平衡，代表期末貸款餘額尚差這金額。 =FV( rate, nper, pmt, pv, type)

  12. 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

  13. PV函數及參數 =PV( rate, nper, pmt, fv, type)

  14. 單筆借款 James想跟朋友借一筆款項，雙方同意以年利率10%計息，借期2年、一年複利一次，到期還款10萬元。請問James可以借到多少錢？ =PV(10%, 2, 0, -100000) = 82,645 以James角度來看，因為是借款，期末有一筆現金10萬元流出，所以fv = -100000。算出來的答案是82,645，代表應該拿到借款(現金流入)82,645，這筆帳才會平衡。 =PV( rate, nper, pmt, fv, type)

  15. 銀行貸款 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)

  16. 退休規劃 Michael現年35歲，預計60歲退休，目前『每年』可結餘30萬元，均投入5%報酬率的商品。希望退休時可有擁有2000萬的退休金，請問現在必須已經擁有多少存款才有辦法達到這目標？ =PV(5%, 25, -300000, 20000000) = -1,677,872 期末時希望擁有2,000萬元(fv=20,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期初必須拿出-1,677,872元，這帳才會平衡。也就是尚欠1,677,872元。 =PV( rate, nper, pmt, fv, type)

  17. 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

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

  19. 存錢買車 Jeff預計5年後買一輛新車60萬元，若目前年利率3%，從現在起於每月需存多少錢？ =PMT(3%/12, 12*5, 0, 600000) =-9,281 =PMT( rate, nper, pv, fv, type)

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

  21. 房屋貸款(二) Susan向銀行貸款100萬元，利率5%，到第3年底(36期)時，尚有餘額905,717，問Susan每月繳款的金額是多少元？ =PMT(5%/12, 36, 1000000, -905717) = -6,600 =PMT( rate, nper, pv, fv, type)

  22. 退休規劃 Peter現有存款200萬，希望15年後退休可達1500萬，若Peter的投資報酬率每年有8%，每年需要另存多少錢？ =PMT( 8%, 15, -2000000, 15000000) = -318,784 =PMT( rate, nper, pv, fv, type)

  23. 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

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

  25. (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」直到找答案為止。

  26. 基金年化報酬率(一) Peter於10年前，以10萬元買了一個基金，現在該基金淨值15萬元，請問這樣相當於多少的年報酬率？ =RATE(10, 0, -100000, 150000, 0, 10%) = 4.14% =RATE( nper, pmt, pv, fv, type, guess)

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

  28. 房屋貸款 Susan向銀行貸款100萬元，期限20年，每月本息攤還6,600元，問這貸款年利率是多少？ =RATE(240, -6600, 1000000, 0, 0, 1%)*12 = 5.0% =RATE( nper, pmt, pv, fv, type, guess)

  29. 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

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

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

  32. 退休規劃 Pete目前30歲，擁有存款200萬元，每年底可結餘30萬元，均投資年報酬率8%的商品，希望退休時可以擁有1,500萬元，那麼幾歲可以退休？ =NPER(8%, -300000, -2000000, 15000000) =15.4 =NPER( rate, pmt, pv, fv, type)

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

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

  35. 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)

  36. 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」直到找答案為止。

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

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

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

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

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

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

  43. 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

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

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

More Related