Presentation is loading. Please wait.

Presentation is loading. Please wait.

葉兆輝.

Similar presentations


Presentation on theme: "葉兆輝."— Presentation transcript:

1 葉兆輝

2 P N F R (A=PMT) 當題目是沒有A時(如1-5題), 則給定F、P 、r、n 其中的三個,可求第四個。 當題目是有A時(如6-15題), 則給定A、F、r、n 其中的三個,可求第四個(如6-9題)。 則給定A、P、r、n其中的三個,可求第四個(如10-15題)。 附註: 1. EXCEL 的符號Pmt=A,Nper=n,Rate=r。 2. 五個變數中,只要給定其中三個,即可求解。

3

4

5

6

7

8 理財 發財 買豪宅 葉教授老師又來了 來幹什麼 來教各位理財 理財幹什麼 發財阿 發財幹什麼 買豪宅阿 因此理財發財買豪宅理財投資不投機 股市變幻莫測 唯一不變的是訊息萬變 有人問我說長線好還是短線好 我說都不好 內線最好 又有人問我說 技術面好還是基本面好 我說都不好 有朋友在裡面最好 讀萬卷書不如行萬里路 行萬里路不如有名師來開路俗話說的好,買對股票讓你上天堂、住洋房,買錯股票讓你住套房。買對房子幸福一輩子。

9 Excel 函數-FV未來值 讀完本篇後,不了解Excel裡的FV函數應用也難。未來值或終值是屬於貨幣的時間價值之一環,Excel也提供了一個相對函數FV (Future Value)來相呼應。相信許多人或多或少都知道什麼是未來值,但是當使用Excel FV函數時,卻常常碰到一些問題,尤其是正負號部分常常讓使用者頭疼。本篇主要介紹該函數的意義及其應用,除了理論介紹以外,還附有範例供讀者參考。

10 何謂未來值 未來值的英文為Future Value (FV),另一個名稱為終值,就是當一筆金額經過一段時間的複利成長後,於未來具有的貨幣價值。簡單說現在的100元和未來的100元是不同價值的,這100元於未來的價值就稱未來值。未來值既然是以複利成長,那麼利率是多少,以及多久複利一次,就會影響到貨幣的未來價值。

11 在現實世界之財務狀況除了單獨的一筆金額需要求未來值外,常常也年金也有這需求。Excel的FV函數,主要是計算期初(pv)發生的單筆金額,以及年金(pmt)的現金流量一起考慮,然後計算其未來的價值。
未來值的應用非常廣泛,例如100存入銀行,年利率3%,3年後會拿回多少錢。又如現在100元一碗牛肉麵,假若通貨膨脹率每年2%,20年後一碗牛肉麵會變成多少錢。這些都是很典型的未來值應用範例。

12 單筆的未來值公式 單筆的未來值是計算期初(pv)有一筆金額,然後每期以利率(rate)複利成長,經過(nper)期後,其未來值(FV)公式如下: FV = pv*(1+ rate)nper

13

14 例如期初存入銀行10,000元,年利率10%,每年複利計算一次,請問6年後可領回多少?這是典型未來值的計算: pv = 10000;nper = 6;rate = 10%; 所以未來值 fv = 10000*(1+10%)6 以Excel公式表示:=10000*(1+10%)^6 = 17,716

15 年金之未來值 年金的意義是於期間內共分幾期(nper),每期均於期末產生一筆金額(pmt),每一筆金額都會以每期利率(rate)複利成長,若每期之金額pmt都相等。到了最後一期末時這些金額所累計的未來值的公式為: 詳細年金觀念請參考:年金-理論篇

16 上圖黃色長條代表年金現金流量。

17 例如每年均於年終時存入銀行1000元,總共存了6年,年利率10%,每年複利計算一次,到了第六年終時,總共可領回多少錢? pmt = 1000;nper = 6;rate = 10%; 所以未來值 fv = 1000*((1+10%)6-1)/10% 以Excel公式表示:=1000*((1+10%)^6-1)/10% = 7,716

18 現金流量於期初發生 前述的年金現金流量均發生於期末,若同樣的現金流量都發生於期初,這樣的狀況只需將期末未來值再乘上(1+rate)就可以了,公式如下:

19

20 例如每年均於『年初』時存入銀行1000元,總共存了6年,年利率10%,每年複利計算一次,到了第六年終時,總共可領回多少錢? pmt = 1000;nper = 6;rate = 10% 所以未來值 fv = 1000*(1+10%)*((1+10%)6-1)/10% 以Excel公式表示:=1000*(1+10%)*((1+10%)^6-1)/10% = 8,487

21 如何使用Excel的FV函數 上述算法都是一般財務書籍所使用的方式,可是Excel的 FV函數所使用的計算方式有一些不同,就是這一點點的不一樣,把許多的使用者弄得不知所措。 Excel的FV函數具有三大特色: 1.單筆以及年金混合使用 2.單筆及年金的現金流量,可各自使用正負值來代表 3.未來值也是以正負值代表

22 FV函數的參數 FV函數的參數定義如下: =FV(rate, nper, pmt, pv, type)
下表列出了參數的意義以及與單筆或年金相關的參數,可以看出利率rate及期數nper單筆或年金都會用到。pv為單筆之期初金額;pmt為年金之每期金額,type參數則只跟年金有關,說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略,後面兩項pv及type是可有可無,若這兩項沒有輸入,FV函數會採用預設值pv = 0; type = 0(期末)。

23 參數 意義 必要參數 單筆相關 年金相關 rate 每期利率 必要 nper 期數 pmt 年金之每期金額 pv 單筆之期初金額 選項 type 期初或期末 0:期末(預設) 1:期初

24 pv及pmt的正負值 FV函數可計算: 現值(pv)的未來值 年金(pmt)的未來值

25 又如銀行房貸,以貸款者角度來看:期初時銀行會撥一筆款項進來,這筆金額便是現金流入貸款者(正值),之後每一月(期)均必須繳之攤還金額,是現金流出貸款者(負值)。反過來看,一個一模一樣的房貸,但從銀行角度來切入,期初時銀行會撥一筆款項給貸款者,這筆金額便是現金從銀行流出(負值),之後每一月(期)均收到貸款者的還款金額,這屬於現金流入銀行(正值)。所以說相同一件貸款,期初的撥款動作,對貸款者是現金流入,對銀行卻是現金流出。兩者之分別,端看以何種角度來看。

26 FV計算結果的正負值 計算出來的未來值FV也是以正負值來表示。咦~~~這就奇怪了,正的未來值很容易理解,負的未來值又代表何意義呢?其實FV函數所計算出來的結果等於『現值(pv)的未來值』加上『年金(pmt)未來值』的補數。也就是說: 現值(pv)的未來值 + 年金(pmt)的未來值 + FV = 0

27 這又代表啥意義呢?為簡化說明起見,當年金(pmt)未來值等於零,這時就只剩下單筆未來值。公式簡化為:現值(pv)的未來值 + FV = 0也就是:
FV = - 單筆(pv)未來值。

28 若pv為正值,FV就是負值,代表一個若是現金流出,另一個就必須是現金流入,反之亦然。這樣才會平衡,這也是等號右項為零的意義。
例如期初時單筆拿出100元(pv=-100)去存銀行,年利率10%,3年後的未來值 = -100*(1+10%)^3 = 元,這時FV函數的結果 = -(-133.1) = 133.1元。白話的說就是拿出100元存銀行,3年後該拿回133.1元。這樣才會出入兩相平衡,好似期初(pv)拿出去100元,期末(FV)應該拿回來133.1元,這樣帳才會打平的意思。

29 假若現值(pv)的未來值等於零,這時:年金(pmt)的未來值 + FV = 0,FV = - 年金(pmt)的未來值。
FV = - (現值(pv)的未來值 + 年金(pmt)未來值)。

30 可是pv及pmt都各自可能為正值或負值。若pv及pmt均為正值(兩者都是現金流入),那麼FV就肯定是負值(現金流出)。相反的,若pv及pmt均為負值,那麼FV肯定是正值。若pv及pmt正負號各自相反時,『現值(pv)的未來值』及『年金(pmt)未來值』會有部分自行相互相抵消,那麼FV就只要平衡這兩者之差額了。若差額為 0,代表『現值(pv)的未來值』跟『年金(pmt)未來值』兩者是相等的。

31 範例 由以上這些敘述可以看出Excel的FV函數不只是一個單純的未來值而已,其功能是非常大的,也難怪不容易弄清楚。我想用一些範例加上圖解的方式來解釋,讀者會應該比較容易理解。 範例Excel 以下的範例也提供下載。

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

33

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

35

36 退休規劃 Michael現年35歲,現有資產200萬元,預計每年可結餘30萬元,若將現有資產200萬及每年結餘30萬均投入5%報酬率的商品,請問60歲退休時可拿回多少錢? =FV(5%, 25, , ) = 21,090,840 這是單筆(pv)及年金(pmt)都是負值的例子,期初時拿出200萬元(pv=-2,000,000),而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000),那麼期末當然是要拿回21,090,840元,這帳才會平衡。

37

38 貸款餘額 Peter有一筆100萬元的10年期貸款,年利率10%,每月支付13,215.074元,請問於第5年底貸款餘額為多少?
=FV(10%/12, 12*5, , ) = -621,972 這也是單筆(pv)加年金(pmt)的例子,只是pv為正值,pmt為負值。Peter於期初時拿入100萬元(pv = 1,000,000),每月繳納13, (pmt = -13, ),到第5年時,pv及pmt兩相平衡後尚差-62,1972,也就是說期末時還必須拿出621,972,這筆帳才會平衡,代表期末貸款餘額尚差這金額。 同樣一個公式,如果將期數nper由5年(12*5)改為10年(12*10),FV一定會等於0,代表這貸款還清了。 =FV(10%/12, 12*10, , ) = 0

39

40 Excel的通用公式 喜歡數學的朋友看到下列式子一定很高興,這是從微軟Excel的FV函數的說明裡面節錄下來的。這個公式寫得非常漂亮,一個簡潔的公式道盡了所有貨幣時間價值的真諦。這等式左邊的第一項,就是單筆(pv)未來值的公式,第二項就是年金(pmt)未來值的公式,只不過多乘上(1+rate*type)來解決期初、期末的問題。第三項就是未來值FV了。等式的右邊項是零,代表說這個帳要打平。這真是個漂亮的公式,一個式子適用於所有的狀況。

41

42 以FV函數計算未來值因子(FVIF及FVIFA)
了解Excel FV函數的應用之後,應該不難理解FV函數也可以用來計算單筆未來值因子,及年金未來值因子。欲更深入了解的讀者可參考:現值及未來值因子表。 未來值因子 Excel FV公式 FVIF(r, n) =FV(rate, nper, 0, -1) 期末 FVIFA(r, n) =FV(rate, nper, -1) 期初 FVIFA(r, n)

43 不適用的年金 Excel的FV函數只適用每期金額大小都一致的年金。這種年金的現金流量每期一樣,所以可以使用公式來計算。若年金每期發生的現金流量金額都不一樣,如變額年金、每期金額以一定百分比成長(例如5%)之年金等,就無法使用Excel的FV函數了。這時便必須將每期所發生的現金流量,個別視為單筆,一一計算每一筆之未來值,然後再全部加總即可。

44 PV

45 Excel 函數-PV現值 讀完本篇後,不了解Excel裡的PV函數應用也難。現值是屬於貨幣的時間價值的一環,Excel也提供了一個相對函數PV (Present Value)來呼應。相信許多人或多或少都知道什麼是現值,但是當使用Excel PV函數時,卻常常碰到一些問題,尤其是正負號部分常常讓使用者頭疼。本篇主要介紹該函數的意義及其應用,除了理論介紹以外,還附有範例供讀者參考。

46 何謂現值 現值的英文為Present Value (PV),就是未來之一筆金額經過一段時間以複利折現(Discount)後,相當於現在的貨幣價值。簡單舉個例說,若利率為5%,5年後的100元,經過折現後相當於現值78.4元。也就是說現在只要78.4元,以5%的複利成長,剛好會等於5年後的100元。現值也相當於現在該準備多少,才足以支付未來的現金流量。想要5年後有100元,那現在就得存78.4元。

47 現值既然是以複利折現,那麼利率是多少,以及多久複利一次,就會影響到現值。在現實世界之財務狀況是複雜的,除了單筆金額外,常常也會有年金的應用,Excel的PV函數,除了單筆金額外,年金部分也一併考慮。現值的應用非常廣泛,例如預計5年後可以擁有100萬,若年利率為3%,現在應該存多少錢。又如貸款年利率為4%,期限為3年,每月都繳本息一萬元,相當於向銀行貸了多少錢?這些都是很典型的現值應用範例。

48 單筆的現值公式 單筆的現值於期末時,有一筆金額(fv),然後每期以利率(rate)複利折現,經過(nper)期後,其現值(PV)公式如下:
PV = fv / (1+ rate)nper

49

50 例如年利率10%,每年複利計算一次,請問現在要存多少錢,6年後可領回17,716?這是典型現值計算: fv = 17716;nper = 6;rate = 10%; 所以未來值 pv = 17716/(1+10%)6 以Excel公式表示:=17716/(1+10%)^6 = 10,000

51 一般年金之現值 年金的意義是於期間內共分幾期(nper),每期均於期末產生一筆金額(pmt),每一筆金額都會以每期利率(rate)複利折現,若每期之金額pmt都相等。這些金額所累計的現值公式為:

52

53 例如Peter向Jason借錢,約定每月均於月初時還本息1000元,總共6個月,月利率1%,每月複利一次,問Jason願意借多少錢給Peter? pmt = 1000;nper = 6;rate = 1%;type =1 所以現值 pv = 1000*(1+1%)*(1-1/(1+1%)6)/1% 以Excel公式表示:=1000*(1+1%)*(1-1/(1+1%)^6)/1% = 5,853

54 如何使用Excel的PV函數 Excel的PV函數具有三大特色: 1.單筆以及年金混合使用 2.單筆及年金的現金流量,可各自使用正負值來代表
3.未來值也是以正負值代表

55 PV函數的參數 PV函數的參數定義如下: =PV(rate, nper, pmt, fv, type)
下表列出了參數的意義以及與單筆或年金相關的參數,可以看出利率rate及期數nper單筆或年金都會用到。fv為單筆之期末金額;pmt為年金之每期金額,type參數則只跟年金有關,說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略,後面兩項fv及type是可有可無,若這兩項沒有輸入,PV函數會採用預設值fv = 0; type = 0(期末)。

56 參數 意義 必要參數 單筆相關 年金相關 rate 每期利率 必要 nper 期數 pmt 年金之每期金額 fv 單筆之期末金額 選項 type 期初或期末 0:期末(預設) 1:期初

57 fv及pmt的正負值 PV函數可計算: 未來值(fv)的現值 年金(pmt)的現值

58 例如定期存款:投資者將錢存入銀行,就是現金從投資者流出(負值),到期後收到銀行支付之本利和,就是現金流入投資者口袋(正值)。又如銀行房貸,以貸款者角度來看:期初時銀行會撥一筆款項進來,這筆金額便是現金流入貸款者(正值),之後每一月(期)均必須繳之攤還金額,是現金流出貸款者(負值)。反過來看,一個一模一樣的房貸,但從銀行角度來切入,期初時銀行會撥一筆款項給貸款者,這筆金額便是現金從銀行流出(負值),之後每一月(期)均收到貸款者的還款金額,這屬於現金流入銀行(正值)。所以說相同一件貸款,期初的撥款動作,對貸款者是現金流入,對銀行卻是現金流出。兩者之分別,端看以何種角度來看。

59 PV計算結果的正負值 計算出來的現值PV也是以正負值來表示。咦~~~這就奇怪了,正的現值很容易理解,負的現值又代表何意義呢?其實PV函數所計算出來的結果等於『終值的現值』加上『年金的現值』的補數。也就是說: 終值(fv)的現值+ 年金(pmt)的現值 + PV = 0

60 這又代表啥意義呢?為簡化說明起見,先不考慮年金現值(pmt=0),公式簡化為:終值(fv)的現值 + PV = 0,也就是:
PV = - 終值(fv)的現值。 若fv為正值,PV就是負值,代表一個若是現金流出,另一個就必須是現金流入,反之亦然。

61 例如一筆100元貸款(fv=-100),約定3年後還款,年利率10%,所以PV = -(-100/(1+10%)^3) = 75
例如一筆100元貸款(fv=-100),約定3年後還款,年利率10%,所以PV = -(-100/(1+10%)^3) = 75.1元。白話的說就是借入75.1元,3年後該還人家100元。好似期初(PV)拿進75.1元,期末(fv)應該拿出100元。 假若 fv=0 ,只剩下年金(pmt)現值,這時:年金(pmt)現值 + PV = 0,PV = - 年金(pmt)現值,也是相同的意義。 當終值及年金同時都有時:終值(fv)的現值 + 年金(pmt)的現值 + PV = 0。也就是說 PV = - (終值(fv)的現值 + 年金(pmt)的現值)

62 等於兩項相加之負值。可是fv及pmt都各自可能為正值或負值,問題就複雜了。若fv及pmt均為正值(兩者都是現金流入),那麼PV就肯定是負值(必須現金流出才會平衡)。相反的,若fv及pmt均為負值,那麼PV肯定是正值。 當fv及pmt都同為正值或負值時,PV負責平衡單筆及年金兩者現值之加總;若fv及pmt正負號各自相反時,『單筆現值』及『年金現值』會有部分自行相互抵消,那麼PV就只要平衡這兩者之差額了。若差額為 0,代表『單筆現值』跟『年金現值』兩者是相互平衡或相等。

63 範例 由以上這些敘述可以看出Excel的PV函數不只是一個單純的現值而已,其功能是非常大的,也難怪不容易弄清楚。我想用一些範例加上圖解的方式來解釋,讀者會應該比較容易理解。 範例Excel 以下的範例也提供下載。

64

65 銀行貸款 Lisa於每月底必須繳交貸款本息一萬元,年利率2%、每月計算複利一次、期限為一年。請問Lisa跟銀行貸了多少錢?
=PV(2%/12, 12, ) = 118,710 Lisa每個月底拿出10,000元(所以是年金式的現金流出 pmt = -10,000),那麼期初(PV)當然要拿到118,710的銀行撥款(現金流入),這樣才划算。

66

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

68 如果完全相同的一個例子,但是每年結餘由30萬元變成50萬元,結果又是不一樣了: =PV(5%, 25, , ) = 1,140,917 現值由負轉正喔,每年結餘30萬(pmt = -300,000)時,還缺約168萬。當每年結餘50萬(pmt = -500,000)時,就多了約114萬。當PV=0的那個pmt,就是剛好所需要的每年結餘,一定是介於30萬~50萬之間,答案是-419,049。

69

70 貸款餘額 Peter有一筆10年期貸款,年利率10%,每月支付13,215.07元,第5年底貸款餘額還剩621,972元,請問Peter貸了多少錢? =PV(10%/12, 12*5, , ) = 1,000,000 這也是單筆(fv)加年金(pmt)的例子,只是fv、pmt均為負值。Peter每月繳納13,125.07(pmt = ),到第5年時,還必須拿出62萬多(fv = ),也就是說期初時貸款1,000,000,這筆帳才會平衡。

71 Excel的通用公式 PV函數其實就是當rate、nper、pmt、fv、type為已知時,解下列函數的PV值。

72 以PV函數計算未來值因子(PVIF及PVIFA)
了解Excel PV函數的應用之後,應該不難理解PV函數也可以用來計算單筆現值因子,及年金現值因子。欲更深入了解的讀者可參考:現值及未來值因子表。 未來值因子 Excel PV公式 PVIF(r, n) =PV(rate, nper, 0, -1) 期末 PVIFA(r, n) =PV(rate, nper, -1) 期初 PVIFA(r, n) =PV(rate, nper, -1, 0, 1)

73 不適用的年金 Excel的PV函數只適用每期金額大小都一致的年金。這種年金的現金流量每期一樣,所以可以使用公式來計算。若年金每期發生的現金流量金額都不一樣,如變額年金、每期金額以一定百分比成長(例如5%)之年金等,就無法使用Excel的PV函數了。這時便必須將每期所發生的現金流量,個別視為單筆,一一計算每一筆之現值,然後再全部加總即可。有關這部份可參考: 年金-理論篇

74 PMT

75 Excel函數-PMT PMT就是年金的意思,用途相當廣泛,諸如銀行貸款,年金保險等都會用到。但是Excel的PMT更是把年金的應用發揮的淋漓盡致。 何謂年金 如果讀者對年金尚未了解,請先參考『年金-理論篇』及『年金-應用篇』。

76 PMT與其他函數之關係 Excel提供了下列非常實用的財務函數:PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。這五個函數相互之間是息息相關的,也可以說連成一體。只要知道任何中四個,便可求出另外的那一個。那麼函數之間的關係又是如何?是如何互動的。從觀念上來看,這幾個函數架構了一個平衡系統,可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

77

78 蹺蹺板的左邊有刻度,NPER就是分成幾段的意思,RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢?左邊給PV(單筆現值)及PMT(年金的每期金額),右邊就是FV(未來值或終值)。當期數愈多、RATE愈大,代表左邊長度愈長,也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長,小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是:RATE及NPER決定左邊蹺蹺板的刻度及長度,當PV、PMT及FV坐上去時必須維持平衡。

79 公式 PV、PMT、FV、RATE、NPER之間的關係,若以數學公式表達如下:

80 現金流向 PV、PMT以及FV的現金流向都是有方向性的,現金流出以及現金流入。在圖上是以箭頭向上及向下來表示,只是箭頭方向在蹺蹺板兩邊剛好相反,如下表: 這又是何原因,蹺蹺板左邊箭頭往下代表現金流入,此時反應在蹺蹺板右邊會往上蹺,若是要蹺蹺板平衡,右邊就必須有往下的力量,也就是現金流出(左邊現金流入,右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出,箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

81

82 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同,兩者力量是相加的,反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時,那蹺蹺板是相反的,那就請讀者運動運動,倒立著看這圖了。

83

84 PV、PMT流向相反 PV、PMT方向若相反,兩者力量是相減的,反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小,方向還得看哪個大。當然當FV等於零時,代表PV及PMT剛好力量兩相抵消,剛好是平衡的。

85

86 Excel PMT函數 有了這些基本觀念,接下來就簡單了。利率RATE及期數NPER確定後,只要知道PV及FV的值,就可以求出PMT了。

87 PMT的參數 PMT函數的參數定義如下: =PMT(rate, nper, pv, fv, type)
下表列出了參數的意義,前面三項參數 rate、 nper、pv是一定要有的參數不可以省略,後面兩項fv及type是可有可無。type參數說明年金是發生於期末(預設值)或期初,若這兩項沒有輸入,PMT函數會採用預設值fv = 0; type = 0(期末)。 參數 意義 必要參數 rate 每期利率 必要 nper 期數 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 期初或期末 0:期末(預設) 1:期初

88 範例 存錢買車 Jeff預計5年後買一輛新車60萬元,若目前年利率3%,從現在起於每月需存多少錢。
以這例子每期為一個月,PV=0,NPER =12*5,rate = 3%/12,FV =600,000。 =PMT(3%/12, 12*5, 0, ) =-9,281

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

90 房屋貸款 Susan向銀行貸款100萬元,利率5%、期限20年,本息均攤請問月繳款多少元?
這例子相當於一個月為一期,總共有240期(NPER=240),每期利率=5%/12,Susan期初跟銀行拿了100萬(PV=1,000,000),那麼每月必需繳多少錢,期末餘額才會等於零(FV=0)? =PMT(5%/12,240, ) =-6,600

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

92 房屋貸款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(5%/12,36, , ) =-6,600

93 Suan的月繳款金額是6,600元。

94 退休規劃 Peter現有存款200萬,希望15年後退休可達1500萬,若Peter的投資報酬率每年有8%,每年需要另存多少錢?
存款投資200萬(PV=-2,000,000),每年為一期,總共有15期(NPER=15),每期報酬率為8%(RATE=8%),15年後拿回1500萬(FV=15,000,000),那麼每年還需投資: =PMT(8%,15, , ) =-318,784

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

96 RATE

97 Excel函數-RATE 我們生活週遭充滿了跟『利率』或『報酬率』相關的例子,舉凡房屋貸款、基金報酬率以及儲蓄險,全部都跟報酬率有關。例如房屋貸款100萬、20年期,銀行跟我說每月得繳款6,600元,那麼年利率到底是多少呢?投資基金也是另一個例子:3年前買了一檔股票基金,現在有20%的報酬率,那相當於每年幾趴的報酬率呢?Excel提供了一個全能的函數,可以立即得到答案。

98 RATE與其他函數之關係 了解RATE函數之前,得先了解RATE跟其他函數之間的關係。Excel提供了五個非常實用的財務函數:PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。每個函數相互之間是息息相關的,也可以說連成一體。只要知道任何中四個,便可求出另外的那一個。那麼函數之間的關係又是如何?是如何互動的。從觀念上來看,這幾個函數架構了一個平衡系統,可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

99

100 蹺蹺板的左邊有刻度,NPER就是分成幾段的意思,RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢?左邊給PV(單筆現值)及PMT(年金的每期金額),右邊就是FV(未來值或終值)。當期數愈多、RATE愈大,代表左邊長度愈長,也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長,小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是:RATE及NPER決定左邊蹺蹺板的刻度及長度,當PV、PMT及FV坐上去時必須維持平衡。

101 公式 PV、PMT、FV、RATE、NPER之間的關係,若以數學公式表達如下:

102 現金流向 PV、PMT以及FV的現金流向都是有方向性的,現金流出以及現金流入。在圖上是以箭頭向上及向下來表示,只是箭頭方向在蹺蹺板兩邊剛好相反,如下表: 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

103 這又是何原因,蹺蹺板左邊箭頭往下代表現金流入,此時反應在蹺蹺板右邊會往上蹺,若是要蹺蹺板平衡,右邊就必須有往下的力量,也就是現金流出(左邊現金流入,右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出,箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。

104

105 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同,兩者力量是相加的,反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時,那蹺蹺板是相反的,那就請讀者運動運動,倒立著看這圖了。

106

107 PV、PMT流向相反 PV、PMT方向若相反,兩者力量是相減的,反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小,方向還得看哪個大。當然當FV等於零時,代表PV及PMT剛好力量兩相抵消,剛好是平衡的。

108

109 Excel RATE函數 有了這些基本觀念,接下來就簡單了。RATE函數就是傳回:已知的期數(NPER)的情況下,多少的『期利率』會使得PV、PMT及FV相互得到平衡。

110

111 RATE的參數 RATE函數的參數定義如下: =RATE(nper, pmt, pv, fv, type, guess)

112 參數 意義 必要參數 nper 期數 必要 pmt 年金之每期金額 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 年金發生於期初或期末 0:期末(預設) 1:期初 guess 猜測利率可能之落點

113 type參數說明年金是發生於期末(預設值)或期初,若沒有輸入,RATE函數會採用預設值 type = 0(期末)。fv是期末終值(預設值 = 0),至於guess這參數就得費點功夫說明。

114 guess真是個有趣的參數,RATE函數的任務不就是要解出報酬率的值嗎,怎會要我們自己猜測報酬率的落點呢?這不是很奇怪嗎,Excel計算功能那麼強,難道RATE函數無法直接解出來?沒錯,如果看看上面的公式,當PV、PMT、FV、NPER為已知時,看如何直接解出RATE的值?這就難了,還好雖然無法直接求解,Excel使用代入逼近法,先假設一個可能的rate(預設值為10%),然後代入上面式子看看是否吻合,如果不是就變動rate的值,然後慢慢逼近、反覆計算,直到誤差小於 % 為止。如果真正的解和預設值差距過遠,運算超過20次還是無法求得答案,RATE 函數會傳回錯誤值 #NUM!。這時使用者就必須使用較接近的 guess 值,然後再試一次。

115 所以guess參數只是RATE函數開始尋找答案的起始點而已,跟找到的答案是無關。例如下面範例『基金年化報酬率』下面三個RATE公式,其他參數都一樣,但是guess都不同,結果答案卻都一樣是4.14%。 =RATE(10, 0, , , 0, 10%) =RATE(10, 0, , , 0, 8%) =RATE(10, 0, , , 0, 4%)

116 範例 基金年化報酬率(一) Peter於10年前,以10萬元買了一個基金,現在該基金淨值15萬元,請問這樣相當於多少的年報酬率?
以這例子每年為一期,PV=-100,000,NPER =10,PMT=0,FV =150,000。 =RATE(10, 0, , , 0, 10%) = 4.14%

117

118 基金年化報酬率(二) Peter於10年前,以10萬元買了一個基金,而且每月定期定額2,000元買相同之基金,現在該基金淨值65萬元,請問這樣相當於多少的年報酬率? 以這例子每月為一期,PV=-100,000,PMT=-2,000,NPER =120,FV =650,000。 =RATE(120, -2000, , , 0, 1%)*12 = 9.4% 註:因為這例子每月為一期,所以RATE是傳回『月報酬率』,年報酬率必須再乘上12。

119

120 房屋貸款 Susan向銀行貸款100萬元,期限20年,每月本息攤還6,600元,問這貸款年利率是多少?
以這例子每月為一期,總共240期(NPER =240),期初拿到100萬(PV=1,000,000),每期繳款6,600(PMT=-6,600),期末還清(FV = 0)。 =RATE(240, -6600, , 0, 0, 1%)*12 = 5.0%

121

122 NPER

123 Excel函數-NPER 在財務的應用上,常常需要知道要多久可以達到投資目標,或是需要幾個月可以還清貸款。Excel提供了一個非常實用的函數:NPER (Number of Period),可以輕易的算出我們所需要的答案。

124 NPER與其他函數之關係 了解NPER函數之前,得先了解NPER跟其他函數之間的關係。Excel提供了五個非常實用的財務函數:PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。每個函數相互之間是息息相關的,也可以說連成一體。只要知道任何中四個,便可求出另外的那一個。那麼函數之間的關係又是如何?是如何互動的。從觀念上來看,這幾個函數架構了一個平衡系統,可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

125

126 蹺蹺板的左邊有刻度,NPER就是分成幾段的意思,RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢?左邊給PV(單筆現值)及PMT(年金的每期金額),右邊就是FV(未來值或終值)。當期數愈多、RATE愈大,代表左邊長度愈長,也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長,小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是:RATE及NPER決定左邊蹺蹺板的刻度及長度,當PV、PMT及FV坐上去時必須維持平衡。

127 公式 PV、PMT、FV、RATE、NPER之間的關係,若以數學公式表達如下:

128 現金流向 PV、PMT以及FV的現金流向都是有方向性的,現金流出以及現金流入。在圖上是以箭頭向上及向下來表示,只是箭頭方向在蹺蹺板兩邊剛好相反,如下表: 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

129 這又是何原因,蹺蹺板左邊箭頭往下代表現金流入,此時反應在蹺蹺板右邊會往上蹺,若是要蹺蹺板平衡,右邊就必須有往下的力量,也就是現金流出(左邊現金流入,右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出,箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。

130

131 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同,兩者力量是相加的,反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時,那蹺蹺板是相反的,那就請讀者運動運動,倒立著看這圖了。

132

133 PV、PMT流向相反 PV、PMT方向若相反,兩者力量是相減的,反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小,方向還得看哪個大。當然當FV等於零時,代表PV及PMT剛好力量兩相抵消,剛好是平衡的。

134

135 Excel NPER函數 有了這些基本觀念,接下來就簡單了。NPER函數就是傳回:已知的利率(RATE)的情況下,多少『期』會使得PV、PMT及FV相互得到平衡。

136

137 NPER的參數 NPER函數的參數定義如下: =NPER(rate, pmt, pv, fv, type)

138 type參數說明年金是發生於期末(預設值)或期初,若沒有輸入,NPER函數會採用預設值 type = 0(期末)。fv是期末終值(預設值 = 0)。
意義 必要參數 rate 每期利率 必要 pmt 年金之每期金額 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 期初或期末 0:期末(預設) 1:期初

139 範例 貸款規劃 Queena買了一間房子,希望跟銀行貸款300萬元、利率2.2%,每月有能力繳本息30,000元,請問要多久可以繳清貸款?
以這例子每月為一期,PV=3,000,000,RATE =2.2%/12,PMT=-30000,FV = 0。 =NPER(2.2%/12, , , 0) = 110.6 需要110個月才能繳清貸款,亦就是不到十年就還完了。

140

141 退休規劃 Pete目前30歲,擁有存款200萬元,每年底可結餘30萬元,均投資年報酬率8%的商品,希望退休時可以擁有1,500萬元,那麼幾歲可以退休? 以這例子每年為一期,PV=-2,000,000,PMT=-300,000,RATE =8%,FV =15,000,000。 =NPER(8%, , , ) =15.4 哇~~~真好,Peter大約15年後、45歲就可退休了。

142

143 提早償還貸款 Susan向銀行貸款100萬元,利率3%、期限20年,每月本息攤還5,546元。已經繳了5年,還剩本金餘額803,088元,目前Susan剛好有一筆業務獎金30萬元進帳,想提早還款。Susan希望往後每月還是繳相同的錢,多久以後可以還清貸款? 以這例子每月為一期,目前本金還有503,088(PV=803, ,000),年利率3%(RATE=3%/12),每期繳款5,546(PMT=-5,546),期末還清(FV = 0)。 =NPER(3%/12, -5546, ) =103 也就是103個月後即可還清貸款。

144

145 IRR、XIRR、MIRR

146 Excel 財務函數-IRR、XIRR、MIRR
IRR(Internal Rate of Return)稱為內部報酬率,其應用非常廣大,是學習財務管理不可或缺的工具。Excel也提供了一個相對函數IRR 來呼應。本篇也包含IRR相關的其他兩個函數XIRR以及MIRR,讓讀者可以清楚知道每個函數的應用時機。

147 現金流量(Cash Flow) 閱讀本篇之前讀者必須先了解現金流量,才會知道IRR的意義。對現金流量還不了解的讀者,怪老子網站的『現金流量』篇有詳細介紹。

148 IRR函數 一個投資案會產生一序列的現金流量,IRR簡單說:就是由這一序列的現金流量中,反推一個投資案的內部報酬率。

149 如何反推呢,所用的方法是將每筆現金流量以利率rate折現,然後令所有現金流量的淨現值(NPV)等於零。若C0、C1、C2、C3
如何反推呢,所用的方法是將每筆現金流量以利率rate折現,然後令所有現金流量的淨現值(NPV)等於零。若C0、C1、C2、C3...Cn分別代表為期初到n期的現金流量,正值代表現金流入,負值代表現金流出。 0 = C0 + C1/(1+rate)1 + C2/(1+rate)2 + C3/(1+rate) Cn/(1+rate)n 找出符合這方程式的rate,就稱為內部報酬率。問題是這方程式無法直接解出rate,必須靠電腦程式去找。這個內部報酬率又和銀行所提供的利率是一樣的意思。

150 IRR函數的參數定義如下: =IRR(Values, guess) 參數 意義 必要參數 Values 現金流量 必要 guess

151 IRR的參數有兩個,一個是Values也就是『一序列』現金流量;另一個就是猜個IRR最可能的落點。那麼Value的值又該如何輸入?有兩種方式可輸入一序列的現金流量:
儲存格的範圍:例如=IRR(B2:B4),範圍中每一儲存格代表一期

152 那麼 =IRR({-100, 7, 107}) 或 =IRR(B2:B4) 都會得到同樣答案:7%

153 使用者定義期間長短 IRR的參數並沒有絕對日期,只有『一期』的觀念。每一期可以是一年、一個月或一天,隨著使用者自行定義。如果每一格是代表一個『月』的現金流量,那麼傳回的報酬率就是『月報酬率』;如果每一格是代表一個『年』的現金流量,那麼傳回的報酬率就是『年報酬率』。 例如{-100, 7, 107}陣列有3個數值,敘述著第0期(期初)拿出100元,第1期拿回7元,第2期拿回107元。第一個數值代表0期,也是期初的意思。至於每一期是多久,使用者自己清楚,IRR並不需要知道,因為IRR傳回的是『一期的利率』。當然如果使用月報酬率,要轉換成年報酬率就得乘上12了。

154 一年為一期 例如期初拿出100元存銀行,1年後拿到利息7元,2年後拿到本利和107元,那麼現金流量是{-100, 7, 107}。很清楚的這現金流量的每期間隔是『一年』,所以=IRR({-100, 7, 107}) = 7%傳回的就是『年報酬率』。

155 一個月為一期 換個高利貸公司的例子來看,期初借出100元,1個月後拿到利息7元,2個月拿到本利和107元,整個現金流量還是{-100, 7, 107}喔,不一樣的是每期間隔是『一個月』。那麼IRR傳回的 7%就是『月報酬率』,年報酬率必須再乘上12,得到84%的年化報酬率。所以每一期是多久只有使用者知道,對IRR而言只是傳回『每期』的報酬率。

156 guess-猜測報酬率可能的落點 guess真是個有趣的參數,IRR函數的任務不就是要解出報酬率的值嗎,怎會要我們自己猜測報酬率的落點呢?這不是很奇怪嗎,Excel計算功能那麼強,難道IRR函數無法直接解出來?沒錯IRR是無法解的。以{-100, -102, -104, -106, 450}這現金流量為例,等於得求出下列方程式中rate的解: 0 = /(1+rate)1 -104/(1+rate)2 -106/(1+rate) /(1+rate)4

157 這就難了!因為有4次方。假若現金流量的期數更多,那就更複雜了,而且使用者會輸入幾期還不知道哩。還好雖然無法直接求解,Excel使用代入逼近法,先假設一個可能的rate(10%),然後代入上面式子看看是否吻合,如果不是就變動rate的值,然後慢慢逼近、反覆計算,直到誤差小於 % 為止。如果真正的解和預設值差距過遠,運算超過20次還是無法求得答案,IRR 函數會傳回錯誤值 #NUM!。這時使用者就必須使用較接近的 guess 值,然後再試一次。

158 所以guess參數只是IRR函數開始尋找答案的起始點而已,跟找到的答案是無關。下面三個IRR公式,同樣的現金流量,但是guess參數都不同,結果答案卻都一樣是3.60%。 =IRR({-100, -102, -104, -106, 450}) =IRR({-100, -102, -104, -106, 450}, 1%) =IRR({-100, -102, -104, -106, 450}, 2%)

159 guess是選項參數 guess參數可以省略不輸入,這時Excel會使用預設值10%。通常這是一年為一期報酬率都落在這附近,如果要計算月報酬率最好輸入1%,依此類推。

160 XIRR函數 若要利用IRR函數來計算報酬率,現金流量必須是以『一期』為單位,也就是輸入的現金流量必須有期數的觀念。但是常常有些應用,現金流量並非定期式的。例如一個投資案,現金流量如下表: 可以看到現金流量發生日期是不定期的,並非以一期為單位。XIRR就是專為這類型的現金流量求報酬率,其他觀念和IRR函數沒有差別。XIRR傳回來的報酬率已經是年報酬率。 日期 金額 2007/8/15 -100,000 2007/11/6 23,650 2008/3/4 25,000 2009/6/8 82,500

161 XIRR參數 XIRR(values, dates, guess) 參數 意義 必要參數 Values 現金流量的值 必要 dates
現金流量發生日期 guess 猜測XIRR可能的落點 選項

162 和IRR函數的差別是多了一個日期(dates)參數,此日期參數(dates)必須跟現金流量(Value)成對。例如上面的例子可以如下圖的方式來完成。儲存格B7的公式 =XIRR(A2:A5,B2:B5),算出來這投資案相當於每年24.56%的報酬率。

163 需要開啟分析工具箱 使用XIRR函數必須安裝「分析工具箱」,否則會傳回#NAME? 錯誤。
1) 工具/增益集 2) 將分析工具箱打勾 3) 按確定

164 MIRR函數 MIRR參數 MIRR(values, finance_rate, reinvest_rate) 參數 意義 必要參數
現金流量的值 必要 finance_rate 融資利率 reinvest_rate 再投資報酬率

165 MIRR是Modified Internal Rate of Return的縮寫,意思是改良式的IRR。IRR到底有何缺點,需要去修正呢?主要的原因是IRR並未考慮期間領回現金再投資問題!IRR的現金流量裡可分為正值及負值兩大類,正值部分屬於投資期中投資者拿回去的現金,這些期中拿回去的現金該如何運用,會影響報酬率的。負值部分屬於投資期中額外再投入的資金,這些資金的取得也有融資利率方面要考慮。 MIRR使用的方式是將期間所有的現金流入,全部以『再投資利率』計算終值FV。期間所有的現金流出,全部以『再投資利率』計算現值PV。那麼MIRR的報酬率: =(FV/PV)1/n - 1

166

167 再投資報酬率 以例子來解說會較為清楚,一個投資案的現金流量如下:{-10000, 500, 500, 10500}這現金流量一期為一年,期初拿出10,000元,第1年底拿回500元,第2年底也拿回500元,第3年底拿回10500元。將現金流量代入IRR求內部報酬率: =IRR({-10000, 500, 500, 10500}) = 5%

168 從這投資案的經營者來說,期初拿到10,000元,然後每年支付5%報酬500元,到了第3年底還本10000元,這投資案確實是每年發放5%的報酬沒錯。可是若從投資者角度來看,假若每年底拿到的500元只會放定存2%,也就是投資者期中拿回來的金額,到期末只有2%的報酬率,那麼投資者到第3年底時,實際拿到的總金額為: = 500*(1+2%) + 500*(1+2%) ( 以Excel 表示 =500*(1+2%)+500*(1+2%)^ ) = 11,530

169 期初拿出10,000元,3年後拿回11,530,這樣相當於年化報酬率: = (11530/10000)1/3-1 (Excel 表示 =(11530/10000)^(1/3)-1 ) = 4.86% 這可解讀為拿出10,000元,以複利4.86%成長,3年後會拿回11,530元。 MIRR可以不用那麼麻煩,只需輸入再投資報酬率2%,便可輕易得到實際報酬率: = MIRR(({-10000, 500, 500, 10500}), 0, 2%) = 4.86%

170

171 投資者期間內所拿回的現金(正值),再投資的報酬率,會影響整體投資的實際報酬率。同一個例子,假若另一位投資者的再投資報酬率為4%,那麼實際報酬率修正為: = MIRR(({-10000, 500, 500, 10500}), 0, 4%) = 4.95% 如果投資者的再投資報酬率為5%,實際報酬率等於: = MIRR(({-10000, 500, 500, 10500}), 0, 5%) = 5.00% 可以看到當再投資報酬率為5%時,MIRR = IRR = 5%。這也同時說明了,IRR內部報酬率是假設再投資報酬率等於內部報酬率。

172 融資利率 如果有第0期以外的現金投入(負現金流量),這些資金是在未來的期數才會使用。只要在期初準備這些資金的現值。就足以支付未來的這些金額。所以將所有的現金流出均以『融資利率』折現(PV),代表未來所有的投資額,都相當於期初投資PV的金額。 舉個例子:一個儲蓄險,頭兩年、年初繳保費5萬元。第二年底開始,往後四年均領回30,000元,若再投資利率為3%,融資利率為5%,求MIRR為多少? = MIRR({-50000, , 30000, 30000, 30000, 30000}, 5%, 3%) = 5.15% 這是利率MIRR函數直接套入公式算出來的,為了解其中道理,將這些正負現金流量分開來,以分解動作求取終值及現值,再算投資報酬率。這樣讀者就很清楚MIRR是如何計算的。

173

174 總共有兩筆現金流出(負值部份),但是只有第2期的資金流出需要折現,所以用『融資利率』5%,總現值(PV): = /(1+5%) =97,619 這意思是說:若融資利率為5%,只需要在期初準備97,619,就足以支付前兩期各5萬所需的現金支出。 總共有4筆現金流入(正值部份),以『再投資利率』為3%,求取所有現金流入的終值(FV) = *(1+3%) *(1+3%)^ *(1+3%)^3 =125,508

175 那麼期初投入97,619,期末拿回125,508,期間為5年,這樣的年化投資報酬率: = (125508/97619)^(1/5)-1 = 5.15%
可以看到和直接用MIRR函數所計算出來的報酬率一模一樣都是5.15%。 『融資利率』是微軟的說明所使用的名詞,我認為較為適當的應該是『資金報酬率』(finance_rate),也就是資金存放標的之報酬率。

176 投資理財之應用

177 Excel投資理財之應用 在投資的規劃上,我們常常需要一些計算,例如我們想知道向銀行貸款兩百萬,分20年本息定額償還,那麼每月應該支付多少錢。或者有一個基金,五年的報酬率為96%,那麼年化報酬率是多少?諸如此類問題,Excel是個非常實用的工具,可以快速算出所需要的答案。我相信很多人都會使用Excel,但是除了財務專業人士外,能把財務函數用的很熟練的似乎也不多。在本篇文章裡,我只介紹和投資相關的五個財務函數FV、PV、RATE、PMT及NPER,試著用範例的方式呈現出來,讓非專業人士都能容易上手。

178 基本概念 我們都知道金錢的價值會隨著時間成長,而且是以複利成長。例如將一百萬存放於銀行一年,一年後這一百萬的價值當然不只一百萬,而是一百萬再加上利息,至於利息有多少是由利率來決定,以及經過多少期的複利過程。

179 單筆及定期投資 投資種類依投入次數可分為單筆投資以及定期重複性投資。以銀行的『整存整付式定存』就是最好的單筆投資的例子,在期初時投入一筆金額,期中都沒有再投入,等到期末後領回一筆本利和。『零存整付式定存』便是定期重複性的投資型態,定期於每一期固定投入一筆金額,等到期末後再領回本利和。下圖是這兩種投資型態的圖示。

180 Excel的財務函數是將這兩種混合在一起,所以參數看起來較為複雜容易用錯,但只要了解以後用起來是既簡潔又方便。

181 定期投資的期初及期末 定期投資部份依照每期金額的投入點,又可分為期初及期末兩種(TYPE),如下圖所示:

182 現金流入及流出 Excel財務函數金額部分有正負號之分,正值代表金額流入,負數代表金額流出。例如你要計算整存整付定存的期末領回本利和。期初金額(PV)是由你交給銀行(現金流出所以是負值),期滿後銀行會給付本利和的金額(現金流入所以是正值)。相反的如果是以銀行的角度來看,期初金額(PV)因為存款流入所以是正值,期滿算出的未來值(FV)應該是負值,代表要給付存款人的金額。

183 如果要計算債券的現值(PV),因為債券每期會支付利息給你,屬於定期金額所以用PMT,而且是現金流入所以要用正值。到期領回金額(FV)亦屬於現金流入都要用正值。可是算出來的現值(PV)會是負值,代表要付出PV(現金流出)的金額去換得該債券未來的本息。

184 利率(RATE)及期數(NPER) 財務函數的利率(RATE)不是固定使用常用的年利率,而是視每一期的時間來決定利率。多久為一期則要看應用,每一種應用都不一樣,但是利率的單位一定和每一期的時間長短一致。如果每月為一期,那就要用月利率。每年為一期,就要用年利率,依此類推。 例如銀行的存放款都是以一個月為一期,所以代入的利率參數(RATE)就要用月利率(年利率/ 12)。至於期數就看整體投資期間有多長再除以每一期的時間就可以了。

185 如果我們想計算年利率2.26%的整存整付定存,一年後本利和的金額,因為一個月為一期,一年有12個月所以期數NPER =12。利率必須用月利率RATE = 2.26% / 12。
如果我問:銀行整存整付定存1,000,000元,年利率2.26%一年後可以拿回多少?相信很多人會答說$1,022,600,可是實際上銀行會支付$1,022,836。雖然和預期的金額不符,但是因為銀行付的比較多,大部份的人是不會追究原因的,其實是因為銀行是每月為一期(每月複利一次),所以等於分成12期來計算,每期的利率為月利率計算,等於2.26% / 12 = %, 代入公式 =FV(2.26%/12, 12, 0, ) = $1,022,836。

186 參數介紹 下表所列是本文所要介紹的函數功能及參數: 函數 功能 參數 FV 到期後未來值(終值)
=FV(rate, nper, pmt, [pv], [type]) PV 單筆的現值 =PV(rate, nper, pmt, [fv], [type]) RATE 每期的利率或報酬率 =RATE(nper, pmt, pv, [fv], [type], [guess]) PMT 每期投資金額 =PMT(rate, nper, pv, [fv], [type]) NPER 期數 =NPER(rate, pmt, pv, [fv], [type])

187 這五個函數其實是跟金錢的時間價值息息相關的五個參數。當你知道任何其中四個就可以求出剩下的那一個。例如已知每期利率RATE、期初投資金額PV、每期投入PMT以及期數NPER,就可得知期末本利和FV。
參數部份有打中括號的代表可以省略的參數,其餘部分則是必須有的參數。 下載下列範例之Excel檔案 這Excel檔案有5個工作表:分別為未來值(FV)、現值(PV)、利率(RATE)、每期投資金額(PMT)、期數(NPER)。一個函數一個工作表,方便讀者參考。

188 未來值Future Value(FV) 當利率RATE、期數NPER、期初投資PV及每期投資PMT均為已知時,所求得的未來本利和FV。
公式 =FV(rate, nper, pmt, [pv], [type]) 詳細請參考:Excel函數-FV未來值

189 整存整付定存 以“整存整付定存”存入銀行一百萬,每月複利一次計算,年利率5%,期間為半年。到期後本利和為多少?
RATE = 5% / 12 (每月為一期,月利率 = 年利率 / 12) NPER = 6(半年分6期) PMT = 0 (只有單筆所以設定0) PV = -1,000,000(存入銀行一百萬,現金流出所以為負值) =FV(5%/12, 6, 0, ) = $1,025,262 期末領回本利和$1,025,262

190 零存整付定存 每月期初均存款一萬元至銀行,年利率4.5%,一年後(12期)會領回多少錢?
RATE = 4.5% / 12 (每月為一期,月利率 = 年利率 / 12) NPER = 12(分12期) PMT = -10,000(每月定期一萬元,因為現金流出所以負值) PV = 0 (其初沒有單筆投入,所以等於零) =FV(4.5%/12, 12, , 0, 1) = $122,966。 期末領回$122,966

191 預估投資收益 現年37歲擁有存款兩百萬元可投資,每月扣除生活開銷外尚有餘錢三萬元可做投資運用,假如預計60歲退休,每年平均投資報酬率設定為6%,到退休時,我會擁有多少錢? RATE = 6%/12(每月為一期,月利率 = 年利率 / 12) NPER = 12*(60-37)(投資期數 276期) PMT = -30,000(每月投資3萬元) PV = -2,000,000(期初投200萬元) =FV(6%/12, 12*(60-37), , , 1) = $25,778,895

192 如果想知道每年平均投資報酬率改為8%,結果會變成什麼?只需要將上述公式6%改成8%: =FV(8%/12, 12
我們立即知道相差約一千萬元,這可不是個小數目!

193 現值Present Value (PV) 當利率RATE、期數NPER、期末金額FV及每期投資PMT均為已知時,所求得的現值PV。
公式 =PV(rate, nper, pmt, [fv], [type]) 詳細請參考:Excel函數-PV現值

194 退休金的現值 預期五年後可以拿到的兩百萬退休金,假使通貨膨脹率每年以2%成長,相當於現在多少的價值?
RATE = 2%(一期為一年,每年以2%成長) NPER = 5(一年一期,所以期數等於5) PAYMENT = 0(只有單筆,所以為0) FV = 2,000,000(期末拿到兩百萬退休金) =PV(2%, 5, 0, ) = -1,811,461 這代表五年後的兩百萬,如果計算通貨膨脹後,只相當於現今的1,811,461。 負值的意義是代表現金流出,現在拿出約181萬,五年後換回200萬。

195 債券的價值 債券每半年領息三萬元,三年半後到期,到期領回一百萬,若以年利率5%計算,相當於現值多少錢?
RATE:= 5% / 2(每半年為一期,每期利率為年利率除以2) NPER = 7 (三年半,每半年一期總共七期) PAYMENT = 30,000(每半年定期領息三萬元) FV = 1,000,000(到期領回一百萬) =PV(5%/2, 7, 30000, ) = -1,031,747 負值代表必須現在拿出-1,031,747,才可換得此債券未來之利息及本金。

196 利率(RATE) 當期數NPER、每期投資PMT、期初投資PV及期末金額FV均為已知時,所求得的等值利率RATE。
公式 =RATE(nper, pmt, pv, [fv], [type], [guess])

197 汽車貸款利率 買一輛新車80萬元整,已付頭期款20萬元,其餘60萬元分3年36期貸款,每期需繳納$19,360,求該貸款年利率為多少?通常用來驗證車商告訴我們的貸款利率是否確實。 NPER = 36 (每月一期分36期支付) PMT= -$19,360(每期支付19,360) PV = 600,000(貸款60萬) =RATE(36, , ) = % 所求出為月利率,年利率必須再乘以12,所以等於10.00%

198 投資年化報酬率 I 有一股票型基金期初投資10萬元經過5年後該基金淨值成長至22萬元,求該基金之年化報酬率?
NPER = 5(每年為一期,分5期計算) PMT = 0(每期沒有投資,所以為0) PV = -$100,000(期初投資10萬元,現金流出) FV = $220,000(期末淨值22萬元,現金流入) =RATE(5, 0, , ) = 17.08% 這相當於每年固定以年利率17.08%成長

199 投資年化報酬率 II 有一股票型基金每月定期定額投資1萬元經過5年後該基金淨值為80萬元,求該基金之年化報酬率?
NPER = 5*12(每月為一期,分60期計算) PMT = -10,000(每月投資10,000,現金流出) PV = 0(期初沒有單筆投資) FV = $800,000(期末淨值80萬元) =RATE(5*12, , 0, ,1)*12 = 10.89% 這相當於每年固定以年利率10.89%成長 (每月為一期,所以RATE計算結果為月利率,必須乘以12才會成為年利率)

200 每期投資金額PAYMENT (PMT) 當期初投資PV、每期利率RATE、期數NPER及期末值FV均為已知時,求得每期該投資多少金額PMT。
公式 =PMT(rate, nper, pv, [fv], [type])

201 本息定額償還貸款 向銀行房屋貸款350萬元,年利率3.5%,以本息定額分20年償還,每月該繳款多少錢?
RATE = 3.5%/12(每月為一期,月利率 = 年利率 / 12) NPER = 20*12(分20*12 = 240期償還) PV = $3,500,000(貸款金額350萬) =PMT(3.5%/12, 20*12, ) = -$20,299 每月必需繳款本息$20,299元 (因為是現金流出所以是負值)

202 退休規劃 目前擁有存款200萬元,希望15年後退休,退休時必需擁有現金1000萬元,如果以年報酬率6%計算,每月該定期定額投資多少錢?
RATE = 6%/12(每月為一期,月利率 = 年利率 / 12) NPER = 15*12(分15*12 = 180期投資) PV = -$2,000,000(期初投資200萬) FV = $10,000,000(期末金額1000萬) =PMT(6%/12, 15*12, , ) = -17,509

203 每月需要投資17,509才有機會達成目標。 如果每月投資金額過大,試著把投資期間改為20年,再看結果 =PMT(6%/12, 20

204 期數(NPER) 當期每期利率RATE、每期投資金額PMT、初投資PV及期末值FV均為已知時,求多少期可以達成目標。
公式 =NPER(rate, pmt, pv, [fv], [type])

205 退休規劃 目前擁有存款200萬元,退休時必需擁有現金1000萬元,如果以年報酬率6%計算,每月有能力投資五萬元,多久後可以退休?
RATE = 6%/12(每月為一期,月利率 = 年利率 / 12) PMT = -50,000(每月投資5萬元) PV = $-2,000,000(已有200萬) FV = $10,000,000(期末金額1000萬) =NPER(6%/12, , , ) = 103期 因為每月為一期,除以12得到約8.5年可達成目標

206 貸款相關

207 Excel財務函數-貸款相關 (PMT/IPMT/PPMT/CUMIPMT/CUMPRINC)
函數名稱 計算下列金額 PMT 每期繳款金額(本金+利息) IPMT 第n期的利息金額 PPMT 第n期的本金金額 CUMIPMT 兩期間(m~n)的利息金額總和 CUMPRINC 兩期間(m~n)的本金金額總和

208 何謂本息平均攤還 本息平均攤還的意思是,貸款者向債權者借了一筆款項,以約定的年利率,在約定的期間內每期以固定金額償還本金及利息。每期除了利息外還償還部分本金,一直到最後一期將本金還清。也就是說第一期到最後一期所繳的金額都一樣。 例如信用貸款30萬元,年利率6.5%,以每月為一期繳納利息及本金,總共分24期(2年)還清。本息平均攤還特色是每期除了繳納利息外,還得償還本金,但是每一期的本金加上利息的金額都一樣。下圖所示24期所繳納的金額,咖啡色部份是利息,青色部份是本金部分,可以看出每期兩者加總的金額都一樣(13,364),但是利息及本金的比例每期都不一樣,前期利息的份量比較多,愈到後期本金的比例就比較大。

209

210 PMT函數 每期繳款金額可以用PMT函數輕易算出,只要輸入貸款條件當參數,立刻可求出每期該繳的本息平均攤還金額。 PMT的參數如下:
PMT(rate,nper,pv, fv, type) 名稱 意義 範例參數值 rate 每期利率 6.5% / 12 nper 總期數 24 pv 貸款金額 300000 fv 年金終值 省略(0) type 金額的給付時點 省略(期末?付)

211 因為PMT函數是年金通用函數,fv和type於貸款計算時可以省略,所以暫時不提比較不會複雜。所以這例子每月繳款金額:
要注意的是利率要和期數一致,本範例為每月為一期,那麼利率就必須用『月』利率,亦就是年利率除以12,這就是為何rate參數用6.5%/12的原因。

212 製作攤還表 我最喜歡用『反推法』來做攤還表,為何說是反推法呢?就是先用PMT函數算出每期繳款金額(下圖儲存格B4),然後從第一期所繳的利息及償還本金,一路反算貸款餘額,直到最後一期的貸款餘額為零為止。這種方法驗算方式是:最後一期的貸款餘額若為零,就知道正確無誤。其實這種算法是最符合直覺,也最為讓人接受。下圖便是用反推法算出的攤還表。

213 算法是每一期都以“前期”的貸款餘額為基礎,計算當期該繳的利息。然後將每期繳款金額扣除當期利息後,就是當期所償還的本金了。那麼“前期”的貸款餘額扣除掉當期所償還本金,當然就變成當期的貸款餘額了。然後由第一期開始一路往下算,到最後一期的貸款餘額當然一定是零,否則就是哪裡有錯了。

214 根據這做法: 第一期:利息=300000*(6.5%/12) = 1,625;償還本金= =11,739;貸款餘額= =288,261 第二期:利息=288261*(6.5%/12) = 1,561;償還本金= =11,802;貸款餘額= =276, 依此類推(如下圖所示)

215 可以看出每期的貸款餘額一路下降,到最後一期的貸款餘額(儲存格B31)恰好等於零!也就是算法無誤。

216 IPMT及PPMT函數 除了反推法外,Excel提供了IPMT函數可以立即知道任何一期所繳的利息是多少,以及PPMT函數可以計算任何一期所償還的本金是多少。IPMT及PPMT函數的參數如下: IPMT(rate, per, nper, pv, fv, type) PPMT(rate, per, nper, pv, fv, type) 這兩個函數和PMT函數唯一不一樣的是多了一個第二參數per,就是要算第幾期的意思啦。例如我們要知道上圖攤還表第4期的利息部分金額是多少,可以用IPMT函數來計算: =IPMT(6.5%/12, 4, 24, ) = -1,433 跟上圖儲存格D11的數值一模一樣。

217 同樣很容易的,也可以用PPMT函數求出第4期償還本金部分是多少: =PPMT(6
同樣很容易的,也可以用PPMT函數求出第4期償還本金部分是多少: =PPMT(6.5%/12, 4, 24, ) = -11,931 跟上圖儲存格C11的數值是一樣的。

218 攤還表也可以直接利用IPMT及PPMT,來計算每期所要繳交的利息以及償還本金的金額。讀者可以下載本範例的Excel,裡面有兩個攤還表,一個是反推法做的,另一個是用IPMT及PPMT計算的,可以發現兩者數值是一模一樣。

219 CUMIPMT及CUMPRINC函數 Excel財務函數不只可以計算某一期的本金及利息外,也可計算兩期之間總共繳納的利息是多少,或者償還了多少本金。CUMIPMT可以求出兩期之間總共繳了多少利息;CUMPRINC則可以求出兩期之間總共償還多少本金。這兩個函數的參數分別如下: CUMIPMT(rate,nper,pv,start_period,end_period,type) CUMPRINC(rate,nper,pv,start_period,end_period,type) 這兩個函數所有的參數都必須填入,不可省略。rate, nper, pv, type是貸款條件,start_perido及end_perido分別代表所要計算起始期數及結束期數。

220 參數 意義 備註 rate 每期利率 貸款條件 nper 總期數 pv 貸款金額 貸款條件,pv只能輸入正值,不接受負值 start_period 計算的起始期數 end_period 計算的終止期數 type 0:期末付款 1:期初付款 貸款條件,通常為0

221 還有這兩個函數必須安裝「分析工具箱」,否則會傳回#NAME? 錯誤。
1) 工具/增益集 2) 將分析工具箱打勾 3) 按確定

222 例如範例的攤還表,如果想知道第2期至第6期總共繳交了多少利息,只要將貸款條件月利率6
例如範例的攤還表,如果想知道第2期至第6期總共繳交了多少利息,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數: =CUMIPMT(6.5%/12, 24, , 2, 6, 0) = -7,164 (負值代表現金流出) 便可得到2~6期利息之加總7,164元(如下圖粉紅色儲存格)。

223 同樣的,如果想知道第2期至第6期總共償還了多少本金,只要將貸款條件月利率6
同樣的,如果想知道第2期至第6期總共償還了多少本金,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數: =CUMPRINC(6.5%/12, 24, , 2, 6, 0) = -59,655 (負值代表現金流出) 便可得到2~6期已償還本金之加總59,655元(如下圖粉紅色儲存格)。

224 第n期之貸款餘額 如果我們想知道某一期的貸款餘額,Excel並沒有提供這樣的函數,但是可以配合CUMPRINC函數來計算得到所要的答案。例如想知道第10期末時,貸款餘額還剩多少?這時可以用貸款金額扣除掉1~10期所有償還的本金就可以了。例如本範例第10期之貸款餘額也可以由下列方式求得: = CUMPRINC(6.5%/12, 24, , 1, 10, 0) 因為CUMPRINC結果是負值所以前面用加號 =179,708

225 如何計算總利息 總繳利息計算方式如下: 總繳利息 = 每期本息繳款*總期數 - 貸款金額
例如範例中每期繳款 元,24期總共繳交 =24* = 320,733 扣除掉貸款金額30萬,全部利息等於20,733元。 我們也可以用CUMIPMT函數來計算總利息,只要將起始期數設定為1,終止期數設定為最後一期24就可以了: =CUMIPMT(6.5%/12, 24, , 1, 24, 0) = -20,733 可以看出兩者答案完全相吻合。

226 -$13,300.03 -$1,433.21 -$11,930.67 -20733 PMT IPMT PPMT CUMIPMT CUMPRINC 如何計算總利息 PMT= IPMT+


Download ppt "葉兆輝."

Similar presentations


Ads by Google